TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it. This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however, shrink database does shrink the log file. Not shrinking your database is one of the more counter intuitive …
DBCC ShrinkDatabase – I want to shrink my database. Read more »
When running DBCC CHECKDB, you will often times get screens of information back stating that the database is all okay, and that it checked every table. This can be quite tedious when you are trying to search through thousands of lines of output to see where the real error is. You can use the WITH NO_INFOMSGS when running checkdb to …
One of the common CheckDB errors that I see is the Message 824, level 24. This is something that I regularly work to repair for customers with great success. DBCC results for ‘YourDatabaseName’. CHECKDB found 0 allocation errors and 0 consistency errors in database ‘ YourDatabaseName’. Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical …
The REPAIR_ALLOW_DATA_LOSS option for DBCC CHECKDB and DBCC CHECKTABLE can be one of the most misleading and possibly catastrophic options. What the REPAIR_ALLOW_DATA_LOSS option does for DBCC CHECKDB and DBCC CHECKTABLE is to simply just throw away any pages (8k blocks of data) that contain rows. This may mean it is throwing away a couple of rows, or dozens to …
From time to time I get asked about checkDB, and there are many solutions out there, but I have one that I generally use that is very simple and does the job. The script below created a stored procedure in the DBHealthHistory database that can be used to check as many databases as you can get through in a specific …
TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it. This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file. Not shrinking your database is one of the more counter intuitive …
DBCC ShrinkDatabase – I want to shrink my database. Read more »
There are many times that CheckDB ends up being extremely slow, sometimes taking more than a day to run. This can make life difficult if you are trying to find out what is corrupt. There are several of the tricks that I use to speed up DBCC CheckDB, depending on the specific environment. What I am looking for is what others do …
Welcome to the DataBase Corruption Challenge, this is an about weekly blog challenge where I will post a corrupt SQL Server database with some details on what happened to it. If at this point you are already a bit irked by my use of capitalization in the DataBase Corruption Challenge, and the acronym of DBCC that I have used to …
Introducing the DataBase Corruption Challenge (DBCC) – Week 1 Challenge Read more »
The following DBCC commands are all hidden in the word search. Be aware, some of the DBCC commands are undocumented. CACHESTATS CHECKCATALOG CHECKCONSTRAINTS CHECKDB CHECKFILEGROUP CHECKIDENT CLEANTABLE DROPCLEANBUFFERS FLUSHPROCINDB FREE FREEPROCCACHE FREESESSIONCACHE FREESYSTEMCACHE INDEXDEFRAG OPENTRAN OUTPUTBUFFER PAGE PROCCACHE SHOWCONTIG SHRINKDATABASE TRACEON TRACESTATUS UNPINTABLE UPDATEUSAGE USEROPTIONS END OF PUZZLE ————————- See also: For more information on DBCC Commands: DBCC CheckAlloc DBCC …
SQL Sunday Fun – Word Search with SQL Server DBCC Commands Read more »
As the month of May comes to an end so does DBCC command month at SteveStedman.com. I didn’t cover all the DBCC Commands this month, but here is what I did have time to cover: May 1st – DBCC CheckAlloc May 2nd – DBCC CheckCatalog May 3rd – DBCC CheckConstraints May 4th – DBCC CheckDB May 5th – DBCC CheckFilegroup May 6th – DBCC CheckIdent May …