Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

space

Posted on 2016-10-27
16
40 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 16

Expert Comment

by:Ivan
ID: 41862076
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 48

Expert Comment

by:Vitor Montalvão
ID: 41862082
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
ID: 41862152
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41862161
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
ID: 41862165
I found the issue. What is the file *.ldf?  it is 1.1T ...thanks
0
 

Author Comment

by:mcrmg
ID: 41862171
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
ID: 41862176
I will try to shrink it...
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 250 total points
ID: 41862179
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
 

Author Comment

by:mcrmg
ID: 41862181
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 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41862183
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 48

Expert Comment

by:Vitor Montalvão
ID: 41862186
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
ID: 41862192
okay, it is back to 1.06TB free of 1.08TB

thank you
0
 

Author Comment

by:mcrmg
ID: 41862193
no, it is one of two dbs I restored.
0
 

Author Closing Comment

by:mcrmg
ID: 41862197
Thank you. EEs.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41862199
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
ID: 41862202
I appreciate your help..thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

829 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