Solved

SQL 2008 SQL Agent Job to E-mail Query result for DB freespace

Posted on 2010-11-16
4
817 Views
Last Modified: 2012-05-10
Hello,

I have the following working query and results:
USE TopsData
GO

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
Results:
bName                                                                                                                           FileName                                                                                                                         CurrentSizeMB                           FreeSpaceMB
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
TopsData                                                                                                                         NewTops_Data                                                                                                                     36946.437500                            33787.250000
TopsData                                                                                                                         NewTops_Log                                                                                                                      8146.562500                             8084.000000
However, when I attempt to use it with the following to send the results as an e-mail:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
EXEC msdb.dbo.sp_send_dbmail',
@subject = 'Error Log ',
@attach_query_result_as_file = 1 ;
I get an error at Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'SpaceUsed'.
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
A  'SpaceUsed' is not a recognozed option appears when I mouse hover. However, the works without using a send mail attempt.
I am attempting to monitor the freespace inside a single DB and want to run this as a scheduled job once a night.
Help!

0
Comment
Question by:NetManaged
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 62 total points
ID: 34152322
Change your query to:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
EXEC msdb.dbo.sp_send_dbmail',
@subject = 'Error Log ',
@attach_query_result_as_file = 1
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 63 total points
ID: 34153148
You could also create a view for the query specified in the @query statement.

Also you seem to be attempting to execute the sp_send_dbmail statement in teh query text too. You should take this out as it wont work.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files;
[b]EXEC msdb.dbo.sp_send_dbmail',[/b]
@subject = 'Error Log ',
@attach_query_result_as_file = 1 ; 

Open in new window


Also when working with query outputs I always use
@query_result_no_padding=1
as it makes the email more readable.

0
 

Author Comment

by:NetManaged
ID: 34155410
Jorge & EvilPostit

Thanks for the prompt response. Using your suggestions from each of you I got the results I was looking for. I want to split the points as both of you had input that corrected problems I was having with the original script.
Here is the finished product:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@somebody.com,
@query = 'USE TopsData
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;',
@subject = "Error Log",
@attach_query_result_as_file = 1

Thanks heaps!
0
 

Expert Comment

by:cgitst
ID: 34774122
I dont know if this thread is still being monitored but I am trying to something similiar and getting errors when its trying to process the query.  My query is below:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'CGSQL',
    @recipients = 'user@abc.com',
    @query = 'SELECT object_name(IPS.object_id) AS [TableName],
            SI.name AS [IndexName],
            IPS.Index_type_desc,
            IPS.avg_fragmentation_in_percent,
            IPS.avg_fragment_size_in_pages,
            IPS.avg_page_space_used_in_percent,
            IPS.record_count,
            IPS.ghost_record_count,
            IPS.fragment_count,
            IPS.avg_fragment_size_in_pages
      FROM sys.dm_db_index_physical_stats(db_id(N'SQLDBName'), NULL, NULL, NULL , 'DETAILED') IPS
            JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
            JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
      WHERE ST.is_ms_shipped = 0
      ORDER BY 1,5',
      @subject = 'Automated Success Message',
      @attach_query_result_as_file = '1'

The error I am getting is:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'SQLDBName'.

If i run this query without the EXEC msdb.dbo.sp_send_dbmail, it runs fine.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question