May 28, 2008 at 5:39 pm
Hi,
I have 10 GB LOG file. I'm trying to shrink it by using DBCC SHRINKFLE (2,1024) (2 is id of my log file) before moving it to another location. DBCC executes with no errors but does just nothing.
DBCC SQLPERF (LOGSPACE) shows that log is used in 0.98%. DBCC OPENTRAN(dbid) gives 'No active open transactions'. I've tried BACKUP LOG [database] WITH TRUNCATE_ONLY - no effect. Database is in Full recovery mode.
Any clue ??
Regards,
Slawek
May 28, 2008 at 6:06 pm
Using various commands to shrink a database can be troublesome. Try using the following commands.
---------------------------------------------
ALTER DATABASE <Db name)
set recovery simple
DBCC SHRINKDATABASE (db name,5,TRUNCATEONLY)
ALTER DATABASE
set recovery full
----------------------------------
This works for me everytime.
May 28, 2008 at 6:33 pm
Thanks,
Funny thing:
ALTER DATABASE [dbname]
set recovery simple
DBCC SHRINKFILE (2,TRUNCATEONLY)
ALTER DATABASE [dbname]
set recovery full
worked fine... (I didn't wanted to use SHRINKDATABASE as I have pre-sized data files, which I didn't want to touch). Funny because I've already tried DBCC SHRINKFILE (2,TRUNCATEONLY) alone, before I posted here, and didn't work...
Thanks for help,
Slawek.
May 29, 2008 at 12:11 am
The problem is that shrinking (on a logfile) only truncates the free space at the end of your logfile (an almost exact question was also on this forum this week, see my reply on that). That's why it's almost impossible to shrink a logfile if you don't have the recovery mode in simple.
If you put your db in simple mode, used space in a logfile will be marked as reusable after the transaction which used the space is committed. That's the reason why shrinking a logfile in simple mode nearly almost works. If you have your database in full recovery mode, try shrinking the logfile immediately after a full or logbackup.
Keep in mind that shrinking and expanding files is a very resource intensive operation. If your logfile is large (in your opinion), consider making more backups or changing to simple recovery.
It's useless to shrink the logfile on a regularly base if it's growing back to the size before shrinking
Wilfred
The best things in life are the simple things
May 29, 2008 at 3:26 am
Hi,
Actually I had this problem with two databases. Two out of seven on that server. One was in Full recovery model, and another was already in Simple, and on both DBCC SHRINKFILE(2,1024), neither DBCC SHRINKFILE(2,TRUNCATEONLY) didn't even touch the log files.
I've even changed recovery model for that databases to Simple in SSMS... What I didn't try is to run 3 mentioned commands in one batch..
Really wreid..
Regards,
Slawek
May 29, 2008 at 3:28 am
.. and, frankly speaking, i was pretty sure that truncating log with BACKUP LOG [db] WITH TRUNCATE_ONLY also marks log space as reusable...
Regards,
Slawek
May 30, 2008 at 4:14 am
This is what BOL has to say regarding TruncateOnly:
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
Given the current conversation, I thought you might find it interesting.
May 30, 2008 at 4:18 am
Oh, and regarding the BACKUP LOG... Truncate_Only option...
Under the simple recovery model, performing a checkpoint removes the inactive part of the log without making a backup copy. This truncates the log by discarding all but the active log. This option frees space, but risks possible data loss. After the log is truncated by using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the truncated portion of the log are not recoverable until the next database backup. Therefore, for recovery purposes, after using either of these options, immediately execute BACKUP DATABASE to take a full or differential database backup.
Caution:
We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.
Of course, BOL also mentions this option is going away in the future.
May 30, 2008 at 5:03 am
Here it comes again .... :hehe:
THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL to find out how a db-log works !
With full recovery, you need to perform LOG-backups on a regular basis or your log file will grow !! (Backup log ...)
-- Shrink_TrxLog.SQL
--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 = 'logicalname', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- 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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 30, 2008 at 8:49 am
ALZDBA (5/30/2008)
THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !!
Thanks AL.
May 30, 2008 at 9:00 am
a shrink of the log file can only shrink down to the last used virtual log file. Use command 'dbcc loginfo(dbname)' to find the last used (they have a status of 2). if the very last one is used the file won't shrink. Thats why you need a script like ALZDBA's to 'wrap' the last used virtual log file to the front of the log.
---------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply