March 25, 2023 at 2:50 pm
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?
Regards,
Sam
March 25, 2023 at 6:37 pm
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
March 27, 2023 at 6:27 am
Is there a way what queries caused the data growth and what tables grown big during that time frame? Any ideas?
March 27, 2023 at 8:39 am
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