Monitoring alerts and firing jobs

Remember I had posted an entry earlier about setting and responding to transaction log alerts. Well I had a chance to expand on this, so here goes :

First, create a job that does the shrinking. In my case, I hardcoded the value of a single database, and executed it without using cursors :

— Created by Rajiv for monitoring the log growth

use BjcScoresV2

go

declare @tempTable table (aid int identity, aquery varchar(2000))

 

— First TLog backup

insert into @tempTable (aquery)

select ‘backup log BjcScoresV2 to disk = ‘ + ”” +

‘D:\DBABackups\BJCScoresV2_’ + replace(replace(convert(varchar(100), getdate()),’ ‘, ‘_’), ‘:’, ‘_’)

+ ‘_1.trn’ + ””

 

— First DBCC Shrinkfile

insert into @tempTable (aquery) select

‘dbcc shrinkfile (‘ + ”” + ‘bjcscoresV2_dev_log’ + ”” + ‘,1)’

 

— Second TLog backup:

insert into @tempTable (aquery)

select ‘backup log BjcScoresV2 to disk = ‘ + ”” +

‘D:\DBABackups\BJCScoresV2_’ + replace(replace(convert(varchar(100), getdate()),’ ‘, ‘_’), ‘:’, ‘_’)

+ ‘_2.trn’  + ””

 

— Second DBCC Shrinkfile

insert into @tempTable (aquery) select

‘dbcc shrinkfile (‘ + ”” + ‘bjcscoresV2_dev_log’ + ”” + ‘,1)’

 

— Loop through and execute the dynamic queries

declare @aid int, @bid int, @sqlQuery varchar(2000)

set @aid = 1

set @bid = (select count(*) from @tempTable)

while @aid<=@bid

begin

                set @sqlQuery = (select aquery from @tempTable where aid = @aid)

                exec (@sqlquery)

                set @aid = @aid + 1

end

The job is only triggered through an alert named Monitor_BJCScoresV2_Logs. This is actually easier than using Performance Monitor, but uses the same concept. It is raised when the transaction log of this database rises above 25 GB :

https://cid-5257c6e71b41d7ba.photos.live.com/self.aspx/.BlogImages/Alert%5E_1.jpg

 When that happens, it runs the aforementioned job :

https://cid-5257c6e71b41d7ba.photos.live.com/self.aspx/.BlogImages/Alert%5E_2.jpg 

Hope this helps!

Advertisements
This entry was posted in Transaction Logs. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s