Thursday, January 9, 2014

SQL 2008 R2 - Failed to flush the commit table to disk

Other errors
Failed to flush the commit table to disk in dbid 10 due to error 2601
Failed to flush the commit table to disk in dbid 8 due to error 8630

Solution 1 (does absolutely nothing):
This issue is resolved in the latest service pack

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

If the service pack does not resolve the issue, you must repair your SQL installation
Launch SQL Install>Maintenance>Repair


Solution 2 (this one worked for me):
Run a

DBCC CHECKDB (two) WITH NO_INFOMSGS, ALL_ERRORMSGS

then most likely a

ALTER DATABASE two SET SINGLE_USER
GO
DBCC CHECKDB (two,repair_rebuild)
GO
ALTER two SET MULTI_USER
GO

Solution 3 (others have reported this, i tried it, it did not work):
You can also disable change tracking on all objects and the database, and delete the uncommitted transactions

--Find all objects with change tracking
select (select OBJECT_NAME(object_id)) from  sys.change_tracking_tables

--Disable change tracking on each object
alter table GL00102 disable change_tracking
alter table MC00200 disable change_tracking
alter table GL10001 disable change_tracking
alter table GL10100 disable change_tracking
alter table GL10101 disable change_tracking
alter table MC40000 disable change_tracking
alter table SY00300 disable change_tracking
alter table GL40000 disable change_tracking
alter table GL12000 disable change_tracking
alter table GL00201 disable change_tracking
alter table GL12001 disable change_tracking
alter table GL10000 disable change_tracking
alter table GL32000 disable change_tracking
alter table GL40200 disable change_tracking
alter table GL20000 disable change_tracking
alter table GL00200 disable change_tracking
alter table GL00100 disable change_tracking
alter table GL30000 disable change_tracking
alter table SY40100 disable change_tracking
alter table SY40101 disable change_tracking



--Disable Change tracking on database
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF

--Delete the sys.syscommittab table
Click New Query
Click Change connection to bring up the login screen for the query
enter ADMIN: before your sql server name to use the DAC connection
Select your company
--backup the syscommittab table
select * into dbo.syscommittab_bak from sys.syscommittab
delete sys.syscommittab

--Enable change tracking on each object
alter table GL00102 enable change_tracking alter table MC00200 enable change_tracking alter table GL10001 enable change_tracking alter table GL10100 enable change_tracking alter table GL10101 enable change_tracking alter table MC40000 enable change_tracking alter table SY00300 enable change_tracking alter table GL40000 enable change_tracking alter table GL12000 enable change_tracking alter table GL00201 enable change_tracking alter table GL12001 enable change_tracking alter table GL10000 enable change_tracking alter table GL32000 enable change_tracking alter table GL40200 enable change_tracking alter table GL20000 enable change_tracking alter table GL00200 enable change_tracking alter table GL00100 enable change_tracking alter table GL30000 enable change_tracking alter table SY40100 enable change_tracking alter table SY40101 enable change_tracking
--Enable Change tracking on database
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON

No comments:

Post a Comment