March 11, 2019 at 7:34 am
Hi to all,
I'm puzzled !
Running a database on an older server stoped executing its jobs all of a sudden.
Digging into this I noticed that the errorlog is telling me: "The transaction log for database is full due to 'CHECKPOINT'" // Error: 9002, Severity: 17, State: 1.
This database is running in a FULL mode,
The database cannot be set to SIMPLE mode,
it cannot perform any backups (no - not even logbackups),
it cannot perform re-index/integritycheck/ update statistics,
the log file on this database can not be schrinked,
the logfile is 100 GB but the space on the lun is 270 GB,
DBCC SQLPERF(LogSpace) indicates 100% full (!),
DBCC OPENTRAN gives no result,
select name, log_reuse_wait,log_reuse_wait_desc from sys.databases => nameofthedatabase , 1 , CHECKPOINT,
a restart of the server did not wordk (although it took an awful long time to RECOVER).
So it seems the logfile is held by the CHECKPOINT and I do not know how to lift up this CHECKPOINT blockade.
Can some one please help me out on this?
Guus Kramer
The Netherlands
March 11, 2019 at 7:53 am
gkramer 23701 - Monday, March 11, 2019 7:34 AMHi to all,I'm puzzled !
Running a database on an older server stoped executing its jobs all of a sudden.
Digging into this I noticed that the errorlog is telling me: "The transaction log for database is full due to 'CHECKPOINT'" // Error: 9002, Severity: 17, State: 1.This database is running in a FULL mode,
The database cannot be set to SIMPLE mode,
it cannot perform any backups (no - not even logbackups),
it cannot perform re-index/integritycheck/ update statistics,
the log file on this database can not be schrinked,
the logfile is 100 GB but the space on the lun is 270 GB,
DBCC SQLPERF(LogSpace) indicates 100% full (!),
DBCC OPENTRAN gives no result,
select name, log_reuse_wait,log_reuse_wait_desc from sys.databases => nameofthedatabase , 1 , CHECKPOINT,
a restart of the server did not wordk (although it took an awful long time to RECOVER).So it seems the logfile is held by the CHECKPOINT and I do not know how to lift up this CHECKPOINT blockade.
Can some one please help me out on this?
Guus Kramer
The Netherlands
What about the autogrowth setting for the log file? Is that turned off?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 11, 2019 at 7:58 am
Steve,
autogrowth is turned on with plenty of space to grow if needed.
Guus
March 11, 2019 at 8:22 am
Have you tried doing a manual checkpoint on that database?
March 11, 2019 at 8:31 am
Beatrix Kiddo - Monday, March 11, 2019 8:22 AMHave you tried doing a manual checkpoint on that database?
Beatrix,
Off course I tried to set a manual CHECKPOINT - the error when trying:
Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'DB_NAME' failed to generate a checkpoint.
This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption.
Examine previous entries in the error log for more detailed information on this failure.
Could not write a checkpoint record in database DB_NAME because the log is out of space.
Contact the database administrator to truncate the log or allocate more space to the database log files.
Msg 9002, Level 17, State 1, Line 1
The transaction log for database 'DB-NAME' is full due to 'CHECKPOINT'.
as I told before there is enough space avialable but CHECKPOINT is blocking ever other action which needs the LOG-file.....
Guus
March 11, 2019 at 8:34 am
gkramer 23701 - Monday, March 11, 2019 8:31 AMBeatrix Kiddo - Monday, March 11, 2019 8:22 AMHave you tried doing a manual checkpoint on that database?Betatrix,
Off course I tried to set a manual CHECKPOINT - the error when trying:
Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'DB_NAME' failed to generate a checkpoint.
This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption.
Examine previous entries in the error log for more detailed information on this failure.
Could not write a checkpoint record in database DB_NAME because the log is out of space.
Contact the database administrator to truncate the log or allocate more space to the database log files.
Msg 9002, Level 17, State 1, Line 1
The transaction log for database 'DB-NAME' is full due to 'CHECKPOINT'.as I told before there is enough space avialable but CHECKPOINT is blocking ever other action which needs the LOG-file.....
Guus
Are you current on service packs, updates? This may apply:
FIX: Error 9002 and error 3052 when you try to add or back up log file in SQL Server 2012 or SQL Server 2014
Sue
March 12, 2019 at 6:24 am
Sue,
Thanks for pointing to theMS article.
I will make a VM-snapshot and upgrade to the given SP/CU (or to SP4).
Guus
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply