Solved

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

Posted on 2010-11-16
4
775 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
4 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 62 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now