Cannot shrink data file sql 2005 sp2

Question

Post reply

Cannot shrink data file sql 2005 sp2

murray50

Old Hand

Points: 386
More actions
September 5, 2008 at 9:26 am

#127266

I have tried shrinking the database (log files shrink fine) using both the gui and t-sql,
I have reindexed with re-org and tried again still nothing,
I have updated statistics and tried again - still nothing
I ran checkdb - no errors
The database was created initially with a 3 gig file size
Has anyone else had this happen? Any suggestions on what else to try?

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

When you say 'can't shrink' do you mean it's giving you an error?

Why are you shrinking the data and log files anyway?

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

murray50 Old Hand Points: 386 More actions · Answer 2

There is no error - it just doesn't get any smaller.

I'm shrinking it because I'm trying to move it to a test server that doesn't have enough space for the database - I cleaned out some data and am trying to shrink the database so it can be moved.

sodeep SSC Rookie Points: 34 More actions · Answer 3

you shouldn't shrink Production server just to get data to test server. Do you need everything from Prod server to be in Test server?

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

Is there space free within the data file? What does the following return?

exec sp_spaceused

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

murray50 Old Hand Points: 386 More actions · Answer 5

Its a copy of the prod database that is being shrunk - not the actual production data

sp_spaceused returns:

database size unallocated space

name 35217.63 MB28683.45 MB

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

Check if there is any open transaction in database by running DBCC OPENTRAN. If it returns nothing then its fine.

Shrink in pieces as u said u are not able to do it at one shot.

Do this way:

DBCC Shrinkfile(Fileid,30000)

then once the above statement completes it execution slightly reduce the target size:

DBCC Shrinkfile(Fileid,28000)

and so on....

HTH

MJ