Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to create index on table when DB is set auto shrink Expand / Collapse
Author
Message
Posted Monday, September 08, 2008 12:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 09, 2008 9:31 PM
Points: 12, Visits: 18
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.
Post #565230
Posted Monday, September 08, 2008 12:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #565235
Posted Monday, September 08, 2008 1:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 09, 2008 9:31 PM
Points: 12, Visits: 18
Thanks so much.

Huasheng
Post #565240
Posted Monday, September 22, 2008 8:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
GilaMonster (9/8/2008)
Turn autoshrink off!


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


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #573998
Posted Tuesday, September 23, 2008 12:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 41,529, Visits: 34,445
Jeff Moden (9/22/2008)
GilaMonster (9/8/2008)
Turn autoshrink off!


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


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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #574058
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse