Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db bac
kup. So if full recovery
model, you either have to do regular transaction log backups or set the data
base to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/in...r />
hrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.ph
x.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
just[vbcol=seagreen]
the[vbcol=seagreen]
>sql

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> > At one client site (running SQL Server 2000), I have a log file that
just
> > grows & grows. The maintenance plan performs a full backup nightly and
the
> > AutoShrink option is checked.
> >
> > I can run a script to forcibly shrink the thing, but the automated DB
> > maintenance should be handling this.
> >
> > What else can I check?
> >
> > Thanks.
> >
> > dwilson
> >
> >
>

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilson
What recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
just[vbcol=seagreen]
the
>

log grows after shrinking

I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:

> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.
|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas

"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:

> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.

log grows after shrinking

I have truncated the log file and then used the DBCC Shrinkfile command to
reduce to physical size of the file. This seemed to work perfectly.
However, after a few days the transaction log file returns to its huge size
(8Gb). Has anyone else experienced this problem and if so can you suggest a
solution?
Thanks in advance, Simon.Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge size
> (8Gb). Has anyone else experienced this problem and if so can you suggest a
> solution?
> Thanks in advance, Simon.|||Even in SIMPLE RECOVERY mode, the transaction log file(s) will grow to about
the size of the data portion of your data files if you are doing regular
maintenance with the DB maintenance wizard. The reason is that the
Organization task will serially reorganize your data tables. The largest
table will dictate the amount of free space in your data file and the size
of transaction log for normal operations.
If you do not want your tranaction log to be bigger than this, you need to
be in SIMPLE RECOVERY or backup the tlogs frequently if in FULL RECOVERY or
BULK INSERT RECOVERY modes.
How big is your data files?
Sincerely,
Anthony Thomas
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:9B630219-A733-4032-87DF-45B858628C31@.microsoft.com...
Simon
It sounds like you have your database in full recovery mode, but you are not
performing transaction log backups.
If you want to use your transaction logs as part of your backup and recovery
strategy, (recommended for production systems) you need to back them up
regularly. Something like every 15 or 30 minutes (or whatever is suitable
for
your requirements) during your business day.
If you really do not want your transaction logs, set your recovery mode to
simple.
Hope this helps
John
"Simon" wrote:
> I have truncated the log file and then used the DBCC Shrinkfile command to
> reduce to physical size of the file. This seemed to work perfectly.
> However, after a few days the transaction log file returns to its huge
size
> (8Gb). Has anyone else experienced this problem and if so can you suggest
a
> solution?
> Thanks in advance, Simon.sql

Log File Size grows enormously

Hi,
I have a database with data file size 26 MB and log file
just growing. The log file size is 1.2GB. It increases by
110MB in half a day. I am taking transaction log backups
every half an hour. But still the log file size keep
growing. This is happening for the past 20 days. DBCC
OPENTRAN command gives the following:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
with SP4
What action should I take to control the log file size?
The below link gives a patch for SQL 7.0.
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;240039
But what to do with SQL 2K with SP3a?Here's some additional links that may be of use :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
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=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
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
HTH
Ryan Waight, MCDBA, MCSE
"Mangai" <anonymous@.discussions.microsoft.com> wrote in message
news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> Hi,
> I have a database with data file size 26 MB and log file
> just growing. The log file size is 1.2GB. It increases by
> 110MB in half a day. I am taking transaction log backups
> every half an hour. But still the log file size keep
> growing. This is happening for the past 20 days. DBCC
> OPENTRAN command gives the following:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> My SQL VERSION is: 8.00.818 (Intel X86) running in Win 2K
> with SP4
> What action should I take to control the log file size?
> The below link gives a patch for SQL 7.0.
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;240039
> But what to do with SQL 2K with SP3a?
>|||I have implemented all the recommendations mentioned in
the links. I am taking regular log backup (every 30
mins). But still the log file size is 1110MB even when no
users are connected.
While using dbcc shrinkfile, i am getting the following
msg:
Cannot shrink log file 2 (Gsap_Log) because all logical
log files are in use.
DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (1134:527:1)
Oldest non-distributed LSN : (1134:1041:4)
Execution of the SP sp_repldone gives "The database is
not published."
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1
Can anyone help me in truncating my log file?
Regards,
Mangai
>--Original Message--
>Here's some additional links that may be of use :-
>http://www.support.microsoft.com/?id=256650 INF: How
to Shrink the SQL
>Server 7.0 Tran Log
>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=272318 INF:
Shrinking Log in SQL
>Server 2000 with DBCC SHRINKFILE
>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
>
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Mangai" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081101c3ae85$1584e460$a501280a@.phx.gbl...
>> Hi,
>> I have a database with data file size 26 MB and log
file
>> just growing. The log file size is 1.2GB. It increases
by
>> 110MB in half a day. I am taking transaction log
backups
>> every half an hour. But still the log file size keep
>> growing. This is happening for the past 20 days. DBCC
>> OPENTRAN command gives the following:
>> Replicated Transaction Information:
>> Oldest distributed LSN : (1134:527:1)
>> Oldest non-distributed LSN : (1134:1041:4)
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
2K
>> with SP4
>> What action should I take to control the log file size?
>> The below link gives a patch for SQL 7.0.
>> http://support.microsoft.com/default.aspx?scid=kb;EN-
>> US;240039
>> But what to do with SQL 2K with SP3a?
>>
>
>.
>|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:090a01c3af1b$40eeb770$a301280a@.phx.gbl...
> I have implemented all the recommendations mentioned in
> the links. I am taking regular log backup (every 30
> mins). But still the log file size is 1110MB even when no
> users are connected.
> While using dbcc shrinkfile, i am getting the following
> msg:
> Cannot shrink log file 2 (Gsap_Log) because all logical
> log files are in use.
> DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (1134:527:1)
> Oldest non-distributed LSN : (1134:1041:4)
> Execution of the SP sp_repldone gives "The database is
> not published."
> EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1
>
> Can anyone help me in truncating my log file?
> Regards,
> Mangai
>
> >--Original Message--
> >Here's some additional links that may be of use :-
> >
> >http://www.support.microsoft.com/?id=256650 INF: How
> to Shrink the SQL
> >Server 7.0 Tran Log
> >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=272318 INF:
> Shrinking Log in SQL
> >Server 2000 with DBCC SHRINKFILE
> >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
> >
> >
> >
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Mangai" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:081101c3ae85$1584e460$a501280a@.phx.gbl...
> >> Hi,
> >> I have a database with data file size 26 MB and log
> file
> >> just growing. The log file size is 1.2GB. It increases
> by
> >> 110MB in half a day. I am taking transaction log
> backups
> >> every half an hour. But still the log file size keep
> >> growing. This is happening for the past 20 days. DBCC
> >> OPENTRAN command gives the following:
> >>
> >> Replicated Transaction Information:
> >> Oldest distributed LSN : (1134:527:1)
> >> Oldest non-distributed LSN : (1134:1041:4)
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> My SQL VERSION is: 8.00.818 (Intel X86) running in Win
> 2K
> >> with SP4
> >>
> >> What action should I take to control the log file size?
> >> The below link gives a patch for SQL 7.0.
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;EN-
> >> US;240039
> >>
> >> But what to do with SQL 2K with SP3a?
> >>
> >>
> >
> >
> >.
> >

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?

Log file grows (Error 9002).

Hello Group,
We have a project where we store the state of object instances in Sql Server
tables. Primarely these tables consist of an id column and an IMAGE type
column. We use .NET binary serialization to create byte arrays and use
(ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
The size of a binary array is approximately 300-350k. The database is set to
automatically grow the data en log files. The recovery model is set to
SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
up. Backing up the log file helps, but... what is caution this error? Are we
using the wrong CRUD
statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
to see that the log file grows (why isn't Sql reclaiming the used (old)
space): am I missing the point of the SIMPLE recovery model?
(btw: I'm pretty sure there are no transactions 'hanging')
Many thanks in advance!
Kind regards,
Johan Bouwhuis.Try issuing Checkpoint through the application or whenever a heavy
transaction is applied...
"Johan Bouwhuis" wrote:
> Hello Group,
> We have a project where we store the state of object instances in Sql Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002 shows
> up. Backing up the log file helps, but... what is caution this error? Are we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>|||Hi,
Since the recover for your database is SIMPLE, the transction will be
cleared after each recovery interval. In your case looks like you
are doing a bulk DML operation. In this case the coomit will be done only
after completing the entire operation. To overcome this
instead of doing bulk DML operation do a batch by batch DML operation. THis
will ensure that your LDF will not grow to a higher
extend.
In SIMPLE recovery the log will be cleared automatically and you can not
perform a transaction log backup. If it is a production server then
it is recommened to go for FULL recovery model and schedule a Transaction
log backup. This will help you to recover the database fully/POINT IN TIME.
Thanks
Hari
SQL Server MVP
"Johan Bouwhuis" <JohanBouwhuis@.discussions.microsoft.com> wrote in message
news:E60F4B7A-430D-4E85-94AD-EC41A6187DE9@.microsoft.com...
> Hello Group,
> We have a project where we store the state of object instances in Sql
> Server
> tables. Primarely these tables consist of an id column and an IMAGE type
> column. We use .NET binary serialization to create byte arrays and use
> (ordinary) INSERT, UPDATE and SELECT statements (in conjunction with the
> System.Data.SqlClient classes) to write to and read from Sql's IMAGE type.
> The size of a binary array is approximately 300-350k. The database is set
> to
> automatically grow the data en log files. The recovery model is set to
> SIMPLE. Our problem is that the log file grows (huge!) until error 9002
> shows
> up. Backing up the log file helps, but... what is caution this error? Are
> we
> using the wrong CRUD
> statements (should we use WRITETEXT and READTEXT instead)? I'm so suprised
> to see that the log file grows (why isn't Sql reclaiming the used (old)
> space): am I missing the point of the SIMPLE recovery model?
> (btw: I'm pretty sure there are no transactions 'hanging')
> Many thanks in advance!
> Kind regards,
> Johan Bouwhuis.
>