Transaction Log Sizes in 6.5

Question

Transaction Log Sizes in 6.5

runelords

Ten Centuries

Points: 1275
More actions
July 24, 2002 at 11:33 am

#158748

I am able to find allocated space and used space for a database by querying the sysusages tables for a database and the allocated space of the log but how can I get the space used for the log?
This is the code I am currently working with:
DECLARE @DBSIZE DEC(15,0)
DECLARE @FREESPACE DEC(15,0)
DECLARE @SPACEUSED DEC(15,0)
DECLARE @LOGSIZE DEC(15,0)
SELECT @DBSIZE = SUM(CONVERT(DEC(15),SIZE))
FROM master..SYSUSAGES
WHERE dbid = db_id('SDDSS') AND segmap =3
SELECT @DBSIZE AS AllocatedDBSize
SELECT @LOGSIZE = SUM(CONVERT(DEC(15),SIZE))
FROM master..SYSUSAGES
WHERE dbid = db_id('SDDSS') AND segmap =4
SELECT @LOGSIZE AS AllocatedLogSize
SELECT database_name = 'SDDSS',
database_size = (@DBSIZE/512),
spaceused=(@DBSIZE/512-(@DBSIZE/512 -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM SDDSS..sysindexes
WHERE indid in(0,1,255))/512))
SELECT database_name = 'SDDSS',
Log_size = (@LOGSIZE/512),
spaceused=(@LOGSIZE/512-(@LOGSIZE/512 -
(SELECT SUM(CONVERT(DEC(15),reserved))
FROM SDDSS..sysindexes
WHERE indid in(0,1,255))/512))
Thank you
Art Lorenzini
DBA
State of South Dakota
Arthur Lorenzini

Arthur Lorenzini

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ashishd_24 Valued Member Points: 62 More actions · Answer 1

I use this command: DBCC SQLPERF(LOGSPACE) in order to display logspace allocation info and %used for each DB in the Query window.

runelords Ten Centuries Points: 1275 More actions · Answer 2

I need this in a recordset so I can do some reporting. Could you advise me how I can take the commands that you have suggested and create a recordset.

Arthur Lorenzini

runelords Ten Centuries Points: 1275 More actions · Answer 3

I need this in a recordset so I can do some reporting. Could you advise me how I can take the commands that you have suggested and create a recordset.

Arthur Lorenzini

ashishd_24 Valued Member Points: 62 More actions · Answer 4

The output of DBCC SQLPERF(LOGSPACE) is a recordset. I have used it in a VB code and it works just like a stored procedure or an SQL query. By default all users have permission to execute this command.

quote:
I need this in a recordset so I can do some reporting. Could you advise me how I can take the commands that you have suggested and create a recordset.
Arthur Lorenzini

runelords Ten Centuries Points: 1275 More actions · Answer 5

Why does this not work:

SELECT INTO tblFileStats

EXEC ('sp_spaceused')

I keep geeting a sysntax error in 6.5

Arthur Lorenzini