The following script helps in understanding the log file. It gives all the details about transaction log file such as no of VLF’s associated,log internal space usage,last log backup taken,DB recovery mode etc. I think this script helps in understanding the internal log file usage and can be used as a health check script.
/*Databases that are set to 'AutoClose' are not considered by dbcc sqlpref(logspace) and Offline databases are not considered.[FreeSpace_IN_LogFile_toShrink_InBytes] means the amount of shrinkable Log File space in bytes.It calculates this by summing the inactive VLF's from bottom up till it hits active VLF's(Status=2).This does not mean you *can* shrink the Log File.
Please use caution. This is only for informational purposes.
Also, added code to check SQL version because dbcc loginfo on sql 2012 and above returns one extra column.*/
If object_ID('Tempdb.dbo.#TempMain') is not null
Begin
Drop Table #TempMain
End
Create Table #TempMain (
SeqID int Identity(1,1),[Database Name] varchar(max),[Log Size(MB)] decimal(15,3),[Log Space Used(%)] decimal(15,3),[Status] int,UsedLogFileSize_InMB decimal(15,3),No_of_Active_VLFs int,
UnUsedLogFileSize_InMB decimal(15,3),No_Of_InActive_VLFs int,
ReasonFor_LogFile varchar(50),LastLogBackupTaken datetime,Db_RecoveryMode varchar(11),[FreeSpace_IN_LogFile_toShrink_InMB] decimal(15,3))
If object_ID('Tempdb.dbo.#Temp') is not null
Begin
Drop Table #Temp
End
Create Table #Temp (ID int Identity(1,1),RecoveryUnitId int,FileID tinyint,FileSize bigint,StartOffset varchar(max),FSeqNo int,[Status] tinyint,Parity tinyint,CreateLSN varchar(max))
declare @SQlLogSpace nvarchar(max)
set @SQlLogSpace = 'dbcc sqlperf(logSpace)'
Insert #TempMain([Database Name],[Log Size(MB)],[Log Space Used(%)],[Status])
Execute(@SQlLogSpace)
update #TempMain set Db_RecoveryMode =A.recovery_model_desc,
ReasonFor_LogFile=Log_reuse_wait_desc,LastLogBackupTaken=C.[LastLogBackupTime]
from Sys.databases A INNER JOIN #TempMain B on B.[Database Name]=A.Name
LEFT OUTER JOIN (Select max(backup_finish_date) as [LastLogBackupTime],database_name from msdb.dbo.backupset A
INNER JOIN Sys.databases B on B.Name=A.Database_Name
where type ='L' and B.recovery_model_desc in ('FULL','Bulk-Logged')
group by database_name) C on C.[database_Name]=A.Name
update #TempMain set UsedLogFileSize_InMB=cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3)),
unUsedLogFileSize_InMB=cast(([Log Size(MB)]) as decimal(20,3))-cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3))
from #TempMain
declare @a int,@b varchar(max)
select @a =min(SeqID) from #TempMain
While (@a>=1)
Begin
Select @b=[Database Name] from #TempMain where seqID=@a
declare @Sql nvarchar(max)
IF (Substring(cast(SERVERPROPERTY('productversion') as varchar(16)),1,
CHARINDEX('.',cast(SERVERPROPERTY('productversion') as varchar(16)),1)-1))>10
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( RecoveryUnitId,FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End
Else
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End
Execute Sp_Executesql @SQL
Update #TempMain Set No_Of_InActive_VLFs =B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 0) A where #TempMain.SeqID=@a
Update #TempMain Set No_Of_Active_VLFs = B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 2) A where #TempMain.SeqID=@a
Update #TempMain Set [FreeSpace_IN_LogFile_toShrink_InMB] = A
From (select cast((cast(sum(FileSize) as decimal(15,3))/1024.00/1024.00) as decimal(15,3)) as A from #Temp where ID>(Select Max(ID) from #Temp where Status=2)) A where #TempMain.SeqID=@a
Truncate table #Temp
IF (@a<(Select Max(seqId) from #TempMain))
Begin
Set @a=@a+1
continue
End
Else
Begin
select SeqID,[Database Name],Db_RecoveryMode,[Log Size(MB)],
[Log Space Used(%)],UsedLogFileSize_InMB,No_Of_Active_VLFs,
UnUsedLogFileSize_InMB,No_Of_InActive_VLFs,[FreeSpace_In_LogFile_ToShrink_InMB],
ReasonFor_LogFile,LastLogBackupTaken from #TempMain
Return
End
Drop table #TempMain
End
SQL Server Repository 