how to reduce the log file of a large database?
-
i have a database with a large log file database and i don't know how to do to make it smaller,,, the file have a large area space what can i do???
You are not telling us what type of DB you are using. Beware of erasing log files or minimizing log levels since mistakes do happen and when they do, these files are life savers especially in production.
Natza Mitzi
-
You are not telling us what type of DB you are using. Beware of erasing log files or minimizing log levels since mistakes do happen and when they do, these files are life savers especially in production.
Natza Mitzi
-
thanks... :) sorry,,, SQL SERVER database would u help me ???
modified on Tuesday, March 17, 2009 7:06 PM
-
i have a database with a large log file database and i don't know how to do to make it smaller,,, the file have a large area space what can i do???
run this if you are working in sql 2005 server. but for this u need to make your database offline first and then run this code. --INF: How to Shrink the SQL Server 7.0 Transaction Log -- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD -- SQL7 http://www.support.microsoft.com/kb/256650 -- SQL2000 http://support.microsoft.com/kb/272318/en-us -- SQL2005 http://support.microsoft.com/kb/907511/en-us -- select db_name() -- select * from sysfiles -- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !! SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT -- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. *** SELECT @LogicalFileName = 'Test_Log', -- Use sp_helpfile to identify the logical file name that you want to shrink. @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 305 -- 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 'Fina
-
run this if you are working in sql 2005 server. but for this u need to make your database offline first and then run this code. --INF: How to Shrink the SQL Server 7.0 Transaction Log -- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD -- SQL7 http://www.support.microsoft.com/kb/256650 -- SQL2000 http://support.microsoft.com/kb/272318/en-us -- SQL2005 http://support.microsoft.com/kb/907511/en-us -- select db_name() -- select * from sysfiles -- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !! SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT -- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. *** SELECT @LogicalFileName = 'Test_Log', -- Use sp_helpfile to identify the logical file name that you want to shrink. @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 305 -- 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 'Fina