April 20, 2012 at 4:56 am
I want to write a SQL job that generates an excel file containing the result of DBCC SQLPERF(LOGSPACE).
All the examples I have seen don't seem to work with the output of DBCC commands.
What approaches can I use for this? I am using SQL 2008.
Thanks.
April 20, 2012 at 5:09 am
SSIS is probably the way to go here. Otherwise, create a table with the same columns and data types as the DBCC output, and do INSERT INTO MyTable ('EXEC DBCC SQLPERF(LOGSPACE)'). You can then bcp out to Excel (or set it up your spreadhseet as a linked server) in the normal way.
John
April 20, 2012 at 6:13 am
I'm going to completely turn this around now and say, I don't want to capture the result and email a file, I want to show the result in the body of an email.
How can I do that in SSIS?
April 20, 2012 at 6:25 am
Hi John,
I just didn't know about that system proc. Thanks!
April 20, 2012 at 7:27 am
I was sick of creating SSIS packages, especially for something that should be quite simple, and wanted to try
the sp_send_dbmail proc that John pointed out.
The proc worked straight away but the results were pretty ugly, so I made it all HTML. This is what I came up with:
DECLARE @HTML NVARCHAR(MAX);
SET @HTML = '<table><tr><td>Database</td><td>Log Size</td><td>Percent</td><tr>';
DECLARE @SQLPERFRESULT TABLE
(
DBName varchar(32),
LogSize real,
LogSpaceUsed real,
Status int
);
DECLARE @SQLPERFCOMMAND varchar(500);
SELECT @SQLPERFCOMMAND = 'DBCC SQLPERF(LOGSPACE)';
INSERT INTO @SQLPERFRESULT EXEC (@SQLPERFCOMMAND);
SELECT @HTML = @HTML + '<tr><td>' + DBName + '</td><td>' + CONVERT(VARCHAR,LogSize) + '</td><td>' + CONVERT(VARCHAR,LogSpaceUsed) + '</td></tr>'
FROM @SQLPERFRESULT;
SET @HTML = @HTML + '</table>';
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admins@company.com',
@body = @HTML,
@body_format = 'HTML',
@subject = 'SQL Log Space';
Craig
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply