Scheduling a Stored Procedure
How do I schedule a stored procedure as a job? :confused:
I have a created a Maint plan but it won't run correctly... here is the stored procedure I am trying to schedule to run daily to alert me when a database file is running out of space
CREATE PROCEDURE [dbo].[DBsizeNotification]
as
declare @dbname as varchar(100)
declare @checksize as varchar(1000)
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT name
FROM sys.databases
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @dbname
CREATE TABLE DBSize (DB sysname, SizeMB int)
WHILE @@FETCH_STATUS = 0
BEGIN
Set @checksize = 'use ' + @dbname +
' insert DBSize select db_name(), round(sum(([size]/128.0)),2) from sysfiles'
exec (@checksize)
FETCH NEXT FROM CRS_db INTO @dbname
END
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBA',
@recipients = 'sqldba@ibsa.com',
@query = 'SELECT * FROM DBSize WHERE SizeMB > 40' ,
@subject = '**************************Large Database Alert',
@attach_query_result_as_file = 1 ;
CLOSE CRS_db
DEALLOCATE CRS_db
DROP TABLE DBSize:confused: