SQL server system table corruption

Recently I came across a sage database that has been corrupted. When running DBCC CHECKDB command I was getting the following errors:

There are 226 rows in 1 pages for object "sys.sysprivs".

DBCC results for 'sys.sysschobjs'.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 66 and 67.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 72 and 73.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 93 and 94.

There are 4147 rows in 93 pages for object "sys.sysschobjs".

CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysschobjs' (object ID 34).

Normally the re-indexing should have fixed this type of issue when the base table is user table but it didn't. To fix the issue I have to perform the following commands

use DatabaseName

 go

ALTER DATABASE DatabaseName  SET SINGLE_USER

go

DBCC CHECKDB('DatabaseName', REPAIR_REBUILD)

 go

The outcome from the above command was

Repair: The Nonclustered index successfully rebuilt for the object "sys.sysschobjs, nc1" in database "FCE_Sage".

Msg 8945, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2 will be rebuilt.

        The error has been repaired.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 66 and 67.

        The error has been repaired.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 72 and 73.

        The error has been repaired.

Msg 2511, Level 16, State 1, Line 1

Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 93 and 94.

        The error has been repaired..

Latest version of SQL server

To get an idea of what the latest version of SQL server will be, you can ask a SQL guru @Ask SQL Guru

Cannot shrink tempdb

How to shrink tempDB

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

select * from sys.dm_tran_locks
where resource_database_id= 2

select * from sys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0

SELECT * FROM sys.all_objects
where is_ms_shipped = 0

DBCC FREEPROCCACHE
BCC SHRINKFILE ('tempdev', 1024)

Purge all data in database

Recently I have been tasked to purge all data in the database. When trying to delete data from tables which have foreign key relationship the standard purge routine will fail or you have to spend time to get which table to purge first. Also there is an issue of identity as when you delete data from the table the identity will not go back to starting point. After few research, I have got the best method which is shown below. 1. Disable all constraints in the database using the following command exec sp_msforeachtable "ALTER TABLE ? nocheck contraint all" 2. Delete all data in the database exec sp_MSForEachTable "DELETE FROM ?" 3. Enable all constraints exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 4. Reset tables with identity. Note when you run the following it will give you error if the table doesn't have identity column. Ignore the errors. exec sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

SQL server 2012 lauch date

New version of SQL server 2012 launch date is set for 7th of March. The offering will be in three main edition. Details of edition feature comparison can be found at Feature comparison

Enterprise Edition (EE) will be licensed based on compute capacity measured in cores

Business Intelligence (BI) Edition will be available in the Server + CAL model, based on users or devices

Standard Edition (SE) offers both licensing models to address basic database workloads

There will be Web Developer and Express versions but this is not detailed enough and we just have to wait and see.

SQL Server 2012 will continue to offer two licensing options one is based on
one based on computing power and the other is based on based on users or devices. The way the power is measured is not processor rather core based.