![]() However having said that and having to come to know this system which I work on quite a bit there are "SELECT INTO" strewn all over in SP's which would mean there is an additional reason which I believe is probably the combination of 3rd table sitting on the foreign server. My hypothesis is that the SELECT INTO locks the system table which stores the new entry for the destination table and the EM can not access this table to build/refresh the list in its menu's thus causing the lock error. Note down the spid of this query window (it should be visible in the bottom of SSMS, right next to username). During the backup of the SQL 2008 DB we might see the below error message: AE50015 Backup failed. Note the SPID of this query window connection. Drop the table using T-SQL as then the query will not timeout but instead get blocked. In the lower right hand corner of SSMS, you should see the username and (in parentheses) the SPID of the connection you're using. What I did however do was change the code (not mine by the way) to drop the table then recreate it and then do INSERT INTO, SELECT FROM instead of the SELECT INTO. In the first, type/paste the command that is timing out (due to a lock). ![]() I've managed to trace it back to a "SELECT INTO" statement in an SP which runs for about 26 minutes in a job where one of the 3 tables in the select sits on another server but I'm not sure if this has anything to do with it. If the transaction is still holding the lock, terminate that transaction if appropriate. The issues are not with Unidac at all, but with how SQL Server uses locks on either rows, pages or full tables, which is done automatically and not foreseeable. That latest link does not work or there is a temporary problem with the site. Starting with SQL Server 2005 you could also enable Snapshots in the database (requires most likely an alter database statement to allow that and also a specific isolation level). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |