What happens when you try to copy a large file from different hard disks? The operating system tries to see if the destination location has enough space available for the copy to be successful. If we don’t have enough space, then it is second to human nature to make space and restart the copy operation. I am sure most of us who use USB drives have done this task. If you look at SQL Server, it does a similar act. When there is no space in the file while writing, SQL Server goes ahead and expands the file based on our configuration. But if you are a seasoned DBA, you would want to know when these expansions happen. This blog is inspired by a question asked by one of the blog readers. All these questions are a major source of my blog content. Recently I received below email:
We see the database file size getting increase automatically every day. We are not having many active users, or transactions in the database. I read your blogs and understood that this might be due to auto grow setting of the database. Is there any way to find “who” is doing it?
The problem is half solved if we understand the reason. DBA has already nailed it down till auto growth and to help my reader further I provided him below script to find the auto growth events happen on the server. This information is pulled from Default Trace which runs by default on every SQL Server installation.
Here is the script to get the auto grow events which happened of database “SQLAuth”. Please change it as per database name in your environment
DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SET @database_name = 'SQLAuth'
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
,Filename
,(Duration / 1000) AS 'TimeTaken(ms)'
,StartTime
,EndTime
,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
,ApplicationName
,HostName
,LoginName
FROM::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
AND DatabaseName = @database_name
AND (d.create_date < EndTime)
ORDER BY t.StartTime DESC;
To test the script, you can created a dummy database, insert some rows so that auto growth is caused. Then check the report.
CREATE DATABASE [SQLAuth]
GO
ALTER DATABASE [SQLAuth] SET RECOVERY FULL
GO
BACKUP DATABASE [SQLAuth] TO DISK = 'NUL'
GO
USE [SQLAuth]
GO
CREATE TABLE PinalDave (Pinal INT ,Dave CHAR(8000))
GO
SET NOCOUNT ON
GO
DECLARE @i INT
SET @i = 1
WHILE @i < 10000
BEGIN
INSERT INTO PinalDave
VALUES (1,'Pinal Dave')
SET @i = @i + 1
END
Here is the output of the script which shows the growth event details.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
an interesting issue, thanks.
Glad you liked it rsrobin!
I got a error line no22
fn_trace_gettable
@Anil – please share exact error message.
The default trace doesn’t give a lot history. I just ran this script and got a few results for 10/9/17. Is there a better way to get weeks or even months worth of data? Are there any DMV’s out there?
No DMV for that. You need to device your own solution where you keep data in some other tables.
Thank you very Much Pinal Dave…this is very helpful!!
Thank you, this script is very useful!
Is it possible to know the current size of log file (or data file) when autogrowth happen?
Or the initial size of the file when the first autogrowth happened, so I can reconstruct the size history
Also, is possible to trace shrink events?
Thank you