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 3, 2012 11:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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 4, 2012 5:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:48 AM
Points: 323, Visits: 986
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 4, 2012 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
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 4, 2012 6:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 6, 2014 11:55 AM
Points: 77, Visits: 169
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 5, 2012 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:30 AM
Points: 1,950, Visits: 2,122
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 7, 2012 2:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:11 PM
Points: 1,302, Visits: 1,146
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 7, 2012 6:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:30 AM
Points: 1,950, Visits: 2,122
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