13

I have a large SQL Server 2008 test database (1.9TB) and I want to reduced its size on the disk. I have been deleting unused tables, and purging significant amounts of data, but its size on disk it no reducing. What should I do?

I've looked at the Shrink task in SSMS, but the options are bewildering. Do I shrink the database or the files? And with what options?

enter image description here enter image description here

migrated from superuser.com Jun 16 '11 at 10:57

This question came from our site for computer enthusiasts and power users.

10

Select the second radio button Reorganize pages before releasing unused space, and then in the Shrink file to: option, put the minimum MB specified. You will be corrected if you put a lower MB than that specified. Then click OK.

I have found that using the DBCC commands for these work all the time, as the GUI did not work in some of my cases.

Also note, the extra space is there for a reason. You will need the space for future growth, but then that is a whole new topic in performance.

  • 3
    To better answer the question, I would explain the difference between shrinking the database and shrinking the files, as well as the difference between releasing unused space and reorganizing pages. Instead, you just said "do this" without explanation. – Nick Chammas Aug 17 '11 at 18:36
5

If you have the 2008 compression available (Enterprise or Developer editions), you should be able to trim down your big database using compressed indexes and tables. The MS whitepaper "Data Compression: Strategy, Capacity Planning and Best Practices" should be able to help you find what you need. Quote from it:

"The data compression feature in the Microsoft® SQL Server® 2008 database software can help reduce the size of the database as well as improve the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress. This white paper provides guidance on the following:

How to decide which tables and indexes to compress
How to estimate the resources required to compress a table
How to reclaim space released by data compression
The performance impacts of data compression on typical workloads"

Here is a shorter explanation.

You can find an estimation of how much space you can save by using the stored procedure sp_estimate_data_compression_savings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.