Posts

Showing posts from September, 2014

Growing Log file issues - How to shrink a SQL server Log and data file.

Image
Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file.

Before doing this backup your databases using Management studio Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)



Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

Use [Mydatabase]

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL



The following screen shows the log file before the shrink. Log file after shrink Points of interest Transaction logs have to be backed up in order for them to ‘truncate…