• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • 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
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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