Solved

MSSQL Log files taking up 28 GB

Posted on 2016-08-07
5
40 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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
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 the fundamental information of how to create a table.

744 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

14 Experts available now in Live!

Get 1:1 Help Now