How to catch the output of a DBCC-Statement in a temptable
I tried the following on SQL-Server:
create table #TmpLOGSPACE(
, LOGSIZE_MB decimal(18, 9)
, LOGSPACE_USED decimal(18, 9)
, LOGSTATUS decimal(18, 9))
insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS)
…but this rises an syntax-error…
Put the statement to be run inside
insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) EXEC('DBCC SQLPERF(LOGSPACE);')
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.