Every Saturday MS SQL Server Database automatically grow to 6 GB and next day Database size reduce automatically

Question

Hi All,

Every Saturday my SQL Server Database size automatically grow to 6 GB and next day Database size reduce automatically .

I used default tracer to find the which process cause this increment but didnt get any clue, the result of the query didnt give anything.

then i configured extended events database_file_size_change but interesting thing is when i manually grow the initial size of database it give the result but it didnt work automatically and didn't trace any activity running on the database not only on Saturday but other day same.

I want to know which process causing this database size increase , could anyone help me about this issue.

My Default Tracer SQL query.

DECLARE @id INT
SELECT EventClass,*
FROM fn_trace_gettable
WHERE EventClass IN (92,93)
ORDER BY StartTime;

please refer the attached file.

Regards
Syed

Answer 1

Hi @NAVEED SHAH ,

What exactly grows, the data or the log or both files? Please using SSMS Disk Usage Report to get details of Auto Growth Events. Right click database>Report > Standard Reports and Disk Usage

Please using below scripts to check auto growth events in SQL Server database. the output shows useful information for all file grow events. We also get the application name, hostname and login name that caused this file size growth.

DECLARE @current_tracefilename VARCHAR(500);  
DECLARE @0_tracefilename VARCHAR(500);  
DECLARE @indx INT;  
SELECT @current_tracefilename = path  
FROM sys.traces  
WHERE is_default = 1;  
SET @current_tracefilename = REVERSE(@current_tracefilename);  
SELECT @indx = PATINDEX('%\%', @current_tracefilename);  
SET @current_tracefilename = REVERSE(@current_tracefilename);  
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';  
SELECT DatabaseName,   
       te.name,   
       Filename,   
       CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds,   
       StartTime,   
       EndTime,   
       (IntegerData * 8.0 / 1024) AS 'ChangeInSize MB',   
       ApplicationName,   
       HostName,   
       LoginName  
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t  
     INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id  
WHERE(trace_event_id >= 92  
      AND trace_event_id <= 95)  
ORDER BY t.StartTime;  

Refer to the blog Get details of SQL Server Database Growth and Shrink Events.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 2

I can't really match the screenshot with what you say initially. My interpretation is that the consumed database size grows through the week, and then is reduced a lot during the weekend.

My first question is: why do you care in the first place? The database seems to be moderate in size with peaks of 50 GB. To give a little bit of professional advice: I am not certain that your employer/client is keen on paying you for tracking something that cheap as 6 GB of disk space.

As for why this is happening, I guess this depends on the workload. It seems that the database acquires data every data, but there is a purge job that runs on the weekend. I see that the name of the database is OperationsManager, so I guess this is SCCM. If you ask in an SCCM forum, they may know more about the details.

By the way, the data you posted shows the size used inside the database. But your post seems to indicate that the size on disk changes. If that is the case, you have a problem that you need to fix, but that should be a simple thing.

Answer 3

Without the query which produced that result, it is impossible to guess what you seeing.

However, the results of screen shot show the database is growing. This looks like the SCOM database name, in which case that is completely normal and expected.