Monday, February 15, 2016

Useful DBCC Commads


1. DBCC CHECKDB

Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
Validates link-level consistency between table metadata and file system directories and files when storingvarbinary(max) data in the file system using FILESTREAM.

Syntax:

DBCC CHECKDB
[
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
]

Example:
-- Check the current database.
DBCC CHECKDB;
GO
-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO


2. DBCC CHECKTABLE

Check the integrity of all the pages and structures that make up the table or indexed view.

Syntax:

DBCC CHECKTABLE
(
table_name view_name
{ NOINDEX | index_id }
|{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
]
)
[ WITH
{ ALL_ERRORMSGS ]
EXTENDED_LOGICAL_CHECKS ]
NO_INFOMSGS ]
TABLOCK ]
ESTIMATEONLY ]
{ PHYSICAL_ONLY | DATA_PURITY } ]
}
]

Example:

USE AdventureWorks;
GO
DBCC CHECKTABLE ("HumanResources.Employee");
GO


3. DBCC CHECKALLOC

Check the consistency of disk space allocation structures for a specified database.

Syntax:

DBCC CHECKALLOC
[
( database name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
)
[ WITH
{
[ ALL_ERRORMSGS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
}
]
]

Example:
-- Check the current database.
DBCC CHECKALLOC;
GO
-- Check the AdventureWorks database.
DBCC CHECKALLOC (AdventureWorks);
GO


4. DBCC SHOWCONTIG

Display fragmentation information for the data and indexes of the specified table or view.

Syntax:

DBCC SHOWCONTIG
[ (
{ table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
) ]
[ WITH
{
[ , [ ALL_INDEXES ] ]
[ , [ TABLERESULTS ] ]
[ , [ FAST ] ]
[ , [ ALL_LEVELS ] ]
[ NO_INFOMSGS ]
}
]

Example:

USE AdventureWorks;
GO
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;
GO


5. DBCC DBREINDEX

Rebuild one or more indexes for a table in the specified database.

Syntax:

DBCC DBREINDEX
(
table_name
[ , index_name [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]

Example:
USE AdventureWorks;
GO
DBCC DBREINDEX ("HumanResources.Employee", " ", 70);
GO


6. DBCC INDEXDEFRAG

Defragment indexes of the specified table or view.

Syntax:

DBCC INDEXDEFRAG
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , { index_name | index_id } [ , { partition_number | 0 } ] ]
)
[ WITH NO_INFOMSGS ]

Example:

DBCC INDEXDEFRAG (AdventureWorks, "Production.Product", PK_Product_ProductID)
GO


7. DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan.

Syntax:

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]

Example:

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH HISTOGRAM;
GO


8. DBCC DROPCLEANBUFFERS

Remove all clean buffers from the buffer pool.

Syntax:

DBCC DROPCLEANBUFFERS [ WITH NO_INFOMSGS ]

Example:

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS


9. DBCC OPENTRAN

Display information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

Syntax:

DBCC OPENTRAN
[
( [ database_name | database_id | 0 ] ) ]
{ [ WITH TABLERESULTS ]
[ , [ NO_INFOMSGS ] ]
}
]

Example:

DBCC OPENTRAN


10. DBCC SQLPERF()

Provide transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

Syntax:

DBCC SQLPERF
(
[ LOGSPACE ]
|
[ "sys.dm_os_latch_stats" , CLEAR ]
|
[ "sys.dm_os_wait_stats" , CLEAR ]
)
[WITH NO_INFOMSGS ]

Example:

DBCC SQLPERF(LOGSPACE)


11. DBCC TRACEON

Enable the specified trace flags.

Syntax:

DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

Example:

DBCC TRACEON (3205);
GO


12. DBCC TRACEOFF

Disable the specified trace flags.

Syntax:

DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

Example:

DBCC TRACEOFF (3205);
GO


13. DBCC UPDATEUSAGE

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Syntax:

DBCC UPDATEUSAGE
( { database_name | database_id | 0 }
[ , { table_name | table_id | view_name | view_id }
[ , { index_name | index_id } ] ]
) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ]

Example:

DBCC UPDATEUSAGE (0);
GO


14. DBCC TRACESTATUS

Display the status of trace flags.

Syntax:

DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] )
[ WITH NO_INFOMSGS ]

Example:

DBCC TRACESTATUS (2528, 3205);
GO


15. DBCC SHRINKDATABASE

Shrink the size of the data and log files in the specified database.

Syntax:

DBCC SHRINKDATABASE
( database_name | database_id | 0
[ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
[ WITH NO_INFOMSGS ]

Example:

DBCC SHRINKDATABASE (AdventureWorks, TRUNCATEONLY);


16. DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.

Syntax:

DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]

Example:

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO


17. DBCC CHECKIDENT

Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Syntax:

DBCC CHECKIDENT
(
table_name
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

Example:

USE AdventureWorks;
GO
DBCC CHECKIDENT ("HumanResources.Employee");
GO


18. DBCC CLEANTABLE

Reclaims space from dropped variable-length columns in tables or indexed views.

Syntax:

DBCC CLEANTABLE
(
{ database_name | database_id | 0 }
, { table_name | table_id | view_name | view_id }
[ , batch_size ]
)
[ WITH NO_INFOMSGS ]

Example:

DBCC CLEANTABLE (AdventureWorks,"Production.Document", 0)
WITH NO_INFOMSGS;
GO


19. DBCC USEROPTIONS

Returns the SET options active (set) for the current connection.

Syntax:

DBCC USEROPTIONS
[ WITH NO_INFOMSGS ]

Example:

DBCC USEROPTIONS;


20. DBCC CHECKCONSTRAINTS

Check the integrity of a specified constraint or all constraints on a specified table in the current database.

Syntax:

DBCC CHECKCONSTRAINTS
[
(
table_name | table_id | constraint_name | constraint_id
)
]
[ WITH
[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
[ , ] [ NO_INFOMSGS ]
]

Example:

USE AdventureWorks;
GO
DBCC CHECKCONSTRAINTS ("Production.CK_ProductCostHistory_EndDate");
GO


21. DBCC CHECKCATALOG

Check for catalog consistency within the specified database. The database must be online.

Syntax:

DBCC CHECKCATALOG
[
(
database_name | database_id | 0
)
]
[ WITH NO_INFOMSGS ]

Example:

DBCC CHECKCATALOG (AdventureWorks);
GO


22. DBCC CHECKFILEGROUP

Check the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.

Syntax:

DBCC CHECKFILEGROUP
[
[ ( { filegroup_name | filegroup_id | 0 }
[ , NOINDEX ]
) ]
[ WITH
{
[ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , PHYSICAL_ONLY ]
}
]
]

Example:

USE AdventureWorks;
GO
DBCC CHECKFILEGROUP;
GO

Could not obtain exclusive lock on database ‘model’.

Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later

Error:

TITLE: Microsoft SQL Server Management Studio

------------------------------
Create failed for Database 'Test'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not obtain exclusive lock on database 'model'. Retry the operation later.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=1807&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------





Solution:

Solution 1:

            Disconnect and Reconnect your SQL Server Management Studio’s session. Your error will go away.

Solution 2:


Root Cause: Root cause of this error may be one of the following reason


1. Somebody exclusively open the Model database in different session

2. If more than one create database statements attempt to run simultaneously

How to identity:


Use master 

GO
IF EXISTS(SELECT request_session_id  FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('Model'))
 PRINT 'Model Database being used by some other session'
ELSE
 PRINT 'Model Database not used by other session'

So we can identify that whether the Model database being execlusively used by any session.., If you found anybody using the Model database execlusively, You can identify what kind of statement being used by the session...using the script given below

SELECT request_session_id  FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID('Model')


The script will return the session ID (i.e: 52)

We have to identity what kind of query/activity being performed by this session(52).

DBCC InputBuffer(52) 


    EventInfo column returns the query performed, Based on that, you have to decide whether the Session ID(52) can be Aborted/ Not. If you want to abort the session(52),
run the command 
    Kill 53 

    Now you can try to create a new Database..!  

COMMON SQL SERVER BACKUP FAILURE ERRORS AND ISSUES

  One of the most common task for a DBA’s are to perform installation of new SQL Server versions and installing patches. Most often or not e...