Msdb.dbo.sp_delete_backup_history Causes DEADLOCKS With Log-Shipping Restore Jobs
Jan 28, 2008
Hi,
I have a weekly job that runs to execute msdb.dbo.sp_delete_backup_history on history older than 7 days on a server that is a log-shipping destination server. However, I have now noticed after putting a new deadlock alert, that this job causes RESTORE LOG jobs for all 6 of my log-shipped databases, which run every minute, to deadlock.
I am not concerned with the fact that the RESTORE LOG jobs are then chosen as the deadlock victims, as they will simply re-run in the next minute anyways.
What I would like to do though is to somehow lock all the tables being cleared by sp_delete_backup_history, so that the restore jobs are forced to wait or fail without causing a deadlock to appear. I was wondering if I could do this via setting the job which runs sp_delete_backup_history to use serializable isolation level, or would that cause other problems/issues? If not that, would setting the deadlock priority level to high prevent deadlocks, or just simply force the restore log jobs to be chosen as the victim?
The main thing is that I would like to stop being "annoyed" by the deadlock messages being generated.
Any comments would be appreciated.
Regards,
Diane.
View 2 Replies
ADVERTISEMENT
Dec 3, 2007
I have a sql2000 server that has failed
i moved the databases to another server
I am trying to restore msdb to another server is this possible in order to get the dts packets and the jobs
View 5 Replies
View Related
Dec 12, 2002
I restored msdb on another server after moving all user databases and logins. I expected to see the jobs after the restore but I didn't.
I've read the article(Q314546) about moving databases and it indicates that you don't have to script them if you move msdb.
I didn't restore master. Only msdb.
Your thoughts please?
Sidney
View 3 Replies
View Related
Dec 21, 2004
Our sysadmin accidentally uninstalled SQL Server started to panic and reinstalled. Thankfully the data/transaction files to our important databases were still present and I simply reattached them but our DTS packages our gone.
However, we've done weekly backups of the msdb database. How do I get the DTS jobs out of these backups?
View 1 Replies
View Related
May 20, 2008
Developers need to view and modify jobs on some servers without being sysadmin. We've added the developers to the following MSDB roles:
SQLAgentOperatorRole
SQLAgentReaderRole
SQLAgentUserRole
Technically they should be able to view and modify jobs through Management Studio when being added only to SQLAgentOperatorRole. However they receive the following error when clicking on the Jobs folder regardless of the msdb role.
Execute Permission Denied on Object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Our original two SQL 2005 servers do not produce this message, but all other instances do. All servers, but one, are SP2 so the service pack is not an issue. Any thoughts?
Thanks, Dave
View 3 Replies
View Related
Nov 13, 2007
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
Any suggestions? Thanks.
T
View 3 Replies
View Related
May 12, 2001
I am trying to restore my msdb database after rebuilding my RAID, and reinstalling sql. I can restore my user databases fine, but my msdb gives me error 3132:
The media set for database msdb' has 2 family members but only 1 are provided. All members must be provided.
I really don't want to lose all my jobs, so any help would be appreciated. thanks.
View 2 Replies
View Related
Nov 13, 2000
hi, I already have jobs in the sql server agen. I want to copy all these jobs to another sql server, What is the best way to do it.
here what I am thinking to do
1. Back up the msdb from the sql server which has the jobs
2. restore msdb into the sql server where I need to create the jobs.
I got an error message, 15089 cannot change the 'single user' option of a database while another user is in the database.
But no one is in except me.? thanks for your input
Ahmed
View 5 Replies
View Related
May 14, 2001
I am trying to restore my msdb database after rebuilding my RAID, and reinstalling sql. I can restore my user databases fine, but my msdb gives me error 3132:
The media set for database msdb' has 2 family members but only 1 are provided. All members must be provided.
I just performed a normal database backup to disk, and i was able to restore the other db's i backed up the exact same way...
I really don't want to lose all my jobs, so any help would be appreciated. thanks.
View 1 Replies
View Related
Aug 9, 2007
Hello all!
First of all our servers:
SQL_PROD: production server with SQL Server 2005
SQL_TEST: test server with SQL Server 2005
What we are trying to do:
We are testing our backup and recovery procedures.
To do so we are using the backups from SQL_PROD and restoring them on SQL_TEST.
We are restoring: all user databases, master database, msdb database and model database.
The issue we are experiencing:
I think we've hit a kink in our recovery procedures concerning msdb database in the context of a recovery on another machine (SQL_TEST in this case).
We include msdb in our nightly backups so that, in the event of server loss, our maintenance plans and jobs get restored.
On SQL_TEST (after a recovery), the maintenance plans and jobs do get restored but the problem is that they reference the SQL_PROD server (server's name, disks, etc...).
Deleting the jobs and maintenance plans from SQL_TEST we got no errors and the jobs and plans disappeared from beneath the Maintenance Plans and Jobs nodes. Doing a refresh on the nodes, the €œdeleted€? plans and jobs reappear beneath their respective nodes! Upon the second deletion (and any consecutive delete) we get the error:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
...
The SELECT permission was denied on the object 'sysmaintplan_plans', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)
Clicking on "Show technical details" reveals this:
...
Server Name: SQL_PROD
Error Number: 229
Severity: 14
State: 5
Line Number: 1
...
Note that SQL_PROD is being referenced in the error message!
This morning I took a look at SQL_PROD€™s Maintenance Plans and Jobs (the source of the msdb backup) and surprise surprise: THEY WERE ALL GONE!!! This is a MAJOR BUG!!! Deleting a job or a plan on one server should not have the effect of deleting it from a different server (regardless of the origins of the msdb database). Is this a known bug? Or is it considered €œnormal€? behavior?
My questions are:
How do we delete the maintenance plans and jobs from SQL_TEST without deleting them from SQL_PROD?
What is the proper/recommended method to backup and restore maintenance plans and jobs (in fact msdb)? Is it by scripting things and recreating them on the recovery server in an empty msdb or is there a way to restore msdb properly from the crashed server?
Thanks for your help.
Regards,
Mark Tabash
View 11 Replies
View Related
Jan 28, 2008
I had a development server that had all the raid drives fail at once.
This included most of the data/log files and all of the backups.
I need to get DTS packages from the MSDB database, I have the ldf/mdf's for that database, is there a way to restore it?
Thanks
Susan
View 6 Replies
View Related
Dec 3, 2015
Late last Sunday I had to do an emergency rebuild of our SQL Server (v2012). In my haste, I neglected to restore the MSDB and MODEL DBs, and am instead using those which came with the fresh SQL install -- which obviously don't contain my jobs and maintenance plans. Would there be any problem/conflicts with me restoring those DBs this weekend?
View 5 Replies
View Related
Sep 18, 2007
hi,
The log shipping between my 2 serves have failed. Can somebody please tell me the steps to follow in order to restore the log shipping between the two servers. I applied full backup. But restoring of transaction logs is failing, I am not able to figure out the cause for the failure
View 4 Replies
View Related
Sep 21, 2000
How does one correctly go about restoring the MSDB database?
How/what should I do with the status (loading)?
I have a good backup of the MSDB.
Any help will be appreciated.
View 2 Replies
View Related
Aug 21, 2003
hello everybody
I have custom log shipping .
it run every 15 min
DB1 located on Server1
DB1 backup logs to server 2
DB2 located on server 3(300 km away from server1 and server 2)
Db2 restore logs from server 2
If server1 and server2 become unavailable
1.
Would enough login to server3
and
run
RESTORE DATABASE Db2
WITH RECOVERY
to bring db from read only to production mode up last restored log?
Thank you
Alex
View 1 Replies
View Related
Nov 8, 2007
I am getting following error in windows event viewer so can u tell me how to slove this problem
1.The log shipping primary database Aric-PCSQL2005.AdventureWorks has backup threshold of 30 minutes and has not performed a backup log operation for 319 minutes. Check agent log and logshipping monitor information.
2.The log shipping secondary database Aric-PCDEVSQL1.AdventureWorks has restore threshold of 5 minutes and is out of sync. No restore was performed for 304 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.
View 1 Replies
View Related
Nov 5, 2007
I am using sql 2000 standard. I set up log shipping 3 month ago it was working fine but yesterday its fail I got this error 4305. I don€™t know why this error I am getting
error messages:
failure /4305
activity : Load
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
View 3 Replies
View Related
Sep 20, 2007
I am trying to imitate a DR situation where the primary db is down and I need to recover the secondary db on another server. They are a log shipping pair and so to imitate a DR, I remove the log shipping in the primary server maintenance plan. Then I go to the secondary server and disable the log shipping jobs there and attempt to do the following
RESTORE DATABASE database_name WITH RECOVERY
but I can't get exclusive use because the database is in use. But I don't see any other users... am I wrong in thinking that the log shipping was completely deleted? Anything I can do to force exclusive access?
View 10 Replies
View Related
Jul 23, 2014
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
View 6 Replies
View Related
Jan 31, 2007
I want to restore all jobs from one server to another. I seem to remember that in SQL 2000 there was an option to script all jobs but it seems that in 2005 you have to script them 1 at a time. Is there a more efficient way than scripting each job and then executing each script individually?
Thanks,
-Dave
View 1 Replies
View Related
Oct 1, 2001
Hi,
While backing up our database, I am getting the following message:
Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate a problem with the MSDB database. DUMP/LOAD was still successful. (Message 3009)
Does anybody have any resolution for the same ?
Thanks,
Rajan
View 2 Replies
View Related
Nov 6, 2006
We're planning to implement log shipping on our databases, and I have been toiling with it all weekend trying to get it to work on some test databases. The result is the same whether I do it via the wizard or manually via T-SQL.
I am using 3 servers, all SQL Server 2005 Standard SP1. All 3 SQL Servers are configured identically.
When I setup log shipping, it initializes with no problems. When it processes the first tran log file, it restores it with no problem. Every successive log file thereafter is not restored. No errors are generated. The only outright indication of a problem is that the monitor server shows that there has not been a recent restore.
The backup and copy both suceed. The restore claims to suceed. If I review the job history for each step, it says that it skipped the log file and then reports that it did not fina any log files to restore.
Message
2006-11-06 05:00:01.92 Skipped log backup file. Secondary DB: 'MyDemo', File: 'D:MSSQLBackupMyDemoMyDemo_20061106115619.trn'
Message
2006-11-06 05:00:01.95 Could not find a log backup file that could be applied to secondary database 'MyDemo'.
2006-11-06 05:00:01.96 The restore operation was successful. Secondary Database: 'MyDemo', Number of log backup files restored: 0
Any ideas or suggestions?
View 3 Replies
View Related
Nov 21, 2005
Whenever I start my SQL Express 2005 database, I get the following in the logs :
View 10 Replies
View Related
May 13, 2007
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
View 1 Replies
View Related
Jun 8, 2006
Hi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
View 5 Replies
View Related
Mar 17, 2005
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
What do you guys think?
View 8 Replies
View Related
Aug 13, 2002
Hi,
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Any help and/or suggestions would be appriciated.
View 2 Replies
View Related
Dec 17, 1998
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?
Thanks!
ben
View 2 Replies
View Related
Mar 5, 2001
Hi ,
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
Can anybody throw some light on this.
Thanks in Advance
Siv
View 1 Replies
View Related
Jul 10, 2002
I am getting the following dead lock error message writtent to the Error Log.
How do i interpret this...?
2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
View 1 Replies
View Related
Oct 27, 2004
Hi,
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim
I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
Thanks for any help.
View 4 Replies
View Related
Feb 16, 2004
Hi folks,
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499
respectively. Any help would be welcome.
Thanks in advance,
Don
View 9 Replies
View Related
Sep 16, 2007
hi,
We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.
The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.
There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?
Any ideas?
Thx
Sri
View 6 Replies
View Related
