SQL SERVER – Transaction Log Full Due to ‘CHECKPOINT’

Problem:

We encountered the Error 9002 ansaction Log Full Due to ‘CHECKPOINT’.

Solution:

I was able to resolve it by rebuilding the log. Below are the scripts that I used.

ALTER DATABASE DatabaseName  set  EMERGENCY 
ALTER DATABASE DatabaseName  REBUILD LOG ON (NAME='DatabaseName_log',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseName_Log.LDF')
ALTER DATABASE DatabaseName  set ONLINE
ALTER DATABASE DatabaseName  SET MULTI_USER 

Below is a script to get the logical name and filepath.

SELECT 
	DB_NAME ([database_id]) AS [Database Name]
	,[file_id] 
	,name
	,physical_name
	,type_desc
	,state_desc
	,CONVERT(bigint, size/128.0) AS [Total Size in MB]
	,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB]
FROM
	sys.master_files WITH(NOLOCK)
WHERE
	[database_id] > 4
	AND [database_id] <> 32767
	OR [database_id] = 2
ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)

Cheers!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter