Wednesday, September 10, 2008

timing of shrink database vs shrink datafile

I have a sql server 2005 database with single data file and transaction log. The data file size is 40Gb and there is 23Gb free space.
So I want to shrink it by click "shrink database". By the help of belw sql, it only completed 50% even after running 3.5 hours.
select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests;

I decide to stop it and change to use "shrink file". Surprising, it completed less than 1 minutes and return 7Gb to disk space.

Funny.