NetManaged
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@dayspringfh c.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_fi le = 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!
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@dayspringfh
@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_fi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.
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'SQLDBNa me'), 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.
ASKER
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.co
@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_fi
Thanks heaps!