DB Shrink not releasing disk space.

Question

question

aslam2510 asked Dec 5, '16

DB Shrink not releasing disk space.

Dear All, We have a database and archived some data, I want to shrink the database(I know it is not recommended) but I don't have choice. I will do rebuild index after that. I am shrinking the database but it is not releasing space to the disk, tried all the options. Please help me on this.

shrink-database

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO commented · Dec 07, 2016 at 04:19 AM

"...I am shrinking the database..." You need to describe in detail, how you are shrinking the database.

2 ·

aslam2510 commented · Dec 08, 2016 at 09:50 AM

Currently allocated space :87643.81 MB Available Free space: 41782.50 MB (47%) Tried with below queries. DBCC SHRINKDATABASE(N'DBNAME' ) GO USE [DBNAME] GO DBCC SHRINKFILE (N'LOGICALNAME' , 0, TRUNCATEONLY) GO USE [DBNAME] GO DBCC SHRINKFILE (N'LOGICALNAME' , 40000) GO

0 ·

JohnM commented · Dec 09, 2016 at 02:14 PM

Just to clarify, that free space amount you listed is for the data file correct and not the log file?

0 ·

aslam2510 commented · Dec 12, 2016 at 09:59 AM

Yes, it is for data file.

0 ·

Answer 1 · 2016-12-12T18:01:22Z

kevaburg answered Dec 12, '16

I am assuming your database is running in full recovery mode. I don't think the results you have posted are only for the MDF file. The Information displayed here can be gained by looking at the properties for the database. I recommend the following: The SHRINKDATABASE will try to shrink not only the MDF file but also the transaction log and that could be where your problem is. Run DBCC SQLPERF(LOGSPACE) and see whether or not there is a lot of free space in your log file. If so, run DBCC SHRINKFILE(2) and examine the free space again. If you see no change, try backing up the log first and try the shrink again.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aslam2510 commented · Dec 13, 2016 at 04:42 AM

Yes, My DB is in full recovery model, I don't have any problem with shrinking log file. Main problem is the db file, which is not releasing space. But the same exercise I am doing in test and it is going fine and I got the space back to OS which I was expecting. Please help.

0 ·

Answer 2 · 2016-12-13T08:09:39Z

kevaburg answered Dec 13, '16

OK, have you tried rebuilding all the Indexes in the database and then performing the Shrink? What is the Initial Size of the database when you look in the Properties > Files window?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aslam2510 commented · Dec 13, 2016 at 08:31 AM

We purge the data, completed rebuilding indexes then we restored the db in test and it went fine, but in production no luck. Initial size of the db file is 87,644 MB. I tried to reduce the size also but not accepting.

0 ·

Answer 3 · 2016-12-13T08:55:19Z

kevaburg answered Dec 13, '16

What was the command you used to perform the shrink? If you used the GUI did you click the Checkbox for "Release Unused Space" before running the shrink?

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aslam2510 commented · Dec 13, 2016 at 11:10 AM

Already I mentioned all the commands which I used. Even I used GUI also and tried both the options.

0 ·

rvsc49 commented · Dec 13, 2016 at 03:08 PM

So, when you launch this command, what happens? Does it appear to shrink the database any at all? How long does the following take to run? -- Shrinks both the data and log files USE [databasename] GO DBCC SHRINKDATABASE(N'databasename') GO

0 ·

question