Post reply

Data Page Size question

  • April 28, 2007 at 6:57 pm

    #69738

    I have SQL Server 2000 & my USER database compt level is 6.5. my question is that data page size for this user database is 2KB or 8KB. Which SP or DBCC command i can run to see the actual PAGE SIZE (8060 or 8192) in the tables.
    My 2nd question is my SQL 6.5 store procedure creating certain temp tables in SQL Server 2000 TEMPDB, does it work in a normal way or is there any effects. basically i want to know is that TEMPDB works same way with SQL Server 2000 Database or its approach is different for SQL Server 6.5 User Database that as been RESTORE\Attach on SQL Server 2000 with Comptlevel 6.5. As we know that MASTER & TEMPDB Comptlevel is 80 & we have to rebuild if we change thier comptlevel to 70 or 60.
    any information on this is highly appreciated. thank you

  • April 29, 2007 at 9:07 am

    #703159

    Very intersting questions. I'd try to experiment and with dbcc page or even add data to a new table in 2k chunks and see how many pages are used.

    My suspicion is the pages are 8k and that only the compatability level affects keywords, not the physical structure.

    I would never change the master, tempdb, or msdb comp levels.

  • April 29, 2007 at 10:19 am

    #703163

    Steve,

    thanks for your reply, i tried using DBCC PAGE & didn't see the PAGE size in the result or i might be looking at the wrong place. i agree to you about comptlevel but i am not sure about how SQL Server 2000 Database Engine reacts when it sees SQL server 6.5 USER database.  thank you

  • May 1, 2007 at 7:30 am

    #703529

    Look at "Connecting Early Version Clients to SQL Server 2000" in BOL.

    As for storage the system enforces 8K on even 6.5 compatibility mode databases and you cannot restore them to a 6.5 server.

    As for storage it is 8192 per page. However, there are some control bits on a page so the free space will never be more than 8188. The field you want to look for is m_freeData in the DBCC PAGE output. Also make sure you turn on trace flag 3604 to see the output

    Ex.

    DBCC TRACEON (3604)

    GO

    DBCC PAGE (Risk_Assessment,1,1,3)

    GO

    Hope this helps.

  • May 1, 2007 at 8:42 am

    #703548

    I was not sure about what this "m_freeData" after doing DBCC PAGE, but you explained well.

    Thanks for your feedback,

     

    Thanks

    Jay

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

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