March 2, 2010 at 3:27 am
Hello,
I'm facing a strange situation, or it seems so.
Let me tell you:
In the weekend we have emptied our production DB. And after that, the size remained 25G and availabele space 20 G. And then the mess started... Although the DB is in full recovery mode, and log backups are done from 45 to 45 minutes, the ldf file is not shrinking anymore, and I didn't suceed to free that available free space. I don't understand.... Even if the DB size is 14G and available free space is 12G, why is the DB size increasing instead of using space from that free amount?
Please, can you give me some ideas...
Thank you vrey much
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 4:02 am
You database consists at least of two files ( e.g. db_data.MDF and db_log.LDF)
The sum of both files make your total db size, but the LDF file will only contain log records (to be used for roll forward / rollback operations)
The actual data and indexes are stored in the db_data.mdf file(s).
As you have noticed, these files don't shrink automatically, or at least it is not advised to have these files shrunk automatically !
A log file is a special one, because it contains virtual log files that can roll over _within_ this physical file.
Hence it can only be schrunk down to the last active VLF in that file.
That's what why you may need to run the shrink log file procedure a couple of times before your ldf file comes to the size you aim for.
This is a common thread in our forums.
---------------------------
-- 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
-- SQL2008 http://msdn.microsoft.com/en-us/library/ms189493.aspx
-- ----- 'BACKUP LOG ... WITH TRUNCATE_ONLY' no longer supported (sql2008)
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
Declare @OriginalRecoveryMode varchar(50)
Select @OriginalRecoveryMode = convert(varchar(50), DATABASEPROPERTYEX(DB_NAME(),'Recovery'))
Print 'OriginalRecoveryMode: ' + @OriginalRecoveryMode
Declare @AlterDb varchar(1000)
If @OriginalRecoveryMode <> 'simple'
begin
select @AlterDb = 'alter database [' + DB_NAME() + '] set recovery simple;
print ''Recovery model altered for shrink;'''
exec (@AlterDb)
end
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'DDBAStatistics_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 = 25 -- 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(7000)
SELECT @StartTime = GETDATE(),
-- @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
@TruncLog = 'use [' + db_name() + ']
DBCC SHRINKFILE (' + @LogicalFileName + ', ' + convert(varchar(15),@NewSize) + ' , TRUNCATEONLY) WITH NO_INFOMSGS'
-- 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
If @OriginalRecoveryMode <> 'simple'
begin
select @AlterDb = 'alter database [' + DB_NAME() + '] set recovery ' + @OriginalRecoveryMode + ' ;
print ''Recovery model restored to [' + @OriginalRecoveryMode + '] after shrink;'''
exec (@AlterDb)
end
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
March 2, 2010 at 4:21 am
When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size.
To shrink a transaction log file that has little free space in SQL Server 2005, follow these steps:
1)
Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step.
BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
2)
Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
For more detail : http://support.microsoft.com/kb/907511
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 4:35 am
Thank you for your answers. I'll try to see what to do further.
The thing I can't understand at all is why till this weekend, when log backups were doing, the log file (.ldf) was shrinking and now, it isn't 🙁 Can you explain this?
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 4:48 am
can u tell me how much space recovered in weekends and how can u say that it didnt work today(didnt u find any shrinking in log not even in MB ? ) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 4:56 am
In this weekend we've made the DB size something like 6G (from 23) and I'm reffering only to the data file (.mdf) because the .ldf file was constantly at a small size(at the log backups it was decreasing without making any shrink!!!!).
Now, the .ldf size is not decreasing anymore after log backups and it is growing and growing.
I don't know I think it's weird because till now, I haven't had such problems... and all I've tried to do yesterday didn't call back the normal way of acting 🙁
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 5:00 am
blue_inelush (3/2/2010)
the .ldf file was constantly at a small size(at the log backups it was decreasing without making any shrink!!!!).
Do you have any maintenance plan or (job running periodically ) to shrink it .if yes, then check it its working fine or not ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 5:03 am
I would start sqlprofiler, your cleaning up of data may have caused some application to fail, hence it is trying to do its work, but rolls back time and again ... causing your LDF to grow ...
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
March 2, 2010 at 5:05 am
Yes, I have a log backup maintenance plan which is running from 45 to 45 minutes. It's working just fine. I have never had included in my maintenance plans, a shrink step... Just log backup which truncates the log...
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 5:08 am
That' what I don't understand. I knew that if the db is in full recovery model, a constant log backup causes its truncation, so... the .ldf file size decreasing. Am I gettind mad?
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 5:33 am
No a log backup only marks the log content "ready to be overwritten".
It will not shrink your transaction log file !
What is the result of DBCC OPENTRAN (to be executed in the troublesome database !)
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
March 2, 2010 at 5:51 am
then try to work with ALZDBA first post but with secure hands and watchful eyes. see if it can help you
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 6:04 am
ALZDBA, I have run the script provided by you and I've minimized my .ldf files.
My Production database is very intensive, so the .ldf file will increase and increase. In order to avoid this, my understanding is that I have to run manually from time to time a shrink operation.
Thank you very much for your support and hope I won't bother you again with that kind of stuff 😉
Wish you good ideas! 🙂
Andreea
March 2, 2010 at 6:08 am
blue_inelush (3/2/2010)
In order to avoid this, my understanding is that I have to run manually from time to time a shrink operation.
Well if it works for you. you can schedule it in a job(but after good testing).no need of manual run
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 2, 2010 at 6:25 am
blue_inelush (3/2/2010)
ALZDBA, I have run the script provided by you and I've minimized my .ldf files.My Production database is very intensive, so the .ldf file will increase and increase. In order to avoid this, my understanding is that I have to run manually from time to time a shrink operation.
Thank you very much for your support and hope I won't bother you again with that kind of stuff 😉
NO !
Don't shrink a production database (unless you have actually performed huge data cleanup and don't expect it to grow in the near future).
You should size your db-log so it can cope ( size wize ) with "normal" transaction load during two log backups.
If you lack the needed disk space, you should request disk replacement to provide more space to your system.
Keep in mind, extending a file is a costly ( = slow ) operation and may even stall your system for a while !
Gail published a great article "managing transaction logs" as SSC.http://qa.sqlservercentral.com/articles/64582/
This is a must read !
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply