Problem

You need to add a data file and transaction log file to your database.

Solution

Utilize the ALTER DATABASE statement to add new files to a database.

ALTER DATABASE BookStoreArchive
ADD FILE
( NAME = 'BookStoreArchive2',
FILENAME = 'O:ApressBookStoreArchive2.NDF' ,
SIZE = 1MB ,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
TO FILEGROUP [PRIMARY];
  
ALTER DATABASE BookStoreArchive
ADD LOG FILE
( NAME = 'BookStoreArchive2Log',
FILENAME = 'P:ApressBookStoreArchive2_log.LDF' ,
SIZE = 1MB ,
MAXSIZE = 5MB,
FILEGROWTH = 1MB );
GO

How It Works

Once a database is created, assuming you have available disk space, you can add data files or transaction log files to it as needed. This allows you to expand to new drives if the current physical drive/array is close to filling up or if you are looking to improve performance by spreading I/O across multiple drives. It usually makes sense to add additional data and log files to a database only if you plan on putting these files on a separate drive/array. Putting multiple files on the same drive/array doesn’t improve performance and may benefit you only if you plan on performing separate file or filegroup backups for a very large database.

Adding files doesn’t require you to bring the database offline. The syntax for ALTER DATABASE for adding a data or transaction log file is as follows:

ALTER DATABASE database_name {ADD FILE <filespec> [ , ... n ]
[ TO FILEGROUP { filegroup_name | DEFAULT } ] ADD LOG FILE <filespec> [ , ... n ] }

Table 25-1 describes the syntax arguments.

In this recipe, a new data and transaction log file are added to the BookStoreArchive database. To add the data file, the ALTER DATABASE statement is used with the ADD FILE argument, followed by the file specification.

ALTER DATABASE BookStoreArchive ADD FILE

The filegroup where the new file is added is specified using the TO FILEGROUP clause, followed by the filegroup name in brackets.

TO FILEGROUP [PRIMARY]

In the second query in the recipe, a new transaction log file is added using the ALTER DATABASE statement and the ADD LOG FILE argument.

ALTER DATABASE BookStoreArchive ADD LOG FILE

Neither file addition requires the database to be offline.

Problem

You need to remove a data or transaction log file from a database.

Solution

Utilize the ALTER DATABASE statement to remove data or transaction log files from a database.

ALTER DATABASE BookStoreArchive REMOVE FILE BookStoreArchive2;

Running this command produces the following message:

The file 'BookStoreArchive2' has been removed.

How It Works

The ALTER DATABASE statement removes the specified logical file name from the database. You might want to do this if you are relocating a database from one drive to another by creating a new file on the one drive and then dropping the old file.

The syntax for dropping a file is as follows:

ALTER DATABASE database_name
REMOVE FILE logical_file_name

where database_name is the name of an existing database, and logical_file_name is the name of the logical file to be removed from the database.

The logical file being removed must be empty (no data and no active transactions), and it cannot be the primary data or primary transaction log file. You can use DBCC SHRINKFILE with the EMPTYFILE parameter to empty a file and move any data within it to another file.

Problem

You need to move a data or transaction log file from one physical location to another, for example, from one drive to another.

Solution

Utilize the ALTER DATABASE statement to move data or transaction log files belonging to a database.

ALTER DATABASE BookStoreArchive
MODIFY FILE
(NAME = 'BookStoreArchive', FILENAME = 'O:ApressBookStoreArchive.mdf')
GO

Upon executing this statement, the following message is returned:

The file "BookStoreArchive" has been modified in the system catalog. The new path will be used the next time the database is started.

How It Works

The ALTER DATABASE statement updates the specified logical file name to a new file name. As the returned message indicates, this new path will be used when the database is next started. This can occur by stopping/starting the SQL Server instance or by taking the database offline and then back online. After the SQL Server instance has been shut down or the database has been taken offline, you will have to move this file to its new location before starting up the SQL Server instance or bringing the database back online. The database can be taken offline, and brought back online, with the following commands:

USE master;
GO
ALTER DATABASE BookStoreArchive SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE BookStoreArchive SET OFFLINE;
GO
-- Move BookStoreArchive.mdf file from N:Apress to O:Apress now.
-- On my Windows 7 PC, I had to use Administrator access to move the file.
-- On other operating systems, you may have to modify file/folder permissions
-- to prevent an access denied error.
  
  
  
USE master;
GO
ALTER DATABASE BookStoreArchive SET ONLINE;
GO
ALTER DATABASE BookStoreArchive SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

The ALTER DATABASE BookStoreArchive SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE; statement sets the database to where only users that are members of the db_owner database role, or the db_creator or sysadmin server roles, can connect to the database. Any statements currently being run by other connections are canceled and rolled back. The database is then taken offline.

After the database file has been physically moved to its new location, the database is brought back online, and then the database is opened back up to all users.

Problem

You need to change the logical name of a file in a database.

Solution

Utilize the ALTER DATABASE statement to rename the logical name of a file belonging to a database.

SELECT name
FROM sys.database_files;
  
ALTER DATABASE BookStoreArchive
MODIFY FILE
(NAME = 'BookStoreArchive',
NEWNAME = 'BookStoreArchive_Data'),
  
SELECT name
FROM sys.database_files;

This statement returns the following message and result set:

name
---------------------------------------------------
BookStoreArchive
BookStoreArchive_log
BookStoreArchive2Log
  
The file name 'BookStoreArchive_Data' has been set.
name
---------------------------------------------------
BookStoreArchive_Data
BookStoreArchive_log
BookStoreArchive2Log

How It Works

The ALTER DATABASE statement allows you to change the logical name of a file belonging to the database without taking the database offline. The logical name of a database doesn’t affect the functionality of the database itself, allowing you to change the name for consistency and naming convention purposes. For example, if you restore a database from a backup using a new database name, you may want the logical name to match the new database name.

The syntax of the ALTER DATABASE statement to change the logical name is as follows:

ALTER DATABASE database_name
MODIFY FILE
(NAME = logical_file_name, NEWNAME = new_logical_name);

where database_name is the name of an existing database, logical_file_name is the logical name of the file to be renamed, and new_logical_name is the new logical file name.

Problem

You have a scheduled downtime for your database. During this downtime, you want to increase its size to prevent autogrowth operations until your next scheduled downtime.

Solution

Utilize the ALTER DATABASE statement to increase the size of a file belonging to a database.

SELECT name, size FROM BookStoreArchive.sys.database_files;
  
ALTER DATABASE BookStoreArchive
MODIFY FILE
(NAME = 'BookStoreArchive_Data',
 SIZE = 5MB);
  
SELECT name, size FROM BookStoreArchive.sys.database_files;
  

This statement returns the following result sets:

name size
---------------------- ----
BookStoreArchive_Data 384
BookStoreArchive_log 64
BookStoreArchive2Log 128
  
name size
---------------------- ----
BookStoreArchive_Data 640
BookStoreArchive_log 64
BookStoreArchive2Log 128

How It Works

The MODIFY FILE clause of the ALTER DATABASE statement allows you to increase the size of a file. In the previous example, the size of the BookStoreArchive_Data file is changed from 3MB to 5MB. If you specify the same file size, or lower, you will receive this error message:

Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.

The syntax of the ALTER DATABASE statement to increase a file size is as follows:

ALTER DATABASE database_name
MODIFY FILE
(
NAME = logical_file_name
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ]
UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | % ] ]
)

Table 25-2 shows the arguments of this syntax.

Problem

You want to add a new filegroup to your database.

Solution

Utilize the ALTER DATABASE statement to add a filegroup to a database.

ALTER DATABASE BookStoreArchive
ADD FILEGROUP FG2;
GO

How It Works

The ALTER DATABASE is utilized to add a filegroup to a database. The syntax is as follows:

ALTER DATABASE database_name
ADD FILEGROUP filegroup_name

where database_name is the name of an existing database, and filegroup_name is the name of the new filegroup being added.

You might want to add a new filegroup for a multitude of reasons. Some of these include the following:

• Putting read-only tables into a read-only filegroup.
• Moving data that must be restored first into a separate file group in order to bring your application back up faster in the event of a disaster. Filegroups can be backed up and restored individually. This may enable your core business functions to get back online faster while the restoration of other filegroups proceeds.
• Relocating the database for disk maintenance.

Problem

You want to add a new file to a filegroup.

Solution

Utilize the ALTER DATABASE statement to add a new file to a specified filegroup.

ALTER DATABASE BookStoreArchive
ADD FILE
( NAME = 'BW2',
FILENAME = 'N:ApressFG2_BookStoreArchive.NDF' ,
SIZE = 1MB ,
MAXSIZE = 50MB,
FILEGROWTH = 5MB )
TO FILEGROUP [FG2];

How It Works

Just like in Recipe 25-1, this adds a new file to the database. The difference is the specification of the filegroup that the file should be added to. Without this specification, the file would be added to the default filegroup.

Problem

You want to change which filegroup will have new tables added to the files in a filegroup.

Solution

Utilize the ALTER DATABASE statement to make a filegroup the default filegroup for a database.

ALTER DATABASE BookStoreArchive
MODIFY FILEGROUP FG2 DEFAULT;
GO

This query returns the following message:

The filegroup property 'DEFAULT' has been set.

How It Works

The ALTER DATABASE statement is used to set the default filegroup for a database. The default filegroup is the filegroup to which new objects will be built in if a filegroup is not specified. Only one filegroup can be the default filegroup at any point in time. The syntax for this statement is as follows:

ALTER DATABASE database_name
MODIFY FILEGROUP filegroup_name DEFAULT

where database_name is the name of an existing database, and filegroup_name is the name of an existing filegroup within the specified database.

Problem

You want to add a new table to a specific filegroup.

Solution

In the CREATE TABLE statement, specify the filegroup that the table is to be added to.

CREATE TABLE dbo.Test
  (
  TestID INT IDENTITY,
  Column1 INT,
  Column2 INT,
  Column3 INT
  )
ON [FG2];

How It Works

The ON clause specifies the partition scheme or filegroup that the table will be built in.

Problem

You need to remove a table from one filegroup and place it on a different filegroup.

Solution #1

If the table does not have a clustered index, add a clustered index or constraint to the table, specifying the new filegroup.

ALTER TABLE dbo.Test
 ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (TestId)
 ON [PRIMARY];

Solution #2

If the table does have a clustered index that is enforcing a constraint, drop and re-create the clustered constraint, specifying the new filegroup.

CREATE TABLE dbo.Test2
  (
  TestID INT IDENTITY
  CONSTRAINT PK__Test2 PRIMARY KEY CLUSTERED,
  Column1 INT,
  Column2 INT,
  Column3 INT
  )
ON [FG2];
GO
  
ALTER TABLE dbo.Test2
DROP CONSTRAINT PK__Test2;
  
ALTER TABLE dbo.Test2
ADD CONSTRAINT PK__Test2 PRIMARY KEY CLUSTERED (TestId)
ON [PRIMARY];

Solution #3

If the table has a clustered index that is not enforcing a constraint, rebuild the index with the DROP EXISTING clause and specify the file group that it should be moved to.

CREATE TABLE dbo.Test3
  (
  TestID INT IDENTITY,
  Column1 INT,
  Column2 INT,
  Column3 INT
  )
ON [FG2];
GO
  
CREATE CLUSTERED INDEX IX_Test3 ON dbo.Test3 (TestId)
ON [FG2];
GO
  
CREATE CLUSTERED INDEX IX_Test3 ON dbo.Test3 (TestId)
WITH (DROP_EXISTING = ON)
ON [PRIMARY];
GO

How It Works

Since a clustered index contains, at the leaf level, all the data for the table, moving the clustered index to a different filegroup moves the table to the new filegroup. In the same manner, adding a clustered index to a table without one will move the data from the table into the clustered index to the filegroup as specified by the index. If the clustered index is enforcing a constraint, the constraint will need to be dropped and re-created to move the table; you can rebuild an index on a constraint only if everything about the new index is identical to the current index and the filegroup that the index is on is being changed. If this is the only method available to you, you should do this during a maintenance period so that you can ensure that data won’t be entered that would violate the constraint.

In the first solution, the dbo.Test table does not have a clustered index, so one is created on it with the ALTER TABLE statement, specifying the filegroup to put the index on. Creating the clustered index on a different filegroup moves the table to the other filegroup.

In the second solution, a new table is created on filegroup FG2 with a clustered index on a primary key constraint. To move this table, the constraint is first dropped with the ALTER TABLE statement, creating a table without any clustered index. The clustered primary key constraint is then re-created on the desired filegroup, moving the table to that filegroup.

In the third solution, a table and a clustered index are created on FG2. Since the clustered index is not enforcing a constraint, this table can be moved to the new filegroup by utilizing the CREATE INDEX statement and by specifying the DROP_EXISTING = ON clause and the filegroup to put the index on.

Problem

You want to remove an empty filegroup from your database.

Solution

Utilize the ALTER DATABASE statement to remove filegroups from a database.

ALTER DATABASE BookStoreArchive
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO
  
ALTER DATABASE BookStoreArchive
REMOVE FILE BW2;
GO
  
ALTER DATABASE BookStoreArchive
REMOVE FILEGROUP FG2;
GO

These statements return the following messages:

The filegroup property 'DEFAULT' has been set.
The file 'BW2' has been removed.
The filegroup 'FG2' has been removed.

How It Works

To remove a filegroup, it cannot contain any files within it. Furthermore, you cannot remove the last file from the default filegroup. Therefore, the first ALTER DATABASE statement is necessary to change the default filegroup back to the PRIMARY filegroup. Since the filegroup name PRIMARY is a keyword, it must be enclosed in brackets. The second ALTER DATABASE statement removes the empty file from the filegroup (see Recipe 25-2). The third ALTER DATABASE statement removes the filegroup. The syntax is as follows:

ALTER DATABASE database_name
REMOVE FILEGROUP filegroup_name

where database_name is the name of the existing database, and filegroup_name is the name of the existing and empty filegroup to be removed.

Problem #1

You have historical data in your database that cannot have any modifications made to it. However, the data needs to be available for querying.

Problem #2

You entire database contains historical data, and it cannot have any modifications made to it. However, the data needs to be available for querying.

Solution #1

Move the historical data to a separate filegroup, and then set the filegroup to be read-only.

ALTER DATABASE BookStoreArchive SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
  
ALTER DATABASE BookStoreArchive
ADD FILEGROUP FG3;
GO
  
ALTER DATABASE BookStoreArchive
ADD FILE
( NAME = 'ArchiveData',
FILENAME = 'N:ApressBookStoreArchiveData.NDF' ,
SIZE = 1MB ,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
TO FILEGROUP [FG3];
GO
-- move historical tables to this filegroup
  
ALTER DATABASE BookStoreArchive
MODIFY FILEGROUP FG3 READ_ONLY;
GO
  
ALTER DATABASE BookStoreArchive SET MULTI_USER;
GO

Solution #2

Since the entire database consists of the historical data, you can set the entire database to READ_ONLY with this statement:

ALTER DATABASE BookStoreArchive SET READ_ONLY;
GO

How It Works

In Solution #1, a new filegroup is created on this database, and a file is added to this filegroup. You would then move the archived data into this filegroup. Finally, you set the filegroup to READ_ONLY. In changing the status of the filegroup, you cannot have other users in the database, so the database is first set to only allow restricted users in before these actions, and once all work has been finished, it is opened back up to all users. The filegroup can be set back to a read-write status by executing this statement (after setting it to restricted users again).

ALTER DATABASE BookStoreArchive
MODIFY FILEGROUP FG3 READ_ONLY;

In Solution #2, the entire database is set to a READ_ONLY status. You can set it back to a read-write status with this statement:

ALTER DATABASE BookStoreArchive SET READ_WRITE;

Problem

You need to know how much space is being used by the objects in the database.

Solution #1

Utilize the sp_spaceused stored procedure to obtain information about space usage within the database and transaction log.

EXECUTE sp_spaceused;

Executing the sp_spaceused stored procedure without any parameters returns the following result set:

database_name database_size unallocated space
----------------- ------------------ ------------------
BookStoreArchive 7.50 MB 3.88 MB
  
reserved data index_size unused
------------------ ------------------ ------------------ ------
2168 KB 824 KB 1128 KB 216 KB

Solution #2

Utilize the sp_spaceused stored procedure to obtain information about space usage for a specific object within a database.

EXECUTE sp_spaceused 'dbo.test';

Executing the sp_spaceused stored procedure with an object name returns the following result set:

name rows reserved data index_size unused
--------- ------ ------------------ ------------------ ------------------ ------
Test 0 0 KB 0 KB 0 KB 0 KB

Solution #3

Utilize DBCC_SQLPERF to obtain space used information about all transaction logs on your SQL Server instance.

DBCC SQLPERF(LOGSPACE);

Executing this returns the following result set (results will contain a row for each database on your SQL Server instance that this command is being run on):

Database Name Log Size (MB) Log Space Used (%) Status
------------------ ------------- ------------------ ------
master	0.9921875	45.52165	0
tempdb	0.4921875	85.71429	0
model	0.4921875	91.76588	0
msdb	0.7421875	48.02632	0
AdventureWorks2012	12.05469	14.25794	0
BookStoreArchive	1.484375	38.94737	0
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

The sp_spaceused system stored procedure returns information about the specified object, including the number of rows in the object, how much space the data and indexes are using, and unused space. If an object isn’t specified, the information returned is about the database: the size, unallocated space, data space, index space, and unused space. The syntax for this procedure is as follows:

sp_spaceused [[ (@objname = ] 'objname' ]
[,[ (@updateusage = ] 'updateusage' ]

Table 25-3 describes the parameters of this procedure.

In Solution #3, DBCC SQLPERF is used to obtain transaction log space usage statistics for all databases. (It can also be used to reset wait and latch statistics.) The syntax for DBCC SOLPERF is as follows:

DBCC SQLPERF
(
  [ LOGSPACE ]
  |
  [ "sys.dm_os_latch_stats" , CLEAR ]
  |
  [ "sys.dm_os_wait_stats" , CLEAR ]
)
  [WITH NO_INFOMSGS ]

Table 25-4 briefly describes this DBCC command’s arguments.

Problem

You need to shrink one database file or the entire database.

Solution #1

Utilize DBCC SHRINKDATABASE to shrink an entire database. We will first expand some of the files in the database, and then we will use DBCC SHRINKDATABASE to shrink all of the files in the database (we will use sp_spaceused to show the information before and after executing DBCC SHRINKDATABASE).

ALTER DATABASE BookStoreArchive
MODIFY FILE (NAME = 'BookStoreArchive_log', SIZE = 100MB);
  
ALTER DATABASE BookStoreArchive
MODIFY FILE (NAME = 'BookStoreArchive_Data', SIZE = 200MB);
GO
  
USE BookStoreArchive;
GO
  
EXECUTE sp_spaceused;
GO
  
DBCC SHRINKDATABASE ('BookStoreArchive', 10);
GO
  
EXECUTE sp_spaceused;
GO

These statements produce the following results sets and messages:

database_name database_size unallocated space
---------------- ------------------ -----------------
BookStoreArchive 302.00 MB 198.88 MB
  
reserved data index_size unused
-------- ------ ---------- ------
2168 KB 824 KB 1128 KB 216 KB
  
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
---- ----------- ----------- ----------- ----------- --------------
10 1 384 384 272 272
10 2 1656 64 1656 64
10 4 128 128 128 128
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  
database_name database_size unallocated space
---------------- ------------------ -----------------
BookStoreArchive 17.94 MB 1.88 MB
  
reserved data index_size unused
-------- ------ ---------- -------
2168 KB 824 KB 1128 KB 216 KB

Solution #2

Utilize DBCC SHRINKFILE to shrink one file in the database. Here we will expand one file in the database and then use DBCC SHRINKFILE to shrink that file. Again, we will use sp_spaceused to view the database space information before and after shrinking the file.

ALTER DATABASE BookStoreArchive
MODIFY FILE (NAME = 'BookStoreArchive_Log', SIZE = 200MB);
GO
  
USE BookStoreArchive;
GO
  
EXECUTE sp_spaceused;
GO
  
DBCC SHRINKFILE ('BookStoreArchive_Log', 2);
GO
  
EXECUTE sp_spaceused;
GO

These statements produce the following result sets and messages:

database_name database_size unallocated space
---------------- ------------------ ------------------
BookStoreArchive 204.19 MB 1.07 MB
  
reserved data index_size unused
-------- ------ ---------- -------
2168 KB 824 KB 1128 KB 216 KB
  
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
10 2 1656 64 1656 64
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  
database_name database_size unallocated space
---------------- ------------------ ------------------
BookStoreArchive 17.13 MB 1.07 MB
  
reserved data index_size unused
-------- ------ ---------- -------
2168 KB 824 KB 1128 KB 216 KB

How It Works

DBCC SHRINKDATABASE shrinks the data and log files in your database. In the first example, data and log files are both increased to a larger size. After that, the DBCC SHRINKDATABASE command is used to reduce it down to a target free-space size of 10 percent.

DBCC SHRINKDATABASE (BookStoreArchive, 10)

After execution, the command returns a result set showing the current size (in 8KB pages), minimum size (in 8KB pages), currently used 8KB pages, and estimated 8KB pages that SQL Server could shrink the file down to.

The syntax for DBCC SHRINKDATABASE is as follows:

DBCC SHRINKDATABASE
( 'database_name' | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]

Table 25-5 describes the arguments for this command.

In the second solution, one of the log files is increased to a larger size. This time, the DBCC SHRINKFILE command is used to shrink that individual file down to a specified size (in megabytes).

DBCC SHRINKFILE ('BookStoreArchive_Log', 2);

The syntax for DBCC SHRINKFILE is as follows:

DBCC SHRINKFILE (
{ ' file_name ' | file_id }
{ [ , EMPTYFILE]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
} ) [ WITH NO_INFOMSGS ]

Table 25-6 describes the arguments for this command.

This command shrinks the physical file by removing inactive virtual log files. The transaction log for any database is managed as a set of virtual log files (VLFs). VLFs are created when the transaction log is created or undergoes expansion, and the quantity and size of the new VLFs are based upon the size of the growth of the transaction log file, with a minimum size of 256KB.

Within the transaction log is the “active” logical portion of the log. This is the area of the transaction log containing active transactions. This active portion does not usually match the physical bounds of the file but will instead “round-robin” from VLF to VLF. Once a VLF no longer contains active transactions, it can be marked as reusable through a BACKUP LOG operation or automated system truncation, which makes the VLFs available for new log records.

It needs to be pointed out that when SQL Server talks about truncating the transaction log, the transaction log is not actually truncated; the process will mark zero or more VLFs as reusable. This is an example of misused verbiage in SQL Server documentation.

DBCC SHRINKFILE or DBCC SHRINKDATABASE will make its best effort to remove inactive VLFs from the end of the physical file. SQL Server will also attempt to add “dummy” rows to push the active logical log toward the beginning of the physical file—so sometimes issuing a BACKUP LOG after the first execution of the DBCC SHRINKFILE command and then issuing the DBCC SHRINKFILE command again will allow you to free up the originally requested space.

Database files, when autogrowth is enabled, can expand because of index rebuilds or data modification activity. You may have extra space in the database because of those data modifications and index rebuilds. If you don’t need to free up the unused space, you should allow the database to keep it reserved. However, if you do need the unused space and want to free it up, use DBCC SHRINKDATABASE or DBCC SHRINKFILE.

Problem

You want to test a database’s disk space allocation structures for consistency.

Solution

Utilize DBCC CHECKALLOC to check page usage and allocation within the database.

DBCC CHECKALLOC ('BookStoreArchive'),

This statement produces the following messages. (Since this actually produces more than 500 lines of output, this result set as shown is greatly abridged.)

DBCC results for 'BookStoreArchive'.
***************************************************************
Table sys.sysrscols Object ID 3.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). FirstIAM (1:157). Root (1:158). Dpages 12.
Index ID 1, partition ID 196608, alloc unit ID 196608 (type In-row data). 14 pages used in 1 dedicated extents.
Total number of extents is 1.
***************************************************************
Table sys.sysrowsets Object ID 5.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). FirstIAM (1:131). Root (1:270). Dpages 1.
Index ID 1, partition ID 327680, alloc unit ID 327680 (type In-row data). 4 pages used in 0 dedicated extents.
Total number of extents is 0.
***************************************************************
...
File 3. The number of extents = 1, used pages = 6, and reserved pages = 8.
  File 3 (number of mixed extents = 0, mixed pages = 0).
  Object ID 99, index ID 0, partition ID 0, alloc unit ID 6488064 (type Unknown), index extents 1, pages 6, mixed extent pages 0.
The total number of extents = 36, used pages = 257, and reserved pages = 288 in this database.
  (number of mixed extents = 21, mixed pages = 168) in this database.
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'BookStoreArchive'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

DBCC CHECKALLOC checks page usage and allocation in the database and will report on any errors that are found (this command is automatically included in the execution of DBCC CHECKDB, so if you are already running CHECKDB periodically, there is no need to also run CHECKALLOC). The syntax is as follows:

DBCC CHECKALLOC (
[ 'database_name' | database_id | 0 ] [ , NOINDEX
{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] )
[ WITH { [ ALL_ERRORMSGS ]
[ , N0_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
}
]

Table 25-7 describes the arguments of this command.

The output includes information about pages used and extents for each index. The key piece of information is in the next-to-last line, where you can see the reporting of the number of allocation and consistency errors encountered in the database being checked.

When DBCC CHECKALLOC is executed, an internal database snapshot is created to maintain transactional consistency during the operation. If for some reason a database snapshot can’t be created or if TABLOCK is specified, an exclusive database lock is acquired during the execution of the command (thus potentially hurting database query concurrency). Unless you have a good reason not to, you should allow SQL Server to issue an internal database snapshot so that concurrency in your database is not impacted.

Problem

You want to check the integrity of all objects in a database.

Solution

Use DBCC CHECKDB to check the allocation and structural integrity of all objects in the database.

ALTER DATABASE BookStoreArchive SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE BookStoreArchive MODIFY FILEGROUP FG3 READ_WRITE;
ALTER DATABASE BookStoreArchive SET MULTI_USER;
  
DBCC CHECKDB ('BookStoreArchive'),

Executing this command produces the following messages (as in the previous recipe, this output can be quite large, so only abridged results are being displayed):

DBCC results for 'BookStoreArchive'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
...
DBCC results for 'sys.sysrscols'.
There are 883 rows in 12 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 127 rows in 2 pages for object "sys.sysrowsets".
...
DBCC results for 'Test'.
There are 0 rows in 0 pages for object "Test".
...
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BookStoreArchive'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

The DBCC CHECKDB command checks the integrity of objects in a database. Running DBCC CHECKDB periodically against your databases is a good maintenance practice. Weekly execution is usually sufficient; however, the optimal frequency depends on the activity and size of the database in question. If possible, DBCC CHECKDB should be executed during periods of light or no database activity. Executing DBCC CHECKDB in this manner will allow DBCC CHECKDB to finish faster and keep other processes from being slowed down by its overhead.

When executing DBCC CHECKDB, an internal database snapshot is created to maintain transactional consistency during the operation when this command is executed. If for some reason a database snapshot cannot be created (or the TABLOCK option is specified), shared table locks are held for table checks and exclusive database locks for allocation checks. (One of the reasons a snapshot cannot be created is if there are read-only filegroups; for this reason, the example first changes the FG3 filegroup to be read-write.)

As part of its execution, DBCC CHECKDB executes other DBCC commands that are discussed elsewhere in this chapter, including DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOG. In addition to this, CHECKDB verifies the integrity of Service Broker data indexed views and FILESTREAM link consistency for table and file system directories.

The syntax for DBCC CHECKDB is as follows:

DBCC CHECKDB
(
  'database_name' | database_id | 0
  [ , NOINDEX
  | { REPAIR_ALLOW_DATA_LOSS
  | REPAIR_FAST
  | REPAIR_REBUILD
  } ]
)
  [ WITH {
  [ ALL_ERRORMSGS ]
  [ , [EXTENDED_LOGICAL_CHECKS] ]
  [ , [ NO_INFOMSGS ] ]
  [ , [ TABLOCK ] ]
  [ , [ ESTIMATEONLY ] ]
  [ , { PHYSICAL_ONLY | DATA_PURITY } ]
  }
  ]

Table 25-8 describes the arguments of this command.

Despite all of these syntax options, the common form of executing this command is also most likely the simplest. The example for this recipe executes DBCC CHECKDB against the BookStoreArchive database. For thorough integrity and data checking of your database, the default is often suitable.

DBCC CHECKDB('BookStoreArchive'),

As with the previous recipe, it is the next-to-last line of output that is the most important, where CHECKDB reports on the number of allocation and consistency errors found.

Problem

You want to perform CHECKDB on a database, but you want to limit it to running against a specific filegroup.

Solution

Utilize DBCC CHECKFILEGROUP to perform CHECKDB operations against a specific filegroup.

USE BookStoreArchive;
GO
DBCC CHECKFILEGROUP ('PRIMARY'),
GO

This returns the following abridged results:

DBCC results for 'BookStoreArchive'.
DBCC results for 'sys.sysrscols'.
There are 883 rows in 12 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 127 rows in 2 pages for object "sys.sysrowsets".
...
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'sys.filetable_updates_2105058535'.
There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
CHECKFILEGROUP found 0 allocation errors and 0 consistency errors in database 'BookStoreArchive'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

The DBCC CHECKFILEGROUP command is very similar to DBCC CHECKDB, limiting its integrity and allocation checking to objects within a single filegroup. For very large databases (VLDBs), performing a DBCC CHECKDB operation may be time prohibitive. If you use user-defined filegroups in your database, you can employ DBCC CHECKFILEGROUP to perform your weekly (or periodic) checks instead—spreading out filegroup checks across different days.

When this command is executed, an internal database snapshot is created to maintain transactional consistency during the operation. If for some reason a database snapshot can’t be created (or the TABLOCK option is specified), shared table locks are created by the command for table checks, as well as an exclusive database lock for the allocation checks.

Again, if errors are found by DBCC CHECKFILEGROUP, Microsoft recommends that you solve any discovered issues by restoring from the last good database backup. Unlike other DBCC commands in this chapter, DBCC CHECKFILEGROUP doesn’t have repair options, so you would need to utilize DBCC CHECKDB to resolve them (although repair options are no longer recommended by Microsoft anyway).

The syntax is as follows:

DBCC CHECKFILEGROUP
(
[ { 'filegroup' | filegroup_id | 0 } ]
[ , NOINDEX ]
)
  [ WITH
  {
  [ ALL_ERRORMSGS | NO_INFOMSGS ]
  [ , [ TABLOCK ] ]
  [ , [ ESTIMATEONLY ] ]
  }
  ]

Table 25-9 describes the arguments of this command.

As with the previous recipes, it is the next-to-last line of output that is the most important, where the number of allocation and consistency errors are reported.

Problem

You want to check for integrity issues with a specific table or indexed view.

Solution

Utilize DBCC CHECKTABLE to check a specific table or indexed view for integrity issues. (This solution utilizes the AdventureWorks2012 database.)

DBCC CHECKTABLE ('Production.Product'),

DBCC results for 'Production.Product'.
There are 504 rows in 13 pages for object "Production.Product".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKTABLE ('Sales.SalesOrderDetail') WITH ESTIMATEONLY;

Executing this command produces the following messages:

Estimated TEMPDB space (in KB) needed for CHECKTABLE on database AdventureWorks2012 = 1154.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECT index_id
FROM sys.indexes
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail')
AND name = 'IX_SalesOrderDetail_ProductID';
  
DBCC CHECKTABLE ('Sales.SalesOrderDetail', 3) WITH PHYSICAL_ONLY;

Executing this command produces the following result set and messages:

index_id
--------
3
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

To identify issues in a specific table or indexed view, you can use the DBCC CHECKTABLE command. (If you want to run it for all tables and indexed views in the database, use DBCC CHECKDB instead, which performs DBCC CHECKTABLE for each table in your database.)

When DBCC CHECKTABLE is executed, an internal database snapshot is created to maintain transactional consistency during the operation. If for some reason a database snapshot can’t be created, a shared table lock is applied to the target table or indexed view instead (thus potentially hurting database query concurrency against the target objects). DBCC CHECKTABLE checks for errors regarding data page linkages, pointers, verification that rows in a partition are actually in the correct partition, and more.

The syntax is as follows:

DBCC CHECKTABLE
(
  table_name | view_name
  [ , { NOINDEX | index_id }
  |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
  ]
)
  [ WITH
  { ALL_ERRORMSGS ]
  [ , EXTENDED_LOGICAL_CHECKS ]
  [ , NO_INFOMSGS ]
  [ , TABLOCK ]
  [ , ESTIMATEONLY ]
  [ , { PHYSICAL_ONLY | DATA_PURITY } ]
  }
  ]

Table 25-10 describes the arguments of this command.

In the first example, the integrity of the AdventureWorks2012.Production.Product table is examined for integrity issues.

In the second example, an estimate of tempdb space required for a check on the AdventureWorks2012.Sales.SalesOrderDetail table is returned. This allows you to know ahead of time if a specific CHECKTABLE operation requires more space than you have available.

The third example examines a specific index for physical errors only (not logical errors). To specify an index, you must pass in the index_id, so we first have to query the sys.indexes system view to obtain this value.

Problem

You want to check a specific table or constraint for any violations in CHECK or FOREIGN KEY constraints.

Solution

Utilize DBCC CHECKCONSTRAINTS to validate that CHECK or FOREIGN KEY constraints in a table are valid. (This solution utilizes the AdventureWorks2012 database.)

ALTER TABLE Production.WorkOrder NOCHECK CONSTRAINT CK_WorkOrder_EndDate;
GO
-- Set an EndDate to earlier than a StartDate
UPDATE Production.WorkOrder
SET EndDate = '2001-01-01T00:00:00'
WHERE WorkOrderID = 1;
GO
ALTER TABLE Production.WorkOrder CHECK CONSTRAINT CK_WorkOrder_EndDate;
GO
DBCC CHECKCONSTRAINTS ('Production.WorkOrder'),
GO

This code produces the following messages:

Table Constraint Where
------------------------- ----------------------- ------------------------------------------
[Production].[WorkOrder] [CK_WorkOrder_EndDate] [StartDate] = '2005-07-04 00:00:00.000'
  AND [EndDate] = '2001-01-01 00:00:00.000'
  
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

DBCC CHECKCONSTRAINTS alerts you to any CHECK or foreign key constraint violations found in a specific table or constraint. This command allows you to return the violating data so that you can correct the constraint violation accordingly (although this command does not catch constraints that have been disabled using NOCHECK). The syntax is as follows:

DBCC CHECKCONSTRAINTS
[( 'table_name' | table_id | 'constraint_name'
constraint_id )]
[ WITH
{ ALL_CONSTRAINTS | ALL_ERRORMSGS } [ , NO_INFOMSGS ] ]

Table 25-11 describes the arguments of this command.

In this recipe, the check constraint named CK_Work0rder on the Production.WorkOrder table is disabled, using the ALTER TABLE...NOCHECK CONSTRAINT command.

ALTER TABLE Production.WorkOrder NOCHECK CONSTRAINT CK_WorkOrder_EndDate;

This disabled constraint restricts values in the EndDate column from being less than the date in the StartDate column. After disabling the constraint, a row is updated to violate this check constraint’s rule.

UPDATE Production.WorkOrder SET EndDate = '2001-01-01T00:00:00' WHERE WorkOrderID = 1;

The constraint is then reenabled.

ALTER TABLE Production.WorkOrder CHECK CONSTRAINT CK_WorkOrder_EndDate;

The DBCC CHECKCONSTRAINTS command is then executed against the table.

DBCC CHECKCONSTRAINTS('Production.WorkOrder'),

When the command is run, it returns the data that failed the validation. Now that we know that the table has invalid data in the table, the data can be corrected and validated.

UPDATE Production.WorkOrder
SET EndDate = '2005-07-14T00:00:00'
WHERE WorkOrderID = 1;
GO
  
DBCC CHECKCONSTRAINTS ('Production.WorkOrder'),
GO

This code returns the following message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Problem

You want to check for consistency in and between system tables in your database.

Solution

Execute DBCC CHECKCATALOG against the database to verify consistency in and between system tables.

DBCC CHECKCATALOG ('BookStoreArchive'),

Assuming no errors are found, the following message is returned:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How It Works

DBCC CHECKCATALOG checks for consistency in and between system tables. The syntax is as follows:

DBCC CHECKCATALOG
[ ( 'database_name' | database_id | 0)] [ WITH N0_INFOMSGS ]

Table 25-12 describes the arguments of this command.

In this recipe, the system catalog data is checked in the BookStoreArchive database. If any errors are identified, they will be returned in the command output. DBCC CHECKCATALOG doesn’t have repair options, so if any errors are found, then a restore from the last good database backup may be your only repair option.

When DBCC CHECKCATALOG is executed, an internal database snapshot is created to maintain transactional consistency during the operation. If for some reason a database snapshot cannot be created, an exclusive database lock is acquired during the execution of the command (thus potentially hurting database query concurrency).