Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Increasing the size of the container Expand / Collapse
Author
Message
Posted Monday, December 03, 2012 11:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 2:22 PM
Points: 60, Visits: 128
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.

Post #1392078
Posted Tuesday, December 04, 2012 5:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
let the the Sql server do its work itself , if you are not worry about Space.

Resizing (Shrinking) database ,can create performance problem.


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1392404
Posted Tuesday, December 04, 2012 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 28, 2014 3:22 AM
Points: 42, Visits: 286
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?
Post #1392419
Posted Tuesday, December 04, 2012 6:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 2:22 PM
Points: 60, Visits: 128
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?
Post #1392430
Posted Wednesday, December 05, 2012 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 1,783, Visits: 1,920
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
Post #1393028
Posted Friday, December 07, 2012 2:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 9:27 PM
Points: 1,242, Visits: 1,097
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




Post #1393924
Posted Friday, December 07, 2012 6:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 1,783, Visits: 1,920
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
Post #1394026
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse