CODEWITHSUNDEEP
sql-server
Jay
Jay

Reputation: 3082

Freeing space on SQL Server Drive

I have ran into an error today in regards to unavailable space on the drive where my SQL tables and logs are stored which has caused me to be unable to update any of the databases.

I have looked at the database server and deleted a database of approximatedly 1.5GB to allow me to continue. On looking on the server drive I can see backups for the database that I have deleted in the location:

E:\Program Files\Microsoft SQL Server\MCSSQL12.SQL2014\MSSQL\Backup

Inside this folder there are 5 backup copies for the last 5 days which I would like to delete to clear up the space. However when I delete the .BAK files from this folder it does not reallocate the free space. Am I missing a step here somewhere?

Upvotes: 0

Views: 1477

Answers (2)

alroc
alroc

Reputation: 28174

Quick wins:

  • If this is on a SAN, get the drive expanded by 20% just to give you some breathing room while you sort everything else.
  • Dropping databases does not delete their backups. You have to delete those too (and make sure they don't just land in the Recycle Bin). But not until you've verified that you either A) really don't need them anymore or B) have copies of those backups somewhere more durable.
  • Get your backups moved to a different drive - both now and for your scheduled backups. Your backups should not be on the same drive as your data. File this under "putting all of your eggs into one basket". Consider this: If your E drive fails, it's going to take both your data files and your backups with it. What do you have left?
  • Review your backup retention. Do you really need all 5 of those backups? On my instances, we do daily Full backups and transaction log backups every 15 minutes. We keep at most 2 Fulls on local storage - everything else is on tape. And once you have a Full, the transaction logs between that Full and the previous one aren't really needed unless you need to a Point In Time Restore to somewhere in between them. All managed by Agent jobs executing Ola Hallengren's backup scripts so we're hands-off and just monitoring.
  • Are you using backup compression? If not, this may help you get more backups on your volume if you can't change your retention period or backup location (but really, put your backups on another volume, in the interest of safety).

Less-quick wins:

  • Consider moving your logs to a separate drive. Transaction logs and data files have different I/O profiles/requirements, so by moving them to a different drive you can tune each appropriately - for both performance and space requirements.
  • Review your indexes and eliminate any that you don't really need (or consolidate them). Indexes take up space! Dropping unused indexes won't give free space back to the volume, but it will free space in your MDF files that can be used for other data instead of growing right away.

Do not shrink your database unless absolutely necessary and you're confident that it won't re-grow significantly in the very near future.

Upvotes: 3

Neo
Neo

Reputation: 3399

What are your data and log file growth increments? If you have free space on the drives where the log and data files live, and your are allowing for automatic file growth, sql server will grow the files when space is needed.

You may also need to review the information here MSDN regarding dbcc command shrinkfile.

Upvotes: 1

Related Questions