Backup SQL Server

Question

Hi,

We are using Third party software to archive email for Exchange 2007. This third party software running with SQL 2005. Lately we found that the database log file (.ldf) consume a lot of HDD space. We tried to backup with third party software but it size not decreased after successfull backup.

May I know is there settings which I missed to prevent database from growing or at least size decrease after backup (full, incremental)

Please advice

Answer 1

You may have to use DBCC SHRINKFILE to decrease the size after the backup. See http://support.microsoft.com/kb/907511/en-us.

Jason

Answer 2

Make sure that  your database is set to SIMPLE recovery mode, then use DBCC SHRINKFILE command to reduce physical size of the LOG

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

You can also schedule a transaction log backup. This has dual benefits. You will be better equipped to recover the database in case of a disaster. Also the size of the transaction log will reduce.

---

Pradeep Adiga
My blog: http://www.sqldbadiaries.com

Answer 4

I found this link: http://serverfault.com/questions/54958/sql-server-2005-2008-does-full-backup-truncate-the-log-in-full-recovery-mode

They had mentioned that Full backup will not truncate the log file. The only way is " The only thing that allows the log to clear/truncate in the FULL or BULK_LOGGED recovery models is a log backup - no exceptions"

But how do i do the log backup?

 

Please advice

Answer 5

The article that I pointed to on shrinking the log file shows the T-SQL syntax for backing up the log file: http://support.microsoft.com/kb/907511/en-us.

Jason