DBCC -Database Console Commands

 DBCC TRACEON (3205)---Enables the specified trace flags.

DBCC HELP('?')

DBCC activecursors

--DBCC OPENTRAN

 CREATE TABLE T1(Col1 int, Col2 char(3));

GO

BEGIN TRAN

INSERT INTO T1 VALUES (101, 'abc');

GO

DBCC OPENTRAN;

ROLLBACK TRAN;

GO

DROP TABLE T1;

GO

--before Rollback Trnsaction[Display Message]

 Transaction information for database 'Northwind'.

Oldest active transaction:

    SPID (server process ID): 52

    UID (user ID) : -1

    Name          : user_transaction

    LSN           : (49:495:1)

    Start time    : Nov 28 2012 11:05:03:940AM

    SID           : 0x01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

After Rollback Trnsaction

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--DBCC addinstance (objectname, instancename)

--DBCC adduserobject (name)

--DBCC addextendedproc (function_name, dll_name)--Unloads the specified extended stored procedure DLL from memory.

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for Microsoft® SQL Server™ 2000. These statements check the physical and logical consistency of a database. Many DBCC statements can fix detected problems.

 

USE northwind

GO

EXEC sp_addextendedproc xp_hello, 'D:\NHibernate.dll'

DBCC addextendedproc ( dataset,xp_hello)

--DBCC CHECKALLOC

DBCC CHECKALLOC

--DBCC TABLE CHECK

use Northwind

select * from Northwind.dbo.orders

DBCC CHECKTABLE("Northwind.dbo.orders")

DBCC CHECKALLOC --Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKTABLE("Northwind.dbo.orders") --Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKCATALOG --Checks for catalog consistency within the specified database. The database must be online.

sp_MSforeachtable @command1=" dbcc checktable ('?')"

EXEC sp_MSforeachtable 'SELECT ''?'''

Browse More Articles

Tweet

Written by Test

We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.