SQL Server Tracking Suspect Pages

Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?

Continue reading

WinDbg and SQL Server Fun

I have a SQL Server that is constantly producing “dump” files (with a MDMP File type), these are named SQLDumpxxxx (xxx = numerical value). They are located in the following directory: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ – sound familiar?

Have a look at this screen shot – it’s messy!

Continue reading

SQL Server Recovering Data via a Hex Editor

I got bored (really bored) one weekend I decided to challenge myself.  I had corruption within a specific table (localised within a page) with no backups handy only an old image of the data files. The challenge being salvage data without attaching the data files.

Continue reading

Extreme situations require extreme commands

The title is adapted from a child-hood movie of mine and is my daily (database-related) WTF moment and it is my entry for this month’s T-SQL Tuesday found: http://www.pontop.dk/single-post/2017/03/07/Announcing-T-SQL-Tuesday-88-%E2%80%93-The-daily-database-related-WTF

tsql

Continue reading

CHECKDB and Page Restoring from SSMS

Did you know that you can run DBCC CHECKDB (WITH PHYSICAL_ONLY) and issue page restores from SSMS (SQL Server Management Studio). I never, and I probably will never because I rather use TSQL – however I have only just seen this ( yes it’s been around for a while ) and felt like blogging about it.

I clicked on restore > page.

ssms

I corrupted page 126 and look it knows about it!

ssmsmain

Yes it’s in bad shape – Level 24 severity.

SELECT *
 FROM [ZoraDB].[dbo].[people]

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa154e798; actual: 0xa144e788). It occurred during a read of page (1:126) in database ID 11 at offset 0x000000000fc000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

So SSMS is so good it will restore for you – and build the restore chain.

Hitting the SCRIPT button gives us:

USE [master]
RESTORE DATABASE [ZoraDB] PAGE='1:126'
 FROM  DISK = N'C:\SQLSERVER\ZoraDB.bak' WITH  FILE = 1,
 NORECOVERY,  NOUNLOAD,  STATS = 5

-- Tail Log
BACKUP LOG [ZoraDB]
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOFORMAT, NOINIT,  NAME = N'ZoraDB_LogBackup_2016-12-15_09-51-17', NOSKIP, NOREWIND, NOUNLOAD,  STATS = 5

RESTORE LOG [ZoraDB]
FROM  DISK = N'C:\SQLSERVER\ZoraLog.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

RESTORE LOG [ZoraDB]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH  NOUNLOAD,  STATS = 5

GO

ssms3

 SELECT *
 FROM [ZoraDB].[dbo].[people]

(21 row(s) affected)

How cool is that?

Detaching corrupt databases

Question – Can you detach a corrupt database?

Answer – IT DEPENDS!

More specifically it depends on the SQL Server version. SQL 2005 and prior it was possible, with the newer versions of SQL you will not be able to issue the command.

So let’s confirm this with SQL 2016.

Well I manufactured some corruption (I am starting to enjoy doing this too much for my own liking 😉 ) where the error log stated:

The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLMCA\MSSQL\DATA\AmazonUK.mdf’ is not a valid database file header

I try to detach the database. (By the way, there is no good reason to do this; corruption does not just go away)

USE [master]
GO
ALTER DATABASE [AmazonUK] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AmazonUK'
GO

SQL Server Management Studio returns

ALTER DATABASE statement failed.
Msg 3707, Level 16, State 2, Line 7
Cannot detach a suspect or recovery pending database. It must be repaired or dropped.

I totally forgot how helpful this is. I thought that I would refresh the minds of whoever reads this too.