x

How to shrink data file in small sizes?

I have data file with 52GB and it have free space of 5GB ... After I ran dbcc shrinkfile('databfilename',0)... It is taking long time to shrink (about 7min) and still running so I stopped it....

So please let me know how to shrink small sizes relaseing 500MB space ang again 500MB space... May be it could be speed up?
more ▼

asked Dec 10, 2012 at 08:43 PM in Default

narendba gravatar image

narendba
570 26 37 45

Are you running low on space? If you have plenty left when I would advise you leave the space, especially if you are still feeding the DB.

However, if you are sure. Then check what activity is currently on the system using something like sp_who2 as it is possible there is some transaction still active.
Dec 11, 2012 at 08:32 AM sp_lock
Don't forget - every time someone shrinks a database, God (or was it Paul Randal? tricky to tell the difference sometimes) kills a kitten.
Dec 11, 2012 at 09:45 AM ThomasRushton ♦
Are you comparing Paul Randal to God?!
Dec 11, 2012 at 10:00 AM sp_lock

We are having low disk space and the data base is using for reporting purpose... So there is now problem if we shrink also as of now the data file having 36GB of unused space and the database is option set to "Autoshrink"... And I have takem Full backup and it completed successfully... I have checked the details by using sys.dm_exec_requests there Command Column have AutoShrink and Percent_complete is 40%... But still the data file 36GB of unused space no released...

Note: Database is in SIMPLE RECOVERY so there is no problem we have with log file and it is fine.

Just I want to relase that 36GB of unused space.... AutoShrink enabled....
Dec 11, 2012 at 10:12 AM narendba
The way to deal with this problem is not to shrink your databases over and over. That causes severe fragmentation of your database files and will seriously impact performance. Instead, you need to manage the space you have available, add additional drives, rearrange the storage of your databases. It's hard work, but that's why it's called work.
Dec 11, 2012 at 10:32 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Did you checked your shrink process is blocked by any other session? DBCC OPENTRAN or run sys.dm_exec_requests monitor the columns percent_complete,wait_type,blocking_session_ID columns.
more ▼

answered Dec 11, 2012 at 09:53 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

If you really need to shrink....

Check for any heaps, generally when you deletes rows from heap, pages freed remains allotted to the heaps, they are not released (but Truncate and DELETE WITH(TABLOCK) does). The option is to create a clustered index on them and then shrink.

To find heaps


SELECT OBJECT_NAME(object_ID) as TabeName
FROM sys.indexes
WHERE index_id = 0
more ▼

answered Dec 11, 2012 at 10:25 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
Also remember, that you must rebuild all your indexes and statistics after shrinking as the process caused fragmentation.
more ▼

answered Dec 11, 2012 at 10:32 AM

anthony.green gravatar image

anthony.green
1.2k 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1943

asked: Dec 10, 2012 at 08:43 PM

Seen: 511 times

Last Updated: Dec 11, 2012 at 10:32 AM