Post reply

Unable to create index on table when DB is set auto shrink

  • September 8, 2008 at 12:33 am

    #126164

    I am a newer to SQLServer. I intend to create index after data is inserted into a table in an DB with auto-shrink set on to reduce the log file size. But sometimes I get the following exception:

    Could not proceed with index DDL operation on table xxx.dbo.tblUsage' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE.

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    at UsageBuildLib.Builder.CreateLogIndex()

    I am wondering if the CreateIndex conflicts with log file shrink. Can anybody throw lights on this problem? Any comments would be greatly appreciated.

    Thanks.

  • September 8, 2008 at 12:56 am

    #868303

    Turn autoshrink off!

    Autoshrink doesn't just shrink the log file, it shrinks the data file as well. Shrinking data files causes index fragmentation. The next time data gets added, the data file will just have to grow again. That growth can cause file-level fragmentation.

    Shrinking and growing the log file repeatedly causes internal log fragmentation (lots of virtual log files). This slows down backups, especially log backups

    In addition, you have no control over when the shrink and grow operations happen, and when they do, they slow or even stop all activity in the database. Basically, you spend lots of CPU and IO shrinking the file, then lots more CPU and IO growing the file next time its needed and in the process you leave your indexes shuffled and your logs in pieces.

    Ref:

    http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

    http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx

    http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 8, 2008 at 1:15 am

    #868308

    Thanks so much.

    Huasheng

  • September 22, 2008 at 8:44 pm

    #875382

    GilaMonster (9/8/2008)

    Turn autoshrink off!

    Heh... Daaannngg Gail! Didn't know you could shoot porkchops that hard! 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 23, 2008 at 12:34 am

    #875426

    Jeff Moden (9/22/2008)

    GilaMonster (9/8/2008)

    Turn autoshrink off!

    Heh... Daaannngg Gail! Didn't know you could shoot porkchops that hard! 😀

    :hehe: I prefer paw-paws. So much easier to aim.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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