Shrink Database runs out of space on drive

Question

Shrink Database runs out of space on drive

jason.stephens

Ten Centuries

Points: 1088
More actions
July 6, 2009 at 7:10 am

#384921

I have a database that recently got 47 GB of data removed and a purge process initiated to stop the growth. It should now have a nice sinusoidal size. I won't go into the reasons for why it got this way in the first place, but suffice it to say, I need to shrink it down.
Here is the issue. When I shrink the database, it runs out of space on the drive and fails. The database is in full recovery mode. Is the proper solution for fixing the issue to do a backup, set it to simple, shrink database, and then set it back to full recovery mode? Would that stop the growth of the transaction log so that it can do the shrink or does SQL still grab the space to do the shrink regardless of the recovery method?
The reason I ask is I have to call a downtime to make this happen and shrinking a database isn't the quickest operation. The longer the downtime, the more grief I get. Not to mention, if it fails again, I will get even more grief.
Is the proper order of operations what I said above or is there a step down method I should employ?
Thanks for any input.

Viewing 9 posts - 1 through 8 (of 8 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

If you are not already, use shrinkfile rather than shrinkdatabase.

Take a backup, put the database in bulk_logged mode, shrink the database file in chunks , not one big go, and backup the log between shrinks. At the end of the process put database back in full recovery mode and take a transaction log backup. You can cancel shrink jobs and any work they have done in moving extents will remain done it just won't release space back to the OS.

you will need to reindex the database afterwards as shrink causes fragmentation so do not shrink database file all the way down, leave some room for reindex to use.

An alternative you could try is to run shrink with truncateonly and see what that gives you. If it drops right down you can always then grow it back to a sensible size. Won't need to amend recovery mode that way.

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I like George's first suggestion. Go in chunks, use shrinkfile.

jason.stephens Ten Centuries Points: 1088 More actions · Answer 3

Currently the database is at 67 GB. I need it around 25 GB. Would the following be an exact example of what you are saying? T-SQL correct?

-- STEP 1-----------------------------------------------------------------------

USE MedReconNet

GO

ALTER DATABASE MedReconNet

SET RECOVERY BULK_LOGGED;

GO

-- STEP 2-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 40000);

GO

-- STEP 3-----------------------------------------------------------------------

BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';

GO

-- STEP 4-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 35000);

GO

-- STEP 5-----------------------------------------------------------------------

BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';

GO

-- STEP 6-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 30000);

GO

-- STEP 7-----------------------------------------------------------------------

BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';

GO

-- STEP 8-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 25000);

GO

-- STEP 9-----------------------------------------------------------------------

BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';

GO

-- STEP 10-----------------------------------------------------------------------

ALTER DATABASE MedReconNet

SET RECOVERY FULL;

GO

george sibbald SSC Guru Points: 104210 More actions · Answer 4

jason.stephens (7/6/2009)
Currently the database is at 67 GB. I need it around 25 GB. Would the following be an exact example of what you are saying? T-SQL correct?
-- STEP 1-----------------------------------------------------------------------
USE MedReconNet
GO
ALTER DATABASE MedReconNet
SET RECOVERY BULK_LOGGED;
GO
-- STEP 2-----------------------------------------------------------------------
DBCC SHRINKFILE(MedReconNet_Data, 40000);
GO
-- STEP 3-----------------------------------------------------------------------
BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';
GO
-- STEP 4-----------------------------------------------------------------------
DBCC SHRINKFILE(MedReconNet_Data, 35000);
GO
-- STEP 5-----------------------------------------------------------------------
BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';
GO
-- STEP 6-----------------------------------------------------------------------
DBCC SHRINKFILE(MedReconNet_Data, 30000);
GO
-- STEP 7-----------------------------------------------------------------------
BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';
GO
-- STEP 8-----------------------------------------------------------------------
DBCC SHRINKFILE(MedReconNet_Data, 25000);
GO
-- STEP 9-----------------------------------------------------------------------
BACKUP LOG MedReconNet_Log TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn';
GO
-- STEP 10-----------------------------------------------------------------------
ALTER DATABASE MedReconNet
SET RECOVERY FULL;
GO

don't need ; and GO

full backup first, thats your recovery point in event of disaster.

why the jump from 67 straight to 40?

backup log should refer to the dsatabase name MedReconNet not the file name. and different names for the transaction log backup files.

One more log backup after reseting recovery mode to full

Then reindex as soon as you can.

starting of with an initial DBCC SHRINKFILE(MedReconNet_Data, truncateonly) seems worth a try to me, could save some time.

---------------------------------------------------------------------

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Also, make sure you don't shrink too far. Leave enough space available for normal growth (usually 6 months) and enough space to rebuild/reorganize your largest table.

And finally, remember that shrinking a database causes logical fragmentation of your indexes and you will need to rebuild all of your indexes. If you shrink the file too much, then perform a reindex the database is going to grow again.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jason.stephens Ten Centuries Points: 1088 More actions · Answer 6

All excellent points. Thanks.

I don't have a clue why I started at 40. I am just that absent-minded I guess 🙂

I didn't have the reindex or backup in there because I was going to be using the GUI to fire them off, but I went ahead and put the backup in there. Still going to use the optimization job from my maintenance plan for the reindex part.

I left a good chunk of space for the database. It is actually only about 21-22 GB total. I figure 3 GB should be enough space.

-- STEP 1-----------------------------------------------------------------------

USE MedReconNet

GO

BACKUP DATABASE MedReconNet to DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_Data.bak'

GO

-- STEP 2-----------------------------------------------------------------------

ALTER DATABASE MedReconNet

SET RECOVERY BULK_LOGGED

GO

-- STEP 3-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, TRUNCATEONLY)

GO

-- STEP 4-----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_1.trn'

GO

-- STEP 5-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 60000)

GO

-- STEP 6-----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_2.trn'

GO

-- STEP 7-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 55000)

GO

-- STEP 8-----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_3.trn'

GO

-- STEP 9-----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 50000)

GO

-- STEP 10-----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_4.trn'

GO

-- STEP 11----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 45000)

GO

-- STEP 12----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_5.trn'

GO

-- STEP 13----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 40000)

GO

-- STEP 14----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_6.trn'

GO

-- STEP 15----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 35000)

GO

-- STEP 16----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_7.trn'

GO

-- STEP 17----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 30000)

GO

-- STEP 18----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_8.trn'

GO

-- STEP 19----------------------------------------------------------------------

DBCC SHRINKFILE(MedReconNet_Data, 25000)

GO

-- STEP 20----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_9.trn'

GO

-- STEP 21----------------------------------------------------------------------

ALTER DATABASE MedReconNet

SET RECOVERY FULL;

GO

-- STEP 22----------------------------------------------------------------------

BACKUP LOG MedReconNet TO DISK = N'F:\MSSQL\BACKUP\MedReconNet\MedReconNet_tlog_10.trn'

GO

-- STEP 23----------------------------------------------------------------------

Reindex step is going to be me firing off the optimizations job that exists currently in the maintenance plan.

That cover it?

I would just like to say that this is an incredible resource and community. Thanks for all your help guys!

P.S. I don't need that first t-log backup, do I?

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

The tlog backup won't matter. If there isn't much happening, it's a tiny file. Better to have it than not.

As far as space, I'd agree you need 3-6 months of space for data, for logs you need the space to handle peak activity between log backups.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

looks like you are good to go. Of course what values you shrink to after the truncateonly will depend on what effect the truncateonly actually has, might chop a lot off, might do nothing.

after your first real shrink you will have an idea of what size chunks you can get away with from the size of the transaction log backup. You can also use dbcc sqlperf(logspace) to monitor log usage whilst the shrink is running.

enjoy.

---------------------------------------------------------------------