Backups and log file growth issues

I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:

BACKUP DATABASE [DSS] TO DISK =

N'G:\MSSQL\Backup\DSSbackup.BAK'

WITH INIT , NOUNLOAD , NAME = N'DSS backup',

SKIP , STATS = 10, NOFORMAT

Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?

Thanks!

Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO

Thanks!

|||

I have few observation

(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.

(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup

(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit

(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script

EG.

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

Read more about TL architecture and Backup method /Recovery model in BOL

Read these article also :-

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu

|||

Thank you for your response.

(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.

(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.

(d) As this database is new, I will try your script commands to reduce the log file this one time.

This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.

Thanks again for your response!

|||

I performed a full database backup and a transaction log backup. I ran the following query:

DBCC SHRINKFILE (DSS_log)

..and received the following message:

Cannot shrink log file 2 (DSS_log) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?