Log file size increases over and over

Hi everybody,

I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
The features of this database are:

SQL Server 2000
Recovery Model = Full
Auto Update Statistics = Yes
Torn page detection = Yes
Auto create statistics = Yes
Full database backup taken once daily.
No log backup is taken.

So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions?
My free disk space is very low.

Thank you all,Since you do not backup the transaction log anyway, you can create a job to run on whatever schedule you choose to run code below:

BACKUP LOG DB_Name WITH TRUNCATE_ONLY

Lystra|||This command didn't work...
My log file size didn't decrease anything...
Does someone can help me with another suggestion?|||I'd try to shrink the database from SQL Enterprise Manager, or use the DBCC SHRINKDATABASE command from SQL Query Analyzer.

-PatP|||Before shrinking anything do DBCC OPENTRAN (db_name) to ensure there are no uncommitted transactions. Their presence wil;l prevent any attempt to shring log files.

It's also clear that you don't want to take advantage of Full recovery mode (I wonder why?), so set it to Simple.|||ok, now I got it!
It worked...

Thank you all...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?
> >>
> >>
> >
> >
> >.
> >