Locks and Blocks: mode

Showing posts with label mode. Show all posts

Wednesday, March 28, 2012

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default...b;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIA
Open Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default...b;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>
sql

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>

Log files WAY too big

I know this seems to be a common issue. My dbs transaction logs are
absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
an effort to shink these bloated transaction logs, I'm trying to follow this
article: http://support.microsoft.com/defaul...kb;EN-US;272318
...but what does step 1 really mean? "Run this code"? How? Where? What
do you mean 'run this code'? As a stored procedure? As a query? Any
advice would be appreciated.
TIAOpen Query Analyzer. Switch the database context to the offending database
with a USE statement or using the dropdown up top. Paste the code. Hit
F5.
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:#fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>|||Hi
1.. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2.. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size
Run BACKUP LOG ....(see a syntax in the BOL) in QA within your database.
Run DBCC SHRINKFILE in QA within your database as well.
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23fdZHNRiEHA.3664@.TK2MSFTNGP11.phx.gbl...
> I know this seems to be a common issue. My dbs transaction logs are
> absurdly too big. Therefore, I switched the back mode to SIMPLE. Now, in
> an effort to shink these bloated transaction logs, I'm trying to follow
this
> article: http://support.microsoft.com/defaul...kb;EN-US;272318
> ...but what does step 1 really mean? "Run this code"? How? Where? What
> do you mean 'run this code'? As a stored procedure? As a query? Any
> advice would be appreciated.
> TIA
>

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
Why shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe
|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:

> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>
|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe
|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'************************************************* ******************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '************************************************* ******************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe
|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/Tr...leGrows_1.html
|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas

"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size
.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run
a
DBCC shrinkfile how small can you get it? This may well be the original siz
e.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:

> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do
a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same si
ze.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files bac
k
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'***************************************
****************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '***************************************
****************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about th
e
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/T...ileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Log Files Growing out of control

Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
JoeWhy shrink the logs if they're only going to have to grow again? Meanwhile,
your performance will suffer, since transactions will have to wait while the
log does an auto-grow.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe|||My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||Hi jaylou,
Almost all our databases run in Full mode. Why do you not want to run in
this mode? When you create the Transaction Log, think carefully since it
won't shrink past the size it was originally created at. If you schedule a
TRUNCATE_ONLY then you leave your database in an unrecoverable state
although, I guess if it's over night it's not likely to cause you a problem.
What I suggest is that you put your database into full mode and backup the
log throughout the day which should stop it from growing so uncontrollably
large. Do you know what size you originally set it at? If not you can run a
DBCC shrinkfile how small can you get it? This may well be the original size.
Another option is there maybe something in the application that is not
committing it's jobs properly?
Andrew
"jaylou" wrote:
> Hi All,
> In one of my servers my log files grow uncontrollably. I have set all the
> individual databases to simple mode but the logs still grow. I need to do a
> backup log with truncate only then shrink the files. This is on a daily
> basis at this point. If I set the databases back to full mode and run a
> backup of the trans log in a maintenance plan the files remain the same size.
> Should I schedule a backup log with truncate only and a shrink file on a
> nightly basis? Is there a maintenance plan that would shrink my files back
> to normal?
> TIA,
> Joe
>|||It really depends on your app. If you have a large transaction, then if you
don't break it down into smaller ones, you're looking at having a large log
file.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
My question is...
Is there a way to stop the log files from growing like this?
Thanks,
Joe|||in line with what other NG members have said, I support the breaking down of
the transaction . . however before you can do that you need to know which
process is causing your transaction log to fill up. and grow uncontrolably.
the first thing I would do is is setup a sqlalert to capture percentage log
used . . .based on 75% of( a very large transaction) you can setup a
response (this can be any thing, sqlagent job,email etc.) I would go for a
sqlagent job and the job will run something similar to the following(you do
not have t use this exact sql but just to point you in the right direction)
this will help identify what process is causing your log to grow. on the
other hand you could setup a server side profiler trace using a sql agent
job and sp_trace_setevent etc . . .but this may prove to be laborious
depending on how busy your database is will depend on the volume of data you
have to trawl through to identify the sql causing the problem.
-- Olu Adedeji
-- 12/04/03
-- dump open transaction info
set nocount on
declare @.inputbuffer nvarchar(1000),
@.spid varchar(5),
@.dbname nvarchar(128) -- your database name
set @.dbname = 'Pubs'
-- identify oldest open transactions in the dbname
dbcc opentran(@.dbname)
-- Please note that accessing system tables directly is not supported
-- every effort should be made to refrain from doing this
-- only display inputbuffer for spids with open transaction
if (select count(spid) from master..sysprocesses(nolock)) > 1
begin
declare inputbuffer_cur cursor read_only for
select cast(spid as varchar) from master..sysprocesses(nolock) where
db_name(dbid) = @.dbname and open_tran !=0
open inputbuffer_cur
fetch next from inputbuffer_cur into @.spid
while @.@.fetch_status = 0
begin
select @.inputbuffer = 'dbcc inputbuffer('+@.spid+')'
print @.inputbuffer
exec master..sp_executesql @.inputbuffer
print
'*******************************************************************'
fetch next from inputbuffer_cur into @.spid
end
close inputbuffer_cur
deallocate inputbuffer_cur
end
else
begin
select @.inputbuffer =(select top 1 ' set nocount on dbcc
inputbuffer('+cast(spid as varchar) + ') ' from master..sysprocesses(nolock)
where db_name(dbid) = @.dbname
and open_tran !=0)
print @.inputbuffer
exec master.dbo.sp_executesql @.inputbuffer
print '*******************************************************************'
end
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:12D5EEEB-6698-4100-B0FF-1BC5739F3158@.microsoft.com...
> My question is...
> Is there a way to stop the log files from growing like this?
> Thanks,
> Joe|||I would only put databases in full recovery mode if the log backups are
needed. Setting them to simple makes for easier maintenance if you are happy
with full and diff backups - a lot of systems are but people tend to leave
the databases as full because it's the default and they don't think about the
way the database is to be used.
Have a look at
http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html|||Look, the transaction log will not grow any more in SIMPLE RECOVERY than it
will in the other two recovery modes but it will grow to handle the busiest
and largest single transactions and periods of time. A problem that you may
not have considered is that while in SIMPLE mode, the transaction log is
only flushed out on CHECKPOINT operations. Perhaps, the CHECKPOINT is not
happening frequently enough for your purposes. If the transaction log is
not flushed, through backup, manual or automated purge, it will grow to
accomodate all transactions.
Sincerely,
Anthony Thomas
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:46B0BF06-CCD3-439B-96B6-3888473BDB51@.microsoft.com...
Hi All,
In one of my servers my log files grow uncontrollably. I have set all the
individual databases to simple mode but the logs still grow. I need to do a
backup log with truncate only then shrink the files. This is on a daily
basis at this point. If I set the databases back to full mode and run a
backup of the trans log in a maintenance plan the files remain the same
size.
Should I schedule a backup log with truncate only and a shrink file on a
nightly basis? Is there a maintenance plan that would shrink my files back
to normal?
TIA,
Joe

Monday, March 26, 2012

log files

I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
order to optimize for speed, I set the database recovery mode to Simple, both
on my user DB and tempdb in order to avoid huge log files.
(1) why does my tempdb log file still grow large (10 GB) ?
(2) why can't I specify "unrestricted growth" on additional log files for
tempdb? (every time I specify a location with ample HD space for an
additional tempdb log file in the DB Properties, the server sets Autogrowth
to a restricted growth and my changes don't take effect)Can you elaborate on exactly how you are doing these mass insertions?
--
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
> In
> order to optimize for speed, I set the database recovery mode to Simple,
> both
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets
> Autogrowth
> to a restricted growth and my changes don't take effect)|||> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowth
> to a restricted growth and my changes don't take effect)
What max size are the files set to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
> order to optimize for speed, I set the database recovery mode to Simple, both
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowth
> to a restricted growth and my changes don't take effect)|||The raw data is in text files. I created a SSIS package in Management Studio
using the DTS-like import/export tool that reads the file and fills a table
in tempdb. Every time I read in a new table, the SSIS package drops and
re-creates the existing table in tempdb.
"Andrew J. Kelly" wrote:
> Can you elaborate on exactly how you are doing these mass insertions?
> --
> Andrew J. Kelly SQL MVP
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
> > In
> > order to optimize for speed, I set the database recovery mode to Simple,
> > both
> > on my user DB and tempdb in order to avoid huge log files.
> >
> > (1) why does my tempdb log file still grow large (10 GB) ?
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets
> > Autogrowth
> > to a restricted growth and my changes don't take effect)
>
>|||The default for log files was 100MB, I think... But since I could not set it
to "unrestricted growth", I simply changed the max size to 20GB to make sure
I never run out of space.
"Tibor Karaszi" wrote:
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets Autogrowth
> > to a restricted growth and my changes don't take effect)
> What max size are the files set to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
> > order to optimize for speed, I set the database recovery mode to Simple, both
> > on my user DB and tempdb in order to avoid huge log files.
> >
> > (1) why does my tempdb log file still grow large (10 GB) ?
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets Autogrowth
> > to a restricted growth and my changes don't take effect)
>|||Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
database file cannot grow beyond the max specification) but the presentation might confuse a bit. So
if you set to maxsize and it show 2TB, all is fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
> The default for log files was 100MB, I think... But since I could not set it
> to "unrestricted growth", I simply changed the max size to 20GB to make sure
> I never run out of space.
> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>|||Will SQL delete the table in TEMPDB when it get restarted? Would it be
easier just to create a blank database and store data there?
James
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Reason I asked is that when you set to unlimited, SQL Server will actually
> store and show the maximum size for a log file (which is 2TB). This is, of
> course the same as unlimited (as any database file cannot grow beyond the
> max specification) but the presentation might confuse a bit. So if you set
> to maxsize and it show 2TB, all is fine.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set
>> it
>> to "unrestricted growth", I simply changed the max size to 20GB to make
>> sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files
>> > for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets
>> > Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data
>> > insertion. In
>> > order to optimize for speed, I set the database recovery mode to
>> > Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files
>> > for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets
>> > Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>|||Sorry, but I didn't follow the other threads of this discussion, so I don't know that table you are
referring to...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be easier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>> Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
>> maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
>> database file cannot grow beyond the max specification) but the presentation might confuse a bit.
>> So if you set to maxsize and it show 2TB, all is fine.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set it
>> to "unrestricted growth", I simply changed the max size to 20GB to make sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>|||Perhaps I should add: Tempdb is re-created every time SQL Server starts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be easier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>> Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
>> maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
>> database file cannot grow beyond the max specification) but the presentation might confuse a bit.
>> So if you set to maxsize and it show 2TB, all is fine.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set it
>> to "unrestricted growth", I simply changed the max size to 20GB to make sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>sql

log files

Log file won't Shrink in Simple Recovery Mode

Ok, I know there are a TON of threads out there on this but I still can't find exactly what I am looking for.
I have a 4GB DB with a 1GB log file. Backups are done nightly and written to a NAS via UNC path. When I went home on Friday the log was 200MB -- this morning it was 1GB. As I stated the recovery model is set to simple so it should just reuse itself. I issued a DBCC loginfo to see that each part of the log was a status 2. I then tried a backup log... with truncate_only and backup log...with no_log. This did not reclaim any space.
I have exhausted what I THOUGHT I knew about simple recovery. If anyone else has an idea, please post!
It has always been my understanding thatIf there are a lot of VLF's with a status of 2 that means you have
uncommitted transactions in them. What does DBCC OPENTRAN() say?
--
Andrew J. Kelly SQL MVP
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||Is there any open transaction? DBCC OPENTRAN.
Was there any replication properties set on for this database?
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||Well that will sure do it<g>.
--
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> There were 10 open trans in the DB
> "Andrew J. Kelly" wrote:
> > If there are a lot of VLF's with a status of 2 that means you have
> > uncommitted transactions in them. What does DBCC OPENTRAN() say?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> > news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> > > Ok, I know there are a TON of threads out there on this but I still
can't
> > find exactly what I am looking for.
> > >
> > > I have a 4GB DB with a 1GB log file. Backups are done nightly and
written
> > to a NAS via UNC path. When I went home on Friday the log was 200MB --
this
> > morning it was 1GB. As I stated the recovery model is set to simple so
it
> > should just reuse itself. I issued a DBCC loginfo to see that each part
of
> > the log was a status 2. I then tried a backup log... with truncate_only
and
> > backup log...with no_log. This did not reclaim any space.
> > >
> > > I have exhausted what I THOUGHT I knew about simple recovery. If
anyone
> > else has an idea, please post!
> > >
> > > It has always been my understanding that
> >
> >
> >|||Dave,
A log file works in a round robin format. It starts adding transactions
sequentially from the beginning to the end. Once it gets to the end it will
wrap around only if the first VLF is free and has no uncommitted
transactions. Otherwise it will grow the log and continue on. If you have
an open tran for 12 hours you have some serious issues with your app. A
transaction should ideally be open for seconds at the very most. You
absolutely need to find out who is keeping these trans open and why.
Whether you kill them or not is up to you but you have to either commit them
or roll them back.
--
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:DD934F0C-13B9-4E67-876E-DB027E285BAF@.microsoft.com...
> Yes and no...(I think)
> Even if there are open transactions; shouldn't sql be able to reclaim the
space that was before the LSN of those transactions? I understand that that
portion of the log can't be reclaimed, but shouldn't the older?
> The only other thing I can think of is would have I a transaction that say
starts now and runs for 12 hours, just holding a resource open and not
allowing that portion of the log to be 'checkpointed'?
> Would I just cancel the SPIDS submitting those transactions so that there
are NO transactions occuring? Also why would sql care if it is in simple
recovery mode?
> Thanks for all you help.
> "Andrew J. Kelly" wrote:
> > Well that will sure do it<g>.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
> > news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> > > There were 10 open trans in the DB
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > > > If there are a lot of VLF's with a status of 2 that means you have
> > > > uncommitted transactions in them. What does DBCC OPENTRAN() say?
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in
message
> > > > news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> > > > > Ok, I know there are a TON of threads out there on this but I
still
> > can't
> > > > find exactly what I am looking for.
> > > > >
> > > > > I have a 4GB DB with a 1GB log file. Backups are done nightly and
> > written
> > > > to a NAS via UNC path. When I went home on Friday the log was
200MB --
> > this
> > > > morning it was 1GB. As I stated the recovery model is set to simple
so
> > it
> > > > should just reuse itself. I issued a DBCC loginfo to see that each
part
> > of
> > > > the log was a status 2. I then tried a backup log... with
truncate_only
> > and
> > > > backup log...with no_log. This did not reclaim any space.
> > > > >
> > > > > I have exhausted what I THOUGHT I knew about simple recovery. If
> > anyone
> > > > else has an idea, please post!
> > > > >
> > > > > It has always been my understanding that
> > > >
> > > >
> > > >
> >
> >
> >

Log file won't Shrink in Simple Recovery Mode

Ok, I know there are a TON of threads out there on this but I still can't fi
nd exactly what I am looking for.
I have a 4GB DB with a 1GB log file. Backups are done nightly and written t
o a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it s
hould just reuse itself. I
issued a DBCC loginfo to see that each part of the log was a status 2. I th
en tried a backup log... with truncate_only and backup log...with no_log. T
his did not reclaim any space.
I have exhausted what I THOUGHT I knew about simple recovery. If anyone els
e has an idea, please post!
It has always been my understanding thatIf there are a lot of VLF's with a status of 2 that means you have
uncommitted transactions in them. What does DBCC OPENTRAN() say?
Andrew J. Kelly SQL MVP
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||Is there any open transaction? DBCC OPENTRAN.
Was there any replication properties set on for this database?
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||There were 10 open trans in the DB
"Andrew J. Kelly" wrote:

> If there are a lot of VLF's with a status of 2 that means you have
> uncommitted transactions in them. What does DBCC OPENTRAN() say?
> --
> Andrew J. Kelly SQL MVP
>
> "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- th
is
> morning it was 1GB. As I stated the recovery model is set to simple so it
> should just reuse itself. I issued a DBCC loginfo to see that each part o
f
> the log was a status 2. I then tried a backup log... with truncate_only a
nd
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>|||Well that will sure do it<g>.
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...[vbcol=seagreen]
> There were 10 open trans in the DB
> "Andrew J. Kelly" wrote:
>
can't[vbcol=seagreen]
written[vbcol=seagreen]
this[vbcol=seagreen]
it[vbcol=seagreen]
of[vbcol=seagreen]
and[vbcol=seagreen]
anyone[vbcol=seagreen]|||There were 10 open.
"Richard Ding" wrote:

> Is there any open transaction? DBCC OPENTRAN.
> Was there any replication properties set on for this database?
> "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- th
is
> morning it was 1GB. As I stated the recovery model is set to simple so it
> should just reuse itself. I issued a DBCC loginfo to see that each part o
f
> the log was a status 2. I then tried a backup log... with truncate_only a
nd
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>|||Yes and no...(I think)
Even if there are open transactions; shouldn't sql be able to reclaim the sp
ace that was before the LSN of those transactions? I understand that that p
ortion of the log can't be reclaimed, but shouldn't the older?
The only other thing I can think of is would have I a transaction that say s
tarts now and runs for 12 hours, just holding a resource open and not allowi
ng that portion of the log to be 'checkpointed'?
Would I just cancel the SPIDS submitting those transactions so that there ar
e NO transactions occuring? Also why would sql care if it is in simple reco
very mode?
Thanks for all you help.
"Andrew J. Kelly" wrote:

> Well that will sure do it<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
> news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> can't
> written
> this
> it
> of
> and
> anyone
>
>|||Dave,
A log file works in a round robin format. It starts adding transactions
sequentially from the beginning to the end. Once it gets to the end it will
wrap around only if the first VLF is free and has no uncommitted
transactions. Otherwise it will grow the log and continue on. If you have
an open tran for 12 hours you have some serious issues with your app. A
transaction should ideally be open for seconds at the very most. You
absolutely need to find out who is keeping these trans open and why.
Whether you kill them or not is up to you but you have to either commit them
or roll them back.
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:DD934F0C-13B9-4E67-876E-DB027E285BAF@.microsoft.com...
> Yes and no...(I think)
> Even if there are open transactions; shouldn't sql be able to reclaim the
space that was before the LSN of those transactions? I understand that that
portion of the log can't be reclaimed, but shouldn't the older?
> The only other thing I can think of is would have I a transaction that say
starts now and runs for 12 hours, just holding a resource open and not
allowing that portion of the log to be 'checkpointed'?
> Would I just cancel the SPIDS submitting those transactions so that there
are NO transactions occuring? Also why would sql care if it is in simple
recovery mode?[vbcol=seagreen]
> Thanks for all you help.
> "Andrew J. Kelly" wrote:
>
message[vbcol=seagreen]
still[vbcol=seagreen]
200MB --[vbcol=seagreen]
so[vbcol=seagreen]
part[vbcol=seagreen]
truncate_only[vbcol=seagreen]

Log file won't Shrink in Simple Recovery Mode

Ok, I know there are a TON of threads out there on this but I still can't find exactly what I am looking for.
I have a 4GB DB with a 1GB log file. Backups are done nightly and written to a NAS via UNC path. When I went home on Friday the log was 200MB -- this morning it was 1GB. As I stated the recovery model is set to simple so it should just reuse itself. I
issued a DBCC loginfo to see that each part of the log was a status 2. I then tried a backup log... with truncate_only and backup log...with no_log. This did not reclaim any space.
I have exhausted what I THOUGHT I knew about simple recovery. If anyone else has an idea, please post!
It has always been my understanding that
If there are a lot of VLF's with a status of 2 that means you have
uncommitted transactions in them. What does DBCC OPENTRAN() say?
Andrew J. Kelly SQL MVP
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that
|||Is there any open transaction? DBCC OPENTRAN.
Was there any replication properties set on for this database?
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that
|||There were 10 open trans in the DB
"Andrew J. Kelly" wrote:

> If there are a lot of VLF's with a status of 2 that means you have
> uncommitted transactions in them. What does DBCC OPENTRAN() say?
> --
> Andrew J. Kelly SQL MVP
>
> "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
> morning it was 1GB. As I stated the recovery model is set to simple so it
> should just reuse itself. I issued a DBCC loginfo to see that each part of
> the log was a status 2. I then tried a backup log... with truncate_only and
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>
|||Well that will sure do it<g>.
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...[vbcol=seagreen]
> There were 10 open trans in the DB
> "Andrew J. Kelly" wrote:
can't[vbcol=seagreen]
written[vbcol=seagreen]
this[vbcol=seagreen]
it[vbcol=seagreen]
of[vbcol=seagreen]
and[vbcol=seagreen]
anyone[vbcol=seagreen]
|||There were 10 open.
"Richard Ding" wrote:

> Is there any open transaction? DBCC OPENTRAN.
> Was there any replication properties set on for this database?
> "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
> morning it was 1GB. As I stated the recovery model is set to simple so it
> should just reuse itself. I issued a DBCC loginfo to see that each part of
> the log was a status 2. I then tried a backup log... with truncate_only and
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>
|||Yes and no...(I think)
Even if there are open transactions; shouldn't sql be able to reclaim the space that was before the LSN of those transactions? I understand that that portion of the log can't be reclaimed, but shouldn't the older?
The only other thing I can think of is would have I a transaction that say starts now and runs for 12 hours, just holding a resource open and not allowing that portion of the log to be 'checkpointed'?
Would I just cancel the SPIDS submitting those transactions so that there are NO transactions occuring? Also why would sql care if it is in simple recovery mode?
Thanks for all you help.
"Andrew J. Kelly" wrote:

> Well that will sure do it<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
> news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> can't
> written
> this
> it
> of
> and
> anyone
>
>
|||Dave,
A log file works in a round robin format. It starts adding transactions
sequentially from the beginning to the end. Once it gets to the end it will
wrap around only if the first VLF is free and has no uncommitted
transactions. Otherwise it will grow the log and continue on. If you have
an open tran for 12 hours you have some serious issues with your app. A
transaction should ideally be open for seconds at the very most. You
absolutely need to find out who is keeping these trans open and why.
Whether you kill them or not is up to you but you have to either commit them
or roll them back.
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:DD934F0C-13B9-4E67-876E-DB027E285BAF@.microsoft.com...
> Yes and no...(I think)
> Even if there are open transactions; shouldn't sql be able to reclaim the
space that was before the LSN of those transactions? I understand that that
portion of the log can't be reclaimed, but shouldn't the older?
> The only other thing I can think of is would have I a transaction that say
starts now and runs for 12 hours, just holding a resource open and not
allowing that portion of the log to be 'checkpointed'?
> Would I just cancel the SPIDS submitting those transactions so that there
are NO transactions occuring? Also why would sql care if it is in simple
recovery mode?[vbcol=seagreen]
> Thanks for all you help.
> "Andrew J. Kelly" wrote:
message[vbcol=seagreen]
still[vbcol=seagreen]
200MB --[vbcol=seagreen]
so[vbcol=seagreen]
part[vbcol=seagreen]
truncate_only[vbcol=seagreen]

Friday, March 23, 2012

Log file size continuously grows

Log file size continuously grows, even I placed Simple Recovery mode. Any one
tells me, how can I prevent this problem?
http://support.microsoft.com/?kbid=873235
HTH, Jens Suessmeyer.
"Farhan" <Farhan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14587936-A487-4A9C-BE5E-7A2A963612CD@.microsoft.com...
> Log file size continuously grows, even I placed Simple Recovery mode. Any
> one
> tells me, how can I prevent this problem?
|||My guess would be you have an open transaction. What does DBCC OPENTRAN say
for that db?
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Farhan" <Farhan@.discussions.microsoft.com> wrote in message
news:14587936-A487-4A9C-BE5E-7A2A963612CD@.microsoft.com...
> Log file size continuously grows, even I placed Simple Recovery mode. Any
> one
> tells me, how can I prevent this problem?

Monday, March 19, 2012

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
Jon
Is the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>
|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
JonIs the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
--
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>>I have an issue where a database on SQL 2005 (set to "simple" recovery
>>mode) has exponential log growth when I run one particular software
>>service against the DB, it grows by 100-200 megabytes a minute.
>> This service is very simple; it contains several "select" statements
>> (that wouldn't cause log growth, right?), and two or three insert/update
>> statements. The insert/update statements are all in stored procedures
>> that the service calls (it is written in .Net 2.0). The stored procedures
>> have no transaction statements in them at all, and if I add begin/end
>> transaction statements it doesn't make a difference.
>> So, how do I figure out exactly what in the service is causing the log
>> file growth? I have no idea how to determine that.
>> Thanks for any help.
>> Jon
>

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
JonIs the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>

Wednesday, March 28, 2012

Log files WAY too big

Log files WAY too big

Log files WAY too big

Log Files Growing out of control

Log Files Growing out of control

Log Files Growing out of control

Monday, March 26, 2012

log files

log files

log files

Log file won't Shrink in Simple Recovery Mode

Log file won't Shrink in Simple Recovery Mode

Log file won't Shrink in Simple Recovery Mode

Friday, March 23, 2012

Log file size continuously grows

Monday, March 19, 2012

Log file growth- what's the culprit?

Log file growth- what's the culprit?

Log file growth- what's the culprit?

Locks and Blocks

Blog Archive

About Me