sql large inserts: explain

Showing posts with label explain. Show all posts

Monday, March 19, 2012

Last Logins

To anyone who could help:
Let me explain what I'm trying to accomplish. We now have a business
requirement that we need to track last logins on all of our SQL servers. I've
been searching, reading, googling everything that I can think of and there is
no real defined way of doing this. Some suggested using C2 auditing, but that
is too intrusive and caused a performance hit on our servers. My perfect
solution would be to have it either:
1. Go into a new table that I could query
2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
just trying to find a solution)
3. Update a flat file
Does anyone have any suggestions?You can enable success level auditing (without needing C2) so that every time
a connection is established to SQL Server it is logged to the SQL Server
Errorlog ie.
Login succeeded for user 'sa'. Connection: Non-Trusted. or Login succeeded
for user 'WARDYIT01\pward'. Connection: Trusted.
To enable auditing in Enterprise Manager Right Click on your SQL Server
instance and select Properties. Select the security tab and select the
Success or All radio buttons. Note: SQL Server will need to be restart for
this to take effect.
You could then populate a table on a scheduled basis with the contents of
the SQL Server Errorlog and query this table. Below is a quick illustration
of how this could be done.
CREATE TABLE #audit
(
ERRORLOG VARCHAR(2000),
ContinuationRow INT
)
INSERT #audit EXEC xp_readerrorlog
SELECT MAX(SUBSTRING(ERRORLOG, 1, 22)) AS LastLogin
FROM #audit
WHERE ERRORLOG LIKE '%Login succeeded for user ''sa''%'
HTH
- Peter Ward
WARDY IT Solutions
"Big Ern" wrote:
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers. I've
> been searching, reading, googling everything that I can think of and there is
> no real defined way of doing this. Some suggested using C2 auditing, but that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?|||Set up a server side tracing for "Audit".. You can use SQL profiler to
generate the scripts..
Jayesh
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:3C4CCD66-CB0B-46DD-9CFE-CE2C73885E54@.microsoft.com...
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers.
> I've
> been searching, reading, googling everything that I can think of and there
> is
> no real defined way of doing this. Some suggested using C2 auditing, but
> that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?

Last Logins

To anyone who could help:
Let me explain what I'm trying to accomplish. We now have a business
requirement that we need to track last logins on all of our SQL servers. I'v
e
been searching, reading, googling everything that I can think of and there i
s
no real defined way of doing this. Some suggested using C2 auditing, but tha
t
is too intrusive and caused a performance hit on our servers. My perfect
solution would be to have it either:
1. Go into a new table that I could query
2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
just trying to find a solution)
3. Update a flat file
Does anyone have any suggestions?You can enable success level auditing (without needing C2) so that every tim
e
a connection is established to SQL Server it is logged to the SQL Server
Errorlog ie.
Login succeeded for user 'sa'. Connection: Non-Trusted. or Login succeeded
for user 'WARDYIT01\pward'. Connection: Trusted.
To enable auditing in Enterprise Manager Right Click on your SQL Server
instance and select Properties. Select the security tab and select the
Success or All radio buttons. Note: SQL Server will need to be restart for
this to take effect.
You could then populate a table on a scheduled basis with the contents of
the SQL Server Errorlog and query this table. Below is a quick illustration
of how this could be done.
CREATE TABLE #audit
(
ERRORLOG VARCHAR(2000),
ContinuationRow INT
)
INSERT #audit EXEC xp_readerrorlog
SELECT MAX(SUBSTRING(ERRORLOG, 1, 22)) AS LastLogin
FROM #audit
WHERE ERRORLOG LIKE '%Login succeeded for user ''sa''%'
HTH
- Peter Ward
WARDY IT Solutions
"Big Ern" wrote:

> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers. I
've
> been searching, reading, googling everything that I can think of and there
is
> no real defined way of doing this. Some suggested using C2 auditing, but t
hat
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?|||Set up a server side tracing for "Audit".. You can use SQL profiler to
generate the scripts..
Jayesh
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message
news:3C4CCD66-CB0B-46DD-9CFE-CE2C73885E54@.microsoft.com...
> To anyone who could help:
> Let me explain what I'm trying to accomplish. We now have a business
> requirement that we need to track last logins on all of our SQL servers.
> I've
> been searching, reading, googling everything that I can think of and there
> is
> no real defined way of doing this. Some suggested using C2 auditing, but
> that
> is too intrusive and caused a performance hit on our servers. My perfect
> solution would be to have it either:
> 1. Go into a new table that I could query
> 2. Update the xdate2 in sysxlogins (even though this not recommended, I'm
> just trying to find a solution)
> 3. Update a flat file
> Does anyone have any suggestions?

Wednesday, March 7, 2012

Large Tran log backups

Can someone explain me this? I am doing some research and testing and
cannot understand the behaviour I am noticing.
I have a database data file = 1.6 gig and I have the tran log for
testing purposes set to 101.06MB set to auto growth by 10% unrestricted
file size. I also have an alert set up to take the transaction log back
up when it is over 70% full.
This morning I noticed that tran log back up job was fired twice
yesterday by the alert and the size of the tran log back ups were 3.42G
and 3.48G. When I look at the size of the tran log file it is 101.06MB.
I do not have the Auto_shrink option turned on.
Can someone explain how is it possible to have such a large tran log
backup file when the file itself is only 100MB?
Thanksshub wrote:
> Can someone explain me this? I am doing some research and testing and
> cannot understand the behaviour I am noticing.
> I have a database data file = 1.6 gig and I have the tran log for
> testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> file size. I also have an alert set up to take the transaction log back
> up when it is over 70% full.
> This morning I noticed that tran log back up job was fired twice
> yesterday by the alert and the size of the tran log back ups were 3.42G
> and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> I do not have the Auto_shrink option turned on.
> Can someone explain how is it possible to have such a large tran log
> backup file when the file itself is only 100MB?
> Thanks
>
Are you writing each backup to a new file, or appending to the same file
each time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am writing them to a new file. But I am still not understanding how
the backup file of the tran log is larger than the actual size of the
tan log file.
Thanks
Tracy McKibben wrote:
> shub wrote:
> > Can someone explain me this? I am doing some research and testing and
> > cannot understand the behaviour I am noticing.
> >
> > I have a database data file = 1.6 gig and I have the tran log for
> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> > file size. I also have an alert set up to take the transaction log back
> > up when it is over 70% full.
> >
> > This morning I noticed that tran log back up job was fired twice
> > yesterday by the alert and the size of the tran log back ups were 3.42G
> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> > I do not have the Auto_shrink option turned on.
> >
> > Can someone explain how is it possible to have such a large tran log
> > backup file when the file itself is only 100MB?
> >
> > Thanks
> >
> Are you writing each backup to a new file, or appending to the same file
> each time?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What do you get back when you run these:
RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
Roy Harvey
Beacon Falls, CT
On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
>I am writing them to a new file. But I am still not understanding how
>the backup file of the tran log is larger than the actual size of the
>tan log file.
>Thanks
>Tracy McKibben wrote:
>> shub wrote:
>> > Can someone explain me this? I am doing some research and testing and
>> > cannot understand the behaviour I am noticing.
>> >
>> > I have a database data file = 1.6 gig and I have the tran log for
>> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
>> > file size. I also have an alert set up to take the transaction log back
>> > up when it is over 70% full.
>> >
>> > This morning I noticed that tran log back up job was fired twice
>> > yesterday by the alert and the size of the tran log back ups were 3.42G
>> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
>> > I do not have the Auto_shrink option turned on.
>> >
>> > Can someone explain how is it possible to have such a large tran log
>> > backup file when the file itself is only 100MB?
>> >
>> > Thanks
>> >
>> Are you writing each backup to a new file, or appending to the same file
>> each time?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||When I ran the first command here is what I got ....
Data file size = 1751777280
Tran log size = 5437521920
Please help me understand how could the tran log get that big when I
have an alert setup and also how is it that it is only 100MB now. I do
not have the auto shrink option turned on.
On the second command, here is what I got, providing you the info that
you would care
BackupSize FirstLsn LastLsn
CheckpointLsn
3754968576 546000002908400001 661000001920700001
648000000145600034
And Backuptype and Devicetype were both = 2.
Thanks
Roy Harvey wrote:
> What do you get back when you run these:
> RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
> RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
> Roy Harvey
> Beacon Falls, CT
> On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
> >I am writing them to a new file. But I am still not understanding how
> >the backup file of the tran log is larger than the actual size of the
> >tan log file.
> >Thanks
> >Tracy McKibben wrote:
> >> shub wrote:
> >> > Can someone explain me this? I am doing some research and testing and
> >> > cannot understand the behaviour I am noticing.
> >> >
> >> > I have a database data file = 1.6 gig and I have the tran log for
> >> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> >> > file size. I also have an alert set up to take the transaction log back
> >> > up when it is over 70% full.
> >> >
> >> > This morning I noticed that tran log back up job was fired twice
> >> > yesterday by the alert and the size of the tran log back ups were 3.42G
> >> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> >> > I do not have the Auto_shrink option turned on.
> >> >
> >> > Can someone explain how is it possible to have such a large tran log
> >> > backup file when the file itself is only 100MB?
> >> >
> >> > Thanks
> >> >
> >>
> >> Are you writing each backup to a new file, or appending to the same file
> >> each time?
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I really can't explain it any more than you can. I can only assume
that Something Is Not As It Appears. The question is, what?
Whatever it is, expect more questions (as you have had from Tracy and
me) before you get any answers.
I am not suggesting any of the following, but they all have to be
tested and dismissed. I am sure I am missing some others.
- The log is really not 110MB, but multiple GB.
- The file is from backing up some other database.
- The file is from backup up this database at some time in the past.
I hope some else has more specific advice that this!
Roy
On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>When I ran the first command here is what I got ....
>Data file size = 1751777280
>Tran log size = 5437521920
>Please help me understand how could the tran log get that big when I
>have an alert setup and also how is it that it is only 100MB now. I do
>not have the auto shrink option turned on.
>On the second command, here is what I got, providing you the info that
>you would care
>BackupSize FirstLsn LastLsn
> CheckpointLsn
>3754968576 546000002908400001 661000001920700001
>648000000145600034
>And Backuptype and Devicetype were both = 2.
>Thanks
>|||Here is a question for you, Is there a way to find out if the
transaction log file size was shrunk manually after the transaction
log backups?
Roy Harvey wrote:
> I really can't explain it any more than you can. I can only assume
> that Something Is Not As It Appears. The question is, what?
> Whatever it is, expect more questions (as you have had from Tracy and
> me) before you get any answers.
> I am not suggesting any of the following, but they all have to be
> tested and dismissed. I am sure I am missing some others.
> - The log is really not 110MB, but multiple GB.
> - The file is from backing up some other database.
> - The file is from backup up this database at some time in the past.
> I hope some else has more specific advice that this!
> Roy
> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
> >When I ran the first command here is what I got ....
> >Data file size = 1751777280
> >Tran log size = 5437521920
> >
> >Please help me understand how could the tran log get that big when I
> >have an alert setup and also how is it that it is only 100MB now. I do
> >not have the auto shrink option turned on.
> >
> >On the second command, here is what I got, providing you the info that
> >you would care
> >BackupSize FirstLsn LastLsn
> > CheckpointLsn
> >3754968576 546000002908400001 661000001920700001
> >648000000145600034
> >
> >And Backuptype and Devicetype were both = 2.
> >
> >Thanks
> >|||Not that I know of, but there is a LOT I don't know. I do know that
shrinking the transaction log doesn't usually take effect immediately,
as it only happens after the logging reaches the end and has to go
back to the beginning.
Roy
On 4 Aug 2006 13:26:46 -0700, "shub" <shubtech@.gmail.com> wrote:
>Here is a question for you, Is there a way to find out if the
>transaction log file size was shrunk manually after the transaction
>log backups?
>Roy Harvey wrote:
>> I really can't explain it any more than you can. I can only assume
>> that Something Is Not As It Appears. The question is, what?
>> Whatever it is, expect more questions (as you have had from Tracy and
>> me) before you get any answers.
>> I am not suggesting any of the following, but they all have to be
>> tested and dismissed. I am sure I am missing some others.
>> - The log is really not 110MB, but multiple GB.
>> - The file is from backing up some other database.
>> - The file is from backup up this database at some time in the past.
>> I hope some else has more specific advice that this!
>> Roy
>> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>> >When I ran the first command here is what I got ....
>> >Data file size = 1751777280
>> >Tran log size = 5437521920
>> >
>> >Please help me understand how could the tran log get that big when I
>> >have an alert setup and also how is it that it is only 100MB now. I do
>> >not have the auto shrink option turned on.
>> >
>> >On the second command, here is what I got, providing you the info that
>> >you would care
>> >BackupSize FirstLsn LastLsn
>> > CheckpointLsn
>> >3754968576 546000002908400001 661000001920700001
>> >648000000145600034
>> >
>> >And Backuptype and Devicetype were both = 2.
>> >
>> >Thanks
>> >|||Another thing that needs checking: is the log on multiple files.
Roy

Large Tran log backups

Monday, February 20, 2012

large log

the log file grow in a week from 10 mb to 2.5 gb idon't inderstand how .
someone can explain me this.
ManuHave a look at
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...b;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/defaul...kb;EN-US;317375
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Fulop Emmanuel" <manuf@.netvision.net.il> wrote in message
news:btur51$man$1@.news2.netvision.net.il...

quote:

> the log file grow in a week from 10 mb to 2.5 gb idon't inderstand how .
> someone can explain me this.
> Manu
>

large log

the log file grow in a week from 10 mb to 2.5 gb idon't inderstand how .
someone can explain me this.
ManuHave a look at
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110139
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317375
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Fulop Emmanuel" <manuf@.netvision.net.il> wrote in message
news:btur51$man$1@.news2.netvision.net.il...
> the log file grow in a week from 10 mb to 2.5 gb idon't inderstand how .
> someone can explain me this.
> Manu
>

Monday, March 19, 2012

Last Logins

Last Logins

Wednesday, March 7, 2012

Large Tran log backups

Large Tran log backups

Monday, February 20, 2012

large log

large log

sql large inserts

Blog Archive

About Me