数据库日志收缩

A. 将数据文件收缩到指定的目标大小

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

B. 将日志文件收缩到指定的目标大小

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. 截断数据文件

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

D. 清空文件

USE AdventureWorks2008R2;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    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