Your SQL Server database is the foundation of your ERP solution, supporting the applications that power your business with solid data and storage. Those databases are filled with critical data for your corporation — irreplaceable business history, intelligence, and all the data your business needs to maintain the overall health of your company. Keeping them organized and running smoothly is integral to all of your organization’s daily functions and requires meticulous database management. 

For DBAs, that means a hefty to-do list that includes everything from clearing out old, unnecessary data to regular updates and everything in between. But is right-sizing on your database management checklist? There are lots of great tactics to apply to keep databases agile and efficient. Right-sizing your database and keeping your SQL Server database well-managed and agile are vital to the efficiency and growth of your business. 

Right-Size the Right Way

Right-sizing a database is a planning process that plays a major role in the preliminary design of a database. Databases are deep, and they must be kept as narrow as possible through sensible design. For example, you may transition from Excel to the MS Access database because your spreadsheets are so deep and wide that you can't see the forest for the trees. 

An Access database text field can hold as many as 255 characters. So, if you're designing a table with a 255-character field that will never hold more than 20 characters, your database will eventually become unnecessarily fat and slow.

MS Access also has several useful archiving queries, which will gather up all that old data, place it in separate tables, and shunt it off to storage and out of the way of daily operations. There’s also the compact and repair command in MS Access, which, in addition to repairing your data, gets rid of accumulated junk that degrades your overall performance. Your database design and strategy plays a big role in the efficiency of your apps, and entire business operations.  

Migrating to SQL Server

Likewise, when you transitioned to MS SQL Server, it was because you wanted to store more data and keep everything separate from the engine that drives it.  Microsoft Access is a great application, but it (and other lightweight databases) reaches an end point where its objects — forms, switchboards, queries, macros, etc. — begin crowding your data and competing for storage space. So, what can your organization do when you reach this point?

Downsizing options for SQL Server database

Why would you want to reduce the size of your SQL Server database? The short answer is this: over the years your database has begun to carry the dead weight of empty data pages, and unused or archaic data.

Like Microsoft Access, SQL Server has several ways to shunt obsolete data and archive it. A Make-Table Query, for example, will find old records and create a new table. A Delete query will, in turn, clean up the original table by deleting the archived records.

You have two additional options to reduce the size of your SQL Server database: shrink the database  or compress it.  

Shrinking Your SQL Server Database

Shrinking SQL data files recovers space somewhat in the way a disk defragmentation runs. It moves unused 8-kilobyte pages of data from the end of the file to empty space closer to the beginning of the file. The idea is to free enough space at the end of the file so it can be "deallocated" and returned to the system.

Don't fall into the "trap" of shrinking.

Here's what one SQL programmer, Paul Randal, has to say in an outspoken blog post, "Why you should not shrink your data files":

"Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled… Don’t fall into the trap of having a maintenance plan that rebuilds all indexes and then tries to reclaim the space required to rebuild the indexes by running a shrink…"

Randal calls running auto-shrink a "zero-sum game" with no actual gain in performance. Microsoft also advises never to set the AUTO_SHRINK database option to ON unless you "have a specific requirement."

Shrink a SQL Server database when:

• you have deleted a large amount of data from a very large database.
• the database is not likely to grow.
• you need to empty a file before removing it.

Database shrinking has restrictions and limitations. :

Shrinking a database:

•  will not make the database smaller than the minimum size of the database specified when you first created it.
• won’t run while the database is being backed up.
• can’t be done simultaneously with a database backup.
• will fail when it encounters the more recent features of columnstore indexes, which must be disabled and later restored for the shrink to work.

If it’s not broken, don't fix it.

The main takeaway about repeatedly shrinking a SQL Server database is that most databases need free space to be ready for everyday operations. Microsoft's Technet advice is as follows:

"If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation."

Compressing a SQL Server Database

Data compression is the use of special coding to enable your SQL data files to occupy less disk space for storage purposes and to faster transmit the data between the user and the server. Compressing a database doesn't actually reduce its size. Rather, it saves disk space and improves performance of a workload-intensive database by reducing the number of pages and queries needed to read those fewer pages from the disk.

The downside is that you need additional CPU resources on the database server to compress and decompress the data as you call it up. Compressing has no effect on some data types, like text and date, for example. However other data types can be compressed significantly. See the chart in MSDN's "Row Compression Implementation" for SQL Server 2016 data types.

Likewise, not all SQL Server database tables and indexes can be compressed, and not every edition of SQL Server supports data compression. Activating data compression also works best with the Microsoft Server Management Studio data compression wizard. This article on sqlmag.com has an illustrated walkthrough on applying data compression to a SQL Server Database.

Whether you are still running MS Access or you've moved to the power database of SQL Server, your database maintenance plan should include the cyber equivalent of weight control. If your database is getting heavy, our friendly experts know how to do a major overhaul or upgrade. We've done thousands of them! Let us know what you need. We can either put  your valuable business data on a diet or help you bulk it up.

If your database management isn’t helping you with your SQL Server upgrade, it probably isn’t delivering the maximum ROI either. Download our Database ROI Resource to see where your database management stands.

Editor’s Note: This post was originally published in March 2016 and has been updated for freshness, accuracy, and comprehensiveness.