Solved

MSSQL Log files taking up 28 GB

Posted on 2016-08-07
5
43 Views
Last Modified: 2016-08-08
Dear Experts,
I was alerted by the users not receiving emails, and when I went into our Exchange server, I realized that C drive was critically full.  I started to clean up, after running disk clean up, and doing routine things, I still did not have enough room.  
I found out that MSSQL 10_50.SBMONITORING\MSSQL\Log folder was 28GB.  I would like to reduce the size of this folder, but not sure if I can just go in and delete files.  Some .mdmp files are over 5 years old.
We are using Windows Small BUsiness Server 2011 Standard, and besides Exchange Server 2010, there is a database program on this drive.  The data is stored on another drive.
Please advise.
0
Comment
Question by:yballan
5 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 250 total points
ID: 41746502
you can delete old dups ( .mdmp ) files if you do not have active MS support case...
if your big files there named as "ERRORLOG" ( if another - please post details)

you need to set regularly running job that runs this  system proc
sp_cycle_errorlog  ( it will create a new errorlog and delete oldest one)
you can run this several time to clean all error logs if you do not need them to see what was logged there ..


(https://technet.microsoft.com/en-us/library/ms182512.aspx)
/Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created./

I'd recommend to check the Sql error log in order to identify potential problem... (just in case)

Also you can review MS recommendations

How to manage the SQL Server error log
https://support.microsoft.com/en-us/kb/2199578
---
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41746525
You should do a backup to include the transaction logs. I think they'll automatically shrink after that, in most circumstances.

Good luck!
1
 
LVL 2

Accepted Solution

by:
JesNoFear earned 250 total points
ID: 41746590
Yes, you need to include the transaction logs in your backups, It will dump them back to 0 and will start rebuilding them.
They hold each SQL transaction that takes place on the database, they are used to roll back changes or differential backups.

just purging them is a bad idea, just all around, SQL expects them to contain data in relation to what it was and what it goes to.

If you do not need this or want to back them up, you can use SQL Management Studio, right click on your database and go to properties, Click on Options, and change "Recovery model" from FULL to Simple, that eliminates the transaction logs all together.

If you would like to read up on it a little and or make the change with SQL commands.
https://msdn.microsoft.com/en-us/library/ms189272.aspx
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41746792
First lesson:
- Do NOT install user databases in C: drive. It will affect your operating system.

Second lesson:
- When using databases with Full Recovery model you need to schedule regular transaction log backups to truncate the file.
0
 

Author Closing Comment

by:yballan
ID: 41747127
Thank you, Experts, for pointing me tot he right direction!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

15 Experts available now in Live!

Get 1:1 Help Now