Log Grow

Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1o Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2o Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>

Log Grow

Hi,
Simple question but, for me, not obvious.
SQL Server 2000 + SP4.
My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
Recovery model simple.
1º Question - Why is growing the tlog for tempdb ?
I could shrink the log but this is just a workaround because in 4, 5 days i
have the same situation again.
2º Question - What could i do '
Thanks in advance
CMCCC
http://www.aspfaq.com/show.asp?id=2446
"CC" <CC@.discussions.microsoft.com> wrote in message
news:EA9F5E5A-043C-4D62-B0F8-BDD4C89A8F3E@.microsoft.com...
> Hi,
> Simple question but, for me, not obvious.
> SQL Server 2000 + SP4.
> My tempdb has 3GB, the Tlog for tempdb is growing and it has now 37 GB.
> Recovery model simple.
> 1? Question - Why is growing the tlog for tempdb ?
> I could shrink the log but this is just a workaround because in 4, 5 days
> i
> have the same situation again.
> 2? Question - What could i do '
> Thanks in advance
> CMC
>
>

log files problem

Hi All,
I have problem with log files on my databases, they grow so much that I have
errors when I backup and restore the data.
The best way that I found to fix this is to detach the db, delete the log
file and attach the data again.
How can I do this programmatically?
Tks in advance
JohnnyThe best way to fix this is to manage the log properly so that it
doesn't grow. If you aren't performing Log backups then set Simple
Recovery mode. On an OLTP system you should not need to allow log files
to grow (disable autogrow) and you should avoid shrinking at all.
David Portas
SQL Server MVP
--|||You can truncate the log file to 1KB using the DBCC command , so do it in a
periodical basis
"JFB" wrote:

> Hi All,
> I have problem with log files on my databases, they grow so much that I ha
ve
> errors when I backup and restore the data.
> The best way that I found to fix this is to detach the db, delete the log
> file and attach the data again.
> How can I do this programmatically?
> Tks in advance
> Johnny
>
>|||Look at "recovering databases-SQL Server" in BOL.
If you don't need transaction logs, put the DB in simple recovery mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
> a
> periodical basis
> "JFB" wrote:
>|||Why? Periodically shrinking has divantages: http://www.karaszi.com/SQLServer/in...dont_shrink.asp
JFB's problem is probably that db is in full recovery mode and periodic log
backups are not
performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:601F468D-0398-4048-8301-70332305B33D@.microsoft.com...
> You can truncate the log file to 1KB using the DBCC command , so do it in
a
> periodical basis
> "JFB" wrote:
>

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

Log Files

Hi All,
I ask the eternal question of what is the best practice to handle log files.
I want to and have given the log file to grow so that I have no performance
issue.
I do backup the database every night.
So is the only solution to detach the Server and start a new log file every
now and then or is there any better solution ?
Thanks
--
Jishnu GuhathakurtaRead these articles
http://www.sql-server-performance.com/database_settings.asp
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log
> files.
> I want to and have given the log file to grow so that I have no
> performance
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file
> every
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurta|||If you aren't dong log backups, have the db in simple recovery mode. The physical size of the log
file need to accommodate your largest transaction. there is no performance penalty of having a "too
big" log file. There is to regularly shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Never delete the log file unless it is OK to lose the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log files.
> I want to and have given the log file to grow so that I have no performance
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file every
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurta

Log Files

Hi All,
I ask the eternal question of what is the best practice to handle log files.
I want to and have given the log file to grow so that I have no performance
issue.
I do backup the database every night.
So is the only solution to detach the Server and start a new log file every
now and then or is there any better solution ?
Thanks
Jishnu GuhathakurtaRead these articles
http://www.sql-server-performance.c...se_settings.asp
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log
> files.
> I want to and have given the log file to grow so that I have no
> performance
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file
> every
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurta|||If you aren't dong log backups, have the db in simple recovery mode. The phy
sical size of the log
file need to accommodate your largest transaction. there is no performance p
enalty of having a "too
big" log file. There is to regularly shrink: http://www.karaszi.com/SQLServer/in...ont_shrink.asp.
Never delete the log file unless it is OK to lose the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log file
s.
> I want to and have given the log file to grow so that I have no performanc
e
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file ever
y
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurtasql

Log Files

Hi All,
I ask the eternal question of what is the best practice to handle log files.
I want to and have given the log file to grow so that I have no performance
issue.
I do backup the database every night.
So is the only solution to detach the Server and start a new log file every
now and then or is there any better solution ?
Thanks
Jishnu Guhathakurta
Read these articles
http://www.sql-server-performance.co...e_settings.asp
http://vyaskn.tripod.com/sql_server_...ices.htm#Step1
--administaiting best practices
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log
> files.
> I want to and have given the log file to grow so that I have no
> performance
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file
> every
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurta
|||If you aren't dong log backups, have the db in simple recovery mode. The physical size of the log
file need to accommodate your largest transaction. there is no performance penalty of having a "too
big" log file. There is to regularly shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
Never delete the log file unless it is OK to lose the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jishnu" <Jishnu@.discussions.microsoft.com> wrote in message
news:53346E6A-7BA4-412B-9E0D-56E587E1BD68@.microsoft.com...
> Hi All,
> I ask the eternal question of what is the best practice to handle log files.
> I want to and have given the log file to grow so that I have no performance
> issue.
> I do backup the database every night.
> So is the only solution to detach the Server and start a new log file every
> now and then or is there any better solution ?
> Thanks
> --
> Jishnu Guhathakurta

Log file not growing

I have a Db that is 3.8Gb and a log file that is 1mb the
log file does not grow. The db is setup with simple
recovery and the log file is configured to autogrow by
10%. Users are complaining that the DB is slow. My
hardware on this server is: 2 pentium xeon's with 4Gb of
ram 2 raid controllers 1 running raid 1 for the OS and
the other Raid 5 for the DB.In simple recovery mode the log is automatically truncated every time a
checkpoint occurs ( default every minute)... So the log will never get
larger than the longest running transaction..
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:1b96f01c4209d$7352d860$a101280a@.phx
.gbl...
> I have a Db that is 3.8Gb and a log file that is 1mb the
> log file does not grow. The db is setup with simple
> recovery and the log file is configured to autogrow by
> 10%. Users are complaining that the DB is slow. My
> hardware on this server is: 2 pentium xeon's with 4Gb of
> ram 2 raid controllers 1 running raid 1 for the OS and
> the other Raid 5 for the DB.|||Thanks,
>--Original Message--
>In simple recovery mode the log is automatically
truncated every time a
>checkpoint occurs ( default every minute)... So the log
will never get
>larger than the longest running transaction..
>"Carlos Santos" <anonymous@.discussions.microsoft.com>
wrote in message
> news:1b96f01c4209d$7352d860$a101280a@.phx
.gbl...
the
of
>
>.
>

Log file not growing

I have a Db that is 3.8Gb and a log file that is 1mb the
log file does not grow. The db is setup with simple
recovery and the log file is configured to autogrow by
10%. Users are complaining that the DB is slow. My
hardware on this server is: 2 pentium xeon's with 4Gb of
ram 2 raid controllers 1 running raid 1 for the OS and
the other Raid 5 for the DB.In simple recovery mode the log is automatically truncated every time a
checkpoint occurs ( default every minute)... So the log will never get
larger than the longest running transaction..
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:1b96f01c4209d$7352d860$a101280a@.phx.gbl...
> I have a Db that is 3.8Gb and a log file that is 1mb the
> log file does not grow. The db is setup with simple
> recovery and the log file is configured to autogrow by
> 10%. Users are complaining that the DB is slow. My
> hardware on this server is: 2 pentium xeon's with 4Gb of
> ram 2 raid controllers 1 running raid 1 for the OS and
> the other Raid 5 for the DB.|||Thanks,
>--Original Message--
>In simple recovery mode the log is automatically
truncated every time a
>checkpoint occurs ( default every minute)... So the log
will never get
>larger than the longest running transaction..
>"Carlos Santos" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1b96f01c4209d$7352d860$a101280a@.phx.gbl...
>> I have a Db that is 3.8Gb and a log file that is 1mb
the
>> log file does not grow. The db is setup with simple
>> recovery and the log file is configured to autogrow by
>> 10%. Users are complaining that the DB is slow. My
>> hardware on this server is: 2 pentium xeon's with 4Gb
of
>> ram 2 raid controllers 1 running raid 1 for the OS and
>> the other Raid 5 for the DB.
>
>.
>sql

Log file not growing

I have a Db that is 3.8Gb and a log file that is 1mb the
log file does not grow. The db is setup with simple
recovery and the log file is configured to autogrow by
10%. Users are complaining that the DB is slow. My
hardware on this server is: 2 pentium xeon's with 4Gb of
ram 2 raid controllers 1 running raid 1 for the OS and
the other Raid 5 for the DB.
In simple recovery mode the log is automatically truncated every time a
checkpoint occurs ( default every minute)... So the log will never get
larger than the longest running transaction..
"Carlos Santos" <anonymous@.discussions.microsoft.com> wrote in message
news:1b96f01c4209d$7352d860$a101280a@.phx.gbl...
> I have a Db that is 3.8Gb and a log file that is 1mb the
> log file does not grow. The db is setup with simple
> recovery and the log file is configured to autogrow by
> 10%. Users are complaining that the DB is slow. My
> hardware on this server is: 2 pentium xeon's with 4Gb of
> ram 2 raid controllers 1 running raid 1 for the OS and
> the other Raid 5 for the DB.
|||Thanks,
>--Original Message--
>In simple recovery mode the log is automatically
truncated every time a
>checkpoint occurs ( default every minute)... So the log
will never get
>larger than the longest running transaction..
>"Carlos Santos" <anonymous@.discussions.microsoft.com>
wrote in message
>news:1b96f01c4209d$7352d860$a101280a@.phx.gbl...
the
of
>
>.
>

log file issues

I am having a problem with the size of my primary filegroup. I have the
'automatically grow' option turned on. I have 7GB free disk space.But still
the log file complains that it does not have enough space to grow.Can anyone
please help me out? Thanks in advance.Can somebody pls help?
"HP" wrote:

> I am having a problem with the size of my primary filegroup. I have the
> 'automatically grow' option turned on. I have 7GB free disk space.But stil
l
> the log file complains that it does not have enough space to grow.Can anyo
ne
> please help me out? Thanks in advance.|||Do you back it up regularly ? (Assuming that you're using the FULL recovery
mode which should be the case)
Also, do you have heavy processing which could generate big T-Log ?
Chris
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
5D527A85-E4AC-4EA2-98AA-6910C81E534E@.microsoft.com...
>I am having a problem with the size of my primary filegroup. I have the
> 'automatically grow' option turned on. I have 7GB free disk space.But
> still
> the log file complains that it does not have enough space to grow.Can
> anyone
> please help me out? Thanks in advance.|||Iam getting this error when i try to change the datatype of a field in a
table which has 2 million records.
"Chris V." wrote:

> Do you back it up regularly ? (Assuming that you're using the FULL recover
y
> mode which should be the case)
> Also, do you have heavy processing which could generate big T-Log ?
> Chris
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "HP" <HP@.discussions.microsoft.com> a écrit dans le message de news:
> 5D527A85-E4AC-4EA2-98AA-6910C81E534E@.microsoft.com...
>
>|||You've got your winner...
To do such operation will generae a lot of writting in the transaction (to
be able to roll-back).
How look the transaction you're using for the update ?
________________________________________
______
It's still better that if it would have been worst, isn't it ?
C'est toujours mieux que si c'etait pire !
"HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
2E0A42E1-8CCC-4F1F-806A-3C1EEC880FD3@.microsoft.com...
> Iam getting this error when i try to change the datatype of a field in a
> table which has 2 million records.
> "Chris V." wrote:
>|||HP
Well, sometimes autogrow is a little 'slow' and you get the error message,i
mean it could not allocate free space to grow the log.
Try change autogrow option to less percent or by 100MB.
"Chris V." <tophe_news@.hotmail.com> wrote in message
news:OAeKOgsJFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You've got your winner...
> To do such operation will generae a lot of writting in the transaction (to
> be able to roll-back).
> How look the transaction you're using for the update ?
> --
> ________________________________________
______
> It's still better that if it would have been worst, isn't it ?
> C'est toujours mieux que si c'etait pire !
> "HP" <HP@.discussions.microsoft.com> a crit dans le message de news:
> 2E0A42E1-8CCC-4F1F-806A-3C1EEC880FD3@.microsoft.com...
the
>sql

Log file failed to grow despite lots of free disk space

Hello -
I had a strange problem last night and I wanted to ask if anyone else
has ever experienced it.
I have a transaction log set to Auto-grow by 10% with unrestricted
file growth. When I left last night the .ldf file took up 3GB of
space but only about 10% of it was used. I also had an additional 2GB
of free space on the disk which holds the log.
When I came in this morning, queries were timing out because the log
was full. The log file had NOT grown and Windows was reporting 2GB of
free space.
I cleared out a few other files, freeing up another 1GB and all of the
sudden the log grew and queries started working again.
Does anybody know what could have happened here? Are there any
reasons why the log file would fail to grow into remaining disk space?
Or, alternatively, could Windows ever report an incorrect amount of
free disk space?
Thanks!
David SimardSometimes, grow doesn't seem to be fast enough for the modifications. Could be such a case...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310210656.2c65881b@.posting.google.com...
> Hello -
> I had a strange problem last night and I wanted to ask if anyone else
> has ever experienced it.
> I have a transaction log set to Auto-grow by 10% with unrestricted
> file growth. When I left last night the .ldf file took up 3GB of
> space but only about 10% of it was used. I also had an additional 2GB
> of free space on the disk which holds the log.
> When I came in this morning, queries were timing out because the log
> was full. The log file had NOT grown and Windows was reporting 2GB of
> free space.
> I cleared out a few other files, freeing up another 1GB and all of the
> sudden the log grew and queries started working again.
> Does anybody know what could have happened here? Are there any
> reasons why the log file would fail to grow into remaining disk space?
> Or, alternatively, could Windows ever report an incorrect amount of
> free disk space?
> Thanks!
> David Simard|||See if your error log contains a 'log file is full' message.
If so, it's the problem mentioned by tibor
This is the
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310210656.2c65881b@.posting.google.com...
> Hello -
> I had a strange problem last night and I wanted to ask if anyone else
> has ever experienced it.
> I have a transaction log set to Auto-grow by 10% with unrestricted
> file growth. When I left last night the .ldf file took up 3GB of
> space but only about 10% of it was used. I also had an additional 2GB
> of free space on the disk which holds the log.
> When I came in this morning, queries were timing out because the log
> was full. The log file had NOT grown and Windows was reporting 2GB of
> free space.
> I cleared out a few other files, freeing up another 1GB and all of the
> sudden the log grew and queries started working again.
> Does anybody know what could have happened here? Are there any
> reasons why the log file would fail to grow into remaining disk space?
> Or, alternatively, could Windows ever report an incorrect amount of
> free disk space?
> Thanks!
> David Simard|||If that were the case, wouldn't the problem resolve itself in moments?
We had queries timing out over a period of a few hours.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uZTzBoGmDHA.372@.TK2MSFTNGP11.phx.gbl>...
> Sometimes, grow doesn't seem to be fast enough for the modifications. Could be such a case...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "DaveS" <davesimard@.hotmail.com> wrote in message
> news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > Hello -
> >
> > I had a strange problem last night and I wanted to ask if anyone else
> > has ever experienced it.
> >
> > I have a transaction log set to Auto-grow by 10% with unrestricted
> > file growth. When I left last night the .ldf file took up 3GB of
> > space but only about 10% of it was used. I also had an additional 2GB
> > of free space on the disk which holds the log.
> >
> > When I came in this morning, queries were timing out because the log
> > was full. The log file had NOT grown and Windows was reporting 2GB of
> > free space.
> >
> > I cleared out a few other files, freeing up another 1GB and all of the
> > sudden the log grew and queries started working again.
> >
> > Does anybody know what could have happened here? Are there any
> > reasons why the log file would fail to grow into remaining disk space?
> > Or, alternatively, could Windows ever report an incorrect amount of
> > free disk space?
> >
> > Thanks!
> >
> > David Simard|||Actually, now that you mention it: no, there weren't any 'log file is
full' messages in the log. I just assumed that was the problem since
the queries starting running again right after the log file grew.
But I guess I know what happens when you assume...
"Stressed" <k@.c.co.uk> wrote in message news:<#me0n5HmDHA.644@.TK2MSFTNGP11.phx.gbl>...
> See if your error log contains a 'log file is full' message.
> If so, it's the problem mentioned by tibor
> This is the
> "DaveS" <davesimard@.hotmail.com> wrote in message
> news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > Hello -
> >
> > I had a strange problem last night and I wanted to ask if anyone else
> > has ever experienced it.
> >
> > I have a transaction log set to Auto-grow by 10% with unrestricted
> > file growth. When I left last night the .ldf file took up 3GB of
> > space but only about 10% of it was used. I also had an additional 2GB
> > of free space on the disk which holds the log.
> >
> > When I came in this morning, queries were timing out because the log
> > was full. The log file had NOT grown and Windows was reporting 2GB of
> > free space.
> >
> > I cleared out a few other files, freeing up another 1GB and all of the
> > sudden the log grew and queries started working again.
> >
> > Does anybody know what could have happened here? Are there any
> > reasons why the log file would fail to grow into remaining disk space?
> > Or, alternatively, could Windows ever report an incorrect amount of
> > free disk space?
> >
> > Thanks!
> >
> > David Simard|||Any other 'interesting' messages in the error log on the run up to this?
"DaveS" <davesimard@.hotmail.com> wrote in message
news:f8e708df.0310220842.f863fef@.posting.google.com...
> Actually, now that you mention it: no, there weren't any 'log file is
> full' messages in the log. I just assumed that was the problem since
> the queries starting running again right after the log file grew.
> But I guess I know what happens when you assume...
> "Stressed" <k@.c.co.uk> wrote in message
news:<#me0n5HmDHA.644@.TK2MSFTNGP11.phx.gbl>...
> > See if your error log contains a 'log file is full' message.
> >
> > If so, it's the problem mentioned by tibor
> >
> > This is the
> >
> > "DaveS" <davesimard@.hotmail.com> wrote in message
> > news:f8e708df.0310210656.2c65881b@.posting.google.com...
> > > Hello -
> > >
> > > I had a strange problem last night and I wanted to ask if anyone else
> > > has ever experienced it.
> > >
> > > I have a transaction log set to Auto-grow by 10% with unrestricted
> > > file growth. When I left last night the .ldf file took up 3GB of
> > > space but only about 10% of it was used. I also had an additional 2GB
> > > of free space on the disk which holds the log.
> > >
> > > When I came in this morning, queries were timing out because the log
> > > was full. The log file had NOT grown and Windows was reporting 2GB of
> > > free space.
> > >
> > > I cleared out a few other files, freeing up another 1GB and all of the
> > > sudden the log grew and queries started working again.
> > >
> > > Does anybody know what could have happened here? Are there any
> > > reasons why the log file would fail to grow into remaining disk space?
> > > Or, alternatively, could Windows ever report an incorrect amount of
> > > free disk space?
> > >
> > > Thanks!
> > >
> > > David Simard

Log file behavior

We have a 43 gig database that we are adding lots of data too. The recovery
mode is set to Full. The log file will grow for a while and then shrink
back down. I thought the log file would grow until it was backed up?
I did set the recovery to simple before we started our tests and truncated
the log and then set the recovery mode back to Full.
It's acting like it's still in simple mode...Kevin,
It might be possible that "Auto Shrink" is checked on the Options tab of the
Database Properties dialog.
If not, have you inspected the SQL Server Log files or run profiler to see
what is happening?
Is it possible that someone or some batch process is periodically conducing
a manual Shrink operation?
--
Keith Wilson
This posting is provided "AS IS" without express or implied warranty,
guarantee, or rights.
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:%23Ue3AvnuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> We have a 43 gig database that we are adding lots of data too. The
recovery
> mode is set to Full. The log file will grow for a while and then shrink
> back down. I thought the log file would grow until it was backed up?
> I did set the recovery to simple before we started our tests and truncated
> the log and then set the recovery mode back to Full.
> It's acting like it's still in simple mode...
>|||It appears that when switching back to Full from Simple, you have to do a
BACKUP LOG statement for it to act like Full again. At least that is what
we did and it's now behaving like Full usually behaves
"Keith Wilson" <keithwi@.online.microsoft.com> wrote in message
news:3fcf6cb5$1@.news.microsoft.com...
> Kevin,
> It might be possible that "Auto Shrink" is checked on the Options tab of
the
> Database Properties dialog.
> If not, have you inspected the SQL Server Log files or run profiler to see
> what is happening?
> Is it possible that someone or some batch process is periodically
conducing
> a manual Shrink operation?
> --
> Keith Wilson
> This posting is provided "AS IS" without express or implied warranty,
> guarantee, or rights.
>
> "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> news:%23Ue3AvnuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> > We have a 43 gig database that we are adding lots of data too. The
> recovery
> > mode is set to Full. The log file will grow for a while and then shrink
> > back down. I thought the log file would grow until it was backed up?
> >
> > I did set the recovery to simple before we started our tests and
truncated
> > the log and then set the recovery mode back to Full.
> >
> > It's acting like it's still in simple mode...
> >
> >
>|||Kevin, You actually need a backup DATABASE after changing from simple =to full.
Mike John
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message =news:OJyyQ%23ouDHA.684@.TK2MSFTNGP09.phx.gbl...
> > It appears that when switching back to Full from Simple, you have to =do a
> BACKUP LOG statement for it to act like Full again. At least that is =what
> we did and it's now behaving like Full usually behaves
> > "Keith Wilson" <keithwi@.online.microsoft.com> wrote in message
> news:3fcf6cb5$1@.news.microsoft.com...
> > Kevin,
> >
> > It might be possible that "Auto Shrink" is checked on the Options =tab of
> the
> > Database Properties dialog.
> > If not, have you inspected the SQL Server Log files or run profiler =to see
> > what is happening?
> >
> > Is it possible that someone or some batch process is periodically
> conducing
> > a manual Shrink operation?
> >
> > -- > > Keith Wilson
> >
> > This posting is provided "AS IS" without express or implied =warranty,
> > guarantee, or rights.
> >
> >
> > "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> > news:%23Ue3AvnuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> > > We have a 43 gig database that we are adding lots of data too. =The
> > recovery
> > > mode is set to Full. The log file will grow for a while and then =shrink
> > > back down. I thought the log file would grow until it was backed =up?
> > >
> > > I did set the recovery to simple before we started our tests and
> truncated
> > > the log and then set the recovery mode back to Full.
> > >
> > > It's acting like it's still in simple mode...
> > >
> > >
> >
> >
> >|||"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message news:<#Ue3AvnuDHA.1876@.TK2MSFTNGP09.phx.gbl>...
> We have a 43 gig database that we are adding lots of data too. The recovery
> mode is set to Full. The log file will grow for a while and then shrink
> back down. I thought the log file would grow until it was backed up?
> I did set the recovery to simple before we started our tests and truncated
> the log and then set the recovery mode back to Full.
> It's acting like it's still in simple mode...
Kevin,
A few suggestions :
Check that the Auto Shrink property is set. You can find this in the
properties of the database in enterprise manager, or using
databasepropertyex.
You may also like to check that you are not running a maintenance plan
to shrink the file.
Rod Colledge.|||Kevin,
If you enable the "Auto Shrink" option, the transaction log will grow with insert operations even
the recovery mode of the database is Simple.
After you change the recovery mode to Full, the log willl be shrinked at this time and later the
log will NOT grow with the insert operations until the next log shrink or backup. On the other
hand, if you disable the "Auto Shrink" option, the transaction log will always grow under the Full
recovery mode.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.