?
Solved

MSSQL SBSMonitoring massive log dump

Posted on 2011-10-24
8
Medium Priority
?
2,452 Views
Last Modified: 2015-10-21
Having an issue with SQL creating log dumps on my SBS 2011 box. This seemed to start all of assunden and nothing has been done that should have caused it. It could have been an update but I do not recall any update getting installed around when this started.

Basically I have 400GB's asside for my SBS VM and the SBS without the log dumps takes up 120GB's, within 2 days of doing nothing the HDD will be full due to the folder in the image bellow. I am running CCleaner over the folder on a daily basis to keep it functioning, some files cannot be deleted due to being access by a process.
 Log Folder
The only error in the event log that seems to have any relation to the issue and one of very few errors in the event log at all is in the image bellow.
  Error
The only thing that I get pointed at when doing my research on this is SQL Maintenance of which I have never ever had to do or seen done on an SBS box that is effectively default.
It gets used for exchange and basically nothing else, not files, print, sharepoint, sql (other than built-in SBS usage) or any of that gets used or has had settings changed from a default install.
I am trying to connect to the SQL server with management studio (Server: (local) - Type: Database Engine - Auth: Windows) to look at and run maintenance but all I get is the error bellow.
 SQL Connect Error
Now I am not sure if I am triyng to connect incorrectly and if maintenance will do anything. I am a complete SQL novice so any imput will be very helpful.
0
Comment
Question by:Poncho_AUS
8 Comments
 
LVL 9

Expert Comment

by:SharePointGirl
ID: 37022675
If sql is running on a virtual machine it believes it has all the memory allocated to it. This looks like anther process is nicking its memory. Have you overallocated the memory on the box?

I know with some virtual systems it is possible to do this now, but without more info it is difficult to diagnose,
0
 

Author Comment

by:Poncho_AUS
ID: 37023146
I am running it as a guest on server 2008 r2 with the hyper-v role, it has 8GB allocated and memory is under allocated on the host. I will turn off another guest to free up further space however I can see that as being a problem.
I am happy to provide any information needed for a solution to be achieved, surely I am not the only one having this issue.
0
 

Author Comment

by:Poncho_AUS
ID: 37042977
So after more research I possibly need to run DBCC CHECKDB but I have a fundamental yet simple problem with doing so..
Where does this command get run? I can't type it in CMD nor PowerShell so how does one run it?
I told you I was an SQL novice...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Accepted Solution

by:
Marc Smets earned 2000 total points
ID: 37043576
In SQL management studio click new query and type:

Use [Your database name]
DBCC CheckDB
0
 

Author Comment

by:Poncho_AUS
ID: 37043603
Hi Shamzi
Thank you for your comment but I am unable to connect to my server with the SQL Server Management Studio, please read the initial post.
0
 
LVL 3

Expert Comment

by:Marc Smets
ID: 37043750
Sorry Poncho for not reading the last 2 lines

Can you verify following settings?

Open Sql Server Configuration Manager
- Check under SQL Server Network Configuration - Protocol for SQLExpress if the shared memory is set to 'Enabled'

Also check under SQL Server Services if the SQL Server browser is started
If not doubleclick it
On the 2nd tab ('Service') set the Start Mode to 'Automatic' and click Apply
On the 1st tab ('Log On') click Start

Then restart your SQL Management Studio
At Server Name click 'Browse for More' and select your instance.

Hope this works for you
0
 

Author Comment

by:Poncho_AUS
ID: 37043864
Thanks for you help Shamzi, the info and questions you posted pointed me in the right direction.

I checked for the shared memory for SQLExpress however that protocol does not exist, I do not have SQLExpress installed. The ones I do have is SBSMONITORING, SHAREPOINT, MICROSOFT##SSEE and they all have shared memory enabled.

The server browser was started but I restarted it anyway. What I must have been doing wrong was not clicking on network servers instead of local server when browsing for a server (Unsure why it doesn't find it locally though..) after waiting the SBSMONITORING database showed up.

From there I stumbled my way through the SQL engine query, using Google and guessing how the query commands should be layed out.

I ran
Use SBSMONITORING
DBCC CheckDB

Open in new window

This returned 40+ consistency errors

I than worked out I had to use:
ALTER DATABASE sbsmonitoring SET SINGLE_USER WITH ROLLBACK IMMEDIATE
use SBSMonitoring
DBCC CHECKDB ('SBSMonitoring', REPAIR_ALLOW_DATA_LOSS)

Open in new window

and
ALTER DATABASE sbsmonitoring SET MULTI_USER WITH ROLLBACK IMMEDIATE

Open in new window

To set it back where it was.

I do not recommend anyone uses REPAIR_ALLOW_DATA_LOSS command unless data can be lost. I am not exactly sure what data is kept in the SBSMONITORING database but I am assuming it is not critical data and will repopulate with time. This is a risk I took, backups have not been running of late due to the size of the logs caused by this issue.
This might provide more info for those considering running this SQL procedure.
http://www.sql-server-pro.com/dbcc-checkdb.html

I can confirm this has cleared all errors in the SBSMONITORING database but I cannot yet 100% confirm the error log problem is resolved however I have not seen any data increase in the folder in the last 15 - 20 minutes which has not been the case so far. I will close and award all points Shamzi in a short time.
0
 

Expert Comment

by:StillGrey
ID: 41083525
FWIW, had the same issue and the above fixed it for us.  SBS 2011
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 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