Shrinking databases (and log files)

Support for Micosoft SQL Server in the context of Jiwa installations.

Shrinking databases (and log files)

Postby Mike.Sheen » Wed Jan 21, 2009 6:43 pm

Quite often, people find their log files grow quite large and find managing disk space and backups with such large logs becomes problematic.

This often occurs after an upgrade, or some other event where large numbers of transactions are affected in a single atomic query.

The SQL Server data file and log file often contain slack or unused space. We've written a stored procedure to show the usage of space, and the unused space each table takes up - run the following query to see the results :

Code: Select all
exec usp_JIWA_SpaceUsed


of particular note would be the unused space column returned. Why SQL Server does not always reclaim this space is not something I've ever found an answer to.

However, I have managed to find a sure-fire method of reclaiming it.

The DBCC SHRINKFILE command will try to shrink the database and log file to a specified size. All you need to do is provide the logical file name, and the desired size.

eg :

Code: Select all
DBCC SHRINKFILE (N'jiwadatalog' , 100)
go
DBCC SHRINKFILE (N'jiwadata' , 2000)
go


Where 'jiwadatalog' and 'jiwadata' are the logical database file names – you can determine this by looking at the database properties in management studio. The second parameter passed in is the desired file size. Don't worry about putting a figure in smaller than the actual used space, SQL Server detects this and tries to shrink the files to be smallest space possible, if you provide a file size smaller than the data requires.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron