What are the uses of DBCC commands? Can you give me some DBCC command options?(Database consistency check). Thank you for the answers.
asked
What are the uses of DBCC commands?
Yes..you are correct. I just posted this question to aware little bit in SQL Server to get more confidence in the Databases. Any how Thank you for your patience and answer. I'll go through that.
I noticed your other questions... I guess you are interested in SQL Server.
DBCC is sort of the Admin's Swiss Army Knife of database tools. It does quite a few things. Some are at the 'boundary' of SQL Server and the physical server isntallation and environment and some are there to ensure data consistency and validity at the physical level.
From the web, this is the current list of DBCC commands...
DBCC CHECKALLOC - Check consistency of disk allocation.
DBCC CHECKCATALOG - Check catalog consistency
DBCC CHECKCONSTRAINTS - Check integrity of table constraints.
DBCC CHECKDB - Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP - Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT - Check identity value for a table.
DBCC CHECKTABLE - Check integrity of a table or indexed view.
DBCC CLEANTABLE - Reclaim space from dropped variable-length columns.
DBCC dllname - Unload a DLL from memory.
DBCC DROPCLEANBUFFERS - Remove all clean buffers from the buffer pool.
DBCC FREE... CACHE - Remove items from cache.
DBCC HELP - Help for DBCC commands.
DBCC INPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC OPENTRAN - Display information about recent transactions.
DBCC OUTPUTBUFFER - Display last statement sent from a client to a database instance.
DBCC PROCCACHE - Display information about the procedure cache
DBCC SHOW_STATISTICS - Display the current distribution statistics
DBCC SHRINKDATABASE - Shrink the size of the database data and log files.
DBCC SHRINKFILE - Shrink or empty a database data or log file.
DBCC SQLPERF - Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE... - Enable or Disable trace flags
DBCC UPDATEUSAGE - Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS - Return the SET options currently active
I'm not sure it's 100% complete and then there are some undocumented commands, too.
Probably the two I find most useful are DBCC SHRINKDATABASE and DBCC SHRINKFILE. SQL Server databases are implemented in files on the file system and they can grow automatically. After a major cleanup (data purge, for example), I often shrink them somewhat, to free up space for other purposes. Usually, I use SHRINKFILE, it gives better control, as I recall.
The INPUTBUFFER command is useful to snoop on a process that's giving you trouble (although there's a newer, builtin function for this... but it's a bit harder to use and I can never remember the name when I want it). I once used one of the commnds (I forget which) to clear the cache, which stopped a pathologically bad query plan from being reused (that was in SQL 2000, some years ago, and occurred just once and we adjusted indexing and querying in a particular problem to try and prevent a repeat - this appears successful). The TRACEON is useful for setting certain traceflags for troubleshooting.
Most of the rest, I haven't used since SQL Server 6.5... we've either been very lucky or SQL Server's data management is very solid; we've had no low-level or physical data corruption in many years.
Now, users corrupting data... that's another story. That happens all the time. :-(
You need them, for example when the transaction log uses all space on a drive, which means you can't insert anything into the db...
