How to catch the output of a DBCC-Statement in a temptable

Question

I tried the following on SQL-Server:

create table #TmpLOGSPACE(
  DatabaseName varchar(100)
  , LOGSIZE_MB decimal(18, 9)
  , LOGSPACE_USED decimal(18, 9)
  ,  LOGSTATUS decimal(18, 9)) 

insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
DBCC SQLPERF(LOGSPACE);

...but this rises an syntax-error...

Any sugestions?

Anglonorman

Answer 1

Put the statement to be run inside EXEC('')

insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
EXEC('DBCC SQLPERF(LOGSPACE);')

Nerissa

Answer 2

This does not directly answer the question, but it does answer the intent of the question: presumably, you want a simple way to find the current size of the log file:

SELECT size*8192.0/1024.0/1024.0 as SizeMegabytes 
FROM sys.database_files
WHERE type_desc = 'LOG'
-- If the log file size is 100 megabytes, returns "100".

The reason why we multiply by 8192 is that the page size in SQL server is 8192 bytes.

The reason why we divide by 1024, then again by 1024, is to convert the size from bytes to megabytes.

Hodden