Unable to shrink Database

Question

Post reply

Unable to shrink Database

tikkani.praveen

Old Hand

Points: 353
More actions
August 13, 2009 at 12:37 pm

#134764

Hi all,
We had a 700 GB Database(700GB of mdf file).Since we were running out of space and deletion of data was taking time, we had inserted required data from some huge tables into tables in different database and dropped the tables from original database(700gb one).
Now after doing this activity, if i'll shrink the database i'll get 200 GB of free space. I tried shrinking files using Sql server management studio, but space was not released.
Can anyone please help me regarding this issue.
Its a bit URGENT!!!
Thanks,
Praveen
find out the logical name of the data file: right click on the database, Properties, then click on Files in Select a page on the left. On the right you will see the logical name on the data file. Here is the name you need for next step
assume you are correct that the physical file is 700 GB and unused space is 200 GB. Let's say you want to bring the phisical file down to 600 GB and you want to do 50 GB at a time:

use FINCON_RETAIL_LIABILITIES_MIS

go

dbcc shrinkfile(logical_file_name, 650000) -- 650000 MB = 650 GB

when it completes, shrink again to 600 GB:

use FINCON_RETAIL_LIABILITIES_MIS

go

dbcc shrinkfile(logical_file_name, 600000)

Depend on your situation you may want to bring down 5 GB or 10 GB at a time first to see how long it takes instead starting with 50 GB.

chaitanyagoa Mr or Mrs. 500 Points: 536 More actions · Answer 1

Hey,

Shrinking the database is not advisable Praveen. It might cause a lot of problems.

I think the problem u have faced have been discussed before in this forum.:-)

Try to find it in search:cool:

Chitanya Chitturi :-)

MusabUmair Ten Centuries Points: 1071 More actions · Answer 2

Hi

First of all file shrinking is something else and database shrinking is something else.

Kindly let me know if you have the db as production or just testing.

and the space will be available if you need to shrink the database with say 5 % free space available

LIKE

USE [YourDatabase]

GO

DBCC SHRINKDATABASE(N'YourDatabase', 5 )

GO

Try it and it will help you

but it will effect the performance so dont try it on production without testing.

Musab
http://www.sqlhelpline.com

htt SSCertifiable Points: 5242 More actions · Answer 3

if you right click on the db and go to Reports>Disk Usage, how much space used for data and available in Data Files Space Usage?

if you must shrink the mdf file, do a small chunk at a time:

dbcc shrinkfile(logical_name, 650)

then

dbcc shrinkfile(logical_name, 600)

t2sqldba SSChasing Mays Points: 625 More actions · Answer 4

Hi,

Shrinking of database is different fron shrinking the .mdf files.

I won't recomend you to go ahead with the shrinking process using SQL server management studio as the size of the file is huge so sometimes the process may get hunged rather you can shrink the .mdf file by writing the following DBCC command into your Query Analyser. Fire the SQL statments one by one.

1) USE databasename

2) sp_helpfile (this will give the files detail for that database)

3) DBCC SHRINKFILE(logical_filename,size)

Note 1: Here as the size is 700 GB so you can make several attempts using the above commands to shrink the .mdf file. In a single go you should not attempt as there is a question of maintenance window as well.If the database is in production environment and your application can effort to have @least 8-9 hrs maintenance window daily than you can use 8-9 hrs to run the DBCC SHRINKFILE(logical_filename,size) command for a single day.

use the following command in a different Query analyser to get the spid for the running DBCC command and also to check if there is any blockage to the running DBCC command:

4) select cmd,* from master..sysprocesses where db_name(dbid)='databasename'

After getting the SPID for that process you can kill that process for that day using the below statement :

5) kill spid

example: kill 45

Note 2:

Alternate Solution:

a) You can move the tables with bigger index to a different disk having more capacity. I mean to say make a different file group for that database in a different disk having sufficient space.

b) Now move the tables with bigger indexes to that new file group.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tikkani.praveen Old Hand Points: 353 More actions · Answer 6

Can u tell me what does this logical_name and 650 refers to, and how can i do it in my context.

My database name is FINCON_RETAIL_LIABILITIES_MIS.

Please expalin me a bit more in detail.

htt SSCertifiable Points: 5242 More actions · Answer 7

tikkani.praveen (8/14/2009)
Can u tell me what does this logical_name and 650 refers to, and how can i do it in my context.
My database name is FINCON_RETAIL_LIABILITIES_MIS.
Please expalin me a bit more in detail.

I was wrong at 650, it should be 650000. This is the size in MB that you want to shrink your data file to.

First of all please confirm the total space, used space, and available space by right click on the database name then Reports>Disk Usage. This will tell you how much available space in Data Files Space Usage. Shrinking the data file is not recomended because it will grow again when needed but if you have to do this then:

ps. One Orange Chip Points: 29422 More actions · Answer 8

tikkani.praveen (8/13/2009)
Now after doing this activity, if i'll shrink the database i'll get 200 GB of free space. I tried shrinking files using Sql server management studio, but space was not released.

Please give a moment on the advice provided by Gail. If you can add some disk space, u'd be able have enough disk space. Read out dbcc shrinkfile/dbcc shrinkdatabase from BOL if you dont have a choice to shrink the data files.

Pradeep Singh

Brian Bunin Mr or Mrs. 500 Points: 585 More actions · Answer 9

GilaMonster (8/14/2009)
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

I believe the OP indicated that he'd moved 200GB of tables out of the DB, into a separate DB.

If so, that would be a valid reason for doing a shrink. Those tables would grow in the other DB, not this one. While he needs to leave sufficient room for growth, he could recover quite a bit of valuable disk space by reclaiming the space he just freed up.

Brian Bunin

Charles Kincaid SSChampion Points: 13593 More actions · Answer 10

So we don't have all the details, but:

If the tables were moved to a new database and only data that is seldom updated is in the original database then a shrink might be acceptable.

To tikkani.praveen please note that what has not been said to you is that since you removed a great deal of data from your database is that shrinking the database may not bee needed at all. This is because SQL server keeps track of the areas within the file where the old data used to be. As more data is added to the database SQL server will reuse that old space and not have to grow the file. There are was to check for available space within your database. Now if you need the disk space for something else other than this database then that is another issue. Shrinking the database then is needed.

There are lots of bad things that can happen with shrinking and we want you to protect yourself. Unfortunately this is something that take on an almost "religious" fervor by people who will tell you "don't ever do that" without telling you why. Check the Books On Line (BOL) documentation for the command that have been suggested. Then look at what is is that you need to do. Shrinking a database of the size that you state will take quite some time. Nothing else should touch the database while you are shrinking it. Always make sure you have very good backups before doing anything like this.

ATBCharles Kincaid

Donpi SSC Veteran Points: 252 More actions · Answer 11

In SSMS
-Right click / properties on the database
-In files section check the initial size and set it to a lower value
-Click Ok

Now the shrink file/database will work

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

jjimenez-1040959 - Tuesday, January 16, 2018 5:10 AM
In SSMS
-Right click / properties on the database
-In files section check the initial size and set it to a lower value
-Click Ok
Now the shrink file/database will work

I always appreciate an answer even if it's a bit late. Just so you know, your responding to a post that's over 9 years old. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Ah... and to add to that, shrinking a file or a database causes a type of fragmentation that can actually cripple the database. If you shrink a file or a database, you MUST rebuild indexes after the shrink is complete.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Donpi SSC Veteran Points: 252 More actions · Answer 14

Jeff Moden - Tuesday, January 16, 2018 7:32 AM
jjimenez-1040959 - Tuesday, January 16, 2018 5:10 AM
In SSMS
-Right click / properties on the database
-In files section check the initial size and set it to a lower value
-Click Ok
Now the shrink file/database will work
I always appreciate an answer even if it's a bit late. Just so you know, your responding to a post that's over 9 years old. 😉

I know, but I find the post today with the same problem.
And was a little confused to see everybody telling "no you should not shrink database! Its bad ! gnagnagna " but without answering to the real question.
I think that it good for the comunity to share when a solution is found.

Bye