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