Need an alert for a sudden increase in data

Question

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

Answer 1

Yes, you can set up alerts in SQL Server using SQL Server Agent to monitor sudden data file growth. To capture queries and SPIDs causing the growth, you can use Extended Events or SQL Server Profiler.

1. Create alerts in SQL Server Agent for data file growth.
2. Use Extended Events or SQL Server Profiler to capture queries and SPIDs responsible for file growth.

Monitor your environment regularly to avoid unexpected space issues.

for example

1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. Expand "SQL Server Agent".
4. Right-click "Alerts", then "New Alert".
5. Set "Type" to "SQL Server performance condition alert".
6. Choose "Object" as "Databases" and "Counter" as "Data File(s) Size (KB)".
7. Set "Instance" to your target database.
8. Define the "Alert if counter" condition, e.g., "rises above" a specific value.
9. Configure the "Response" settings, such as sending an email notification.
10. Save the alert and enable it.

or use profiler to detect queries to cause the unexpected growth

1. Open SQL Server Profiler and connect to your SQL Server instance.
2. Click "File" > "New Trace" and connect to your target server.
3. In the "Trace Properties" window, set the trace name, and configure the trace settings, such as file size, file count, and stop time (if necessary).
4. In the "Events Selection" tab, select the events you want to capture. For capturing queries and SPIDs responsible for data file growth, select events like "SQL:BatchCompleted", "RPC:Completed", and "SP:Completed".
5. Add relevant columns to capture the necessary data, such as "DatabaseName", "TextData", "SPID", "StartTime", "EndTime", "Duration", and "Disk Reads".
6. Apply a filter on the "DatabaseName" column to target the specific database you're monitoring.
7. Click "Run" to start the trace. You can analyze the trace data to identify queries and SPIDs responsible for the data file growth.

Answer 2

Thanks for your question.

Please find below blog posts that might be helpful for your specific requirements and situation.

https://www.mssqltips.com/sqlservertip/2359/find-sql-server-data-and-log-files-that-are-almost-out-of-space/

https://www.mssqltips.com/sqlservertip/3128/monitor-sql-server-database-file-growth-with-wmi-alerts/

Answer 3

In addition to the other posts: If the drive was filled up because of an increase of 30 GB, you were too close to the limit already before the incident. Databases do grow. Maybe not always 30 GB at a time, but you should always make sure that there is a margin so that you don't get an outage.