The following is a trick for quickly shrinking the size of the transaction log which you think has become much larger than you would like and remember that this would not work if the database has more than one log file.
|
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', 'tempdb', 'model')
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"
|