• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

space

Hi,

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
0
mcrmg
Asked:
mcrmg
2 Solutions
 
IvanSystem EngineerCommented:
Hi,

use tree size free application, to see what has used all space.
https://www.jam-software.com/treesize_free/

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?

Regards,
Ivan.
0
 
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?
0
 
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.....
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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?
0
 
mcrmgAuthor Commented:
I found the issue. What is the file *.ldf?  it is 1.1T ...thanks
0
 
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..
0
 
mcrmgAuthor Commented:
I will try to shrink it...
0
 
É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.
0
 
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'.
0
 
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:
ALTER DATABASE TypeDatabaseNameHere SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (2);  

Open in new window

0
 
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?
0
 
mcrmgAuthor Commented:
okay, it is back to 1.06TB free of 1.08TB

thank you
0
 
mcrmgAuthor Commented:
no, it is one of two dbs I restored.
0
 
mcrmgAuthor Commented:
Thank you. EEs.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good.
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.
0
 
mcrmgAuthor Commented:
I appreciate your help..thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now