No matter how reliable your database seems to be, you always have issues on the performance grounds. More interestingly, I found out that many performance issues relate to page corruption in SQL Server. It would be good to understand here what exactly is meant by ‘BAD_PAGE_PROCESS’. It usually occurs when the background suspect page logger stops running after every five seconds because of a large number of suspect pages. If you want to checkout suspect pages, run the following T-SQL command:
“select * from msdb.dbo.suspect_pages”
I took the help of DBCC CHECKDB on a corrupt SQL database to find out the actual issue and whether it is able to resolve it. Ran the following command:
“DBCC CHECKDB () WITH ALL_ERRORMSGS, NO_INFOMSGS”
Got errors similar to this one:
“Table error: Allocation page (1:1002912) has invalid PFS_PAGE page header values. Type is 0. Check type, alloc unit ID and page ID on the page.”
The PFS pages can neither be deleted nor reconstructed as there is no easy way to examine which pages are allocated or not.
Now, I tried to view the contents of the page by using the ‘DBCC PAGE’ command as follows:
“dbcc traceon(3604)
go
dbcc page(,1,1002854,0)”
After looking at the content of the page, I found that there was some corruption in the next page pointers. This seemed to be a bit weird as initially the next page pointer was like:
“m_nextPage = (1: 1002855)”
When I decided to look at the next page and ran:
“dbcc traceon(3604)
go
dbcc page('GBLDataWarehouse',1,1002855,0)
go”
It was now pointing to page 0:
“m_nextPage = (0:0)” & “m_prevPage = (0:0)”
Finally, used a recent backup to restore the database and got it up and running once again. But again, it was wise to see where the issue occurred.
No comments:
Post a Comment