05-25-2004, 08:25 AM | #1 (permalink) |
Banned from being Banned
Location: Donkey
|
[SQL Server 2000] Auto shrinking transaction logs?
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: Code:
use db BACKUP LOG db WITH TRUNCATE_ONLY DBCC SHRINKFILE ('db_Log', 2) Anyone have any ideas?
__________________
I love lamp. |
05-25-2004, 01:02 PM | #2 (permalink) |
Addict
Location: Canada
|
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 ?
|
05-25-2004, 04:05 PM | #3 (permalink) |
Tilted
Location: So. Cali
|
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?
__________________
Tell me what we’re fighting for— I don’t remember anymore, only temporary reprieve. And the world might cease if we fail to tame the beast; from the faith that you release comes an atheist peace. |
06-06-2004, 06:14 PM | #5 (permalink) |
Upright
|
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" |
Tags |
2000, auto, logs, server, shrinking, sql, transaction |
|
|