CODEWITHSUNDEEP
sql-serversql-server-2008-r2
A.G.
A.G.

Reputation: 2149

Shrinking a SQL Server database not working

I'm not a DBA but I need to shrink a DB, I tried using the SSMS interface to shrink and it didn't work.

The stats are as follows:

I have also tried the DBCC SHRINKDATABASE ... TRUNCATEONLY command and didn't change anything either.

How can I shrink this DB (free up the unused space to the OS) without losing data?

Upvotes: 12

Views: 45350

Answers (5)

Felix Quehl
Felix Quehl

Reputation: 823

I resolved the issue by the following steps:

  1. MAKE DATABASE BACKUP
  2. Set Database to Single-User-Mode
  3. Take Database Offline
  4. Take Database Online
  5. Lower the initial file size for the database and transaction log files
  6. Shrink the Database/File
  7. Set Database to Multi-User-Mode

Upvotes: 3

KornMuffin
KornMuffin

Reputation: 2957

Just hit this. Was trying to shrink via SSMS and the dialog would just close immediately regardless of the shrink option(s) selected (no errors or any indication that it couldn't shrink the DB or files).

I ended up creating another logical ROWS Data file, shrinking the main data file by emptying / migrating it to the new file and then shrinking / emptying back again to the original data file.

This is a dev DB so I'm not too worried about the performance impact (just looking to free up disk space).

Upvotes: 0

Jimmy John
Jimmy John

Reputation: 89

By the way also check the initial size. If your initial size is 235,013 MB then you need to reduce your initial size first.

Secondly, shrinking is one of the worst things your could do to a data file. If you shrink a tlog file its okay, because tlogs dont use the data page structure. If you shrink your database data file, you can make a perfectly de-fragmented database into a perfectly fragmented database. It will mess the performance beyond recognition. Why do you want to shrink by the way? The db is 200 something GB and has 60 something free.... that's like around 30% free. Don't you think in the matter of some time, that space can be utilized?

If you still need to shrink the db, you can create a file group and move the data to that file group and delete the current file group or in case of primary file group you can reduce the size to the bare minimum.

Upvotes: 8

Sam H
Sam H

Reputation: 181

Not enough points to comment but be careful about shrinking. It can affect performance due to fragmentation of your indexes

Explained in detail here: http://www.theboreddba.com/Categories/indexes/SHRINK-a-data-file-Just-say-NO.aspx

Sam

Upvotes: 1

art
art

Reputation: 41

  1. In SSMS try using Right Click on DB->Tasks->Shrink->Files to see how much space is used by data and transaction log files.
  2. It's likely that the Recovery Model for your DB is set to FULL in which case you'll not be able to shrink the transaction log unless you do a full db backup. If it's not a concern, just change the Recovery Model to Simple and try to shrink the transaction log and data files again.

Upvotes: 2

Related Questions