December 15, 2008 at 3:45 pm
Error messages:
Source: Microsoft.SqlServer.Smo
Target Site: Void PrefetchObjectsImpl(System.Type, Microsoft.SqlServer.Management.Smo.ScriptingOptions)
Message: Prefetch objects failed for Database 'xxxxx'
Stack: at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjectsImpl(Type objectType, ScriptingOptions scriptingOptions)
at Microsoft.SqlServer.Management.Smo.Database.PrefetchObjects(Type objectType)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.ObjectPrefetchControl.DoPrefetch(Database database)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.PrefetchObjects(ObjectPrefetchControl[] objectPrefetchControls)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoPrefetchWithRetry()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
Server XXXXX, Level 13, State 51, Procedure , Line 1
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: MSSQLServer, Error number: 1205)
December 16, 2008 at 12:20 am
It says that a deadlock occurred and this process (whatever it is) was the deadlock victim and hence it was killed.
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
December 18, 2008 at 9:31 am
The snapshot holds locks during the snapshot generation processs. This is what you are seeing.
You need to investigate why these deadlocks are occuring and see if you can change the user code to less the impact of it. Use the nolock hint wherever possible.
December 18, 2008 at 10:04 am
Hey Atul,
If I need to investigate why it was choosen as the deadlock victim where do I start? This is the second time I had this error. Last time it ran successfully on the next day...Please give me some idea how I can investigate which processes were running that time when this process was running and why it was choosen the victim?
December 18, 2008 at 10:14 am
You can enable traceflag 1222 (DBCC TRACEON (1222,-1)) to get the full deadlock graph written into the error log. That will tell you what the other process was.
As for the victim, SQL will pick the process that's the easiest to roll back as the victim.
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
December 18, 2008 at 10:20 am
Gail will If I turn on that flag will it show me the graph of the past like it was occurred yesterday... I have not worked on trace flagss so a lil bit confused
December 18, 2008 at 10:38 am
srawant (12/18/2008)
Gail will If I turn on that flag will it show me the graph of the past like it was occurred yesterday...
No. It will print the deadlock graph into the error log when a deadlock occurs. SQL doesn't keep history of that kind of thing.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply