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?