How to solve error Out of Disk Space In SQL Server
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.
Answer:
What to check in SSMS:
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.
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:
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.
Answer:
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.
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.
 |
| How to solve error Out of Disk Space In SQL Server 2016 |
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).
Post a Comment