Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 9 down vote favorite

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

share|improve this question
p.s. Im not sure whether this belongs on StackOverflow or not? – Dan Jun 16 '11 at 10:25
Actually, perfect question for ServerFault since it is an admin question, not a programming question. – KCotreau Jun 16 '11 at 10:37
@Dan what version of SQL Server are you using? – Andrew Bickerton Jun 16 '11 at 12:38
If you are looking to reduce size, read this - sqlservercentral.com/articles/data-modeling/71725 – SqlSandwiches Jun 17 '11 at 3:22
1  
@Dan : This should be an interview question for any SQL Server DBA, as well as bread-and-butter for everyday usage. +1 for this question. – RolandoMySQLDBA Jun 19 '11 at 22:40
show 1 more comment

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

2 Answers

up vote 10 down vote accepted

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.

share|improve this answer
Stan the Man strikes again !!! +1 – RolandoMySQLDBA Jun 19 '11 at 22:36
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
up vote 4 down vote

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.

share|improve this answer
This is a richer explanation, so +1 here as well !!! – RolandoMySQLDBA Jun 19 '11 at 22:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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