Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL Log files taking up 28 GB

Posted on 2016-08-07
5
Medium Priority
?
58 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 1000 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 1000 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 52

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

636 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