[SQL Server 2000] Auto shrinking transaction logs?

[Stompy]

For some odd reason, SQL Server 2000 doesn't have a "Truncate Log on Checkpoint" option that 7 had... so now all the transaction logs on our new servers are just getting way outta control.

There's a command you can use to shrink it:

use db
BACKUP LOG db WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('db_Log', 2)

But that's tedious to run it on every individual database.. there's gotta be a way to have SQL do this for you. I can't seem to figure out what it is.

Anyone have any ideas?

[Tirian]

Stompy- if you find out, I'd appreciate you posting the answer in this thread. I too have the same question. Weird thing is, there is a "Schedule this task" checkbox on the backup screen when using the enterprise manager, but it seems to have no effect when I click the checkbox to try and schedule the task. Perhaps I am missing something here ?

[f00sion]

Ive never had a problem with this..but cant you just create a new scheduled job manually?... Tools > Job Scheduling, and then Transact-SQL command?

[Stompy]

Yeah, but there's like 200 databases on this server so that'd be pretty tedious.

Isn't there a more automated method that can be set upon DB creation?

[bobd123]

Just run this code in query analyzer against the master db or set up a agent job to run it on a schedule. It will resize all the transaction logs on the server.

/*

Function: For each user database remove all the freespace and truncate the transaction log.

Instructions: Run against master, the script will use the system catalog to produce a list of databases.
If you want to excluce a database add the excluded db name to the 'not in' list below.

cjm@integer.org
http://www.integer.org

Copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted
provided this notice and the above comments are preserved.
*/

Set quoted_identifier off
use master
go

DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind', '2024', 'MOBILESQL', 'THEGLNDB')

OPEN datanames_cursor

FETCH NEXT FROM datanames_cursor INTO @dataname

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
PRINT " "
PRINT @dataname_header
EXEC("BACKUP LOG " + @dataname + " WITH TRUNCATE_ONLY")
EXEC("DBCC SHRINKDATABASE (" + @dataname + ",TRUNCATEONLY)")
FETCH NEXT FROM datanames_cursor INTO @dataname
END

DEALLOCATE datanames_cursor
PRINT ""
PRINT " "
PRINT "Free space removed and transaction log truncated for each user database"

[Stompy]

you rock!

thanks a ton, that worked like a charm

[twister002]

I'm no SQL DBA but if you set up a maintinence plan for the DB's you can backup your T-logs. The backup truncates them, then you can either offload them to media or delete them automatically after a set amount of time.