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?
[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?
Best Answer
What to check in SSMS:
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, nottempdb. 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:
CHECKPOINTcommand to ensure that the space is released immediately.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:
If you don't have any free disk space, and your transaction log is properly sized - then you need more disk space.