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.