Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have deleted tons of records from my database and now i want to shrink the database. So please give me script which shrinks the database

share|improve this question

closed as off-topic by Aaron Bertrand Jun 26 '13 at 15:09

If this question can be reworded to fit the rules in the help center, please edit the question.

1  
there are ton's of script available out if you had Googled or Binged it. This site is to help you out if you have any particular issue which you have atleast tried to solved and you need a right direction/guidance on how to resolve it. Hope this helps you in future ! –  Kin Jun 26 '13 at 14:21
    
This question appears to be off-topic because it is asking for regurgitation of documentation that already exists. –  Aaron Bertrand Jun 26 '13 at 15:09

3 Answers 3

First off you need to determine which database files that you want to shrink. You can get a rollup of space used on files like so:

use YourDatabase;
go

;with space_used_cte as
(
    select
        name,
        size * 8.0 / 1024 as size_mb,
        fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as space_used_mb
    from sys.database_files
)
select
    name,
    size_mb,
    space_used_mb,
    convert(decimal(5, 2), space_used_mb / size_mb * 100) as space_used_percent
from space_used_cte;

Then when you determine whichever database file you want to shrink, you can issue a DBCC SHINKFILE command. See BOL documentation for syntax. But a common file shrink operation would look like the following:

dbcc shrinkfile(YourDatabaseFileName, <target size in MB>);

If you expect this space to be consumed in the future, it makes no point in shrinking the database file to just have it grow again. Database file shrinking should only be incorporated when you absolutely need the database file space to be reclaimed and don't expect it to be utilized again.

share|improve this answer

This goes without saying : Why you should not shrink your data files ?

If you want to shrink, then after the shrink operation, make sure you do reorg/rebuild of your indexes as well as update stats as required.

Below script will help you to shrink datafile in increments

-- Shrink_DB_File.sql
/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.

Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
*/

declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'nameOfDatabaseFileGoesHere'

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 500

-- Show Size, Space Used, Unused Space, and Name of all database files
select
        [FileSizeMB]    =
                convert(numeric(10,2),round(a.size/128.,2)),
        [UsedSpaceMB]   =
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
        [UnusedSpaceMB] =
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
        [DBFileName]    = a.name
from
        sysfiles a

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
        begin

        set @sql =
        'dbcc shrinkfile ( '+@DBFileName+', '+
        convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

        print 'Start ' + @sql
        print 'at '+convert(varchar(30),getdate(),121)

        exec ( @sql )

        print 'Done ' + @sql
        print 'at '+convert(varchar(30),getdate(),121)

        -- Get current file size in MB
        select @SizeMB = size/128. from sysfiles where name = @DBFileName

        -- Get current space used in MB
        select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

        select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

        end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
select
        [FileSizeMB]    =
                convert(numeric(10,2),round(a.size/128.,2)),
        [UsedSpaceMB]   =
                convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
        [UnusedSpaceMB] =
                convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
        [DBFileName]    = a.name
from
        sysfiles a
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.