March 23, 2011 at 8:12 am
I have a 1TB database that I've been shrinking for over 15 hours. I'm wondering if it really is working or hung somewhere. This is an 8 processor enterprise server with 16GB ram. The Activity Monitor shows Physical IO and CPU activity. This is a simple warehouse system, no competing activity. ?????????
March 23, 2011 at 8:16 am
It depends.
I know that answer blows, but it really does depend.
1) Database shrinking or File shrinking?
2) Through the GUI or a DBCC command?
3) If you used the DBCC command, please post the syntax.
4) If you used the GUI, what options did you choose?
March 23, 2011 at 8:19 am
Using Management Studio logged on to the server.
dbcc shrinkfile (TLOG)
Any thing else just ask.
March 23, 2011 at 8:24 am
You didn't give it a target_size?
Run SP_WHO2 to see if anything is blocking the process. Double-check the hard drive to make sure it isn't full. Double-check the tempdb (and your user db) data and log files to make sure they're not maxed out.
March 23, 2011 at 8:31 am
No blocks. Only 206 GB remaining on the server.
March 23, 2011 at 8:34 am
Do you have lots of large data types or images on this database?
March 23, 2011 at 8:45 am
No images. I believe bigint, decimal(18,0),varchar(max) are the biggest data sizes in the DB.
March 23, 2011 at 8:50 am
The only advice I can give you is to engage PerfMon and either Profiler or a server-side trace to see what (if any) activity is going on.
It may very well be that this is the normal time SQL Server needs to do this shrink. But it may be hung and you can't see that hang through SSMS.
Another thought is that if you're shrinking via SSMS on a non-local box (client as opposed to server), you're not seeing the commands come through the network on a timely basis.
March 24, 2011 at 8:29 am
for huge shrinks i've noticed that doing it in small chunks is usually a lot faster than in one
March 25, 2011 at 5:48 am
As Alen said, do it in small chunks. I would add printing a message for each chunk so you know that something is happening. Of course, it takes a while for the buffer to flush you'll a lot messages in bursts. This is a script I use:
USE <DB Name>
GO
DECLARE @vLogicalFilename VARCHAR(250)
DECLARE @vTargetSizeinMB INT
DECLARE @vCurrentSizeinMB INT
DECLARE @vDecrementinMB INT
DECLARE @vsql VARCHAR(250)
SET @vLogicalFileName = 'DataFileName'
/* Please make sure that the Target size is achievable
otherwise the script will go in an infinite loop */
SET @vTargetSizeinMB = 21000
SET @vDecrementinMB = 200 /* Don't set this value to more than 2000 */
IF EXISTS (SELECT *
FROM sysfiles
WHERE name = @vLogicalFileName)
BEGIN
SELECT @vCurrentSizeinMB = size / 128
FROM sysfiles
WHERE name = @vLogicalFileName
WHILE (@vTargetSizeinMB <= @vCurrentSizeinMB - @vDecrementinMB)
BEGIN
SELECT @vsql = 'DBCC SHRINKFILE(' + @vLogicalFileName + ',' + Cast((@vCurrentSizeinMB - @vDecrementinMB) AS VARCHAR(10)) + ')'
SELECT @vsql
EXEC( @vsql)
SELECT @vCurrentSizeinMB = size / 128
FROM sysfiles
WHERE name = @vLogicalFileName
END
END
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply