Sql-server – SQL Server 2008 r2 sp1 upgrade

sql server

I've been given an SQL Server to run maintenance plans on. After running a script called sp_blitz which checks the health and security status of the server and going through the errors logs I realized that there are up to 5 databases with instances of database corruption.

To have my own understanding an experience of these errors, I set up fresh maintenance plans for backup only just to monitor what errors I will get. The result showed that some databases were not being backed up at all and when I checked what the error was, for one of the databases, I got "Failed to flush the commit table to disk in (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

I went on google to see what I could do and it said I could restore the database from a point where it was still ok. I checked the local disks for the most recent backup and that was 24/08/2015 which corresponded to what the database properties said in 'last database backup section'.

Can you please advice me on what you think my next steps should be sir or what I can do in a situation like this.

Also we are using SQL Server 2008 r2 sp1. Should I be looking to upgrade to sp3?

Best Answer

one of the databases, I got "Failed to flush the commit table to disk in (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

The above error occurs when you have CHANGE TRACKING enabled on the database. To find what databases have Change tracking enabled, run below tsql:

SELECT d.name AS 'Database Name', ctd.*
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON d.database_id = ctd.database_id

This error was fixed in SQL Server 2008R2 SP1 +CU1. Even with SP1+CU1, you are running an unsupported patch of SQL Server. I would suggest you to apply SQL Server 2008R2 SP3 then Security Update - KB3045316.

A security issue has been identified in the SQL Server 2008 R2 SP3 that could allow an attacker to compromise your system and gain control over it.

Recommendations :

Related Question