Solved

MSSQL Log files taking up 28 GB

Posted on 2016-08-07
5
51 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
[X]
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
5 Comments
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z 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 50

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

688 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