How to shrink a log file, data file or Truncate a data file.

Problem: How to shrink a log file, data file or Truncate a data file.

Solution: DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

In our day to day activity we need some space on the drive to perform some maintenance or transactions to happen.

Example:

In this article I am going to show you how we can shrink a log file using dynamic sql and to set the parameters based on users input.

Each time we use database and do some activity the log files grows as we run the updates or deletes on the database to simple recovery mode.

DECLARE @db NVARCHAR(50) = 'db',

                @size VARCHAR(10) = '100',

                @sql NVARCHAR(4000);

SET @sql = 'use [' + @db + ']; '

EXEC sp_executesql @sql

SET @sql = '

alter database [' + @db + ']

set recovery simple;'

EXEC sp_executesql @sql

SET @sql = 'dbcc shrinkfile (' + @db + '_log ,' + @size + ');'

EXEC sp_executesql @sql

OUTPUT: