Consistency Checks and Shrinking the Database

There are two other broad categories of maintenance that you need to keep up with for each database. The first deals with the physical state of the database; the other deals with the fragmentation of the database indexes.

In the first category, there are two primary tasks to perform: checking the consistency of the data files and shrinking the database. Checking the consistency of the database means that you will verify that the internal storage structure of the database is correct. To understand how to interpret the results of the maintenance and later the index commands, it is useful to know a little about the internal structure of the objects in SQL Server 2005.

The internal storage of objects within a SQL Server database starts with the page. A page is a unit of 8 kilobytes of storage on the physical disk. The first part of this 8K (96 bytes) is used to tell the database engine what kind of data is stored in the page (tables or indexes), how much room is left in the page, and also contains a unique number for the page location.

The rest of the page is filled with rows of data. As you enter more data or index entries, more rows get filled, until you are out of room in that page. A new page is created, with the same structure of header and data.

SQL Server 2005 calls eight contiguous pages of data or indexes an extent. The extent is the lowest unit that SQL Server allows you to work with, and it is how much the reporting we will see in a moment is done.

Sometimes you do not have enough data in a table to take up eight pages in a row, and because SQL Server 2005 manages at the extent level, it will group different tables on single pages into the same extent. This type of storage is called a "mixed extent."

As you create small tables that become large ones, or add and remove lots of data, the possibility exists that the structure of the pages can become damaged. This is what you are monitoring for in this phase of your maintenance.

Checking Consistency

To check the consistency of the pages and extents in your database, you can use the Database Consistency Check Commands (DBCC) statements. There are other tasks in addition to checking consistency that these commands can perform, and I explain those later.

DBCC commands can repair some damage as well as report it. Usually, however, this is not the best idea. The reason for this is that most DBCC commands verify structure, not data. Depending on the DBCC command, you might correct the structure but lose data. That puts the database in an inconsistent state, and you cannot go forward that way.

If you do find that there is structural damage, make sure you have a current backup handy. You can then repair the damage, and if there is data loss, you can always restore to a known good state.

DBCC CHECKDB

The most comprehensive test you can run on your database is the DBCC CHECKDB command. It actually encompasses some of the same checks that the other DBCC commands provide, but I recommend you work from a "top-down" approach so that you can quickly locate any errors in your database, drilling down to the individual objects that might have problems.

In its simplest form, the CHECKDB command checks the structure of the entire database, and presents a report with the results. The format is DBCC CHECKDB ('Database Name'), with other options that I explain in a moment. I will run the basic command on our test database and show you some of the abbreviated results here:

DBCC CHECKDB ('DBAMDT')

DBCC results for 'DBAMDT'.
Service Broker Msg 9675, State 1: Message Types analyzed:
14.
Service Broker Msg 9676, State 1: Service Contracts
analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed:
3.
Service Broker Msg 9669, State 1: Conversation Endpoints
analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups
analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings
analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 548 rows in 5 pages for object
"sys.sysrowsetcolumns".
...
DBCC results for 'ServerInfo'.
There are 1 rows in 1 pages for object "ServerInfo".
CHECKDB found 0 allocation errors and 0 consistency errors
in database 'DBAMDT'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

I have removed some of the output where you see the three periods because the tables being checked are the system tables. There are quite a few of those.

You can see in the output that there are no problems in the structure of the tables, and you can even see how many rows each table has in each page.

Normally, this command is not a drag on the database's performance, but if there are a lot of large tables, you can check just the tables and not the columns by adding the WITH PHYSICAL_ONLY predicate to the end of the command. That produces a shorter output and takes a lot less time to run.

If you do discover errors, it is time to run a few of the other DBCC commands to locate where it is. When you find them, you can return to the CHECKDB command to try and fix the errors. Sometimes you can correct the errors without data loss.

To correct the problems you find, you first want to try and fix the structure without harming the data. To begin, get everyone out of the database and set it to single-user mode. You can do that with the following commands:

ALTER DATABASE DBAMDT
SET SINGLE_USER
GO

This DBCC command runs the fastest and tries to fix minor errors:

DBCC CHECKDB ('DBAMDT', REPAIR_FAST)
GO

If the errors are corrected, you will get a message stating details. The REPAIR_FAST option does not fix indexes, however, because that takes a lot longer. To include the indexes, but still protect the data, you can use this command:

DBCC CHECKDB ('DBAMDT', REPAIR_REBUILD)
GO

If you find that neither of those commands work, you can try the most drastic method:

DBCC CHECKDB ('DBAMDT', REPAIR_ALLOW_DATA_LOSS)
GO

Remember, even if this works, you may have lost data in the process, so you cannot trust the database. This part of the DBCC command set will not be here foreverMicrosoft has already announced plans to remove it in future versions of SQL Server. The preferred method of dealing with a corrupt database is to restore it.

DBCC CHECKTABLE

The DBCC CHECKTABLE command checks the integrity of a particular table or index. If the CHECKDB command finds errors, you can run this command on a particular table to try and deal with it there. The format is DBCC CHECKTABLE (' Table or Index Name'). Here is a sample run in our example database:

DBCC CHECKTABLE ('ServerInfo')
GO

DBCC results for 'ServerInfo'.
There are 1 rows in 1 pages for object "ServerInfo".
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.

You have the same WITH NOINDEX, WITH PHYSCIAL_ONLY, WITH REPAIR_FAST, WITH REPAIR_REBUILD, and WITH REPAIR_ALLOW_DATA_LOSS predicates to work with on the CHECKTABLE command.

You can also run this command on an index name rather than a table name. Unless the index is large, however, there are other ways to deal with them than using DBCC commands. I explain those in the next section.

DBCC CHECKALLOC

The DBCC CHECKALLOC command is a subset of the CHECKDB command and investigates the space allocation structures for an entire database. Here is an example:

DBCC CHECKALLOC('DBAMDT')
GO

Once again, you have the same WITH NOINDEX, WITH PHYSCIAL_ONLY, WITH REPAIR_FAST, WITH REPAIR_REBUILD, and WITH REPAIR_ALLOW_ DATA_LOSS predicates to work with on the CHECKALLOC command.

Other DBCC Commands

You can use other DBCC commands, most of them similar to the reporting commands we have just seen. Most of these commands do not have a repair component, however. DBCC CHECKFILEGROUP validates a filegroup, and DBCC CHECKCATALOG examines a catalog by database.

A couple of other commands begin to delve into the data side of the equation but still deal with structure. DBCC CHECKCONSTRAINTS ensures that all constraints are consistent within the table, and DBCC CHECKIDENT checks identity values, which are sequential numbers automatically inserted in a column. We used an identity data type in our database. The interesting thing about this DBCC command is that you can use it to change data.

Let's assume that you set a particular table to have an identity column, but you want to renumber all of the values it currently has. Perhaps someone hard-coded a number in the field and it served as the primary key for the table. You can run the following command to renumber the values:

DBCC CHECKIDENT('ServerInfo', RESEED, 1)
GO

The 1 at the end of the command is the value you start the numbering with. You should take extreme care with this command because you can break all manner of relationships in the database. For the most part, SQL Server will prevent you from doing that, but it can happen.

Shrinking the Database

I want to show you one more DBCC command. There are two methods of dealing with the size of a database. The first is to set a finite size for the database when you create it. You can do that with the CREATE DATABASE statement or by accessing the Properties panel of the database using SQL Server Management Studio as I showed you earlier. In the File area, you can click the button with the three periods on it to change the file properties, as shown in Figure 3-13.

Figure 3-13.

[View full size image]

You can do the same thing in code. The following script sets our database not to grow higher than 100 megabytes:

USE [master]
GO
ALTER DATABASE [DBAMDT]
MODIFY FILE ( NAME = N'DBAMDT', MAXSIZE = 102400KB )
GO

You can also set the database to grow automatically, as I did when I originally created the database, as you can see in the previous graphic.

As you use the database, it will grow as data is added. As data is erased, space is freed up but not claimed by SQL Server. You can have SQL Server deal with claiming the space (called shrinking the database) or you can do it as part of your maintenance plan. I usually advocate doing it as part of the maintenance plan, because having SQL Server taking care of it causes extra database activity without your control. You certainly do not want to cause an application delay due to maintenance.

To allow SQL Server to shrink the database all the time, stay in the Properties panel and open the Options section. Change the Auto Shrink option to True and you are all set.

To shrink the database on command, use the following DBCC command:

DBCC SHRINKDATABASE ('DBAMDT', 10)
GO

The 10 at the end of the command is optional. It sets the percentage of space you want left in the database when the command is through. You do this so that the database does not have to spend time asking for more space when it starts up again. In some commands, you will see the reverse of this number, called a fillfactor. That is how much room you want to use for the operation.

Do not expect this command to "defrag" a database. You will not reclaim all the empty space in the database, especially if it has a lot of indexes or uncommitted log entries, but this is a safe, fairly quick command to run during maintenance.

For the DBCC commands that report on data, your users can be in the database. It is best if the user activity is low to shrink the database. For the repair commands, they have to be out.