December 3, 2012 at 11:04 am
Hello --
One of our systems is running SQL Server 2005, and has a database that was initially 'sized out' at thirty, 30, gigabytes. The space that is currently taken up by the database is twenty-nine, gigabytes. We are at a point where resizing the container available to the database is a necessity, and I need feedback on the following:
We can either manually resize the database to fifty, 50, gigabytes, or let the server automatically do the resizing. The database will continue to grow, but giving it the aforementioned space will give us over three years before we need to consider resizing it again. I am of the opinion the manual approach is the way to go in this scenario, but I wanted to get feedback on this.
Prior to doing this, we will do a full backup of the database, and after the resizing is done, we intend to run the database consistency checker to verify the change made did not adversely effect it.
December 4, 2012 at 5:56 am
let the the Sql server do its work itself , if you are not worry about Space.
Resizing (Shrinking) database ,can create performance problem.
-----------------------------------------------------------------------------
संकेत कोकणे
December 4, 2012 at 6:25 am
Even if you try to shrink the database, it will not be shrunk if it has data worth 29 GBs. So it is better to setup the autogrowth of the data file either in percentage or, in MBs (Keeping the growth pattern in mind) and then let SQL server handle it.
If it has free space and you are not worried about the space then, why shrink?
December 4, 2012 at 6:33 am
Hello --
Thank-you for the replies. My intention is NOT to shrink the database. Rather, it is to allow it to expand. I had an idea about approaching this after I made the initial posting. If I do a manual increase in size right now, and keep the auto resize turned on a failsafe option, that would provide the time
needed for the database, as well as let it expand if by some chance it does grow prior to our being able to act on it ourselves.
Your thoughts?
December 5, 2012 at 8:00 am
kaplan71 (12/4/2012)
...If I do a manual increase in size right now, and keep the auto resize turned on a failsafe option, that would provide the timeneeded for the database, as well as let it expand if by some chance it does grow prior to our being able to act on it ourselves...
This is a good plan. manually increasing the size allows you to schedule when that will happen so you can do it durring non-peak utilization times. Here's a good article that talks about some of these concepts. Growing that large you should also look into the "Instant file initialization" capabilities
http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx?pr=blog
December 7, 2012 at 2:09 am
kaplan71 (12/3/2012)
Hello --One of our systems is running SQL Server 2005, and has a database that was initially 'sized out' at thirty, 30, gigabytes. The space that is currently taken up by the database is twenty-nine, gigabytes. We are at a point where resizing the container available to the database is a necessity, and I need feedback on the following:
We can either manually resize the database to fifty, 50, gigabytes, or let the server automatically do the resizing. The database will continue to grow, but giving it the aforementioned space will give us over three years before we need to consider resizing it again. I am of the opinion the manual approach is the way to go in this scenario, but I wanted to get feedback on this.
Prior to doing this, we will do a full backup of the database, and after the resizing is done, we intend to run the database consistency checker to verify the change made did not adversely effect it.
That's a good plan to manually increase the data size in the production server. But if you have more than 100 servers you can't do it for all servers.
The only thing you've to consider is database file sizing is costly operation. Consider you apply on non peak hours. Restrict your file growth 8 GB or less.
File sizing is a harmless operation. But if you prefer, you can do consistency check on database.
-- Babu
December 7, 2012 at 6:07 am
baabhu (12/7/2012)
...The only thing you've to consider is database file sizing is costly operation. Consider you apply on non peak hours. Restrict your file growth 8 GB or less.
Yes, one of the things mentioned in the TechNet article I linked to talks about file initialization. To help speed up file initialization when growing a file that much at once, you should consider using instant initialization. Here's an article with a more in depth look at this:
http://www.sqlskills.com/blogs/kimberly/post/instant-initialization-what-why-and-how.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply