DBCC SHRINKFILE not working for datafile

Question

DBCC SHRINKFILE not working for datafile

Viewing 15 posts - 16 through 30 (of 60 total)

You must be logged in to reply to this topic. Login to reply

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

Bhuvnesh (9/6/2010)
take a full backup and then try shrinkdatabase

Why is a full DB backup going to help?

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

sqldba4u Hall of Fame Points: 3414 More actions · Answer 2

Actually its a dev db that needs to be refreshed from prod but space issue is there in dev. I wnat to shrink some other db's datafile in dev which has 80% free sapce available.Size is abt 122 GB, but i am shrinking in smaller chunks like 500 mb. Its not completing and has taken 3+ hrs. So i cancelled it now.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

Vivek29 (9/7/2010)
Actually its a dev db that needs to be refreshed from prod but space issue is there in dev. I wnat to shrink some other db's datafile in dev which has 80% free sapce available.Size is abt 122 GB, but i am shrinking in smaller chunks like 500 mb. Its not completing and has taken 3+ hrs. So i cancelled it now.

Perhaps you are impatient ?

Is there a benefit to shrinking in small chunks ? If you have 80% free space of 122 gig, wouldn't it be better to shrink to 50 G at one time, leave 25 G free for growth, then rebuild indexes ?

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

What's the output of sp_spaceused?

What's the shrink operation waiting for? (check sys.dm_exec_requests)

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

sqldba4u Hall of Fame Points: 3414 More actions · Answer 5

May be:-) I tried later,it completed but no space gained now. Size is as it was.

sqldba4u Hall of Fame Points: 3414 More actions · Answer 6

If its not shrunk by 500 mb, whats the surity it would do for 50 gb. Also, i tried doing rebuilding indexes, but not fruitful.

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

There have been several questions asked, that you have not answered, that might provide useful information.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 8

Have you tried to see what books online says about DBCC SHRINKFILE?

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 9

OK, maybe someone said this already. I also had the issue of the data file that won't shrink. Upon seeing this post I decided to run it again on a test database. First of all I did some reading on SQL BoL and saw the following:

If the shrink operation runs without error, but the file size have not changed try the following:

use AdventureWorks -- your database

SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;

This will show you the available size of the database files.

Now, the question that I cannot answer is: How much free space should I have in relation to the size of the database file?

Maybe someone can answer that for us 'cause I have been searching and maybe I'm just blind.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

Manie Verster (9/8/2010)
How much free space should I have in relation to the size of the database file?

it might be anything above initial size + auto growth size(percentage). please verify it this is guess of mine

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Manie Verster SSCertifiable Points: 7152 More actions · Answer 11

Did some more reading and testing and here is a link you can check out. This Microsoft link actually does it on the gui and not by script and I tried it just to see if anything happens that did not normally happen. I did as they said and on the reorganize files before releasing unused space I said 5 % in the maximum free space in the files after shrinking (might be a bit low - was 18% before I started) and then I saw a difference. I would not recommend you to do the same but look at what you can make it and be safe and then try it. By the way, this DBCC SHRINKDATABASE and when I clicked on script this action I got the following:

USE [MyDatabase]

GO

DBCC SHRINKDATABASE(N'MyDatabase', 5)

GO

Bet you haven't tried that yet! Yet there is one more question. What is the ideal free space size in relation to the database size.

I have an answer here but not confirmed. Can someone confirm please?

it might be anything above initial size + auto growth size(percentage). please verify it this is guess of mine
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

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

Manie Verster (9/8/2010)
What is the ideal free space size in relation to the database size.

There's no one good answer to that. Depends on how large the tables are, how fast the DB grows, etc. I like to have at least a couple weeks (preferably a month or more) estimated growth as free space, and no less than the size of the largest index in the DB (for rebuilds)

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

vikasaqua15 Grasshopper Points: 16 More actions · Answer 13

What is the size of the datafile you are trying to Shrink

vikasaqua15 Grasshopper Points: 16 More actions · Answer 14

I see that you are trying to shrink a 122 Gig Data file. I think you might have already tried DBCC SHRINKFILE instead of shrinkDatabase. Secondly, Have you checked the values in transaction_sequence_num or first_snapshot_sequence_num columns of sys.dm_tran_active_snapshot_database_transactions view.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 15

What is the size of the datafile and what is the DBCC option you are using to shrink the data file?

Best Regards,

SQLBuddy