Answered by:
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
Wednesday, August 4, 2010 10:04 AM
Answers
-
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- Proposed as answer by Sudeepta Ganguly Wednesday, August 4, 2010 11:38 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:48 PM
Wednesday, August 4, 2010 11:23 AM -
You may have to use DBCC SHRINKFILE to decrease the size after the backup. See http://support.microsoft.com/kb/907511/en-us.
Jason
- Proposed as answer by JasonRothMicrosoft employee Wednesday, August 4, 2010 10:20 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:48 PM
Wednesday, August 4, 2010 10:18 AM -
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
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:49 PM
Thursday, August 5, 2010 12:32 PM
All replies
-
You may have to use DBCC SHRINKFILE to decrease the size after the backup. See http://support.microsoft.com/kb/907511/en-us.
Jason
- Proposed as answer by JasonRothMicrosoft employee Wednesday, August 4, 2010 10:20 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:48 PM
Wednesday, August 4, 2010 10:18 AM -
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/- Proposed as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:48 PM
Wednesday, August 4, 2010 10:19 AMAnswerer -
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- Proposed as answer by Sudeepta Ganguly Wednesday, August 4, 2010 11:38 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:48 PM
Wednesday, August 4, 2010 11:23 AM -
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
Thursday, August 5, 2010 2:24 AM -
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
- Marked as answer by Ed Price - MSFTMicrosoft employee Tuesday, April 16, 2013 4:49 PM
Thursday, August 5, 2010 12:32 PM