2 Autogrow or not 2 autogrow...... 2

[jeffmoore64]

Hi,
I got the following message in my sql logs about 2 weeks ago.

Autogrow of file 'InventoryMart_Data' in database 'InventoryMart' took 825844 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file

Should I:
A) Manually grow my files when they get to X%
B) Set my autogrow to 1MB on both the data and log files.
C) Some combination of both.

Tia
Jeff

 

[ptheriault]

Jeff,
You'll want to manually manage your autogrow events so they happen off hours. You don't want to disable the autogrow because if for some reason you don't monitor it properlly you don't want the data or log files to fill up. If that happens you won't be able to access your database.

When my database reaches 10% free I add another 5% so I always have 15% free space in the datafile.

For my log file. I grow it by 50MB and I leave it alone. unless for some reason I have a huge transaction that causes me to have to shrink it back down to normal operating size.

Some DBA's just let the data and log files autogrow as they need to. The only bad part about that is if you have an autogrow event during business hours it will impact your IO on the server.

For small databases that are not mission crititcal I use that approach. For my large mission crititcal database I manually manage the autogrow events but I don't limit myself by setting growth limits. I wouldn't want the application to come to a screaching halt because the datafile is full!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Thanks Paul for all the current and past help. Here a star just for spite

Is there a quick and dirty way to see what my percentage free space is? Something that I could run as a report on Monday mornings?

 

[ptheriault]

what version of SQL server you using?
In EM for SQL 2000 you can use the task pad view of your database.

In SSMS for SWL 2005 SP2 right click on the database and select reports-> space usage

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

An important question hasn't been asked yet. How large are your files and what are the auto growth settings?

Also,
Never set the auto growth size to 1 Meg. This will cause major fragmention of your hard drive.
Never set the auto growth to a % as you will never know how large the growth will be if/when the auto grow kicks in.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jeffmoore64]

Denny,
The dbs range in size from 10gb to 300gb. I have plenty of free space on the drives so that a percent autogrow should not be an issue for the forseeable future.
What would be cool is a script that would look at each db and autogrow it if it falls below a certain percentage. I could run that nightly and the files could grow then.
Got a script like that handy?
TIA

 

[ptheriault]

Here is a little query that will give you information on your database files. If you set your data files to a max size you can then use the script to check % free and decide if you should grow it. You will have to modify this query a bit to give you that information but it should get you started.

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]db_name[/color](dbid) [COLOR=blue]as[/color] database_name
       ,[COLOR=blue]name[/color]
       ,fileid
      ,[COLOR=#FF00FF]left[/color](filename,1) [COLOR=blue]as[/color] Drive
      ,filename
       ,[COLOR=#FF00FF]convert[/color](nvarchar(15),[COLOR=#FF00FF]convert[/color](bigint, [COLOR=blue]size[/color])*8)+[COLOR=red]'KB'[/color] [COLOR=blue]as[/color] [[COLOR=blue]size[/color]]
      ,([COLOR=blue]case[/color] maxsize [COLOR=blue]when[/color] -1 [COLOR=blue]then[/color] [COLOR=red]'Unlimited'[/color]
        [COLOR=blue]ELSE[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](15), [COLOR=#FF00FF]convert[/color](bigint, maxsize)*8)+[COLOR=red]'KB'[/color]
       [COLOR=blue]end[/color]) [COLOR=blue]as[/color] [maxsize]
      ,([COLOR=blue]case[/color] status & 0x100000 [COLOR=blue]when[/color] 0x100000 [COLOR=blue]then[/color]
             [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](15),growth)+[COLOR=red]'%'[/color]
        [COLOR=blue]else[/color]
       [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color](15),[COLOR=#FF00FF]convert[/color](bigint,growth)*8)+[COLOR=red]'KB'[/color]
       [COLOR=blue]end[/color]) [COLOR=blue]as[/color] growth
      ,([COLOR=blue]case[/color] status & 0x40 [COLOR=blue]when[/color] 0x40 [COLOR=blue]then[/color] [COLOR=red]'log only'[/color]
       [COLOR=blue]else[/color] [COLOR=red]'data only'[/color]
       [COLOR=blue]end[/color]) [COLOR=blue]as[/color] status
[COLOR=blue]FROM[/color] sys.sysaltfiles
[COLOR=blue]Order[/color] [COLOR=blue]by[/color] dbid

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Sweet!! Thanks

 

[jeffmoore64]

Wait, you're saying to set some upper limit for my dbs and monitor when I'm within say 10% of full.

So I have a 100gb db.

I set the MaxSize to say 120gb. (20% free space)

I run a script nightly to watch for the 10%.

If 10% then we branch to a script that will force the increase the db's MaxSize by 5%, (125gb) there by forcing the autogrow.

This is the correct stategy, right?

Thanks Jeff

 

[jeffmoore64]

Also ... where did you get some of that info on the sysaltfiles table? I looked it up on the BOL and it gives no info on the column "Status".
Hence I can't read into what or how you got the "log only/data only" flags baised on the 0x40 in the status column.

 

[ptheriault]

Wait, you're saying to set some upper limit for my dbs and monitor when I'm within say 10% of full.

So I have a 100gb db.

I set the MaxSize to say 120gb. (20% free space)

I run a script nightly to watch for the 10%.

If 10% then we branch to a script that will force the increase the db's MaxSize by 5%, (125gb) there by forcing the autogrow.

This is the correct stategy, right?

That is the correct strategy.

If you are using 2005 you may want to use sys.master_files. You don't need the status column for what you are trying to do. You can just add where type = 0 for data files.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

An interesting side note:
When runing the code you sent me;
All but two of my db's report a growth of 80KB!!
In reality almost all of my db's are set to 10%

 

[mutley1]

Jeff,

I posted this previously (and Paul liked it!!!)

check out the following post and look for my post in the middle. As Paul mentioned, we run this nightly to ger a view of what is going on and that space is free / options set on the DB's etc.

http://www.tek-tips.com/viewthread.cfm?qid=1316443

Cheers,

M.

 

[jeffmoore64]

Very VERY Nice. Of course I read this post after I had half writen my own version of an auto grow sp...
No matter this ggives me that elusive % free space
Thanks and heres your star