Retrieving the Transaction Log Information

The following is a small procedure that retrieves the information about the transaction log in the current database, which can be used to monitor the amount of space used and indicates when to take backup or truncate the log.


Create proc p1
as
Begin
Set nocount on
Declare @dbname varchar (128), @logsize real, @logused real

Create table #t1
(
dbname varchar (128),
logsize real,
logused real,
status tinyint
)

Create table #t2
(
fileid tinyint,
filesize int,
startoffset int,
fseqno int,
status tinyint,
parity int,
dateorLSN varchar (25)
)

Insert into #t1 (dbname,logsize,logused,status)
Execute (‘dbcc sqlperf (logspace)’)

Declare c1 cursor
for
Select dbname, logsize, logused from #t1
Where dbname=db_name ()
Open c1
Fetch next from c1 into @dbname, @logsize, @logused
Print ”
Print ‘The total transaction log size in ‘ + @dbname + ‘database is: ‘ + convert(varchar(38), @logsize) + ‘MB’
Print ”
Print ‘The transaction log space used in ‘ + @dbname + ‘database is: ‘ + convert(varchar(38),(@logsize*@logused)/100) + ‘MB’
Print ”
Print ‘The free transaction log space in ‘ + @dbname + ‘database is: ‘ + convert(varchar(38),((@logsize) – (@logsize*@logused)/100)) + ‘MB’
Insert into #t2
Execute (‘dbcc loginfo’)
Print ”
Print ‘The following is the information about the log: ‘
Print ”
Select fileid, filesize, startoffset, fseqno, status, parity from #t2
Close c1
Deallocate c1
End

When you execute the procedure for example on pubs database the output will be as follows:


The total transaction log size in pubs database is: 0.7421875000MB
The transaction log space used in pubs database is: 0.481933MB
The free transaction log space in pubs database is: 0.260255MB


The following is the information about the log:


fileid filesize startoffset fseqno status parity
—— ———– ———– ———– —— ———–
2 253952 8192 10 0 128
2 253952 262144 11 0 64
2 270336 516096 12 2 64


From this you can see that the transaction log is internally divided into small virtual logs. And the row with status value equal to 2 indicates that it is the active part of the transaction log. Remember shrinking will only happen when the active part of the log is at the beginning of your log.

This procedure also uses DBCC LOGINFO command and if you run this command in SQL7.0 and SQL 2000 , you will get different results.


For example:
DBCC LOGINFO(northwind) in SQL 7.0


FileId FileSize StartOffset FSeqNo Status Parity CreateTime
————————————————————————————–
2 253952 8192 23 2 128 1998-11-13 03:12:19.873
2 253952 262144 22 0 64 1998-11-13 03:12:09.937
2 253952 516096 21 0 64 1998-11-13 03:12:07.703
2 278528 770048 20 0 64 1998-11-13 03:12:06.233

DBCC LOGINFO(northwind) in SQL2000

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
—————————————————————————-
2 253952 8192 23 2 128 0
2 253952 262144 22 0 64 0
2 253952 516096 21 0 64 0
2 278528 770048 20 0 64 0

Notice that the last column has been changed and we will discuss more about this and about the shrinking of transaction log in SQL7.0 and SQL2000 in the next article.


Let me know if you have any concerns, comments or suggestions.


Thanks,
Krishnan.

Latest Articles