Link to home
Start Free TrialLog in
Avatar of NetManaged
NetManagedFlag for United States of America

asked on

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

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!

ASKER CERTIFIED SOLUTION
Avatar of jorge_toriz
jorge_toriz
Flag of Mexico image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of EvilPostIt
EvilPostIt
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NetManaged

ASKER

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!
Avatar of cgitst
cgitst

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.