1

I have a SQL Server database with roughly 1.5 TB test data (size of core tables are calculated using sp_spaceused 'tablename'). At the time of data creation process, I have allocated 350 GB to each data file, and my database contains 9 data files and 3 log files.

I want to shrink the database and release unused space before taking a back up. In the database shrink options it shows Available free space as 51%.

Using Management Studio tasks-> shrink options:

  • I shrunk individual data files and log files.
  • then, shrunk the database.

But still shows as 51% free space is available. Can anyone tell me what I'm doing wrong there or am I missing any steps on shrinking process?

What I'm looking for is, since there is a lot of unused space in the database, I want to release it, so the database size gets reduced.

Improve this question
4
  • "and 3 log files" - there should only ever be 1 TLog file (except temporarily in emergencies when you have to add a file to create enough space to fix things up...)
    – Mitch Wheat
    Commented Oct 7, 2020 at 7:03
  • @MitchWheat thanks for the info. I didn't know that, however it doesn't restrict creating multiple log files.
    – Aruna
    Commented Oct 7, 2020 at 7:08
  • 1
    "I want to shrink the database and release unused space before taking a back up". Pretty sure this will maker no difference to backup size or speed. I'll let another expert confirm that though. Or is this to free up space for the backup file?
    – Nick.Mc
    Commented Oct 7, 2020 at 11:16
  • @Nick is correct - a backup won’t bother with empty pages, so removing empty pages just for the backup is pointless busy work.
    – Aaron Bertrand
    Commented Oct 7, 2020 at 11:39

1 Answer 1

Reset to default
1

You can script the SQL commands that SSMS generates. Without us knowing what commands you execute, we don't have much to go on.

Also, pls be specific about each file and the file size. You have 12 files. How large was each file before the individual file shrink, and what was the file size after the individual file shrink?

Did you use the TRUNCATEONLY options of the DBCC SHRINKFILE commands? That is the default in the GUI.

Finally, no need to do a "final" DBCC SHRINKDATABASE. Work with each file separately instead.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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