Link to home
Start Free TrialLog in
Avatar of zilll53
zilll53

asked on

Cannot shrink datafile in SQL Server database

I am trying to shrink database and having issues, i.e. I ran DBCC SHRINKFILE('data_file', target_size) for a few hours and it finished without any errors, but the size did not change. I ran DBCC SHRINKFILE('data_file', notruncate) and then tried to shrink again with no luck. And I put db into Simple Recovery. It's SQL Server 2005.
Currently allocated space: 421846.81 MB
Available free space: 226521.25 MB (53%)
Shrink file to: Minimum is 195326 MB
Any help appreciated.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

if the data_file does not shrink, it could be because of:
* data being written at the end of the file(s)
* tables being heaps without a clustered index.

in both cases, you could try to (drop and re) create clustered indexes on the table(s) to see if that helps.
Avatar of zilll53
zilll53

ASKER

I thought that NOTRUNCATE moves unused space to the end of the file. I will try to rebuild indexes for all tables though and let you know. Thanks.
Avatar of zilll53

ASKER

I rebuiled indexes on all tables and tried to shrink database with no luck. The weird part is it shows allocated space 421000, used space: 250000 and yet still can't shrink. I done this so many times and never had any issues.
Please check if there are any active transactions on the database (like backup etc) which may prevent from shrinking the database effectively.
Also, shrinking will impact the performance of the database, so it is not suggestible. Instead, allocate the data file required size at initial stage itself and keep a maximum limit for it.
Try this First take full backup and then shrink.
Avatar of zilll53

ASKER

There are no active transactions against this database. I put this db in single user mode and yes I know that this will impact performance though it is a weekend and there's no activity on the server at all.
Not sure what full backup would accomplish, but I did full backup and still can't shrink db.
ASKER CERTIFIED SOLUTION
Avatar of zilll53
zilll53

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zilll53

ASKER

THe reason why I am accepting my own comment is because no other comments gave me the solution.
And the reason why I selected grade lower than 'A' is because MS Support helped me.