1.

DBCC CHECKALLOCDBCC
CHECKALLOC checks page usage and allocation in the database. Use this command
if allocation errors arefound for the database. If you run DBCC CHECKDB, you do not
need to run DBCC CHECKALLOC, as DBCCCHECKDB includes the same checks
(and more) that DBCC CHECKALLOC performs.

2.DBCC CHECKCATALOG
This command checks for consistency in and between system tables. This command is
not executed within theDBCC CHECKDB command, so running this command weekly
is recommended.

3.DBCC CHECKCONSTRAINTSDBCC
CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.Use it if
you suspect that there are rows in your tables that do not meet the constraint or
CHECK constraint rules.

4.DBCC CHECKDB
A very important DBCC command, DBCC CHECKDB should run on your SQL Server
instance on at least a weeklybasis. Although each release of SQL Server reduces
occurrences of integrity or allocation errors, they still do happen.DBCC CHECKDB
includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE.
DBCC CHECKDBcan be rough on concurrency, so be sure to run it at off-peak times.

5.DBCC CHECKTABLE
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it
is performed at the table level, not thedatabase level. DBCC CHECKTABLE verifies
index and data page links, index sort order, page pointers, indexpointers, data page
integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default, but
can use theTABLOCK option to acquire a shared table lock. CHECKTABLE also
performs object checking using parallelism bydefault (if on a multi-CPU system).
6.DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC
CHECKFILEGROUP checks the specifiedfilegroup for allocation and structural
issues. If you have a very large database (this term is relative, and higher endsystems
may be more apt at performing well with multi-GB or TB systems ) , running DBCC
CHECKDB may be time-prohibitive.If your database is divided into user defined
filegroups, DBCC CHECKFILEGROUP will allow you to isolate yourintegrity checks,
as well as stagger them over time.

7.DBCC CHECKIDENTDBCC
CHECKIDENT returns the current identity value for the specified table, and allows
you to correct the identityvalue if necessary.

8.DBCC DBREINDEX
If your database allows modifications and has indexes, you should rebuild your
indexes on a regular basis. Thefrequency of your index rebuilds depends on the level
of database activity, and how quickly your database andindexes become fragmented.
DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like
DBCCCHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running
DBREINDEX during peak activity times cansignificantly reduce concurrency.

9.DBCC INDEXDEFRAG
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with
SQL Server 2000. This DBCCcommand, unlike DBCC DBREINDEX, does not hold
long term locks on indexes. Use DBCC INDEXDEFRAG forindexes that are not very
fragmented, otherwise the time this operation takes will be far longer then running
DBCCDBREINDEX. In spite of it's ability to run during peak periods, DBCC
INDEXDEFRAG has had limited effectivenesscompared to DBCC DBREINDEX (or
drop/create index).

10.DBCC INPUTBUFFER
The DBCC INPUTBUFFER command is used to view the last statement sent by
the client connection to SQL Server.When calling this DBCC command, you designate
the SPID to examine. (SPID is the process ID, which you can getfrom viewing current
activity in Enterprise Manager or executing sp_who. )
11.DBCC OPENTRAN
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest
running transaction for the selecteddatabase. The DBCC command is very useful
for troubleshooting orphaned connections (connections still open onthe database but
disconnected from the application or client), and identification of transactions missing
a COMMIT or ROLLBACK. This command also returns the oldest distributed
and undistributed replicated transactions, if any existwithin the database. If there are
no active transactions, no data will be returned. If you are having issues with
yourtransaction log not truncating inactive portions, DBCC OPENTRAN can show if
an open transaction may be causingit.

12.DBCC PROCCACHE
You may not use this too frequently, however it is an interesting DBCC command to
execute periodically, particularlywhen you suspect you have memory issues. DBCC
PROCCACHE provides information about the size and usage ofthe SQL Server
procedure cache.

13.DBCC SHOWCONTIG
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific
table and its indices. ThisDBCC command is critical to determining if your table or
index has internal or external fragmentation. Internalfragmentation concerns how
full an 8K page is.When a page is underutilized, more I/O operations may be
necessary to fulfill a query request than if the page wasfull, or almost full.External
fragmentation concerns how contiguous the extents are. There are eight 8K pages per
extent, making eachextent 64K. Several extents can make up the data of a table or
index. If the extents are not physically close to eachother, and are not in order,
performance could diminish.

14.DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database. Avoid
executing this command during busy periods in production, as it has a negative
impact on I/O and userconcurrency. Also remember that you cannot shrink a database
past the target percentage specified, shrink smallerthan the model database, shrink a
file past the original file creation size, or shrink a file size used in an
ALTERDATABASE statement.
15.DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files.
(Use sp_helpfile to gather databasefile ids and sizes).

16. DBCC TRACEOFF, TRACEON, TRACESTATUS

Trace flags are used within SQL Server to temporarily enable or disable specific SQL
Server instance characteristics.Traces are enabled using the DBCC TRACEON
command, and disabled using DBCC TRACEOFF. DBCCTRACESTATUS is used to
displays the status of trace flags. You'll most often see TRACEON used in
conjunctionwith deadlock logging (providing more verbose error information).

17.DBCC USEROPTIONS
Execute DBCC USEROPTIONS to see what user options are in effect for your specific
user connection. This can behelpful if you are trying to determine if you current user
options are inconsistent with the database options.

18. DBCC SQLPERF(LOGSPACE)

- To check the current size of log(.LDF) files of all the databases.(in case of disk space
issue or on log file autogrowth error)

19.DBCC OPENTRAN
- To check the active transaction(s) of the current database.

20. DBCC ERRORLOG:

If you rarely restart SQL Server service, resulting server log gets very large and takes
a longtime toload and view. You can truncate (essentially create a new log) the
Current Server log by this.You can accomplish the same thing using this stored
procedure: sp_cycle_errorlog.

21.DBCC DROPCLEANBUFFERS:
To remove all the data from SQL Server's data cache (buffer) between
performancetests to ensurefair testing. Fyi, this command only removes clean buffers,
not dirty buffers.So, before running the DBCC DROPCLEANBUFFERS command,
you may first want to run the CHECKPOINTcommand.Running CHECKPOINT will
write all dirty buffers to disk. So, when you run DBCC DROPCLEANBUFFERS,you
can be assured that all data buffers are cleaned out, not just the clean ones.21.
DBCC updatestaistics
Database Information Procedures:

1. sp__helpdb: Provides general information about databases.

2. sp__helpdevice: Breaks down database devices into a detailed report.
3. sp__helpgroup: Lists groups in the database by access level.
4. sp__helpindex: Shows indexes by table.
5. sp__helpsegment: Provides information about database segments.
6. sp__helprotect: Offers simple protection information for the databases.
7. sp__helpuser: Lists users in the current database by group, including aliases.
8. sp__helptext: Shows comments with line breaks.
9. sp__helpdbdev: Shows how databases use devices.

Dependency and Structure Procedures:

10. sp__depends: Better version of sp_depend.
sp__syntax: Works on any procedure to give you syntax.

Lock and Process Procedures:

12. sp__lock: Provides lock information.
sp__whosp_who: A version that fits on a page of sp_who.
System Administrator Procedures:

1. sp__block: Lists blocking processes.

2. sp__dbspace: Summarizes current database space information.
3. sp__dumpdevice: Lists dump devices.
4. sp__diskdevice: Lists disk devices.
5. sp__helplogin: Shows logins and remote logins to servers.
6. sp__helpmirror: Shows mirror information.
7. sp__segment: Provides segment information.
8. sp__server: Generates a server summary report.
9. sp__stat: Gives basic server performance information.
10. sp__vdevno: Lists who's who in the device world.

Database Administration Procedures:

1. sp__badindexlist: Lists badly formed indexes (allowing nulls) or those

needing statistics.

2. sp__collist: Lists all columns in databases.

3. sp__find_missing_index: Finds keys that do not have associated indexes.

4. sp__flowchart: Makes a flowchart of procedure nestings.

5. sp__groupprotect: Provides permission info by group.

6. sp__indexspace: Reports space used by indexes in databases.

7. sp__id: Gives information on who you are and which database you are in.

8. sp__noindexlist: Lists tables without indexes.

9. sp__helpcolumn: Shows columns for a given table.

10. sp__helpdefault: Lists defaults (part of object list).

11. sp__helpobjectlist: Lists objects, including tables, procedures, rules, triggers,

and views.

12. sp__helpproclist: Lists procedures (part of object list).

13. sp__helprulelist: Lists rules (part of object list).

14. sp__helptablelist: Lists tables (part of object list).

15. sp__helptrigger: Lists triggers (part of object list).

16. sp__helpview: Lists views (part of object list).

17. sp__objprotect: Provides permission info by object.

18. sp__read_write: Lists tables by the number of procedures that read, write, or

do both.

19. sp__trigger: Provides a useful synopsis report of the current database's

trigger schema.

20. sp__who: Lists active processes.

Audit Procedures:

1. sp__auditsecurity: Performs a security audit on servers.

2. sp__auditdb: Audits the current database for potential problems.

3. sp__checkkey: Generates a script for identifying referential integrity

problems using key information.

4. Reverse Engineering Procedures:

5. 24. sp__revalias: Gets an alias generation script for current databases.

6. sp__revdb: Gets a database generation script for servers.

7. sp__revdevice: Gets a device generation script for servers.

8. sp__revgroup: Gets a group generation script for current databases.

9. sp__revindex: Gets an index generation script for current databases.

10. sp__revlogin: Gets a login generation script for servers.

11. sp__revmirror: Gets a mirror generation script for current databases.

12. sp__revsegment: Gets a segment generation script for current

databases.

13. sp__revtable: Gets a table generation script for current databases.

14. sp__revuser: Gets a user generation script for the current database.
Other Procedures:

1. 34. sp__bcp: Creates a Unix script to bcp in/out databases.

2. sp__date: Provides information about date styles.

3. sp__iostat: Loops n times showing active processes only.

4. sp__grep: Searches for patterns.

5. sp__isactive: Shows information about a single active process.

6. sp__ls: Lists specific objects.

7. sp__quickstats: Provides a quick dump of server summary information.

8. sp__whoactive: Shows information about who is active.