Monitoring Databases  «Prev  Next»

Lesson 6Monitoring with dbcc command, continued
Objective Perform miscellaneous database monitoring with dbcc.

Miscellaneous Database Monitoring with dbcc

How to Perform

Dbcc checkfilegroup performs the same checks as dbcc checkdb, but it can not fix any data. Unless you specify NO_INFOMSGS, a report will be generated that lists of all the tables on the filegroup.

dbcc checkident

dbcc checkident command validates the current identity in a table, and it will fix the value if it needs to. You should use this if you are manually setting identity values in a table. If SQL Server is always automatically setting identity values, there is no reason to use this command. The current identity is the next value that will be generated for an identity column. The syntax is as follows:

( [{ 'filegroup' | filegroup_id}] [, NOINDEX] 

dbcc checkfilegroup

dbcc checkfilegroup is used to verify all of the tables on a file group. You can use it to make sure that a filegroup is valid before you perform a filegroup backup. Also, you can use it to check a database one filegroup at a time if it takes too long to check a whole database. The syntax is as follows: (Note that it uses parameters with which you are already familiar.)

The relationship between eBusiness and eCommerce
  1. The name of the table to check
  2. Tells SQL Server to only check the value without fixing it.
  3. Tells SQL Server to fix the identity, default value.
  4. This is the value to use when reseeding the table.
Name of the table to check
dbcc checkindent syntax
When you run dbcc checkident it will report on the current value for the next identity and the current value in the identity column. If the table is not corrupt, the values should match. Checking identity information: current identity value '1', current column value '1'.

dbcc dbreindex

The dbcc dbreindex command rebuilds one or more indexes on a table. This is equivalent to dropping the index and then creating the same index, but it does it in one step and does not require you to remove foreign key constraints while rebuilding primary keys.
The syntax is:

( [ 'database.owner.table_name' [, index_name [, fillfactor ] ] ]

The next lesson will cover how to monitor the transaction log.