Avoid database auto-resizing to resolve access dilemmas

When a data file is created in a SQL Server database, the file's autogrowth factor is set by default: Files are set to grow by 10% of their size whenever they're close to running out of free space -- and they are set to grow in an unrestricted fashion. For modestly sized databases (a few megabytes), this isn't a problem. For databases that grow to be gigabytes in size, this growth model can cause problems, which from the outside look like connectivity issues.

Let's assume you have a 10 GB database file that is used aggressively and is set to grow by 10%. Whenever it runs low on room, SQL Server has to lock all operations to that database and add space on the file. Ten percent of 10 GB is 1 GB. It takes a fair amount of time to allocate that much space on a disk, even on a fast system. What makes the percentile growth model even more problematic is that each successive growth will take a little longer -- 10% of 11 GB is 1.1 GB and so on. Think about the growth requirements for a database that has 50 GB or more and you'll quickly see where this leads.

Eventually, the growth periods become so long that the lack of accessibility for the database during that time becomes noticeable. Any attempts to access the database in question are either held up significantly or time out completely. To people who

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Management A first look at Microsoft SQL Server 2008 R2 Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Using Microsoft Hyper-V for SQL Server consolidation Scaling up vs. scaling out with SQL Server 2008 Migrating to SQL Server 2008 and leveraging new features Testing a SQL Server environment before an upgrade Does upgrading to SQL Server 2008 fit your business? Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness SQL Server Security The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 The fine line between not encrypting your databases and breach notification Securing SQL Server with access control, login monitoring and DDL triggers SQL Server security: Controlling access via database roles Implementing security audit in SQL Server 2008 New security features in SQL Server 2008 leave some work for you Can I encrypt and restore a database backup in SQL Server 2005? FAQ: How to troubleshoot and grant SQL Server permissions Secure SQL Server from SQL injection attacks

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary contiguity  (SearchSQLServer.com) contiguous  (SearchSQLServer.com) drilldown  (SearchSQLServer.com) hashing  (SearchSQLServer.com) hybrid online analytical processing  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

don't know what's really happening on the back end, it will seem like a connection issue. The problem is exacerbated if you have multiple databases on the same physical drive that do this. Even if those databases resize at different times, the larger ones can slow things down terribly.

The most basic way to prevent this from happening is not to use a percentile growth model on databases that are multiple gigabytes in size. I have a number of databases that grow very aggressively on my SQL Server installation, which are eventually trimmed back (the data is exported for archiving). I use the grow-by-megabytes properties for those files rather than grow-by-percentages. For most files, a 1 MB growth allocation should be more than enough, since 1 MB allocates very quickly and keeps the total growth of the file down to a manageable level.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

More information from SearchSQLServer.com