Month: October 2014
This post helped me with my issue on one of my jobs. Thanks
Issue: When trying to delete a Scheduler job which was created as part of a maintenance plan, we get the following error
TITLE: Microsoft SQL Server Management Studio ------------------------------ Drop failed for Job 'manual_db_backups.Subplan_1'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'. The statement has been terminated. (Microsoft SQL Server, Error: 547) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
We were getting the error even after removing the maintenance plan from SSIS.
To Resolve I did the following:
USE [MSDB] GO -- View the Maintenance plan subplans select * from sysmaintplan_subplans -- View the Maintenance plan logs select * from sysmaintplan_log
To Delete the subplan:
USE [MSDB] go --Delete the Log history for the maintenance plan…
View original post 34 more words
I have started setting up a couple of servers with SQL 2012 SP2. And I noticed that the syspolicy_purge_history job is failing on Step 3 with the following error message:A job step received an error at line 1 in a Powershell script. The corresponding line is ‘set-executionpolicy RemoteSigned -scope process -Force’. Correct the script and reschedule the job.
So far I have not seen any valid resolution online, so as a workaround, I have setup the Execution policy to UNDEFINED on the following registry entry:Computer\HKLM\Software\PowerShell\1\Shelllds\Microsoft.SqlServer.Management.PowerShell.sqlps110
Execution Policy, REG_SZ,Undefined
And as a warning, changing the execution policy to UNDEFINED may open security holes on your server. If you found a fix for this issues, please feel free to leave a comment so I can update this post.
Update: I found a Microsoft KB article that talks about adding a GPO to correct the issue. http://support.microsoft.com/kb/2995870
Most of the time when you try to connect to SQL servers, you often have to connect and enter in the name of the server/instance or you have to click on the drop down to show the previous server you have connected to. That works for me most of the time, but I am going to show you how to add those servers in such a way that you can easily connect to them by just clicking on the server name. And you can even setup different types of authentication used and connection properties.
First, you have to open SSMS and select View, then Registered Servers. And then you can add connections to the servers and instances via the roles assigned on your SQL server.
Select the ‘Database Engine’ icon if you would like to connect to the main database, right-click and select ‘New Server Registration’. And setup the server connection properties.