MSSQL SBSMonitoring massive log dump

Posted on 2011-10-24
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.
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.
Question by:Poncho_AUS
    LVL 9

    Expert Comment

    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,

    Author Comment

    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.

    Author Comment

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

    Accepted Solution

    In SQL management studio click new query and type:

    Use [Your database name]
    DBCC CheckDB

    Author Comment

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

    Expert Comment

    by: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

    Author Comment

    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
    DBCC CheckDB

    Open in new window

    This returned 40+ consistency errors

    I than worked out I had to use:
    use SBSMonitoring

    Open in new window


    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.

    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.

    Expert Comment

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    I work for a company that primarily works with small businesses as their outsourced IT vendor. As such the majority of these customers utilize some version of Small Business Server. Due to the economics of running a small business, many of these cus…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now