Solved

space

Posted on 2016-10-27
16
27 Views
Last Modified: 2016-10-27
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
Comment
Question by:mcrmg
16 Comments
 
LVL 15

Expert Comment

by:Ivan
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:mcrmg
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:mcrmg
Comment Utility
I found the issue. What is the file *.ldf?  it is 1.1T ...thanks
0
 

Author Comment

by:mcrmg
Comment Utility
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
 

Author Comment

by:mcrmg
Comment Utility
I will try to shrink it...
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 250 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:mcrmg
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:mcrmg
Comment Utility
okay, it is back to 1.06TB free of 1.08TB

thank you
0
 

Author Comment

by:mcrmg
Comment Utility
no, it is one of two dbs I restored.
0
 

Author Closing Comment

by:mcrmg
Comment Utility
Thank you. EEs.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:mcrmg
Comment Utility
I appreciate your help..thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now