Does SQL Backup reduce Transaction log size?

[blin]

We are running Microsoft SQL 2005. We use SQL Maintenance Plan to do FULL backup every night. Our SQL database is sonly 1GB but the log file(*.ldf) is over 13GB. I assume if we do the full backup, it should reduce the log file.

I have been monitoring the backup every day in last week. All backup are successful but the log file keep 13G. If my assumption is incorrect, how can we keep the log file size low?

[blin]

From the problem description, I know that we wonder how to keep the log file size low.
If I misunderstand, please feel free to let me know.

Firstly I’d like to explain that a full backup of database won’t reduce the size of log file. Generally we can follow the steps below to shrink the log file when the log file is too large.
1. Open Microsoft SQL Server Management Studio
2. Connect the instance
3. Run the following command to make a backup of the database log (Note: If the Recovery Model of the database is Simple, we can skip this step and continue with Step 4.)
backup log dbname to disk = 'd:\dbname.trn'
Note: Please replace dbname with the name of the database and d:\dbname.trn with the location to back up. For example, D:\XXXX\xxxx.trn
4. Run the following command to shrink the transaction log
USE dbname
GO
DBCC SHRINKFILE (log_file_name, target_size)
GO
Note: Please replace the dbname with the name of the database, log_file_name with the logical name of the database log file (We can right-click the database, select Properties, we can see it on Files page.), target_size with the target size. For example: We can have a try to shrink the log file to 1024M. DBCC SHRINKFILE ( XXXX_log, 1024)

If the log file grows too quickly after shrinking, we may change the Autogrowth setting to File Growth In Megabytes 300M or 400M.

1. Right-click on the database and select Properties
2. Click Files on the left pane
3. Check the Autogrowth settings on the right pane

==========================

Regarding how much we can shrink the transaction log, please refer to the article below:

DBCC SQLPERF (Transact-SQL)
http://msdn.microsoft.com/en-us/library ... 8(v=SQL.90).aspx

We may run the following command:

Dbcc sqlperf(logspace)
go

Log Size (MB): Current size allocated to the log. This amount is always smaller than the amount originally allocated for log space because the SQL Server 2005 Database Engine reserves a small amount of disk space for internal header information.
Log Space Used (%): Percentage of the log file currently occupied with transaction log information.

The percentage of log file which we can shrink is: 1 - Log Space Used (%).

For your reference:
=====================
Shrinking the Transaction Log
http://msdn.microsoft.com/en-us/library ... 7(v=sql.90).aspx

If anything is unclear, please feel free to let me know.

Thank you for your kindly understanding and patience! Have a nice day.


--------------------------------------------------------------------------------
Best regards,

Nathan LiuCheng
Partner Online Technical Community

[blin]

Thank you for the tip. However, can we have another portion without running command manually?

I did some research. I find two possible solutions. 1) create a transaction log backup that will reduce the log file size. 2) Change the recovery mode from Full to Simple. Do you think these two options will work?

[blin]

The solutions you mentioned won’t help on this issue. They will just help truncate the transaction log but will not reduce the size. Please refer to this article: http://technet.microsoft.com/en-us/libr ... 5(v=sql.90).aspx And we need read the articles below before we change the recovery model of a database.

http://msdn.microsoft.com/en-us/library ... 3(v=sql.90).aspx

http://msdn.microsoft.com/en-us/library ... 7(v=sql.90).aspx

Now I think we may consider the following methods:

1. Add one maintenance plan task:

Shrink Database Task (Maintenance Plan)
http://technet.microsoft.com/en-us/libr ... 2(v=sql.90).aspx

DBCC SHRINKDATABASE (Transact-SQL)
http://msdn.microsoft.com/en-us/library ... 8(v=sql.90).aspx

2. Or create a SQL Agent job which will run the commands I mentioned in my last reply as scheduled.

How to: Create a Transact-SQL Job Step (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library ... 0(v=sql.90).aspx

If we do want to shrink the log file regularly, I would recommend using method 2 of which effect would be better.

Note: I would like to explain that Partner Online Technical Community support provides service from Monday to Friday (your local business hours), EXCLUDING HOLIDAYS. So, the next reply will be received on the next business day. I'd appreciate your attention and understanding.

Thank you for your kindly understanding and patience! Have a nice day.


--------------------------------------------------------------------------------
Best regards,

Nathan LiuCheng
Partner Online Technical Community

[blin]

transaction log backup does reduce virtual log size. Please check this page: Why and How to Create SQL Transaction Log backup - http://www.howtonetworking.com/msapps/sql14.htm