question

aslam2510 avatar image
aslam2510 asked

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 comments
10 |1200 characters needed characters left characters exceeded

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

"...I am shrinking the database..." You need to describe in detail, how you are shrinking the database.
2 Likes 2 ·
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 Likes 0 ·
Just to clarify, that free space amount you listed is for the data file correct and not the log file?
0 Likes 0 ·
Yes, it is for data file.
0 Likes 0 ·
kevaburg avatar image
kevaburg answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
kevaburg avatar image
kevaburg answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
kevaburg avatar image
kevaburg answered
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 comments
10 |1200 characters needed characters left characters exceeded

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

Already I mentioned all the commands which I used. Even I used GUI also and tried both the options.
0 Likes 0 ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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