Need an alert for a sudden increase in data

Sam 1,371 Reputation points
2023-03-25T14:50:49.26+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions
Accepted answer
  1. Sedat SALMAN 13,160 Reputation points
    2023-03-25T14:53:34.2133333+00:00

    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.
    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rahul Randive 8,271 Reputation points Microsoft Employee
    2023-03-26T11:30:06.69+00:00
    0 comments
  2. Erland Sommarskog 101K Reputation points MVP
    2023-03-26T12:47:20.9+00:00

    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.