Shrink database SQL Query

Shrink database data.

USE DatabaseName;
GO
-- Shrink the truncated file to 1024MB.
DBCC SHRINKFILE (DataBaseFileName, 1024);
GO

Shrink database log.

USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseFileLogName, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Advertisements

3 thoughts on “Shrink database SQL Query

  1. USE AdventureWorks2012;
    GO
    — Truncate the log by changing the database

    recovery model to SIMPLE.
    ALTER DATABASE AdventureWorks2012
    SET RECOVERY SIMPLE;
    GO
    — Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
    GO
    — Reset the database recovery model.
    ALTER DATABASE AdventureWorks2012
    SET RECOVERY FULL;
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s