August 17, 2008 at 11:55 pm
Hello
Can anybody give suggestion for shrink .ldf file my ldf file size is 37 GB how can I shrink 50% radius
uuuuuuuuuuu
August 18, 2008 at 1:40 am
Hello,
unless you give us more info, probably the best answer we can give you is to read about shrinking, especially "Shrinking transaction logs", in BOL. You didn't say anything about your database size, recovery model, how often the log is backed up and so on, so we can hardly give you any specific advice - and the general advice is quite fine in BOL.
If the log is about 10% of the datafile size or less, then there is a chance that your log size is correct and shrinking will not help much... but you can try and you'll se how long it takes before it is back to original size.
August 18, 2008 at 1:49 am
Let me guess. Full recovery mode, no log backups.
Not enough info right now to give useful advice. You can read the following for background info.
http://sqlinthewild.co.za/index.php/2008/07/23/recovery-model-and-transaction-logs/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2008 at 2:38 am
I urge you to read BOL or Gails article.
This is a question that returns very frequent in the forum(s).
As always I'll provide the script from MS. Don't just run it, but read the articles !
THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
-- 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
February 21, 2011 at 10:03 am
Right click on Database in SQL Management Studio, then choose tasks-->shrink --> files.
then u will get option to shrink ldf file..
February 21, 2011 at 10:10 am
Righ click onto the Database in SQL server Management Studio, then choose Tasks...Shrink...Files.
This will give u option to shrink ldf file
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply