Microsoft® SQL Server® 2012 Bible

602

Part V: Enterprise Data Management

The results show that not all the fi les had space to reclaim. The command tells you which fi les

it did change by displaying the old and new statistics. DBCC SHRINKDATABASE affects all

the fi les for a database, whereas the DBCC SHRINKFILE command shrinks individual fi les.

Best Practice

DBCC SHRINKDATABASE causes massive index fragmentation and fi le-system fragmentation of the

data fi les because they will likely need to grow again after you shrink all the space. This severely affects

performance. As a best practice, if you do need to run DBCC SHRINKDATABASE, it’s a good idea to

rebuild all the indexes in your database to remove index fragmentation and boost performance.

For more information on the dangers of shrinking your data files, see Paul Randal’s blog on

Why You Should Not Shrink Your Data Files at http://www.sqlskills.com/BLOGS/PAUL/post/

Why-you-should-not-shrink-your-data-files.aspx.

Shrinking the Transaction Log

When the database shrinks, the transaction log also shrinks. The NOTRUNCATE and

TRUNCATEONLY options have no effect on the transaction log. If multiple log fi les exist,

SQL Server shrinks them as if they were one large contiguous fi le.

A common problem is a transaction log that grows and refuses to shrink down to an

expected minimum size. The most likely cause is an old open transaction. The transaction

log is constructed of virtual log partitions. The success or failure of shrinking the transac-

tion log depends on the aging of transactions within the virtual logs and log checkpoints.

SQL Server can shrink only the transaction log by removing data older than the oldest

transaction within the structure of the virtual logs, or in other words, it can remove only

transactions marked as inactive.

To verify that an old transaction has a hold on the transaction log, use the

DBCC OPENTRAN command. The following example detects open transaction information

for the AdventureWorks2012 sample database.

USE AdventureWorks2012;

CREATE TABLE Test(Col1 int);

BEGIN TRAN;

INSERT INTO Test VALUES (1);

DBCC OPENTRAN;

Result:

(1 row(s) affected)

Transaction information for database 'AdventureWorks2012'.

Oldest active transaction:

SPID (server process ID): 59

UID (user ID) : -1

c22.indd 602c22.indd 602 7/31/2012 9:24:32 AM7/31/2012 9:24:32 AM

http://www.it-ebooks.info