Solved

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

Posted on 2010-11-16
4
805 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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