I've noticed that the transaction logs for my SQL Server databases just keep growing and growing. I know that there is an option to constrain the log file size, but this sounds like a terrible idea - what happens when all the allocated space is used up?

Here is what I want to do -

1) Set up a system whereby the transaction logs are backed up nightly.

2) Once the logs have been backed up, shrink the actual transaction logs down to a reasonable size (like a couple of gigs or something)

Is this possible? If so, how do I do it? Keep in mind that I am not a DBA.

Thanks.

Run this script as a scheduled job in the SQL Server from the Master database


BACKUP LOG database_name WITH TRUNCATE_ONLY
WAITFOR DELAY '00:00:05'
DBCC SHRINKFILE(db_name, 120)


Where database_name is the name of your database.

The "BACKUP LOG" line is used to truncate the transaction log of the database.

The "WAITFOR" line is used to give the previous step time to complete before the next step in the job executes.

Thanks for the suggestion, shahchi1.

Will that script create an actual backup of the transaction log? If so, where will it put the backup?

Also, we have a sql server agent "maintenance plan" job that runs nightly and makes static dumps of our database. Should we modify this job to also make backups of our transaction logs? Or is it safe to backup the transaction logs seperately, after our "maintenance plan" has finished executing?

No it will only truncate the transaction log if you want to backup your transaction log then specify the log backup device using sp_addumpdevice

USE master
EXEC sp_addumpdevice 'disk', 'MyDbLog1',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyDbLog1.dat'

BACKUP LOG database_name TO MyDbLog1 TRUNCATE_ONLY

Is you current "maintenance plan" use DUMP DATABASE and DUMP TRANSACTION statements? They are synonymous with BACKUP DATABASE and BACKUP LOG statements. Support for the DUMP DATABASE and DUMP TRANSACTION statements may be removed in a future release.