RealWorldDevelopers
9/19/2016 - 11:25 PM

Query Auto Grow Settings of a SQL Database

Query Auto Grow Settings of a SQL Database

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