October 28, 2014 at 8:17 am
Hi All,
In the suspect_pages table of msdb i see 30 records and all the records are with the databaseid 2 (tempdb). And the records have last_update_date value as 2014-10-25 02:08:53.510 and the event type value as 1. I ran DBCC CHECKDB WITH ALL_ERRORMSGS on tempdb on 28/10/2014 00:15:32. After running this i checked the suspect_pages table and there is no change in the values of event_type. From one of the msdn links i read "After a listed page is fixed by a restore or a repair operation, the operation updates the suspect_pages row to indicate that the page is repaired (event_type = 5) or restored (event_type = 4).". I am copying the output below
.BCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified. [SQLSTATE 01000] (Message 5232) DBCC results for 'sys.sysrscols'. [SQLSTATE 01000] (Message 2536) There are 791 rows in 10 pages for object "sys.sysrscols". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysrowsets'. [SQLSTATE 01000] (Message 2536) There are 119 rows in 2 pages for object "sys.sysrowsets". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysallocunits'. [SQLSTATE 01000] (Message 2536) There are 137 rows in 3 pages for object "sys.sysallocunits". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysfiles1'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysfiles1". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syspriorities'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.syspriorities". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysfgfrag'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysfgfrag". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysphfg'. [SQLSTATE 01000] (Message 2536) There are 1 rows in 1 pages for object "sys.sysphfg". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysprufiles'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object "sys.sysprufiles". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysftinds'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysftinds". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysowners'. [SQLSTATE 01000] (Message 2536) There are 15 rows in 1 pages for object "sys.sysowners". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysprivs'. [SQLSTATE 01000] (Message 2536) There are 132 rows in 1 pages for object "sys.sysprivs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysschobjs'. [SQLSTATE 01000] (Message 2536) There are 82 rows in 4 pages for object "sys.sysschobjs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syscolpars'. [SQLSTATE 01000] (Message 2536) There are 635 rows in 16 pages for object "sys.syscolpars". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysnsobjs'. [SQLSTATE 01000] (Message 2536) There are 1 rows in 1 pages for object "sys.sysnsobjs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.syscerts'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.syscerts". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysxprops'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysxprops". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysscalartypes'. [SQLSTATE 01000] (Message 2536) There are 34 rows in 1 pages for object "sys.sysscalartypes". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.systypedsubobjs'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.systypedsubobjs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysidxstats'. [SQLSTATE 01000] (Message 2536) There are 187 rows in 5 pages for object "sys.sysidxstats". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysiscols'. [SQLSTATE 01000] (Message 2536) There are 324 rows in 2 pages for object "sys.sysiscols". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysbinobjs'. [SQLSTATE 01000] (Message 2536) There are 23 rows in 1 pages for object "sys.sysbinobjs". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysaudacts'. [SQLSTATE 01000] (Message 2536) There are 0 rows in 0 pages for object "sys.sysaudacts". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysobjvalues'. [SQLSTATE 01000] (Message 2536) There are 170 rows in 33 pages for object "sys.sysobjvalues". [SQLSTATE 01000] (Message 2593) DBCC results for 'sys.sysclsobjs'. [SQLSTATE 01000] (Message 2536) The... The step succeeded.
From the output i observe the checkdb action completed successfully with out any errors but my question is what about the records in suspect_pages table. How do the event_type value updated. do i have to manullay remove the values. Please suggest. Thanks for your Advise.
October 28, 2014 at 8:31 am
I don't see any errors in your CHECKDB output.
In order to fix your tempdb (if you can afford a 2 minutes downtime) stop the instance, delete all tempdb data and log files, then start the instance again.
However, it doesn't seem like you need to do that.
-- Gianluca Sartori
October 28, 2014 at 11:39 am
Hi Gianluca,
Thanks for your reply. We can not afford any downtime for this as this is not affecting any users. And we also do not have any issues with this. This is for my information. 2 Days back there was corrupt pages and now there are no corrupt pages. What happened to those corrupt pages. Was tempdb really got corrupt. If yes, how do they repaired. Why is suspect_pages table not updated when DBCC CHECKDB reading the same pages with out any errors.
Thanks all, for your replies
October 28, 2014 at 3:08 pm
Tempdb contains internal tables and user temp tables. Both type of tables are by nature volatile.
Probably the corrupt pages were part of some object that got dropped.
Do you still have the list of those pages?
Do they still exist?
-- Gianluca Sartori
October 30, 2014 at 4:52 am
Hi Gianluca,
It seems, the corrupt pages are now moved from tempdb. That may be because of shrink task on tempdb that executes weekly. I have the corrupt pages pageid numbers. And i checked the page status using DBCC PAGE and i got the message as the page is out of the boundary of this database. So i concluded this may moved out of this database because of shrink task. With this i have new doubt. Does the disk hosting tempdb got corrupt. In future if tempdb again allocated with those pages we can expect the same issue may repeat. Am i right? In that case how can i repair the disk hosting tempdb. do we have to format that disk or do we have any other alternative. Please suggest.
Thanks
Lakshman
October 31, 2014 at 2:22 am
lakshmanchand.15 (10/30/2014)
That may be because of shrink task on tempdb that executes weekly.
Shrinking tempdb should be an exceptional operation, not something you do every week. Why are you shrinking tempdb?
Does the disk hosting tempdb got corrupt. In future if tempdb again allocated with those pages we can expect the same issue may repeat. Am i right? In that case how can i repair the disk hosting tempdb. do we have to format that disk or do we have any other alternative.
Check the disk with windows tools. I'm under the impression that the disk has no issues at all, but you never can tell.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply