Auto CHECKPOINT not working

Question

Post reply

Auto CHECKPOINT not working

itpcpro

Ten Centuries

Points: 1043
More actions
November 17, 2010 at 11:07 am

#226771

I have a database on SQL 2000 and the log file fills up and will not free up unused space. I have tracked it down to the CHECKPOINT not running. After the nightly jobs run the log fills up to 68GB and 99% used space (DBCC SQLPERF(LogSpace)) I have also ran (DBCC OPENTRAN) came back with:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After all of that all I do is run CHECKPOINT and the log file drops down to 300MB with 68GB free...
So may question is has anyone been able to fix this problem? I have done searches on this website but all of them lead to a dead end..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

What is your setting for 'recovery interval'?

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323518 More actions · Answer 2

I doubt very much the issue is CHECKPOINT not running.

I'll bet your database is set to Recovery Mode = FULL, and you are not taking regular transaction log backups, so it's continues to expand.

select DATABASEPROPERTYEX(db_name(),'Recovery')

select DATABASEPROPERTYEX('SandBox','Recovery')

--change it to simple:

ALTER DATABASE [SandBox] SET RECOVERY SIMPLE WITH NO_WAIT

either change the recovery model to simple, or create a backup schedule.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

itpcpro Ten Centuries Points: 1043 More actions · Answer 3

itpcpro

Ten Centuries

Points: 1043

November 17, 2010 at 11:43 am

#1252071

Recovery Mode is set to simple

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok, run this to find the current recovery interval setting:

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

USE master;

GO

if needed, lets set the interval to a shorter time

--The following example sets the system recovery interval to 3 minutes.

EXEC sp_configure 'recovery interval', '3';

RECONFIGURE WITH OVERRIDE;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

itpcpro Ten Centuries Points: 1043 More actions · Answer 5

David Webb-200187 (11/17/2010)
What is your setting for 'recovery interval'?

Recovery interval is set at 0

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

If this really is SQL Server 2000, it could be:

http://support.microsoft.com/kb/909369

And then again, I might be wrong ...
David Webb