December 11, 2012 at 8:38 pm
Hi,
I have a database in SIMPLE recovery model and the log file size is approx 30GB (99% free). I am not able to find any open transactions or orphaned transactions.
The transaction log file does not go down even after database full backup is completed. The database has 2 data files and 1 log file.
sp_helpfile for the database shows the below results
namefileidfilename filegroup sizemaxsizegrowthusage
DB1_Data1x:\Data\DB1_Data.MDF PRIMARY 39000960Unlimited 10%data only
DB1_Log2x:\Data\DB1Lob_Data.MDF NULL30864960214748364810%log only
DB1Lob_Data3y:\Log\DB1_Log.LDF PRIMARY_LOBS1437888Unlimited 10%data only
The DB1_Log (Tlog file) has the data file mapped to it and the DB1Lob_Data (Secondary Data file) has the log file mapped to it.
Could this be the reason why the log file is not releasing space? Any clarification on this is much appreciated.
December 11, 2012 at 9:59 pm
Mohammed Imran Ali (12/11/2012)
The transaction log file does not go down even after database full backup is completed
Full database backup will not reduce Tlog size.
Have you verified the status of VLF's? Post the result of DBCC Loginfo(Yourdatabasename).
December 11, 2012 at 11:41 pm
Mohammed Imran Ali (12/11/2012)
Could this be the reason why the log file is not releasing space? Any clarification on this is much appreciated.
The only thing that releases space back to the OS is a shrink file. Nothing else reduces the size of the physical file.
Since the log file is 99% free space, log reuse is working properly and there's no problems here, nothing to be concerned about.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply