Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 775
Ice Avatar asked May 10 '11 07:05

Ice


2 Answers

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

insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
EXEC('DBCC SQLPERF(LOGSPACE);')
like image 198
Martin Smith Avatar answered Nov 11 '22 22:11

Martin Smith


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.

like image 39
Contango Avatar answered Nov 11 '22 20:11

Contango