Best practice to shrink a database

I have a server that runs an application with an SQLExpress backend. Over time the database has accumulated too much data and due to the limitations of Express it is now throwing errors when the app tries to run. I traced the problems down to a series of events that are no longer needed.
After running a query to rid the database of these events, I need to shrink the MDF and LDF files down.
What is the best way to do this without loss of any data and creating fragmentation (if possible)? What commands should I run?

Run the SHRINKFILE command on each file in SSMS:

USE [your_db_name_here];

DBCC SHRINKFILE(1, 512)
DBCC SHRINKFILE(2, 2048)

1 Like

Ok will do, can I ask why or why not DBCC SHRINKDB?

Shrinking causes data fragmentation:

You can remedy it by using ALTER INDEX...REORGANIZE after the shrink is done, but the file will probably grow again, albeit slightly. As long as you don't do regular shrinking it's not a big deal.

Another option if you need to remove a lot of data (40% or more overall) and you want to limit fragmentation, is to copy the data you want too keep to a new table or new database. You can then drop the old table/database. Copying data too a new, empty database will also pretty much eliminate fragmentation.

1 Like

For a SQL Express db, I wouldn't worry about using shrink.

You can use SHRINKDB, but you cannot leave a minimum amount of space in the file using that method (afaik: I'm a professional DBA, so I don't use SHRINKDB. For more advanced dbs, it's better to shrink only the specific file(s) that need it.).

1 Like

If you've deleted data, you don't need to kill your database with the index inversion that any kind of shrink will cause. It's just gonna grow again if you do.

thanks Jeff. The problem is the app complains that my database is too big and starts misbehaving. I think I'm ok to have it grow again because at least this time I have trimmed down anything excessive that could potentially blow up the database again. If it grows, it shouldn't grow very much.