Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
OpenUtilities
  • Product Communities
  • More
OpenUtilities
OpenUtilities Wiki Disable Auto Shrink to avoid performance degradation
    • Sign in
    • +OpenUtilities
    • Access Service Request Manager
    • +Bentley sisHYD Multi Utility
    • Design Features - promis.e Functionality - Promis.e Standard
    • +OpenUtilities Substation
    • Promis.e Paths
    • -Promis.e Wiki
      • +CONNECT Edition - Promis.e
      • About Promis.e
      • +Administration Features - Promis.e
      • +APIs_VBA - Promis.e
      • +Automation Features - Promis.e
      • +Component Features - Promis.e
      • +Component Management - Promis.e
      • +CONNECT Services - Promis.e
      • +Data Manager - Promis.e
      • -Database Platform Support - Promis.e
        • Backing Up Promis.e Data Automatically
        • Converting to an SQL Parts Database
        • +Creating and Updating the Project Database
        • Desired SQL server Not Listed in the available Servers List
        • Disable Auto Shrink to avoid performance degradation
        • Error - Could not find SQL Server instance named BENTLEYECAD
        • Error - Could not find stored procedure
        • Error - Database version does not match the application
        • Error - Index was outside the bounds of the array (Microsoft.SqlServer.smo)
        • Error - Invalid database name - on Project Database Utility
        • Error - Invalid object name
        • Error - Login failed for Ecaduser: Reason: The Password of the account Expired
        • Error - No version resource could be located in database
        • Error - OraOLEDB.Oracle provider is not registered
        • Error - T-SQL execution command line utility stopped working
        • Error - The database is for product... you cannot use it
        • Error at Startup - Error 0 No connection could be made because the target machine actively refused it.
        • Error at Startup - Error 40 Could Not Open a Connection
        • Error at startup - The Type initializer for ECT.ECAD.API.SystemSetup threw an exception
        • Error at Startup - Unrecognized database format, catalog.mdb
        • Error deleting project - transaction log full
        • Error messages encountered due to case sensitive collation of SQL Server
        • Error on backup - Invalid database object reference
        • Error on Project Database Utility - Cannot find the file specified
        • Error running SQL scripts - Incorrect syntax
        • Exception thrown when creating a project due to SQL permissions
        • Exception thrown when opening a page, transaction log is full
        • Finding the SQL Data folder
        • Installing SQL Server
        • Minimum SQL user permissions
        • Project Database Version - Promis.e
        • Rebuild indexes in the SQL project database
        • Rules for Regular Identifiers
        • SQL Server FAQ
        • Troubleshooting Project Database Connection Problems
      • +Display - Promis.e
      • +Drawing Management - Promis.e
      • +Engineering Design Considerations - Promis.e
      • Error - Object reference...General
      • +Find_Replace - Promis.e
      • Finding the Tutorial
      • +Installation_Configuration - Promis.e
      • +Interoperability - Promis.e
      • +Licensing - Promis.e
      • +Maintenance Features - Promis.e
      • +Migration - Promis.e
      • +OS Support - Promis.e
      • +Panel Layout - Promis.e
      • +Performance - Promis.e
      • +PowerPlatform Support - Promis.e
      • +Print_Publish Features - Promis.e
      • +ProjectWise Integration - Promis.e
      • Promis.e Help file
      • +Reports_Output Features - Promis.e
      • +Template Management - Promis.e
      • +Text Tools - Promis.e
      • Training FAQs
      • +Wire Numbering - Promis.e
      • +Wiring Features - Promis.e
      • +Project Management - Promis.e
      • Promis.e Title
      • Working from home with Promise.e
    • Replace Family - Find_Replace - Promis.e
    • SQL Server Login

     
     Questions about this article, topic, or product? Click here. 

    Disable Auto Shrink to avoid performance degradation

      Product(s): Promis.e, Bentley Substation
      Version(s): through 08.11.13.140
      Environment: N/A
      Area: Database Platform Support
      Subarea: N/A

    Background

    Manual and automatic shrinking of SQL database data files causes index fragmentation, which leads to poor performance.  Having the Auto Shrink property and the Auto Growth property enabled for a database can result in frequent shrink-grow cycles which causes file system fragmentation, adding to performance issues.  To avoid this situation, disable Auto Shrink on SQL Server databases such as the project databases for Promis.e and Bentley Substation.  This will stop automatic shrinking and further index fragmentation but does not fix existing fragmentation.  To correct the index fragmentation use the method described in Rebuild indexes in the SQL project database after disabling Auto Shrink and defragmenting the disk.

    Note: the scripts that create and update the project database have been changed to set the AUTO_SHRINK property to OFF. This change was made for version 08.11.12.120, so depending on the history of the database, Auto Shrink may already be disabled.

    Steps to Accomplish

    I - Disable Auto Shrink

    Option 1

    1. Connect to the SQL Server using SQL Server Management Studio (SSMS).
    2. In the Object Explorer, expand Databases.
    3. Right-click the database to be affected and select Properties.
    4. On the Options page, set the Auto Shrink property to False, click OK.

    Option 2

    1. Connect to the SQL Server using SQL Server Management Studio (SSMS).
    2. Click the New Query button.
    3. Paste the following into the new query window:

      ALTER DATABASE yourdb SET AUTO_SHRINK OFF

    4. Replace "yourdb" with the actual name of the database.
    5. Click the Execute button.

    II - Defragment the physical disk

    This step may be optional, depending on the level of fragmentation that exists.  It would need to be done when users do not need to access the databases.

    1. Back up all databases that have files located on the fragmented disk(s).
    2. Stop the SQL Server service along with its related services
    3. Run the defragmenter program.
    4. After the disk(s) have been defragmented, restart the SQL Server services.

    III - Re-index the database

    See Rebuild indexes in the SQL project database

    This article based on the following works:

    Turn AUTO_SHRINK off!!

    Auto Grow, Auto Shrink and Physical File Fragmentation in SQL Server

    External Links

    How to: Stop an Instance of SQL Server (SQL Server Configuration Manager)

      Original Author: Matt_P

    Keywords: autoshrink

    • Database Platform Support
    • promis.e
    • Performance
    • SQL Server
    • how to
    • Bentley Substation
    • en
    • Problem Solving
    • Share
    • History
    • More
    • Cancel
    • Matt_P Created by Communities MVP Matt_P
    • When: Tue, Oct 14 2014 4:01 PM
    • Jesse Dringoli Last revision by Bentley Colleague Communities MVP Jesse Dringoli
    • When: Fri, Mar 27 2020 4:14 PM
    • Revisions: 9
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2022 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies