SQL backup and Log file

[guest]

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?


--------------------------------------------------------------------------------

[guest]

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