Solved

space

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

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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 47

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 47

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 47

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 47

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Resolve DNS query failed errors for Exchange
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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