locked
sp_db_increased_partitions on SQL 2008 SP3 RRS feed

  • Question

  • Hi Team,I need to check if SQL DB partition are set, on SQL Server 2008 SP3. Microsoft docs says different and opposite things, about how to interpret the result of the command.in a document it says' 0 'and' ok, in another the oppositewhich is the command to verify that 15000 partitions support is enabled?thank you

    ref. to https://msdn.microsoft.com/en-us/data/hh204563(v=sql.100).aspx

    and to http://download.microsoft.com/download/b/e/1/be1aabb3-6ed8-4c3c-af91-448ab733b1af/support_for_15000_partitions.docx

    Wednesday, December 5, 2018 11:40 AM

Answers

  • Hi Cabar srl,

     

    >>Hi Team,I need to check if SQL DB partition are set, on SQL Server 2008 SP3.

    Microsoft docs says different and opposite things, about how to interpret the result of the command.in a document it says' 0 'and' ok, in another the oppositewhich is the command to verify that 15000 partitions support is enabled?thank you.

     

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-db-increased-partitions?view=sql-server-2017

     

    I also found that for the above link, under the subtitle "Return Code Values" the values meaning is different from the description under the Arguments subtitle.

     

    I will give feedback to someone related. You also can create the feedback on this website.

    https://feedback.azure.com/forums/908035-sql-server

     

    Besides, on SQL Server 2008 SP3 executing the following stored procedure can check whether a database has support for 15,000 partitions enabled.

     

    exec sp_db_increased_partitions '<database-name>'

     

    If it returns 1, the database has support for 15,000 partitions enabled.

    If it returns 0, the database disable support for 15,000 partitions.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 6, 2018 7:53 AM
  • What edition of SQL Server 2008  you are using .SQL Server 2017 supports up to 15,000 partitions by default. In versions earlier than SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default 

    https://www.brentozar.com/sql/table-partitioning-resources/

    To find out whether a database has support for 15,000 partitions enabled, use the following command:
    exec sp_db_increased_partitions '<database-name>'
    The output is:
    increased_partitions
    1

    Changing support for 15,000 partitions cannot be performed inside a user transaction. Changing the state to enable or disable support for 15,000 partitions requires an exclusive lock on the database during the entire process. This command will fail if there are active users in the database because this prevents the exclusive lock on the database. 

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd578580(v=sql.100)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 6, 2018 5:19 AM

All replies

  • What edition of SQL Server 2008  you are using .SQL Server 2017 supports up to 15,000 partitions by default. In versions earlier than SQL Server 2012 (11.x), the number of partitions was limited to 1,000 by default 

    https://www.brentozar.com/sql/table-partitioning-resources/

    To find out whether a database has support for 15,000 partitions enabled, use the following command:
    exec sp_db_increased_partitions '<database-name>'
    The output is:
    increased_partitions
    1

    Changing support for 15,000 partitions cannot be performed inside a user transaction. Changing the state to enable or disable support for 15,000 partitions requires an exclusive lock on the database during the entire process. This command will fail if there are active users in the database because this prevents the exclusive lock on the database. 

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd578580(v=sql.100)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 6, 2018 5:19 AM
  • Hi Cabar srl,

     

    >>Hi Team,I need to check if SQL DB partition are set, on SQL Server 2008 SP3.

    Microsoft docs says different and opposite things, about how to interpret the result of the command.in a document it says' 0 'and' ok, in another the oppositewhich is the command to verify that 15000 partitions support is enabled?thank you.

     

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-db-increased-partitions?view=sql-server-2017

     

    I also found that for the above link, under the subtitle "Return Code Values" the values meaning is different from the description under the Arguments subtitle.

     

    I will give feedback to someone related. You also can create the feedback on this website.

    https://feedback.azure.com/forums/908035-sql-server

     

    Besides, on SQL Server 2008 SP3 executing the following stored procedure can check whether a database has support for 15,000 partitions enabled.

     

    exec sp_db_increased_partitions '<database-name>'

     

    If it returns 1, the database has support for 15,000 partitions enabled.

    If it returns 0, the database disable support for 15,000 partitions.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 6, 2018 7:53 AM