July 29, 2009 at 4:39 pm
I have a SQL 2008 enterprise database that is 26527 megs in size – exactly 26527 megs in size – we’ll get to why that is important later J
We have changed some storage rules and I no longer need the database to be that large. We are currently actually using about 11 gigs of that space and I’d like to shrink to about 15 gigs.
My Database started at 27500 megs
When I started attempting to shrink this database I was running a DBCC SHRINKFILE(N’LogicalDataFileName’,15000)
To monitor the progress on this shrink I was using the following script:
select
T.text,
R.Status,
R.Command,
DatabaseName = db_name(R.database_id),
R.cpu_time,
R.total_elapsed_time,
R.percent_complete
FROM sys.dm_exec_requests R (NOLOCK)
cross apply sys.dm_exec_sql_text(R.sql_handle) T
order by Command
After a few seconds this script would report out a percentage to me of about 56% - it would hang at that percent for hours and say that the command had been suspended.
I tried this a few times – killed the process a few times when it started hanging in the suspended state.
Next theory I had – start trying to shrink the database by much smaller increments.
DBCC SHRINKFILE(N’LogicalDataFileName’,27400) – done in seconds
DBCC SHRINKFILE(N’LogicalDataFileName’,27200) – done in seconds
DBCC SHRINKFILE(N’LogicalDataFileName’,26750) – done in seconds
DBCC SHRINKFILE(N’LogicalDataFileName’,26500) – process started hanging with a suspended status
By process of trial and error I finally got the database down to 26527 megs in size and ran the following:
DBCC SHRINKFILE(N’LogicalDataFileName’,26526) - process started hanging with a suspended status
Throughout this entire ordeal – no processes were blocking the DBCC SHRINKFILE process.
Any idea what could be blocking me from shrinking my database even just 1 meg further?
March 13, 2011 at 5:56 pm
I have exactly the same issue with a large database (about 1000 GB with about 500 GB free space) in SQL Server 2005 Enterprise SP3.
There are 13 data files in the same file group, and each of them can be shrunk up to some value, and then the operation hangs as suspended with percent_complete > 99,9% for hours... there are no locks (tried in SINGLE USER mode as well), there is some IO activity (up and down, and again, and again)... and finally I have to kill it.
Tried multiple times.
This is a clustered SQL Server installation, and drives are on SAN.
spaceused for the database:
database_name database_size unallocated space
MyDb 915185.63 MB 425287.60 MB
reserved data index_size unused
480340312 KB 349189640 KB 9116256 KB 122034416 KB
spaceused for tables (the top 3):
name DataMB rows reserveddata data index_size unused
Table1 308562 7756013 430413048 KB 308562592 KB 147872 KB 121702584 KB
Table2 30352 3811533 30678256 KB 30352344 KB 1280 KB 324632 KB
Table3 5533 30933586 11942792 KB 5533000 KB 6408488 KB 1304 KB
And yes, I do have LOB fields in there, ntext fields.
I've already done the following:
DBCC UPDATEUSAGE (0) WITH COUNT_ROWS
DBCC CHECKDB WITH ALL_ERRORMSGS, DATA_PURITY
DBCC CLEANTABLE for all tables
ALTER INDEX ALL ON ... REBUILD for all tables
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply