After performing a SQL Log file backups, the Transaction Log file does not appear to get truncated and the file size does not decrease.
It is normal for the SQL transaction log files to remain the same size even after they have been truncated. The truncate function only increases the free space in the log file to allow SQL to reuse that space vs creating a new log file.
To view the file usage and decrease the size of the log file, do the following:
1. Open SQL Enterprise Manager, expand the Microsoft SQL Servers | SQL Server Group | SQL Server | Databases
branch (Figure 1)Figure 1
2. Right-click the database desired, and then select View | Taskpad
(Figure 2)Figure 2
3. Scroll on the right pane and view the Space allocated
section. This section shows the file usage for both the database and transaction log (Figure 3). If there is excess free space in the log file, then this file's size can be minimized.Figure 3
3. Move the curser over the yellow arrow button, and then select Shrink Database
from the shortcut menu (Figure 4)Figure 4
4. Click Files
(Figure 5)Figure 5
5. From the Database file
list, select the log file name (Figure 6)Figure 6
6. Click OK
and this will shrink the log file (Figure 7). Important:
To exit and not shrink the database, click Cancel
from the next window.Figure 7
7. Press <F5> to refresh the view of the database files usage. The log file size would be smaller.
If that doesn’t decrease the database size much, do the following:
1. First to be on the safe side, let’s back it up (this step is optional)
Select New Query type the following:
BACKUP LOG [DB_name] TO DISK=’D:\configLogBackup.bak’
where DB_name is the name of the config database file and D:\configlogbackup.bak is the location and file name of where the backup will be.
And click Execute
This may take a while if your log file is big.
2. Next clear the query (or click New Query again) and enter the following commands
BACKUP LOG [DB_name] WITH TRUNCATE_ONLY
and click Execute again
3. Clear the query or open another query tab and enter the next command:
DBCC SHRINKFILE (N’DB_name’ , 50)
The 50 in the command above sets the size in MB to truncate the log to. If the config db is of different name, replace theDB_name part above with the config db name.
And click Execute yet again.
Note: This applies to Microsoft SQL Server 2000, 2005, 2008 and 2008R2