March 13, 2008 at 7:49 am
Ok... after 1 year I am still "new" at being a DBA. From what I understand, if I have my database set in Full Recovery mode (backed up weekly) with transaction log backups (done daily), the transaction logs should "shrink" (not a physical file shrink, but remove the VLFs not in use). Some of the log files have grown absolutely huge:
mydb.mdf: 7,441 MB
mydb.log: 237,000.4 MB
I ran a DBCC SQLPERF (LogSpace) and only 1.8% of the log on this database is actually being used!
I then tried to review the log using DBCC LOGINFO (mydb) and I get the following (truncated just to show an example):
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
215138816819251072640
21513881615147008510421280
21513881630285824510521280
21539276845424640510621280
2150732860817408457306428000001125900400
2150732862324736457406428000001125900400
2150732863832064457506428000001125900400
(data Truncated for brevity)
220578304035399729152524221281876000020620100000
220578304035605512192524321281876000020620100000
220578304035811295232524421281876000020620100000
220630732836017078272524521281876000020620100000
222636134436223385600496901282899000013363700000
222636134436449746944497001282899000013363700000
222636134436676108288497501282899000013363700000
(data Truncated for brevity)
243614208075456970752518521284232000076490800000
243614208075893112832518621284232000076490800000
243660083276329254912518721284232000076490800000
FYI, for those not used to reading this, Status = 2 means VLF is "in use", Status = 0 means VLF is not in use.
Since there are "in use" VLF(s) at the end of the log file, doing a "DBCC SHRINKFILE (mydb_log, 5500)" will fail. After doing a backup of the logfile, the only way to truly free up space is perform another backup with a "TRUNCATE_ONLY", then do the shrinkfile.
I know there are no transactions running against the database (this is a "backup" database... portions of it get sync'd [RedGate Data Compare 6] at the end of the week).
From BOL: After the log is backed up, the space may be truncated when no longer required by transactional replication or active transactions.
I guess the overall question here is what exactly is done to the log file to free up the space/VLFs? If the VLFs at the end of the log file have a status of "0", will they be removed at the next log backup? Should I be doing a shrink after the log backup to try and move the used VLFs to the beginning of the log file?
I also have a few "static" DBs on this server (no activity for 6 months now) and I have a similar problem with log files 10x the size of the data file: cannot do a "shrink" as there are in-use VLFs at the end of the file.
Just as an FYI, here is my daily log backup routine (someday I will get rid of of cursor! hahaha...Wrote this almost 1 year ago today.):
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases --WHERE database_id > 6
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(2000)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement = N'BACKUP LOG [' + @DBNameVar + N'] TO DISK = ''D:\Backup\'
+ @DBNameVar + N'_log_' + CONVERT(varchar(23),getdate(), 112) + '.bak'' WITH RETAINDAYS = 7, INIT, STATS=10 ';
Print @Statement
--EXEC sp_executesql @Statement
PRINT CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO
Server OS: Windows 2003 Server Standard Edition, SP2
SQL Server: 2005 SP2 (9.0.3033) Standard Edition
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 13, 2008 at 8:47 am
You might want to consider taking backup more frequently.
this will reduce the number of VLF's as the space gets freed more frequently. So the file does not need to grow to accomodate a whole days worth of transaction.
Backing up the Transaction log does not shrink the file unless you have autoshrink truned on...which i wouldn't recommend.
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 8:59 am
Ellis,
Thanks for the response! Unfortunately, on this server the databases tend to be pretty static during the week. There will be only one of two things happening:
1) Weekly (Thursday Evening) SQL Data Compare sync (one database table).
2) Periodic (ad hoc) "archive" of data from a Production server to this server. This typically happens once a month or once a quarter, depending on the Production database.
I could change the SQL Agent to just process multiple log backups on the days I know there is a lot of activity (great suggestion!), but what about the databases have have been static (no activity) for 6 month that have huge logs?
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
March 13, 2008 at 9:06 am
Could you switch the recovery model to simple or bulk-logged when these loads run, then take a full backup after? this will help manage the growth in the log.
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 7:29 pm
If the backup database is synchronized using SQL Data Compare then transactions are definitely occurring. That tool generates the DML statements needed to do the synchronization.
From the DBCC LOGINFO output it looks like autogrow has occurred more than a few times. Might be worthwhile putting a profiler trace on it and tracking when autogrow operations occur to see if it coincides with other activities.
This BOL topic might be of interest: Factors That Can Delay Log Truncation
March 18, 2008 at 7:05 am
First of all check for any open transactions by executing command
DBCC OPENTRAN
Second, while syncronizing database do DML in small chunk i.e. commit transaction frequently.
Third, set Tlog to perform every 1 hr.; if there are no activity there wouldn't be much data for tlog backup and there won't be any sideiffect. But it will keep check on the size of your logfile.
In static database also when you will excecute the maintenance task your logfile will grow and if it will happen repetitively without tlog frequently; there will be cumulative effect on size of logfile.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 18, 2008 at 7:24 am
DB_Newbie2007 (3/13/2008)
I could change the SQL Agent to just process multiple log backups on the days I know there is a lot of activity (great suggestion!), but what about the databases have have been static (no activity) for 6 month that have huge logs?
If you have maintenance plans rebuilding indexes, reorg'ing, etc... then that will generate a LOT of activity in the T-logs (even if there's NO change in the data). If those databases really are static - then slow down the rate at which you do those things (say - once a week, maybe?)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply