asked on

MSSQL SBSMonitoring massive log dump

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.

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.

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.

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.

SharePointGirl

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,

Poncho_AUS

ASKER

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.

Poncho_AUS

ASKER

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...

ASKER CERTIFIED SOLUTION

Marc Smets

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Poncho_AUS

ASKER

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.

Marc Smets

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

Poncho_AUS

ASKER

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.

StillGrey

FWIW, had the same issue and the above fixed it for us. SBS 2011