SQL Server Operations Journal-shrinking database

A Little Story

One day, Xiao Wang is chatting with HR sister, is happy, suddenly received the system alarm message, the database disk is the remaining space 500m,omg, no, the disk is almost full, if the business to stop, then the small king can only delete the library to run,,

First check, there are no files that can be deleted, the results are important or not in doubt. First shrink the database bar, click to run. When the contraction is complete, you can continue to the root HR sister chat. Suddenly telephone landline and mobile phone Qi Ming, Xiao Wang heart a kind of ominous feeling? It seems like the scene has been seen somewhere before. It's not a database block, is it?? Rush to pick up the phone first, because the caller is a business department head "Xiao Wang Ah, now the system is stuck, all do not move, is how the matter ah, you hurriedly deal with the next", "En, good, I immediately check under", and then pick up the landline, is another department of the director said the report can not see. Hurry up and check the statements in the database execution. Sure enough the database generated a lot of blocking, and the operation of the database server has become very slow (is my psychological role?) ）。 is preparing to cancel the contraction operation first, the telephone rang at the same time,, alas, regardless of, first deal with the problem. then click Cancel. After a long wait, and finally finished, and then call the various departments to explain, write the accident report, tragedy, today's lunch is not want to eat.

This scene is not very familiar Ah, about the database shrinkage problem, I am in the group, the Forum, see the newcomer asked one of the most frequent problems. Today this article has a framework for database shrinkage, I hope that the small partners in the future to meet the relevant

Problem, be aware of it.

Recommendations on shrinkage

Do not shrink the database unless you have to. Shrinking the database has great impact:

1. Shrinking the database has great impact on the database, generating a large number of logs and fragments, and locking the table. If your library is currently being used, it is normal to shrink.
2. Shrinking the database must be done by hand, and it is done in the Maintenance window period.
3. Try to use the statement to execute, can prompt the error

The following article describes in detail:
Http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/.

Correct posture of contraction

When you have to shrink, refer to the steps below

1. Locate the largest number of tables in the database and rebuild all indexes. First try to specify the truncate only shrink mode. It simply removes free space at the end of the file and does not reorganize the data pages that are already in use.

DBCC Shrinkdatabase (AdventureWorks2012, truncateonly);  

2 final consideration, no shrinkage with options. Contraction does not shrink all at once. Can shrink by about 2G each time. Do not shrink all of the space available space, and you can have some of the remaining parts, such as 4G.

3. The DBCC shrinkdatabase operation can be stopped at any point in the process, and any completed work will be retained.

4. You cannot shrink a database while backing up a database

Summarize

So what's the best way to deal with the lack of disk space? The best way to do this is to anticipate the growth of the data for the next year or two years in the initial planning. Divide the disk into enough space. Set the initial size of the database and automatically grow with a fixed amount of growth.

SQL Server Operations journal-shrinking database