Skip to content

How frequently is database growing?

We always keep our database data and log file to auto grow by some value. But are we sure that the current auto growth parameters is correct. Is the auto growth happening to frequently? Or we have allocated more than sufficient space in one go that database have not grown for a year.

Auto growing data files very frequently with small increments is bad for database as well as OS. When ever auto growth happens database takes a performance hit. At OS level, this new chunk of space is not always adjacent to the previous space allocated to databases. Thus causing disk fragmentation.

So, how to determine, how frequently our database auto growth is happening?

Make use of below script to check how frequently the data and log file is growing.

DECLARE @path NVARCHAR(1000)

SELECT @path = Substring(PATH, 1, Len(PATH) – Charindex(‘\’, Reverse(PATH))) + ‘\log.trc’

FROM   sys.traces

WHERE  id = 1

— id =1 means the active trace file

SELECT databasename,

       te.name   AS eventname,

       starttime

FROM   ::fn_trace_gettable(@path, 0)

       INNER JOIN sys.trace_events te

         ON eventclass = trace_event_id

       INNER JOIN sys.trace_categories AS cat

         ON te.category_id = cat.category_id

WHERE  te.name IN( ‘Data File Auto Grow’, ‘Log File Auto Grow’ )

ORDER  BY databasename,starttime DESC;

Output will be the database name, start time when auto growth of data or log file happened. Now we can check, if auto growth is happening too frequently, we can increase the growth size.

Comments

  1. Pewter Keychain says:

    I wish to offer this post in my blog site. It could possibly?

    1. admin says:

      Please leave your details and topic on which you want to write in form @ http://databasebestpractices.com/about-us/
      We will contact you on the email you will sure with us. Thanks.