When did DBCC CheckDB ran?

DBCC CheckDB checks database for corruption. It is normal routine to schedule this to be run once a week or every few weeks. And as we all know there is no such thing as non-corruptible storage system. Corruption will occur on some stage – sooner or later.

When I’ll start as a DBA for a SQL Server which has been in production some time already, I’ll first want to see if everything is ok. After I sign in it is my responsibility. It doesn’t matter whether the corruption in critical database is 1 hour old or 2 months old, it is at DBA’s responsibility.

I also do quite a many SQL Server health checks every month and no matter whether customer has ordered for me to do one thing I do for sure: I’ll look that DBCC CheckDB runs regularly on the server. There is no menu button on management studio or simple DMV for looking the info as everyone very well knows. Infrormation of the most recent successful DBCC CheckDB is found from every database’s file #1 and from page #9. That’s no secret either. You can look for Paul Randall’s blog post “CHECKDB From Every Angle: When did DBCC CHECKDB last run successfully?” for more information.

But if you regularly need to check new servers and perhaps do that over multiple databases you’ll soon find yourself from creating a stored procedure for that. I found myself doing that last summer. I decided to do a small blog post for that because I’ve found myself sending this small script for multiple friends of mine and if the script is available online it is easier for everyone to get their hands into that.

So here it is: sp_DBCCInfo. Takes one argument: DbName varchar(200). If parameter is given it will print only that database if you pass in NULL it will print out every database.

You can download the Stored Procedure here.

— =============================================
— Author:        Vesa Tikkanen
— Create date: 2012-09-12
— Description:    Prints dataset containing datetime when was the DBCC CheckDB ran for DB.
— If dbName
— is NULL all databases are returned.
— =============================================

CREATE PROCEDURE sp_DBCCInfo
    @DbName varchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @tmpDbName varchar(200);


    IF OBJECT_ID(‘tempdb..#tempDbs’) IS NOT NULL
    BEGIN
        drop table #tempDbs;
    END

    create table #tempDbs (
        DbName varchar(200),
        LastDBCC varchar(200)
    );

    IF @DbName IS NULL
    BEGIN
        DECLARE database_cursor CURSOR FOR
        SELECT name from sys.databases with (NOLOCK);
    END
    ELSE
    BEGIN
        DECLARE database_cursor CURSOR FOR
        SELECT name from sys.databases with (NOLOCK) where name=@DbName;
    END


    OPEN database_cursor

    FETCH NEXT FROM database_cursor INTO @tmpDbName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        
                            
        IF OBJECT_ID(‘tempdb..#temppi’) IS NOT NULL
        BEGIN
            drop table #temppi;
        END

        

        create table #temppi (
            [ParentObject] varchar(200),
            [Object] varchar(200),
            [Field] varchar(200),
            [VALUE] varchar(200)
        );


        insert into #temppi
            execute(‘DBCC TRACEON (3604); DBCC PAGE (”’ + @tmpDbName +”’, 1, 9, 3) with tableresults’)

        insert into #tempDbs
            select @DbName,VALUE from #temppi where Field=’dbi_dbccLastKnownGood’ group by VALUE;

        FETCH NEXT FROM database_cursor INTO @DbName
    END

    CLOSE database_cursor;
    DEALLOCATE database_cursor;

    select * from #tempDbs;

END
GO

Is System Center all good?

I was struggling today with customer’s SharePoint environment. I tried to add PowerPivot to his already configured farm and it just didn’t work. I got error: “Could not obtain exclusive lock on database ‘model’. Retry the operation later.” from PowerPivot configuration tool when it tried to create PowerPivot Service Application and its database.

First I thought that it has to be me because I just recently did a health check for that server. Perhaps I still had some management studio open somewhere on my workstation… Checked that and it wasn’t me. Ok. Let’s check who’s the bad guy? I started to look for the issue on SQL Server.

I don’t ever remember the db-id’s for system databases so first looked from DMV sys.databases what is the id of the Model database (it seemed to be 3).

I ran: “exec sp_lock” and looked who had locks for model database. In my case it was session #82. After that I looked for “exec sp_who2” to look who is session number #82? It was “NT AUTHORITY\SYSTEM” coming from local system. It was sleeping and client information was “.Net SqlClient Data Provider”. After doing some research on which services on this machine are running as “local system”, I found that it was the “System Center management” – service that was idling on my “Model” database. I’m not an expert of System Center but if this is a normal scenario I wouldn’t recommend running system center on SQL Server machines.

I managed to install my PowerPivot by shutting down the “System Center management” service temporarily and disabling it. System Center notices that service has been disabled and activates it again and starts the service so I needed to refresh services list quite often to see when the service was restarted. After system center had restarted the service I shat that down and disabled the service. I did this quite a many times before I got successful for PowerPivot installer and after that I let the service to start and work normally.

Keeping this in mind it is not always good to have monitoring solutions in place. In this particular example I had changed the model database to be quite a big so duplicating pages from model-database took 40 seconds.