SQL Backups Failed, No available disk Space

We are running SQL Server 2008 R2 every night we backup our SQL DB's. Last night it failed as a result of no Disk space. I noticed that D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA folder is a Terabyte in size and appears to be the culprit. Can something be done to either shrink these files or possibly remove them?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Not famiilar with 2008, but in our 2005 there is an option to set the limit of the backup size or to prune the backups by age.
PadawanDBAOperational DBACommented:
I would try running the following in each database that is on your SQL Server to try and identify if there are data/log files that have a large amount of unused space.  You will also want to keep an eye on the size of the transaction logs (type_desc LOG).  If these are particularly gigantic with comparison to the size of the data files, you may have a database that is in full recovery model, which you can move to simple recovery model and resize the log file to a more reasonable size.

use <database_name_here>;

	DB_NAME() AS DbName, 
	name AS FileName,
	size / 128.0 as CurrentSizeMB,  
	size / 128.0 - cast( fileproperty( name, 'SpaceUsed' ) as int ) / 128.0 as FreeSpaceMB 

Open in new window

PS - if the transaction logs are huge because the database is in full recovery model, there is another conversation I would be more than happy to have about that, but I won't go into it unless you find that that's indeed occurring.
mburk1968Author Commented:
My Results. This is just one of many DB's I have. Thoughts?

DbName      FileName      type_desc      CurrentSizeMB      FreeSpaceMB
EXPORTpahpixPROD      EXPORTpahpixPROD_Data      ROWS      20031.562500      6930.312500
EXPORTpahpixPROD      EXPORTpahpixPROD_Log      LOG      247763.625000      18245.000000
EXPORTpahpixPROD      ftrow_ind_polstatkey      ROWS      1.000000      0.937500
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PadawanDBAOperational DBACommented:
It looks like that database is in full recovery model (I can't definitively say that without the output from another query, but I'd bet my dollars to donuts it is).  So we'll go on with the conversation I had mentioned before:

Full recovery model does not have circular logging in the transaction log.  So it will basically continue to grow and grow until it is backed up with a transaction log backup.  There are great reasons for having a database in the full recovery model.  It allows you to restore a database up to a specific point in time, so if you know that your database was good up until 10:42:32 PM, you would be able to restore it to 10:42:30 PM.  There is some additional work in doing this, and I think for the purpose of this conversation we don't need to go into the process for backing up/restoring a database in full recovery mode.  The important take aways are that the transaction log is never truncated/space is never released back to the transaction log as available and that full backups do not truncate the log, only transaction log backups.

The simple recovery model has a circular transaction log (with some things you should know about that).  You are unable to recovery to a specific point in time under this recovery model, you can only the last full backup/differential backup.  The transaction log is circular in so far as it does not free up space within it until the transaction has been completed and has been flushed to disk (via checkpointing, and more specifically the flushing of dirty pages to disk).  In all sincerity, if you have a database that is in full recovery model and you aren't taking transaction log backups, you probably don't need the database in full recovery model.

A full conversation on comparing the two can be found here: http://msdn.microsoft.com/en-us/library/ms189275.aspx

What I would recommend is changing the database into simple recovery model: http://technet.microsoft.com/en-us/library/ms189272.aspx

After that you should then be able to shrink the transaction log data file to a more appropriate size (that isn't 247GB) - keeping in mind that it may still grow to a certain degree.  If you have any questions feel free to ask!

Also, a query to find out what recovery model all of your databases are in:
select name, recovery_model_desc from sys.databases;

Open in new window

mburk1968Author Commented:
I appreciate all your advice and will begin to attempt to follow the directions you laid out however I am not a SQL DBA. Writing queries/functions for front end applications is about the extent of my SQL knowledge.
PadawanDBAOperational DBACommented:
You should be able to change the recovery model and shrink the log file from SQL Server Management Studio, if that helps!  The article for changing a database's recovery model has directions for how to accomplish that via SSMS.  

To use SSMS to shrink the log file after changing the recovery model, you can follow this: http://technet.microsoft.com/en-us/library/ms190757.aspx.  Pay special attention to the File Type option, as you will want to change that from the default Data to Log.  This article also has directions on how to do it with a T-SQL query if you so desire.  Let me know if you have any questions and I'll be happy to answer them!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mburk1968Author Commented:
Thank you so much... Things are running again.
PadawanDBAOperational DBACommented:
Happy to help!  Thanks for the grade!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.