I have a pretty dumb question. I setup a windows Server 2012 R2 box about a week ago with SQL 2014 on it.  After a week, it ran out of space, only 5MB out of 1T.  

I have no idea what has happened. How can I find out what happened?  thaks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

IvanSystem EngineerCommented:

use tree size free application, to see what has used all space.

Run it as admin, and it will list you what has used all space. I use it, and it ok app.

PS: Maybe you had a lot of transactions, and did not do a backup of SQL?

Vitor MontalvãoMSSQL Senior EngineerCommented:
How many disks do you have?
How big are those disks?
Where did you install SQL Server?
How big are the databases?
Where did you store the databases?
Are the data and log file in the same disk?
Where are you storing the backups?
What's the recovery model of the databases?
If Full Recovery model then did you create a regular job for transaction log backup?
mcrmgAuthor Commented:
There is only C drive.  I only restored two db on this box.  Total size is less than 6G.  

I can not even install "treesize" , no space.....
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
So first lesson is:
- Do not store user databases in C: drive since if the space get filled you will be unable to work

Now, let see what we can do to recall some disk space.
Go to the folder where databases are stored and get the TOP 5 file sizes.

Do you have regular backup jobs configured?
mcrmgAuthor Commented:
I found the issue. What is the file *.ldf?  it is 1.1T ...thanks
mcrmgAuthor Commented:
Since I setup this box a week ago. I just use SQLbackFree to bk those two database. I have not done any work yet..
mcrmgAuthor Commented:
I will try to shrink it...
Éric MoreauSenior .Net ConsultantCommented:
ldf file is the SQL log file. You surely have not backed up your databases (which truncates the logs).

if your are not backing databases, you should set the recovery model to "Simple" since it won't create large ldf files.

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
mcrmgAuthor Commented:
this is the errorI have when running SHRINKDATABASE

Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I was expecting that kind of file (.ldf) to be the largest one.
You have the database configured as Full Recovery Model and you didn't configure any Transaction Log Backup job.
To solve your issue ASAP run the following script:

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.
This means you're running some process.
Is the tempdb transaction log file the one that has 1.1TB?
mcrmgAuthor Commented:
okay, it is back to 1.06TB free of 1.08TB

thank you
mcrmgAuthor Commented:
no, it is one of two dbs I restored.
mcrmgAuthor Commented:
Thank you. EEs.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You should plan the following:
  1. Move Database files to a dedicated disk
  2. Move tempdb files to another dedicate disk
  3. Create regular maintenance plans

For the latest task I recommend you to use Ola Hallengren scripts. Just download and run it and you'll see the jobs created. Only think you need to do is schedule those jobs.
mcrmgAuthor Commented:
I appreciate your help..thanks
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
Windows Server 2012

From novice to tech pro — start learning today.