Post reply

Wrong number of total_pages in sys.allocation_units on a from sql2000 restored db?

  • September 30, 2011 at 1:30 am

    #258149

    One of the checks that runs on every SQL installation and on all databases, is the check if the data fill percentage reaches 90%. No problems with it for 'ages'. But now I get weird numbers on a database that was migrated from SQL2000 to SQL2008 (st, 64bit).

    I get the max data size (in MB) by:

    select sum((Maxsize * 8/1024)) from <database>.dbo.sysfiles where status & 0x40 != 0x40

    Then I get the data used (in MB) by:

    select sum(a.total_pages) * 8192 / 1048576.0

    from <database>.sys.partitions p

    join <database>.sys.allocation_units a

    on p.partition_id = a.container_id

    In this second query I get the weird results.

    While the database has a maxSize of 200MB, the result of the total_pages is 6551MB which results in a fill_percentage of more than 3000%.

    This while the result, with the same queries, for all other databases on all other servers, are correct.

    Has anyone got a clue what is going on here?

  • September 30, 2011 at 3:19 am

    #1388738

    Run DBCC UPDATEUSAGE. It may fix the issue.

  • September 30, 2011 at 3:36 am

    #1388748

    Thanks! That did the trick!

    Very simple and effective.

    I queried google with all kinds of terms, but could not find any thing resembling my problem.

  • September 30, 2011 at 4:03 am

    #1388760

    Thanks for informing that it worked.

    🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply