Retrieving the Transaction Log Information

February 21, 2001

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers