Skip to content

ID

Transactions Rolled Back in Database

IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.

msdb transactions rolled back in database

 

How can this be a good thing? Why are transactions being rolled back or rolled forward?

Read More »Transactions Rolled Back in Database

Status of DBCC CheckDB

So you are checking your database with DBCC CheckDB and of course if you are like me you use the WITH NO_INFOMSGS parameter. But it turns out that CheckDB is taking longer to run that you expected, and you want to check and see what table is currently being checked.

Here is a quick SQL Script that I use to find the current table that is being checked by DBCC CheckDB

use [your database];
go

-- use this to find the session id of DBCC CheckDB
SELECT session_id, start_time, command, percent_complete, total_elapsed_time,
       estimated_completion_time, database_id, user_id, last_wait_type
  FROM sys.dm_exec_requests
 CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 WHERE command like '%DBCC%';  

DECLARE @sessionID as integer = 53; -- enter the session ID of DBCC CheckDB Here

SELECT name, schema_id, type_desc
  FROM sys.objects
 WHERE object_id = (SELECT TOP 1 resource_associated_entity_id
                      FROM sys.dm_tran_locks
					 WHERE request_session_id = @sessionID
					   AND resource_type = 'OBJECT'
					   AND resource_associated_entity_id <> 50);



status of dbcc checkdb


Read More »Status of DBCC CheckDB

Corruption Challenge Week 4 – The Winning Solution

Congratulations to Randolph West who won the corruption challenge this week with the following solution which restored all of the data.

First he restored the database to get started. Note some of his code and comments have been reformatted to better fit in the blog format.

Restore database. I use KEEP_CDC and KEEP_REPLICATION because of the hints you dropped in your blog.
The hint is that there is 100% chance of data recovery, so I will look for Change Data Capture tables.

USE master;
GO
--DROP DATABASE [CorruptionChallenge4] ;
RESTORE DATABASE [CorruptionChallenge4]
   FROM DISK = N'C:\DBBackups\CorruptionChallenge4_Corrupt.bak'
   WITH FILE = 1,
        MOVE N'CorruptionChallenge4'
		  TO N'C:\SQL_DATA\CorruptionChallenge4.mdf',
        MOVE N'UserObjects'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_UserObjects.ndf',
        MOVE N'CorruptionChallenge4_log'
		  TO N'C:\SQL_DATA\CorruptionChallenge4_log.ldf',
        NOUNLOAD,
    REPLACE,
    STATS = 5,
    KEEP_CDC,
    KEEP_REPLICATION;

Next he ran CheckDB to see what is wrong with this database.

Read More »Corruption Challenge Week 4 – The Winning Solution

Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge

corruption

Welcome to the DataBase Corruption Challenge, this is an about weekly blog challenge where I will post a corrupt SQL Server database with some details on what happened to it.

If at this point you are already a bit irked by my use of capitalization in the DataBase Corruption Challenge, and the acronym of DBCC that I have used to describe it, then you are already ahead of many people reading about this challenge. Welcome to the challenge.
The challenge will be to download the corrupt database and attempt to recover it. If you can recover it, please send me the steps to recover it, along with some proof that the database has been recovered. The goal each week will be the following:

Read More »Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge