Managing SQL Disk Space

• A Poorly-Configured Model Database
• Mismanaged Backups
• Unnecessary Index Optimisation
• Incorrectly Configured Maintenance Plans or Lack Thereof

If any of the points above been the cause of your disk space woes then give yourself a slap on the wrist, you should know better!

If the Model Database is poorly configured, any databases adopting these properties will make you pay at some point. Whether it be Auto growth – whereby databases are allowed to grow as big as they need when they reach their limit without any manual input, or the Recovery Model – does every database REALLY need full logging? While it makes restoring possible from a point in time, who can honestly say they have used this? 9 out of 10, no 99 out of 100 times an SQL restore will be from a .bak file, am I wrong?

Transactions in the log file are only ever removed when the transaction logs are backed up when in Full Recovery mode. Therefore, if you have determined a definite need for the Recovery Model to utilise Full Recovery for point-in-time restores, then you need to ensure that backups are carried out at regular intervals (this could even be hourly). By only backing up the log files nightly, or even weekly, the transaction logs will balloon in size before the backup shrinks them back down. I don’t know about you, but if there’s one thing that annoys me more than disk space disappearing, it’s disk space that disappears and reappears so I can’t really tell what’s going on.

On the subject of backups, transaction logs will fail to backup until the database has been fully backed up, maybe an obvious point, but one worth mentioning. Especially if you have full recovery and auto growth enabled but are not seeing the benefits. This is because, until that first full database backup has been completed, it is acting in Simple Recovery Mode and will be truncated at checkpoints.

Indexes can really improve query performance and some database applications are heavily reliant on these indexes being in-place, so I’m not for one moment saying that indexing should be disabled in your environment, but WOW these are some data-greedy monsters.

To ensure that disk space is not being eaten without a satisfactory requirement, the need to monitor every index is very real.

• Does the index aid application performance?
• Has the index been implemented just to provide answers to queries faster?

If the application is heavily driven by SQL queries across large tables with many rows, the Index can stay and SQL disk space will have to be allocated accordingly for this. If though, the index was implemented because a single query was taking too long for the impatient monkey, can it be removed? As ever, being proactive is always better than being reactive so try to ensure that indexes are only created where 100% required.

OK, so now we have configured the databases and indexing levels but we still can’t kick back, feet up on the table with a beer… we have to keep on top of it. I know, I know, who’s idea was it to become an SQL DBA in the first place?!

We have already touched on the subject of backups with regards to the recovery model, but backups aren’t just required for this when it comes to handling SQL disk space. Obviously, backups are first and foremost in place for disaster recovery but from this blog posts point of view, let’s just keep the disk space issues in focus.

From SQL Server 2008, which I’m assuming by now we are all at least on, SQL backups added a compression feature. I have yet to find a downside to this. OK, compressed and uncompressed .bak files cannot reside on the media drive and if there is deduplication on the storage after the compression, the restore times from tape maybe longer than we wish – but knowing this going in, we can mitigate against it. Only for databases where compression is relatively redundant would I advise a full backup.

Backup Compression does exactly what it says on the tin, it compresses the backup as much as possible and therefore produces a smaller .bak file, taking up less disk space on the SQL server and as mentioned previously, I haven’t had a single issue when restoring from a compressed .bak file.

Provisioning for storage requirements on the SQL server has to take these backup files into account. As SQL Servers naturally backup only to the local server, enough space has to be assigned for this backup area. Ideally, this area should just be used as a scratch area, where the period these backup files stay here are configured to your business needs. For example, every night we schedule a backup for all databases to an assigned drive for backups, J:. Then hours later the third-party backup program backs up these files to tape, removing any files older than one week from the J: drive.

On occasion, there will be a need to complete on-the-fly backups, like before undertaking an upgrade for example. There is a tendency for these backup files to outlive their welcome and sit there unnecessarily for years. While it’s not always possible to monitor these on-the-fly backups taking place, it is worth following up why some .bak files created during work hours and outside the scheduled backup window exist. If no-one can claim ownership or give reason to it, ensure it is backed up to tape and remove it from the live SQL server.

Maintenance plans are simple to set up. They even come with a GUI wizard! There is no excuse to not having appropriate maintenance plans in place for your databases. Not only do they save disk space but they also keep the databases, and the tables therein, defragmented making the applications well oiled.

Speaking purely from an Enterprise Vault point of view (however I’m sure it relates across many applications), the ideal maintenance carried out on the database is:

• Backup the Databases
• Backup the Transaction Logs
• Shrink the Database
• Rebuild the Index
• Update Statistics

It is often debated whether shrinking the database causes more harm than good as shrinking the data files causes major index fragmentation. This is why Symantec advise to carry out the maintenance plan in the suggested order above.

As always, the best way to achieve anything is by being proactive as opposed to reactive and most of the points here outline the best proactive steps to take.

• Backup Compression
  
• Sizing SQL Disk Space using sp_spaceused
• A Post from Rodney Landrum going under the bonnet of how to configure SQL correctly in order to manage Data Growth
• Paul Randal’s Post debating Database Shrinking
• A Post from Brad McGehee on Maintenance Plans and their benefits