How to Shrink Database File ?

Leave a comment (2) Go to comments

SQL Script to shrink a unused space from a database file.

-- ***********************************************************************************/
-- Description  :    SQL Script to shrink a non used space from a database
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use <database name>
go
-- Knowing information about database file
sp_helpfile
go
--- Shrinking a particular database file
dbcc shrinkfile (name= <datafile name>, <target percent>)
go

Where

  • file_name, Is the logical name of the file to be shrunk.
  • target_size, Is the size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Example, to shrink the database file

First, I need to get the get the information about the current database file, specially the logical file name, which should be shrunk by executing sp_helpfile stored procedure.


In my example, I executed sp_helpfile system stored procedure in pubs database to get current database files information (logical file name and size is important). In my case file index_alter, is of 204800 KB (200 MB), which I wanted to shrunk, as show in below screen shot.

How to Shrink Database File ? sql server shrink database file sql server HOW To SHRINK a Database file sql script to shrink database file shrink database file HOW To SHRINK a Database files DBCC shrinkfile

Shrinking index_alter file size to 20 MB, using the following script.

-- ***********************************************************************************/
-- Description  :    SQL Script to shrink a non used space from a database
-- Compatibility  :     7.0+
-- This Script should be executed in master
-- ***********************************************************************************/
use pubs
go
sp_helpfile
go
DBCC SHRINKFILE ( 'index_alter',20)
go

OUTPUT

How to Shrink Database File ? sql server shrink database file sql server HOW To SHRINK a Database file sql script to shrink database file shrink database file HOW To SHRINK a Database files DBCC shrinkfile

Once the File is shrunk and you recheck the file status by running sp_helpfile, you will see the new size, which 20480 in this case, as shown below.

How to Shrink Database File ? sql server shrink database file sql server HOW To SHRINK a Database file sql script to shrink database file shrink database file HOW To SHRINK a Database files DBCC shrinkfile

EOF - How to Shrink Database File ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. Very Correct Mark, Objective is to have a handy script, just in case required, on the fly.

  2. Yes – but usually in a production environment it’s a bad idea to shrink a file… :grin:

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.