TABLE OF CONTENTS
Introduction
A backup of a database is a way of protecting and restoring a file. It is accomplished by the replication of the database and can be done for a database or database server. Database Administrators can use the backup copy of the MigrationStudio database to restore the database and its data and logs to its operating state. The backup of the database can be saved either locally or on a backup server.
If the backup log records were never deleted from the transaction log, the logical log would grow until it fills all the available space on the disks holding the physical log files. At some point in time, old log records no longer necessary for recovering or restoring a database must be deleted to make way for new log records.
The most common reason for uncontrolled log file growth is being in Full Recovery Mode without having any log backups. By default SQL Server creates databases in full recovery mode, unless someone switches its' recovery mode to Simple otherwise that server will create new databases with Full Recovery mode enabled.
How to reduce log file size on disk?
There are 3 steps to reducing the size of a log file on disk:
A. Perform a full Backup.
B. Perform a Transaction Log Backup.
C. Perform a Transaction Log Shrink Operation.
Here are our initial file sizes on the disk.
Launch the Backup Wizard
- Connect to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, expand the server tree
- Expand Databases, right-click MigrationStudio
- Scroll down to Tasks
- Select Back Up….
A) Perform a full Backup
Perform the steps to launch the backup wizard
- Set Backup type to Full.
- Add a place to save your backup. For demonstration purposes, I'm saving it to the same folder as the database files.
Files after Full Backup.
B) Perform a Transaction Log Backup
Perform steps to launch the backup wizard
- In the General Page
- Set Backup type to Transaction Log
- Note we are writing this backup to the same backup file as before, by default this backup will be appended to the 1st.
- In the Media Options Page
- Ensure that the "Truncate the transaction log" option is selected under Transaction Log
- Click Ok
Files after Transaction Log Backup up.
C) Perform a Transaction Log Shrink Operation
Notice how the backup file has increased in size roughly by the size of the transaction log and also the fact the log file is the same size and doesn't look very truncated! What's going on is the fact that the vast majority of that files space has been earmarked for reuse and transactions will flow into the file and it will not increase in size, the logic being that increasing a files size is an expensive operation and so reusing existing filespace is more efficient.
If you really need to reduce the size of the log file after it's been truncated, you need to right-click on the database and select Tasks\Shrink\Files.
Perform the steps to launch the backup wizard
- Change File Type to Log
- Note how there is 1608.70 MB of the 1672 MB free.
- Select Reorganize pages before releasing unused space, set the size you want the file to be, you can set it to zero but it will force SQL Server to resize the file, it's a balancing act between IO efficiency and disk space.
- Click OK
Files after Shrink operation:
Ok, our log file still hasn't truncated, what's going on there?
When we run the shrink command as an SQL statement we get the message above, essentially there is some piece of live data at the end of the file and the shrink command can't shrink past it, keeping the file size artificially large.
To force the shrink operation, take the database offline and drop any active connections. Then rerun the shrink process with a target of 200MB. This time there with no active transactions at the end of the file the Shrink process could complete its task safely, our log is now resized/reduced.
Files after successful shrink operation.
Further Support
If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.