Need an alert for a sudden increase in data

  • Hi All,

    Today, all of a sudden the size of the data file (.mdf) is increased by 30 GB and drive got filled up. Is there a way, to get an alert on such sudden increase of data and can we capture the query(s) and spids which caused the data grow that huge?



  • You need to monitor the default trace for event ids 92/93/94/95, how often you need to gather this information will depend how busy the server is. A very busy server the 20MB storage of the default trace can overwrite itself very quick, other none busy servers it may take longer to fill 20MB and overwrite it.

    92 - Data File Auto Grow

    93 - Log File Auto Grow

    94 - Data File Auto Shrink

    95 - Log File Auto Shrink

  • Is there a way what queries caused the data growth and what tables grown big during that time frame? Any ideas?

  • You would need to monitor all that, you can track the table spaces quite easily, there is s built in report to do it.  However the query which caused the growth no. It could easily be a simple 1 line insert, but your growth settings say to grow 10% of a 1TB file, so your adding 100GB just to insert 1 row, it could be index maintenance.

    Growth is perfectly normal, you need to control growth yourselves by ensuring you have ample space in the DB, growth settings etc.  Capacity management is a key skill to be looking at and only relying on auto growth as an emergency catch all.

    I would strongly suggest you go and get yourself a proper monitoring solution, Red-Gate SQL Monitor, Quest Spotlight or FogLight, Idera SQL DM, SQLWatch, DBADash.

    A lot of what your asking here (and in your other posts) in terms of monitoring is covered by these tools.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply