Hi folks,
You might have seen my blog posts which was all about DBCC CHECKDB and how to repair the database using DBCC CHECKDB.
But I have tried to focus this blog on the various SQL Server DBCC Commands:
- DBCC CHECKALLOC (‘database’):
- A subset of DBCC CHECKDB that checks the allocation of all pages in the database.
- The report is very detailed, i.e., listing the extent count (64 KB or 8 data pages) and data-page usage of every table and index in the database.
- DBCC CHECKFILEGROUP (‘filegroup’):
- It is very much similar to DBCC CHECKDB but specified to filegroup only.
- DBCC CHECKTABLE (‘table’):
- It performs physical and logical integrity checks on the table and all its non-clustered indexes.
- DBCC CLEANTABLE (‘database’, “table”):
- It reclaims space from a varchar, nvarchar, text, or ntext column that was dropped from the table.
- DBCC CHECKCATALOG (‘database’):
- It checks the integrity of the system tables within a database.
- It also ensures referential integrity among tables, views, columns, and data types.
- It will report any errors.
- DBCC CHECKCONSTRAINTS (‘table’, ‘constraint’):
- It examines the integrity of a specific constraint, or all constraint for a table.
- It essentially generates and executes a query to verify each constraint, and reports any error found.
- If no issues are being detected, nothing will be reported.
- DBCC CHECKIDENT (‘table’):
- It verifies the consistency of the current-identity column value and the identity column for a specific table.
- If the problem exists, then the next value is updated to correct any error.
- If there happens a case that the identity column is being broken, then the new identity value will violate a primary key or unique constraint, and new rows cannot be added to the table.
- The following example will help you to understand this:
USE AdventureWorks2008R2
DBCC CHECKIDENT('HumanResources.Department')
If you are executing a command DBCC CHECKDB, then you don’t need to issue DBCC CHECKALLOC,DBCC CHECKTABLE, and DBCC CHECKCATALOG separately, it will run along with it.
Well, this was all about DBCC Commands.
And also comments on this!!
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
hi,
i found this post to be pretty much helpful, but plz add some more commands and examples as well for better understanding. 🙂
What does one do if theyb have a database they can’t attach to SQL server. I have such a problem and when i try to attach it I get an error. So how can I fix/repair the DB so that I can attach it. Your instructions above seem to work only if the DB is already attached to SQL.
Hi Jerry,
What error are you getting?
Incase you want to attach this DB and let SQL do a auto recovery on this DB follow the below mentioned steps:
1. create a new DB with the same name on your SQL instance.
2. Stop SQL Services
3. Replace the physical files of newly created DB with the files you want to attach
4. Start SQL Services
5. Let SQL do the auto-recovery and monitor the progress via – Error Log.
Do let us know the status.