SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Increasing the size of the container


Increasing the size of the container

Author
Message
kaplan71
kaplan71
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 329
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.
sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)SSC-Addicted (487 reputation)

Group: General Forum Members
Points: 487 Visits: 1022
let the the Sql server do its work itself , if you are not worry about Space.

Resizing (Shrinking) database ,can create performance problem.

-----------------------------------------------------------------------------
संकेत कोकणे
arunyadav007
arunyadav007
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 299
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?
kaplan71
kaplan71
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 329
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?
Chris Harshman
Chris Harshman
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5170 Visits: 4010
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 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...

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
baabhu
baabhu
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1701 Visits: 1218
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
Chris Harshman
Chris Harshman
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5170 Visits: 4010
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search