Failure in Job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Failure in Job

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-03-25 : 23:51:25

Dear All,

I have scheduled job which contains stored procedure,but its getting failed due to below error.

Schema change failed on object ''. Possibly due to active snapshot or other schema change activity. [SQLSTATE 42000] (Error 21386).

Googled around but not found any solution.Please help.

Regards,
Gangadhara

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-26 : 12:34:43

Show us the code.

DBCC CHECKDB uses a snapshot, was that running at the same time?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-03-26 : 21:39:01

Hi tara,

Yes we have Transaction replication set up on the same server for the same article which is used by the job.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-26 : 22:16:46

Oh it was referring to replication snapshot. I hate that Microsoft reuses terms constantly when they mean different things.

We need more information. I would start by showing us the code that failed. Replication handles schema changes by default...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2012-03-26 : 23:07:06

Hi,
In server we have a transactional replication setup for this Db for few articles(which includes only 5 tables)

I have a job which i have scheduled through Agent and its getting below error.

Date 27/03/2012 5:28:30 AM
Log Job History (Maintain emPowering, JCCC and Jems Data - 4 AM)

Step ID 25
Server PRIMARYASERVER
Job Name Maintain emPowering, JCCC and Jems Data - 4 AM
Step Name Consolidate Account
Duration 00:52:53
Sql Severity 16
Sql Message ID 21386
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: AP\GDD. Schema change failed on object '[dbo].[HCP]'. Possibly due to active snapshot or other schema change activity. [SQLSTATE 42000] (Error 21386). The step failed.

The table has been replicated to other Db.
If we run manually its working fine sometime.

Thanks,
Gangadhara MS
SQL Developer and DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources