About Me

My photo
Grant, Florida, United States
I have been in the technology industry for almost 10 years always jumping at the opportunity to learn something new. I founded the Space Coast SQL Users Group to keep me from getting obsolete. When I am not in “Tech-Mode” I love spending time with my family golfing, bowling, boating, fishing, scuba diving, motorcycling and whatever else we come up with.

Thursday, May 28, 2009

shrink the database

Shrink data files process

Think about an 8 oz cup that is only holding 1 oz of water. You can use a 4 oz cup to hold the water and it would be easier to store. You must consider why the decision was to us an 8oz cup to begin with. Often a large purge creates a half empty cup. Once the need has been established, below are the steps that I took to shrink a 30gb file that only needed 4gb of space.


1.
Review the transaction log size prior to the shrinking process.

USE YOURDB
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO


2.
Set the database recovery model to 'simple'.

USE YOURDB
GO
ALTER DATABASE YOURDB SET RECOVERY SIMPLE
GO


3.
Issue a checkpoint against the database to write the records from the transaction log to the database.

USE YOURDB GO
CHECKPOINT
GO


4.
Ttruncate the transaction log.

USE YOURDB
GO
BACKUP LOG YOURDB WITH NO_LOG
GO





5.
Record the logical file name for the transaction log to use in the next step.

USE YOURDB GO
SELECT Name
FROM sysfiles
WHERE name LIKE '%LOG%'
GO

6.
Free the unused space in your transaction log and return the space back to the operating system, shrink the transaction log file.

USE YOURDB
GO
DBCC SHRINKFILE (YOURDB_New_Empty_Log, 100)
GO


7.
Review the database transaction log size to verify it has been reduced.

USE YOURDB
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%LOG%'
GO



To Shrink the DATA File

8.
Review the DATA File size prior to the shrinking process.

USE YOURDB
GO
SELECT *
FROM sysfiles
WHERE name LIKE '%DATA%'
GO

9.
Shrink the DATA File in MB to the size you want the file to be, it can’t be smaller than the actual file.

USE YOURDB
GO
DBCC SHRINKFILE (YOURDB_New_Empty_Log, 100)

No comments: