Shrinking a database after data deletions (SQL Server 2005)

[StevenK]

We'll soon be running a process that removes a large number of records from a client's database (unwanted / unused information).
As such - we feel we'll be deleting about 70% of the data.
Following this we'd like to run a shrink process against the database to reduce the size of the database file(s) as these are currently taking up a lot of room on the server.

Will a shrink achieve this?
How can the shrink process be run against a SQL Server 2005 database (I'm working with SQL Server 2005 Express on my laptop for testing)?
What kind of size reduction for the database file(s) would we expect to see?

Thanks in advance.
Steve

 

[Catadmin]

Use the DBCC commands. Look up DBCC Shrinkfile and DBCC ShrinkDatabase in Books Online.

How long it takes depends on how big the DB is, but these commands work both against SQL 2005 and SQL 2000 regardless of the edition (Express, Standard, Enterprise, etc.).

Size reduction will depend on how much free space you choose to leave in the database. If the database will never have data added to it again, I recommend using the TruncateOnly switch. If your customer will still be adding data to it, I recommend a minimum of 10% free space so the first time they do the add the database isn't bogged down with increasing the file size while adding the data.

Hope this helps.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"