DBCC Commands Daily using Commands and Examples

DBCC Commands Daily using Commands and Examples 

1. xp_fixeddrives  ----- how much free space for all the drives. 

2. dbcc sqlperf(logspace)--- how much logspace and how much logspace used for all the database. 

3. dbcc checkdb(satyamdb) --- checkdb found allocation errors and consistency errors in particular database. 

4. dbcc updateusage(satyamdb,'dbo.t2') -- report and correct page and row count inaccuracies in catlog views. 
use satyamdb

5.DBCC SHOWCONTIG('t3','t3_indexs') -- Displays fragmentation information for the data and indexes of the specified table.
 in this result logical scan fragmentation and extent scan fragmentation values 0 -- 5 ignore, 5 -- 30 reorganization , 30+ rebuild the indexs. 

6.dbcc indexdefrag('satyamdb','t3','t3_indexs') -- command defragments the index rather than rebuilding it. This command is normally used when time is an issue, such as in cases of very large databases. What’s normally done here is that this command is run during the week, and the DBCC DBREINDEX is run once a week.These commands perform such tasks as enabling row-level locking or removing a dynamic-link library (DLL) from memory. 

7.DBCC OPENTRAN(satyamdb)with tableresults -- Display information about the oldest active transaction and the oldest replicated transactions. 

8. dbcc dbreindex('t3','t3_indexs',0) --- default fillfactor is 0 and rebuilds the indexes on a database.A fill factor is how much room to fill up the index before SQL allocates more space to the index. 
sp_who -- it display all the spid values and particular databases.

9.dbcc inputbuffer (59) -- Display the last statement sent from a client to a database instance. 

10. dbcc outbuffer(59) 

11. dbcc traceon(1204) -- Enable or Disable trace flags 


12. DBCC DROPCLEANBUFFERS -- Remove all clean buffers from the buffer pool.