purge the tran log
-
hi folks
i'm sitting here having a stress attack cos i am sure there is a way to clear down the tran log, but can't remember how!
using sql server 2k ee, fully patched, have trn log with current size at 8470mb and space used at 8035mb (ie. only 4% left - AAAGhhh).
have already used shrinkdatabase from em, but it won't let me clear down all the committed trans and then shrink. any ideas?
ta very much
sue.
-
You can try BACKUP LOG DBNAme WITH TRUNCATE_ONLY.
After truncating it, you should backup your db.
Do you have replication setted up in your server? If so, check if the Log reader Agent is running, because you might have transactions marked for replication.
-
I've run into this in the past, I ended up backing up the tran log to a different server with enough space. Then run DBCC SHRINKDATABASE or DBCC SHRINKFILE to shrink your transaction log to the desired size.
-
you can also use WITH NO_LOG
-
I always use the following script and it works great. After the script finishes and the logfile is still bigger then you want, just run it again for a couple of times...
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [databaseName] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'database log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 4, -- Limit on time allowed to wrap log.
@NewSize = 10 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
-
If you still need help, and you are willing to trust me, then provide me
the name of the database, and I will send you a procedure to truncate the log.
-
sounds like there are transactions out there in some of the virtual log files...
"Keep Your Stick On the Ice" ..Red Green -
You can see what is going on with the
DBCC LOGINFO command
"Keep Your Stick On the Ice" ..Red Green -
I use the following script for housekeeping:
use MyDb;
DBCC ShrinkDataBase(MyDb,10);
BACKUP LOG MyDb WITH TRUNCATE_ONLY;
DBCC SHRINKFILE(MyDb_log,1);
It shrinks the database, truncates the log, and shrinks the log.
You should use it only after a backup, or when you don't care about restoring to the present state.
-
may be that can help you:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=103703
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply