Log file too big

I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?
First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>
|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris n mesajul de
tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>
|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris n mesajul de tiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>

Log file too big

I have a transaction log file too big (about 13GB). I think the one who make
it first time has declared a fixed size. Haw can I make it little ?First of all fix up the backup of the log so that it doesn't keep growing -
either has to be backed up regularly or the database should be in Simple
Recovery Mode (mainly for development/test databases).
From Enterprise manager you can then right click on the database name,
select All Tasks-Shrink Database. You can then click the Files button and
change the file to the log file and shrink it as much as possible (leave
some room for growth). The shrink may not happen immediately.
Mary
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>I have a transaction log file too big (about 13GB). I think the one who
>make it first time has declared a fixed size. Haw can I make it little ?
>|||I've tried to shrink from enterprise manager but the minimum size is the
same like log files 13GB and I can't modify it.
Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
"9 2 1693472 128 1693472 128 "
I backup up full this database every night.
What should I do ?
"Mary Bray" <no@.spam.com> a scris în mesajul de
ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
> First of all fix up the backup of the log so that it doesn't keep
> growing - either has to be backed up regularly or the database should be
> in Simple Recovery Mode (mainly for development/test databases).
> From Enterprise manager you can then right click on the database name,
> select All Tasks-Shrink Database. You can then click the Files button and
> change the file to the log file and shrink it as much as possible (leave
> some room for growth). The shrink may not happen immediately.
> Mary
> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who
>>make it first time has declared a fixed size. Haw can I make it little ?
>|||The log file is not emptied when you perform a database backup, only for a transaction log backup.
If you don't perform transaction log backups, set the database in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message news:eZdwGItqEHA.2696@.TK2MSFTNGP15.phx.gbl...
> I've tried to shrink from enterprise manager but the minimum size is the same like log files 13GB
> and I can't modify it.
> Whith query analizer DBCC SHRINKFILE (LOGFILE, 10) i get
> "9 2 1693472 128 1693472 128 "
> I backup up full this database every night.
> What should I do ?
> "Mary Bray" <no@.spam.com> a scris în mesajul de ºtiri:epc6%23ZrqEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> First of all fix up the backup of the log so that it doesn't keep growing - either has to be
>> backed up regularly or the database should be in Simple Recovery Mode (mainly for
>> development/test databases).
>> From Enterprise manager you can then right click on the database name, select All Tasks-Shrink
>> Database. You can then click the Files button and change the file to the log file and shrink it
>> as much as possible (leave some room for growth). The shrink may not happen immediately.
>> Mary
>> "Sorin Sandu" <ssandu@.yahoo.com.del> wrote in message
>> news:%23njEtDrqEHA.2900@.TK2MSFTNGP12.phx.gbl...
>>I have a transaction log file too big (about 13GB). I think the one who make it first time has
>>declared a fixed size. Haw can I make it little ?
>>
>

Log file growth

Is there any way to find out the last time a log file grew? Perhaps a log
that SQL Server keeps?
TIA
CarySQL Server doesn't keep that info by default. You can trace that event using
Profiler...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:39AC150F-3145-4EE5-B5BF-633B604D82C9@.microsoft.com...
> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary|||Transaction logs will grow with every insert, update, delete, or other chang
e
to the database.
There are tools that allow you to kinda read them, but if you're just
looking for a general idea you can look at the date/time stamp on the actual
.LDF file (which is the actual transaction log) and that should provide you
with an idea of when it last changed.
"Cary" wrote:

> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary|||I believe that Cary want to track the growth of the *file* size, not used sp
ace inside the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...[vbcol=seagreen]
> Transaction logs will grow with every insert, update, delete, or other cha
nge
> to the database.
> There are tools that allow you to kinda read them, but if you're just
> looking for a general idea you can look at the date/time stamp on the actu
al
> .LDF file (which is the actual transaction log) and that should provide yo
u
> with an idea of when it last changed.
> "Cary" wrote:
>|||Yes Tibor, that's exactly what I wanted to track. Another question on the
same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size. Th
e
db is backed up every night and the log is truncated with that. I can see
that my log file fills up very fast during the day and is set to autogrow
which it does. Does this detrimentally impact the perfomance of the server?
That is why I wanted to know when the server actually "GROWS" the file. I
believe it is and I am getting a very high I/O rate on my log and DB drives
which is severely affecting performance. I am unsure of whether this high
I/O is the file growth or something more that is currently eluding me.
TIA
Cary
"Tibor Karaszi" wrote:

> I believe that Cary want to track the growth of the *file* size, not used
space inside the file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...
>
>|||Growing definitely impacts performance and usually in a big way when it
happens. The log file does not automatically get truncated when you do a
full backup. You need a log backup for that to happen. If you are not doing
regular log backups then you should set the recovery mode to simple and you
won't have to worry about any of that. Do not shrink the file once it has
grown otherwise you will just have to grow it again.
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0E2C4E19-A16D-4601-A8C2-6544E191CFEF@.microsoft.com...[vbcol=seagreen]
> Yes Tibor, that's exactly what I wanted to track. Another question on the
> same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size.
> The
> db is backed up every night and the log is truncated with that. I can see
> that my log file fills up very fast during the day and is set to autogrow
> which it does. Does this detrimentally impact the perfomance of the
> server?
> That is why I wanted to know when the server actually "GROWS" the file. I
> believe it is and I am getting a very high I/O rate on my log and DB
> drives
> which is severely affecting performance. I am unsure of whether this high
> I/O is the file growth or something more that is currently eluding me.
> TIA
> Cary
> "Tibor Karaszi" wrote:
>

Log file growth

Is there any way to find out the last time a log file grew? Perhaps a log
that SQL Server keeps?
TIA
CarySQL Server doesn't keep that info by default. You can trace that event using Profiler...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:39AC150F-3145-4EE5-B5BF-633B604D82C9@.microsoft.com...
> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary|||Transaction logs will grow with every insert, update, delete, or other change
to the database.
There are tools that allow you to kinda read them, but if you're just
looking for a general idea you can look at the date/time stamp on the actual
.LDF file (which is the actual transaction log) and that should provide you
with an idea of when it last changed.
"Cary" wrote:
> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary|||I believe that Cary want to track the growth of the *file* size, not used space inside the file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...
> Transaction logs will grow with every insert, update, delete, or other change
> to the database.
> There are tools that allow you to kinda read them, but if you're just
> looking for a general idea you can look at the date/time stamp on the actual
> .LDF file (which is the actual transaction log) and that should provide you
> with an idea of when it last changed.
> "Cary" wrote:
>> Is there any way to find out the last time a log file grew? Perhaps a log
>> that SQL Server keeps?
>> TIA
>> Cary|||Yes Tibor, that's exactly what I wanted to track. Another question on the
same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size. The
db is backed up every night and the log is truncated with that. I can see
that my log file fills up very fast during the day and is set to autogrow
which it does. Does this detrimentally impact the perfomance of the server?
That is why I wanted to know when the server actually "GROWS" the file. I
believe it is and I am getting a very high I/O rate on my log and DB drives
which is severely affecting performance. I am unsure of whether this high
I/O is the file growth or something more that is currently eluding me.
TIA
Cary
"Tibor Karaszi" wrote:
> I believe that Cary want to track the growth of the *file* size, not used space inside the file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...
> > Transaction logs will grow with every insert, update, delete, or other change
> > to the database.
> >
> > There are tools that allow you to kinda read them, but if you're just
> > looking for a general idea you can look at the date/time stamp on the actual
> > .LDF file (which is the actual transaction log) and that should provide you
> > with an idea of when it last changed.
> >
> > "Cary" wrote:
> >
> >> Is there any way to find out the last time a log file grew? Perhaps a log
> >> that SQL Server keeps?
> >>
> >> TIA
> >> Cary
>
>|||Growing definitely impacts performance and usually in a big way when it
happens. The log file does not automatically get truncated when you do a
full backup. You need a log backup for that to happen. If you are not doing
regular log backups then you should set the recovery mode to simple and you
won't have to worry about any of that. Do not shrink the file once it has
grown otherwise you will just have to grow it again.
--
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0E2C4E19-A16D-4601-A8C2-6544E191CFEF@.microsoft.com...
> Yes Tibor, that's exactly what I wanted to track. Another question on the
> same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size.
> The
> db is backed up every night and the log is truncated with that. I can see
> that my log file fills up very fast during the day and is set to autogrow
> which it does. Does this detrimentally impact the perfomance of the
> server?
> That is why I wanted to know when the server actually "GROWS" the file. I
> believe it is and I am getting a very high I/O rate on my log and DB
> drives
> which is severely affecting performance. I am unsure of whether this high
> I/O is the file growth or something more that is currently eluding me.
> TIA
> Cary
> "Tibor Karaszi" wrote:
>> I believe that Cary want to track the growth of the *file* size, not used
>> space inside the file.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "matt" <matt@.discussions.microsoft.com> wrote in message
>> news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...
>> > Transaction logs will grow with every insert, update, delete, or other
>> > change
>> > to the database.
>> >
>> > There are tools that allow you to kinda read them, but if you're just
>> > looking for a general idea you can look at the date/time stamp on the
>> > actual
>> > .LDF file (which is the actual transaction log) and that should provide
>> > you
>> > with an idea of when it last changed.
>> >
>> > "Cary" wrote:
>> >
>> >> Is there any way to find out the last time a log file grew? Perhaps a
>> >> log
>> >> that SQL Server keeps?
>> >>
>> >> TIA
>> >> Cary
>>

Log file growth

Is there any way to find out the last time a log file grew? Perhaps a log
that SQL Server keeps?
TIA
Cary
SQL Server doesn't keep that info by default. You can trace that event using Profiler...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:39AC150F-3145-4EE5-B5BF-633B604D82C9@.microsoft.com...
> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary
|||Transaction logs will grow with every insert, update, delete, or other change
to the database.
There are tools that allow you to kinda read them, but if you're just
looking for a general idea you can look at the date/time stamp on the actual
..LDF file (which is the actual transaction log) and that should provide you
with an idea of when it last changed.
"Cary" wrote:

> Is there any way to find out the last time a log file grew? Perhaps a log
> that SQL Server keeps?
> TIA
> Cary
|||I believe that Cary want to track the growth of the *file* size, not used space inside the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"matt" <matt@.discussions.microsoft.com> wrote in message
news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...[vbcol=seagreen]
> Transaction logs will grow with every insert, update, delete, or other change
> to the database.
> There are tools that allow you to kinda read them, but if you're just
> looking for a general idea you can look at the date/time stamp on the actual
> .LDF file (which is the actual transaction log) and that should provide you
> with an idea of when it last changed.
> "Cary" wrote:
|||Yes Tibor, that's exactly what I wanted to track. Another question on the
same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size. The
db is backed up every night and the log is truncated with that. I can see
that my log file fills up very fast during the day and is set to autogrow
which it does. Does this detrimentally impact the perfomance of the server?
That is why I wanted to know when the server actually "GROWS" the file. I
believe it is and I am getting a very high I/O rate on my log and DB drives
which is severely affecting performance. I am unsure of whether this high
I/O is the file growth or something more that is currently eluding me.
TIA
Cary
"Tibor Karaszi" wrote:

> I believe that Cary want to track the growth of the *file* size, not used space inside the file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "matt" <matt@.discussions.microsoft.com> wrote in message
> news:C8424618-ED00-4A2A-A762-9BFD568BE5B4@.microsoft.com...
>
>
|||Growing definitely impacts performance and usually in a big way when it
happens. The log file does not automatically get truncated when you do a
full backup. You need a log backup for that to happen. If you are not doing
regular log backups then you should set the recovery mode to simple and you
won't have to worry about any of that. Do not shrink the file once it has
grown otherwise you will just have to grow it again.
Andrew J. Kelly SQL MVP
"Cary" <Cary@.discussions.microsoft.com> wrote in message
news:0E2C4E19-A16D-4601-A8C2-6544E191CFEF@.microsoft.com...[vbcol=seagreen]
> Yes Tibor, that's exactly what I wanted to track. Another question on the
> same note. I have a db ~5.5 GB in size with a log file ~ 350Mb in size.
> The
> db is backed up every night and the log is truncated with that. I can see
> that my log file fills up very fast during the day and is set to autogrow
> which it does. Does this detrimentally impact the perfomance of the
> server?
> That is why I wanted to know when the server actually "GROWS" the file. I
> believe it is and I am getting a very high I/O rate on my log and DB
> drives
> which is severely affecting performance. I am unsure of whether this high
> I/O is the file growth or something more that is currently eluding me.
> TIA
> Cary
> "Tibor Karaszi" wrote:

log file filling up

Let me start by saying I haven't touched Sybase or SQL Server in a LONG time, but I've been doing Oracle off and on for a while.

Ok, so, here we go. I have an SQL database where the data is 2Gb and the log is 18Gb. The system keeps complaining about the log file being full even though I've set the mode to SIMPLE and trunc on chkpt. I'm out of disk space so I can't, and don't want to grow the log file any further. How can I tell exactly how full that log file is? We don't plan on recovering the database, so as far as I'm concerned, I can turn logging completely off.

Any ideas or suggestions?

You cant make Logging off completely . yes ofcourse you can make it minimally logged when u make it simple recovery model. Once you made it simple ,since your log is of 18 GB , now you have to truncate & shrink it to get the size reduced.

The growth of Transaction Log(TL) is depends on couple of things... (a) the Recovery Model (b) TL Backup freqency.

(a) first check whether you need Full recovery model , if not change it to Simple

(b) WHat is the Backup policy. if its in FUll recovery model and if you are taking the TL backup then increase the frequency of the TL backup

(c) If you want to shrink the file then you will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.

Refer :

http://support.microsoft.com/kb/873235.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1542414&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630021&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1623857&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1447193&SiteID=1

madhu|||a) It's set to SIMPLE - now.
b) We don't backup. We really don't have a need to.
c) the truncating seems to work, BUT in the Console if I select Tasks, Shrink, Files I see no change in the available free space. Should I?

Keep in mind that I have truncate on checkpoint set to true, and if I do a checkpoint, I get an error back saying the transaction log is full - even AFTER I've run backup with no_log.
|||

(a) Truncate the log first

BACKUP LOG yourdatbasename WITH TRUNCATE_ONLY

(b) Shrink the file after truncation

DBCC SHRINKFILE(LogicalLogfilename, Size)

read about this in BOL

Madhu

|||Many thanks for the replies.

I've done the backup several times. I just tried the dbcc command and recieved the following:

Cannot shrink file '2' in database because of minimum log space required.

Current size 2277232
USed pages 2277232

I tried using a Mb size and it choked. I forget the relation between pages and Mb. It's been too long........
|||1page = 8 kb i believe..........just use as specified earlier .........

|||That did it! I was trying to give it a size and it didn't like it. I just dropped off the size parameter and she shrank. Thanks, guys!

log file

How can I delete the transaction logs? Every time I try it errors saying the
file isn't empty and I don't know how to empty it! I've tried everything and
the log file is over 200 mb so far!
Thanks,
ToddHave you tried BACKUP LOG your_db_name WITH TRUNCATE_ONLY?
How did you try to 'delete' the xaction log?
The other thing you might think about is put the DB
recovery option to SIMPLE (equiv to trunc. log on chkpt).
That way, you won't have to manual delete the xaction log.
hth
-a
>--Original Message--
>How can I delete the transaction logs? Every time I try
it errors saying the
>file isn't empty and I don't know how to empty it! I've
tried everything and
>the log file is over 200 mb so far!
>Thanks,
>Todd
>
>.
>|||If you have multiple log files for this database and you want to 'delete'
(i.e. remove one of them), you can try DBCC SHRINKFILE with the EMPTYFILE
option. This do NOT work with the primary transaction log file, though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Todd Ellington" <todd@.vtserve.com> wrote in message
news:u7zuupjiDHA.2824@.tk2msftngp13.phx.gbl...
> How can I delete the transaction logs? Every time I try it errors saying
the
> file isn't empty and I don't know how to empty it! I've tried everything
and
> the log file is over 200 mb so far!
> Thanks,
> Todd
>

Log Backups

Can log backups only be done based on time/scheduling?
What I mean by that, is can log backups be driven based on
the size of the log or based on "activity" (as opposed to
every two hours or every 15 minutes)?You can set an alert to key off of performance counters and then kick off a
job. For example, I have an alert that watches % log used and fires off a
job that backs up the t-log when the counter is over 75%. You really should
use the scheduled ones too though to maintain your business requirements
regarding to data loss.
Christian Smith
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:e15001c3efee$dbdb8ec0$a601280a@.phx.gbl...
> Can log backups only be done based on time/scheduling?
> What I mean by that, is can log backups be driven based on
> the size of the log or based on "activity" (as opposed to
> every two hours or every 15 minutes)?

Log Backups

Can log backups only be done based on time/scheduling?
What I mean by that, is can log backups be driven based on
the size of the log or based on "activity" (as opposed to
every two hours or every 15 minutes)?You can set an alert to key off of performance counters and then kick off a
job. For example, I have an alert that watches % log used and fires off a
job that backs up the t-log when the counter is over 75%. You really should
use the scheduled ones too though to maintain your business requirements
regarding to data loss.
Christian Smith
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:e15001c3efee$dbdb8ec0$a601280a@.phx.gbl...
> Can log backups only be done based on time/scheduling?
> What I mean by that, is can log backups be driven based on
> the size of the log or based on "activity" (as opposed to
> every two hours or every 15 minutes)?

Locks per File

For some time, I have been trying to determine why we are getting
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIAMaxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:

>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:

Locks per File

For some time, I have been trying to determine why we are getting
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIA
Maxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:

>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA
|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>
|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:

Locks Option - Need advanced help

I am having a problem with lock resources on my server. I am getting
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks
on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Locks on a table

For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.
David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com