Labels

How to list Suspect Pages in database


On Sunday evening we got an opportunity to run DBC checkdb on one of our suspected database (Of course UAT). And as usual we started checkdb from one of sessions on SQL box query analyzer. By the end of the day we realize it will take some more time to complete and we were expecting that by next day some one will kill our connection , while searching on internet we came to know about a table which gives us report of corruption “msdb..suspect_pages”


/******************************************************/
SELECT * FROM msdb..suspect_pages

database_id
file_id
page_id
event_type
error_count
last_update_date






/******************************************************/
This report will help us in one more way. If database is huge in size Instead of restoring whole database  we can just restore corrupt pages from latest backup

/******************************************************/
RESTORE DATABASE yourDBNameHere 

PAGE = 'fileid:pageid,fileid:pageid,etc'  -- e.g. 1:5224,1:5225,etc 

FROM DISK = 'D:\SQLBackups\yourDbNameHere_lastFull.BAK' 

WITH 

NORECOVERY 

GO
/******************************************************/
Note : Run restore with no recovery an then we can restore all t-log backups and of course last one with recovery

1 comment:
Write comments

Please do not enter spam links

Services

More Services