metafor: Mart 2011

11 Mart 2011 Cuma

SQL SERVER DBCC

exec sp_who2
kill 54

DBCC PAGE (db, 1, 26839, 1)
DBCC CHECKDB(db, REPAIR_ALLOW_DATA_LOSS)
alter database db set MULTI_USER

;--------------

The error is pretty benign - it's not an actual corruption. It's saying that the PFS (Page Free Space) entry for that page has the wrong amount of free space noted - 100% instead of 0%. All this means is that the page may be picked up be a free-space scanner looking to insert data into a text page and it will find the page is actually full and move on to the next page.

All repair will do is set the correct free space setting, but you'll need to take the database into single-user mode (essentially offline) to do it.

These errors occured reasonably frequently on SQL 2000 as the algorithm to keep track of free space (only tracked for heap and text pages) had some holes in it. They should be fixed in 2005. Is this database recently upgraded from 2000, or is this the first time CHECKDB has been run since it was upgraded?

To use DBCC PAGE, you need to specify the database name too:

DBCC PAGE ('DATABASENAME', 1, 26839, 1)

should do it for you.

Thanks

-------------------------------------------------------

alter database db set SINGLE_USER;

DBCC CHECKDB('db', REPAIR_REBUILD);

alter database db set MULTI_USER;

http://msdn.microsoft.com/en-us/library/aa226363%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/ms188236.aspx

http://msdn.microsoft.com/en-us/library/ms345598.aspx

http://www.cryer.co.uk/brian/sqlserver/howto_start_single_user_mode.htm