DELETE scripts
WHERE name = 'script_check_space'
GO
INSERT scripts
VALUES('script_check_space','DECLARE @dbid INT, ')
INSERT scripts
VALUES('script_check_space',' @dbSize dec(15,0), ')
INSERT scripts
VALUES('script_check_space',' @reserved dec(15,0), ')
INSERT scripts
VALUES('script_check_space',' @used dec(15,0), ')
INSERT scripts
VALUES('script_check_space',' @percentFull INT,')
INSERT scripts
VALUES('script_check_space',' @msg VARCHAR(255),')
INSERT scripts
VALUES('script_check_space',' @qry VARCHAR(255), ')
INSERT scripts
VALUES('script_check_space',' @dbName VARCHAR(30), ')
INSERT scripts
VALUES('script_check_space',' @mailTo VARCHAR(30) ')
INSERT scripts
VALUES('script_check_space','SET NOCOUNT ON ')
INSERT scripts
VALUES('script_check_space','SELECT @dbName = DB_NAME()')
INSERT scripts
VALUES('script_check_space','SELECT @mailTo = "SQL_DBA" ')
INSERT scripts
VALUES('script_check_space','SELECT @dbid = DB_ID(@dbName) ')
INSERT scripts
VALUES('script_check_space','SELECT @dbSize = SUM(CONVERT(DEC(15),size))*2')
INSERT scripts
VALUES('script_check_space','FROM master..sysusages ')
INSERT scripts
VALUES('script_check_space','WHERE dbid = DB_ID(@dbName) ')
INSERT scripts
VALUES('script_check_space','SELECT @used = SUM(CONVERT(DEC(15),used))*2')
INSERT scripts
VALUES('script_check_space','FROM sysindexes ')
INSERT scripts
VALUES('script_check_space','WHERE indid IN (0, 1, 255) ')
INSERT scripts
VALUES('script_check_space','SELECT @percentFull = (CONVERT(INT, (1 - (@dbSize - @used)/@dbSize)*100))')
INSERT scripts
VALUES('script_check_space','IF @percentFull > 90')
INSERT scripts
VALUES('script_check_space',' BEGIN')
INSERT scripts
VALUES('script_check_space',' SELECT @msg = "Database " + @dbName + ')
INSERT scripts
VALUES('script_check_space',' " is " + CONVERT(VARCHAR(2),@percentFull) + "% Full!"')
INSERT scripts
VALUES('script_check_space',' SELECT @qry = "USE " + @dbName + " EXEC sp_spaceused"')
INSERT scripts
VALUES('script_check_space',' EXEC master..xp_sendmail ')
INSERT scripts
VALUES('script_check_space',' @recipients = @mailto,')
INSERT scripts
VALUES('script_check_space',' @subject = "DATABASE SPACE WARNING",')
INSERT scripts
VALUES('script_check_space',' @message = @msg,')
INSERT scripts
VALUES('script_check_space',' @query = @qry')
INSERT scripts
VALUES('script_check_space',' END ')
INSERT scripts
VALUES('script_check_space','SELECT "database " + @dbName + ')
INSERT scripts
VALUES('script_check_space',' CHAR(10) + CHAR(13) + ')
INSERT scripts
VALUES('script_check_space',' "Total Space Available = " + CONVERT(VARCHAR(10), @dbSize) + " KB" +')
INSERT scripts
VALUES('script_check_space',' CHAR(10) + CHAR(13) + ')
INSERT scripts
VALUES('script_check_space',' "Space Used = " + CONVERT(VARCHAR(10), @used) + " KB" + ')
INSERT scripts
VALUES('script_check_space',' CHAR(10) + CHAR(13) +')
INSERT scripts
VALUES('script_check_space',' "Percent Full = " + CONVERT(VARCHAR(3), @percentFull) + " %"')