SQL Server 2016 out of disk space

Question

I'm running SQL Server 2016. I have a database that has run, apparently, out of space on the D: drive. The daily index rebuild started producing the error shown below.

Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'MarketDataAnalysisDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

First thing I did was remove the logs from the D: drive onto the C: drive so that I could (hopefully) delete some older historic data and free up space. I was able to move the log files but the indexes still did not run (same error as above).

So I set out to delete rows of data with the intent of freeing up space so that future rows could grow back into the database. Following the delete statement, and 4 1/2 hours later, I got a similar error

Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

While the error information is very informative I can do none of the suggested items. There is nothing on the drive but the database file. When I started reading about shrinking the database I realized I don't really understand why I'm getting this error. I've attached an image of the used space information in hopes that someone who understands this better than I, can provide a safe way to reduce the database size. The goal is to allow new records to come in while I figure out a rolling partition implementation that is currently beyond my SQL skills to implement on my own.

The other option I might consider is purchasing a larger hard drive for the data. But, from what I see, there appears to be space while at the same time SQL Server thinks I'm out of space.

What is the best way to reduce the size of my database?

DB Usage/space details

[EDIT]: I didn't realize it right away, but, my attempt to delete rows that took 4 hours and the log file located on the c: drive which had nearly 200GB free filled the log file so now the c: drive is out of disk space! What to do?

Answer 1

What to check in SSMS:

• Right click on the database in question, click "Properties", then choose the "Files" tab.
  • Note all the files for the database, and the drives they are on. Note which files have auto-growth turned on, and (of those) which have a maximum file size.
• Switch to the "Options" tab.
  • Check the recovery model (upper right corner). Is it "Full", "Simple", or "Bulk-logged"?
• Repeat the first step for the system database tempdb.

Using Windows/File Explorer, check how much space is available on each of the drives you noted above.

Why you're having problems:

The index rebuild process takes up extra space in either your database itself, or in tempdb (depending on the settings for the index when it was created). In your case, it seems save to say the rebuild uses your database, not tempdb. Once the index is completely rebuilt, this space is released as available again; that's why it looks like you have free space in your data file, but you run out during the rebuild process.

Note that the database rebuild writes data to the transaction log, tracking the changes it's making, in case they have to be rolled back. The precise behavior of the transaction log depends on the recovery model of your database.

Databases using the "Simple" recovery model need to keep the details of every transaction around until the transaction is completed. Once the transaction is completed, the log space can be released to be reused. So, you can run out of space in your transaction log during a transaction, but have almost no space in use when you look at the log ten minutes later.

Databases using the "Full" recovery model (or "Bulk-logged") keep transaction information around until the transaction log is backed up. This means that a database in "Full", even with a regular full database backup, but with no transaction log backups scheduled, will eventually fill up, as transactions that completed months or even years ago must be kept in the log.

The main reason to use the "Full" recovery model is to allow for "point-in-time" recovery. You can combine your full backups with the string of transaction log backups to recover the database up to any point in time from when the full backup was taken, up to when the most recent transaction log backup was taken.

If you want the option to restore to a point more recent than your last full backup, then use the "Full" recovery model, and schedule transaction log backups to happen often enough that you don't mind losing the data that wasn't backed up. If you don't need transaction log backups (and you're sure about it), then put your DB into the "Simple" model.

A bit of advice: if you're using "Full" but haven't been backing up your transaction logs, you have the option of taking that first transaction log backup, if you have enough space to hold it. If you don't, you can basically "start over" by:

• Shifting the recovery model to "Simple". This will mark all completed transactions as being OK to release. You can use the T-SQL CHECKPOINT command to ensure that the space is released immediately.
• Switch back to "Full"
• Immediately take a full backup of the database
• Immediately after that, make sure your regular transaction log backup is set up.

This is one of the few scenarios where it actually makes sense to shrink your transaction log. If it's very large, try cutting it in half. Ideally, you don't want to shrink the file so small that it has to grow for normal operations. If you're looking for a good target size, I've heard the recommendation that you size the transaction log to twice the size of your largest index.

As noted, an index rebuild does write data to the transaction log. So, if you're using the "Full" model, and you do have transaction log backups running, you may want to run them more often when the rebuild is happening. I did this on one of my DBs a few years back - my usual schedule for transaction log backups was every 15 minutes, but I bumped that up to every 5 minutes during the rebuild process.

So, if all of that's set up fine, check to see if your drives are actually full, or if your files have an upper growth limit (or don't grow). If you still have drive space, then you can:

• Manually increase the size of the file (on the afore-mentioned "Files" tab);
• If auto-growth is not allowed, allow it (you can set a maximum if you like); or
• If auto-growth is allowed, but a maximum is set, then increase the maximum (or remove it).

If you don't have any free disk space, and your transaction log is properly sized - then you need more disk space.

Answer 2

There's a way to put a max size on files in SQL Server. You should check those to see if they are artificially restricting growth.

You also need to go to your network admin and ask nicely for a network drive. You may need to switch your SQL services to use an active directory account, then give them permissions to the network drive and move your data there.

If you've got multiple file groups (for example, 1 per CPU core) you can move them one at a time.