AX Consulting

Just another WordPress.com site

Shrink SQL File

A. Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user  database to 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

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


C. Truncating a data file

The following example truncates the primary data file in the AdventureWorks2008R2 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2008R2;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);


D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks2008R2;
GO
— Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2
ADD FILE (
    NAME = Test1data,
    FILENAME = ‘C:\t1data.mdf’,
    SIZE = 5MB
    );
GO
— Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
— Remove the data file from the database.
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE Test1data;
GO

Advertisements

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

%d bloggers like this: