August 17, 2012 at 9:57 am
One of our databases is 12 GB and currently about 800 MB free space left. The database gows about 300MB in a month. I have a few questions on this
1. Should I leave the database to auto grow ? I am thinking it might not be a good idea since I do not want the database size grow during biz hours.
2. Should I increase the size of the database on the exsiting file?
3. Should I add a new file? If I do this what are the benefits and what changes will I have to make administration (back up jobs ) wise.
any help would be greatly appreciated!! THANK YOU!!
August 17, 2012 at 10:21 am
My opinion would be to grow the existing file to what you would expect it to be in a 9 - 12 months time. 12GB is small by today's standards, so presuming you're not running it off an amiga, it should be fine. If the service account running SQL Server can perform volume maintenance tasks (via secpol), you should be able to manually grow the file very quickly.
August 17, 2012 at 10:34 am
I would agree with the above statement, grow it yourself. I tend to grow for 3-4 months, maybe 6, but check it regularly. 12GB is small, so you could do a year, but make sure that you check it every 2-3 months to make sure that 300MB number doesn't change.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 17, 2012 at 1:00 pm
Guras (8/17/2012)
One of our databases is 12 GB and currently about 800 MB free space left. The database gows about 300MB in a month. I have a few questions on this1. Should I leave the database to auto grow ? I am thinking it might not be a good idea since I do not want the database size grow during biz hours.
2. Should I increase the size of the database on the exsiting file?
3. Should I add a new file? If I do this what are the benefits and what changes will I have to make administration (back up jobs ) wise.
any help would be greatly appreciated!! THANK YOU!!
1) No, leave it on. Yes it can be a pain if the database grows during business hours but that's a lot better than the database completely filling up during business hours. Think of it as a last ditch strategy. If you get hit by a bus (a former bosses favorite phrase) and the next person in charge of the database doesn't know the process, or more likely someone runs in a huge import without letting you know in advance at least you know the database will continue to work without major hiccups.
2) I agree with the answers above.
3) Adding a new file won't affect your backups although it will affect your restores (2 data files to restore instead of one). There are whole blogs/articles/chapters in books as to when and if you should add extra data files to a database. Being very brief, unless you have a good reason to do it I wouldn't bother. It won't help with your space problems unless your disk is close to full and you want to expand to another disk.
As a side note if you enable "Instant File Initialization" (a windows setting) on your SQL Server startup/service account then you can drastically decrease the time it takes to grow your data files. This doesn't work on log files, they will still grow normally.
Here is a link to an article on it if you are interested in more information:
http://qa.sqlservercentral.com/articles/Administration/88591/
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 17, 2012 at 1:29 pm
Kenneth.Fisher (8/17/2012)
Guras (8/17/2012)
One of our databases is 12 GB and currently about 800 MB free space left. The database gows about 300MB in a month. I have a few questions on this1. Should I leave the database to auto grow ? I am thinking it might not be a good idea since I do not want the database size grow during biz hours.
2. Should I increase the size of the database on the exsiting file?
3. Should I add a new file? If I do this what are the benefits and what changes will I have to make administration (back up jobs ) wise.
any help would be greatly appreciated!! THANK YOU!!
1) No, leave it on. Yes it can be a pain if the database grows during business hours but that's a lot better than the database completely filling up during business hours. Think of it as a last ditch strategy. If you get hit by a bus (a former bosses favorite phrase) and the next person in charge of the database doesn't know the process, or more likely someone runs in a huge import without letting you know in advance at least you know the database will continue to work without major hiccups.
2) I agree with the answers above.
3) Adding a new file won't affect your backups although it will affect your restores (2 data files to restore instead of one). There are whole blogs/articles/chapters in books as to when and if you should add extra data files to a database. Being very brief, unless you have a good reason to do it I wouldn't bother. It won't help with your space problems unless your disk is close to full and you want to expand to another disk.
As a side note if you enable "Instant File Initialization" (a windows setting) on your SQL Server startup/service account then you can drastically decrease the time it takes to grow your data files. This doesn't work on log files, they will still grow normally.
Here is a link to an article on it if you are interested in more information:
http://qa.sqlservercentral.com/articles/Administration/88591/
Kenneth
Kenneth
Thank you on the "Instant File Initialization" information. I ran a test based on the creating DummyDB as explained on the article and yes the Instant File Initialization" is enabled for the Svc acount but I had no idea 🙂
Now I know ...Thanks again.
One more question, this database is a publisher in the replication process and I think I can just leave the repl on . I pretty much feel it's okay , but just double checking....
August 17, 2012 at 2:13 pm
Just remember that Instant File Initialization is not an excuse to not grow the files yourself. There are other very good reasons to keep an eye on your free space and grow things manually. Again this is a last ditch kind of thing.
As far as replication goes I'm not really sure what you are asking. If you are asking if you can grow the files with replication still turned on then I believe so but I would have to defer to someone who knows more about replication than I do. I only really know the basics.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply