Share via

SQL Server: Set SSIS alert if group of database is getting over size

  • Article

Scenario

This article is suitable for several scenarios, but let’s imagine a particular situation: you are a DBA with hundreds of databases to manage and several different servers (University, Big Company, Hospital, etc).

You need to keep the size of all your databases under control and in order to do this you run “on remote” different queries.  This is error prone. Sometimes you forget to do run the process.  Maybe you are on vacation or involved in early morning meetings. Whatever the reason, it is better to automate a process warning you about the status of your databases. SSIS and T-SQL are perfect for the task.

Steps

Create Table

Create a table Forum with the column Over90 in the DB Control (you can choose your favorite name/db)

Create a procedure in Master DB

USE [master]

GO

 /****** Object: StoredProcedure [dbo].[ForumArticle]    Script Date: 12/31/2014 09:38:19 ******/

SET ANSI_NULLS ON

GO

 SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo]. [ForumArticle] as

SET NOCOUNT ON

DECLARE @Kb float

DECLARE @PageSize float

DECLARE @SQL varchar( 2000)

SELECT @Kb = 1024.0

SELECT @PageSize= v. low/ @Kb FROM master.. spt_values v WHERE v. number= 1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL

      DROP TABLE #FileSize

CREATE TABLE #FileSize
(

      DatabaseName sysname,

      FileName sysname,

      FileSize int,

      FileGroupName sysname,

      LogicalName sysname

)

 

IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL

      DROP TABLE #FileStats

CREATE TABLE #FileStats
(

      FileID int,

      FileGroup int,

      TotalExtents int,

      UsedExtents int,

      LogicalName sysname,

      FileName nchar( 520)

)

IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL

      DROP TABLE #LogSpace

CREATE TABLE #LogSpace
(

      DatabaseName sysname,

      LogSize float,

      SpaceUsedPercent float,

      Status bit

)

INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')

DECLARE @DatabaseName sysname

DECLARE cur_Databases CURSOR FAST_FORWARD FOR

      SELECT DatabaseName = [name] FROM dbo.sysdatabases ORDER BY DatabaseName

OPEN cur_Databases

FETCH NEXT FROM cur_Databases INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

  BEGIN

      SET @SQL = '

USE [' + @DatabaseName + '];

DBCC showfilestats;

INSERT #FileSize (DatabaseName, FileName, FileSize, FileGroupName, LogicalName)

SELECT ''' + @DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]

 FROM dbo.sysfiles sf;

'

       INSERT #FileStats EXECUTE ( @SQL)

      FETCH NEXT FROM cur_Databases INTO @DatabaseName

  END

CLOSE cur_Databases

DEALLOCATE cur_Databases

declare @var as int

SELECT @var=COUNT(*) 

 FROM #FileSize fsi

 LEFT JOIN #FileStats fs

      ON fs.FileName = fsi.FileName

 LEFT JOIN #LogSpace ls

      ON ls. DatabaseName = fsi. DatabaseName

       WHERE CAST(ISNULL((( fsi. FileSize - UsedExtents* 8.0) / fsi. FileSize * 100.0), 100- ls. SpaceUsedPercent) as decimal( 15, 2)) < 10

 

update        [Control] . dbo. Forum set over90= @var

 

GO
 

You can even run this code in SSMS (without the where condition) in order to have the picture of your Instance situation but you can do a lot more using SSIS.

Package to build

Below the final package (but we are going to build it step by step):

SQL Task

We have got one Execute SQL task “Run the Procedure ForumArticle”, another Execute SQL task “Select the number of db over 90% of availability” and three different Send Mail Task.

For this purpose I chose as limit for my alert the 90% of availability.

[ CODE FROM THE PROCEDURE ]

WHERE CAST(ISNULL((( fsi. FileSize - UsedExtents* 8.0) / fsi. FileSize * 100.0), 100- ls. SpaceUsedPercent) as decimal( 15, 2)) < 10

 

It’s up to you to choose the range you want consider as alert. In our case the range is 90%. 

Let’s start with SSIS.

SSIS 

Step 1: New package

Let’s create a new package with Execute SQL task:

We need to set up the Connection and after this to write the TSQL statement

A simple exec forumarticle. (the scope is to update the table Forum in the DB control)

[ CODE FROM THE PROCEDURE ]

update [Control]. dbo. Forum set over90= @var

 

Task to query table

Now we can interrogate the table forum but we need to set up the variable to pass to the mail. Let’s start with another Execute SQL Task.

Set up the Execute SQL Task.

We need a ResultSet as “Single row”, the Connection pointing to Control db and the SQLstatement that read the column over90. Do you remember? Running the procedure in the previous task we updated the over90 with the number of database that have already achieved the 90% of availability. Now we are just checking how many db are over the range. But we need to use this information later so we write this result in a variable. How?

Using SSIS variables

Be careful, the Scope must be the package name and as Data Type INT 32. Now we have the variable. Let’s go back to the Execute SQL Task.

In Result Set click on Add and write cnt (it comes from select over90 as cnt from forum as Result Name and VariableForum as Variable Name

Tasks to send mail

Now I want three different mail (Send Mail Task): one in case I have more than ten db over 90% (Critical Alert), one in case I have less than 10 but more than 0 db over 90% (Low Alert), one in case I have no db over 90%  (No alert).

In order to split the condition we can use the VariableForum that we have already set up on the previous step.  

Let’s join the task. Right click edit (or double click) and you can fix the join with the variable.

As operation we must choose Expression, as Expression we say that the variableforum is greater than 10 and we must use the logical AND.

For the second mail ( Low alert) we change the Precedence Constraint Editor as:

The correct syntax to insert in the expression window is:

@[User::VariableForum]>1 && @[User::VariableForum]<11

** **

For the last Precedence Constraint Editor (No alert) is simple to set up:

@[User::VariableForum]<1

 

Now we have the condition. We need to set up the mail. In order to set up the mail I strongly recommend you to create an email that can be run from SQL agent as owner (ask your network administrator). Let’s say that we already have this mail SSIS@company.com

 

1. Setup Connection

First thing to do is set up the connection:

2. Setup Mail

Second thing to do is to set up the mail:

3. Setup Expression

Third thing to do is to set up the Expression in this way:

The Expression string is :

"You have got "+(DT_WSTR, 10)@[User::Variable1]+" database that have already achieved 90% of availability"

Do the same changing only the subject (Low alert and No alert) with the other mail.

Complete package

The package is now complete:

Create new job

Now we need to create a new job in SQL Agent and to add up the new step:

Schedule task

And to schedule the task:

Final result

The final result is that every morning you’ll be warned with the situation of your database system. You can schedule as many package as you want. You can travel, you can go on vacation, you can take a week off; don’t worry, SSIS will tell you if something is going wrong.