Shrink data and log files - MSSQL

Shrink data and log files

To shrink data and log files for the database, use below query.

use <database>;

DECLARE @mdfName varchar(60);

DECLARE @ldfName varchar(60);

SELECT @mdfName = file_name(1) FROM sys.master_files WHERE database_id = db_id() AND type = 1;

SELECT @ldfName = name FROM sys.master_files WHERE database_id = db_id() AND type = 1;

ALTER DATABASE <database> SET RECOVERY SIMPLE;

    DBCC SHRINKFILE (@mdfName, 0);

    DBCC SHRINKFILE (@ldfName, 0);

    DBCC SHRINKFILE (@mdfName, TRUNCATEONLY);

    DBCC SHRINKFILE (@ldfName, TRUNCATEONLY);

To set database recovery as FULL, use below query.

 ALTER DATABASE <database> SET RECOVERY FULL;