alchemistmatt (4/16/2013)

---

I'm now running this in a production environment on 8 servers. I have some suggested fixes for you

Expand [Filename] to NVARCHAR(255) when creating table #BACKUPS. I have some long database names leading to long backup paths and with nvarchar(128) the stored procedure was reporting an error due to truncation.

Personally, I would have stored FileMBSize as an int in table FileStatsHistory, but you have it as nvarchar. Thus, you need to Cast to an int when performing comparisons. In usp_CheckFiles the code selects from FileStatsHistory into #TEMP and leaves FileMBSize as nvarchar. Then you perform a comparison of "t.FileMBSize < t2.FileMBSize" and that ends up comparing a text value to a numeric value, which results in incorrect results (I was receiving e-mails saying a log file had grown when instead it had shrunk; very perplexing). The solution I went with was to explicitly define #TEMP before creating it, though another option would be to perform the cast on FileMBSize when implicitly creating #TEMP or even to add a cast in tht comparison query looking for FileMBSize increasing.

Also in usp_CheckFiles, you are excluding tables 'model' and 'tempdb' (which is the correct logic). However, those table names are stored in FileStatsHistory as '[model]' and '[tempdb]' so your IN clauses need to have "NOT IN ('model','tempdb','[model]','[tempdb]')". Furthermore, population of #TEMP3 in that procedure needs "IN ('tempdb','[tempdb]')"

My log files commonly grow, so I chose to update usp_CheckFiles to ignore log files less than 200 MB in size. This could potentially be an option if you add an options table

I have a long-running sql backup job that runs using RedGate's Sql Backup and runs via a stored procedure. Thus, I chose to create an AlertExclusions table with two columns: Category_Name and FilterLikeClause. Category_Name has a foreign key relationship to AlertSettings.Category_Name. The Alert Settings table has one row with values 'LongRunningQueries' and 'sqlBackup'. I then updated usp_LongRunningQueries to left outer join to my Alert Exclusions table and filter, like this:

SELECT QueryHistoryID, collection_time, start_time, login_time, session_id, CPU, reads, writes, physical_reads, [host_name], [Database_Name], login_name, sql_text, [program_name]

FROM [dba].dbo.QueryHistory QH

LEFT OUTER JOIN [dba].dbo.T_Alert_Exclusions AlertEx

ON AlertEx.Category_Name = 'LongRunningQueries' AND QH.sql_text LIKE AlertEx.FilterLikeClause

WHERE (DATEDIFF(ss,start_time,collection_time)) >= @QueryValue

AND (DATEDIFF(mi,collection_time,GETDATE())) < (DATEDIFF(mi,@LastCollectionTime, collection_time))

AND [Database_Name] NOT IN (SELECT [DBName] FROM [dba].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)

AND sql_text NOT LIKE 'BACKUP DATABASE%'

AND sql_text NOT LIKE 'RESTORE VERIFYONLY%'

AND sql_text NOT LIKE 'ALTER INDEX%'

AND sql_text NOT LIKE 'DECLARE @BlobEater%'

AND sql_text NOT LIKE 'DBCC%'

AND sql_text NOT LIKE 'WAITFOR(RECEIVE%'

AND AlertEx.Category_Name Is Null

I updated usp_LongRunningQueries to make the following change just in case somebody enters a blank value in the CellList column instead of a null value. It would be good to make this change whereever an e-mail and/or cell info is being retrieved from the AlertSettings table

-- Change from

If @CellList Is Not Null

-- To

If IsNull(@CellList, '') <> ''