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

    @DbName varchar(200)
    DECLARE @tmpDbName varchar(200);

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

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

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

    OPEN database_cursor

    FETCH NEXT FROM database_cursor INTO @tmpDbName

        IF OBJECT_ID(‘tempdb..#temppi’) IS NOT NULL
            drop table #temppi;


        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

    CLOSE database_cursor;
    DEALLOCATE database_cursor;

    select * from #tempDbs;


How-to update Dynamics AX2012 from SQL Server 2008R2 to SQL Server 2012

This comes to my desk once in a while when we try to update some of our already established AX2012 environments to SQL Server 2012.

Running Dynamics AX on SQL Server 2012 was not possible when SQL2012 RTM came out. If you installed AX on top of SQL2012 you soon noticed that almost every form you opened had issues and complains about “FASTFIRSTROW” – hint. This is because when AX 2012 Form uses Datasource with hint “FirstFast” and tries to open object for editing it uses SQL Server hint “FASTFIRSTROW”. This hint was deprecated in SQL Server 2008 and removed in SQL Server 2012. Replacement for this hint is “Fast 1” which should be used on all new development. It is funny that this deprecation was overlooked when AX 2012 shipped and finally led to incompatibility with SQL Server 2012.

One example of this being when you try to create new sales order and attach a customer to that:

Few months after SQL Server 2012 was released to web Microsoft announced Combatibility Certification of SQL Server 2012 with Dynamics AX 2012. It says that Customers and Partners will need to apply KB2680186 hotfix for SQL Server 2012 support. Ok, sounds good! After that you either slipstreamed the update with new installations or patched already installed systems if needed. But unfortunately just following the installation instructions was not enough. After patching an old system and restarted AOS you still got the same error.

This is because there is one step left for all-knowing consultants. 🙂 You still need to apply the patch to take effect in model store by running “axutil schema“. This minor last step is something I haven’t found anywhere and as a result my old AX2012 installations haven’t been combatible with SQL2012.

So to get your established environment to be compatible you need to apply the patch and run on the AOS server command “axutil schema” in AOS bin-folder (Mine being at: C:\Program Files\Microsoft Dynamics AX\60\Server\vesa\bin).

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.

Hello world!

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!