After performing a SQL Log file backups, the Transaction Log file does not appear to get truncated and the file size does not decrease.

Article:TECH82950  |  Created: 2003-01-08  |  Updated: 2013-07-15  |  Article URL http://www.symantec.com/docs/TECH82950
Article Type
Technical Solution


Issue



After performing a SQL Log file backups, the Transaction Log file does not appear to get truncated and the file size does not decrease.


Solution



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’
GO
 
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
USE [DB_name]
GO
 
and click Execute again
 
3. Clear the query or open another query tab and enter the next command:
 
DBCC SHRINKFILE (N’DB_name’ , 50)
GO
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




Legacy ID



253528


Article URL http://www.symantec.com/docs/TECH82950


Terms of use for this information are found in Legal Notices