SQL Agent Alerts

• Casper101

  Hall of Fame

  Points: 3903

  More actions

  October 9, 2008 at 1:25 am

  #123929

  In my environment there is 1 production database with the following settings for the Data File:

  Autogrow = OFF

  Initial Size = 2000 MB

  I want to set up and SQL Agent Alert that will notify me when the amount of Data file Size Used rises above 90% of the Initial Size (2000 MB), i.e. Data File size used >= 1800 MB

  I have tried this using SQL Agent Alert, choosing SQLServer:Database with Counter: Data File Size(s) (KB) and when value rises above 1800000 (KB). BUT the problem is it seems to NOT look at the USED amount but rather the Initial Size amount ?

  So I keep getting alerts saying the current size is 2000MB - where I want the File Size Used instead.

  Hope this makes sense 😛 and that someone can help.

  I see there is a Log File Size Used option (in Alerts) but not a Data File Size Used? And this is exactly what I want - seeing that I am manually growing this file.

  Turning Autogrow ON is not an option as this point so I must have this Alert system.

  Thanks,

• Sakthivel Chidambaram

  SSCertifiable

  Points: 5531

  More actions

  October 9, 2008 at 1:52 am

  #882727

  Hi,

  Not sure whether this will help you... Put the below code into SP and call this SP from Job to send notification to you... UPDATE STATISTICS need to run regularly to get latest statistics... you can also add this command as first step of this SP...

  declare @id int -- The object id of @objname.

  declare @type character(2) -- The object type.

  declare @pages int -- Working variable for size calc.

  declare @dbname sysname

  declare @dbsize dec(15,0)

  declare @logsize dec(15)

  declare @bytesperpage dec(15,0)

  declare @pagesperMB dec(15,0)

  select @dbsize = sum(convert(dec(15),size))

  from dbo.sysfiles

  where (status & 64 = 0)

  select @logsize = sum(convert(dec(15),size))

  from dbo.sysfiles

  where (status & 64 <> 0)

  select @bytesperpage = low

  from master.dbo.spt_values

  where number = 1

  and type = 'E'

  select @pagesperMB = 1048576 / @bytesperpage

  select database_name = db_name(),

  database_size =

  ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),

  'unallocated space' =

  ltrim(str((@dbsize -

  (select sum(convert(dec(15),reserved))

  from sysindexes

  where indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB')

  Regards,
  Sakthi
  My Blog -> http://www.sqlserverdba.co.cc