retrieve log space details

Question

Post reply

retrieve log space details

Mad-Dog

SSCertifiable

Points: 6117
More actions
December 13, 2009 at 7:21 am

#139983

Hi,
how can i retrieve the log space details?
i need something like this command DBCC SHOWFILESTATS but for the log file
how with this commad DBCC SQLPERF (LOGSPACE) i can view every log file by itself with the current size and free space.
THX

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Maybe something like this will do the trick for you:

select file_id, name, physical_name, size * 8 / 1024 as SizeInMB,

fileproperty(name, 'SpaceUsed') * 8 / 1024 as UsedInMB

from sys.database_files

where type = 1 --only log files

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mad-Dog SSCertifiable Points: 6117 More actions · Answer 2

are you sure that the spaceused is calculated right with this syntax?

THX

george sibbald SSC Guru Points: 104210 More actions · Answer 3

--run in context of db you want size info for

declare @dbname sysname

SET @dbName = DB_NAME()

-- Create a temporary table to store the DBCC SQLPERF results.

CREATE TABLE #tempLogSpace (dbName sysname,

LogSize real,

LogSpacePctUsed real,

stat int)

-- Execute the DBCC SQLPERF statement and insert the results into

-- the temporary table.

INSERT INTO #tempLogSpace EXEC ('DBCC SQLPERF (LOGSPACE)')

-- Output the log size and used log space (in MB).

SELECT dbname,CAST (CONVERT (decimal (8,3),

ROUND(LogSize,3)) AS varchar(20)) + ' MB',

CAST (CONVERT (decimal (8,3),

ROUND (LogSize * LogSpacePctUsed / 100.0,3)) AS varchar(20)) +

' MB', getdate()

FROM #tempLogSpace

WHERE LOWER (RTRIM(LTRIM(dbName))) = LOWER (RTRIM(LTRIM(@dbName)))

-- Get rid of the temporary table.

DROP TABLE #tempLogSpace

2000th post!

---------------------------------------------------------------------

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

It isn't accurate because I didn't convert the numbers to a data type that supports fractions, so I might be off by a little bit. If this is not good enough then you can try this one:

select file_id, name, physical_name, size * 8.0 / 1024.0 as SizeInMB,

fileproperty(name, 'SpaceUsed') * 8.0 / 1024.0 as UsedInMB

from sys.database_files

where type = 1 --only log files

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

A few examples are on my blog post:

Getting log space usage without using DBCC SQLPERF

Make sure to read the comments section as well.