Interactive Services Dialog Detection window for SQL Agent

Recently I have encountered "Interactive Services Dialog Detection".

Here is the solution and follow below steps to disable the interactive service dialog detection window.

1. Open services.msc

2. Select SQL Server Agent--> Right click properties

3. Select the logon tab

4. Uncheck the “Allow Service to interact with Desktop”

5. Restart the SQL Server Agent

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Simulate the experience of acutal test -ucertify.com

Recently I have reviewed Prepkit and it does a great job of simulating the experience of the actual test.

Here is the link http://www.ucertify.com/

Unable to start execution of step 1 (reason: line(1): Syntax error)

You may receive the error “Unable to start execution of step 1 (reason: line(1): Syntax error” under below circumstances in SQL Server 2005/2008.
1. Created a new maintenance plan
2. Modified the path in existing maintenance plan
3. Existing database fails due after File servers IP Change..etc

Here is the complete error message:
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details:
Additional Information:
Job ‘ Log_Backup’ failed. (SqlManagerUI)

Additional information from Log File Viewer:-
Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\\\backup\\t..." failed with the following error:"Cannot open backup device \\backup\testdb.trn'. Operating system error 53(The network path was not found.). BACKUP LOG is terminating abnormally.” Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:
1. Check the login used to schedule the backup has access to backup device path and read/write permissions.

Run below t-sql from New Query to check the permission.
EXECUTE master.dbo.xp_create_subdir N'\\backup \testdb'

2. If your Backup Maintenance plan suddenly stopped working after Network maintenance then it may be related to name resolution to File Server Name or IP Change or Alias name that you are using in the backup.

Check whether you are able to resolve correct name, IP address using nslookup or check with your Windows team to fix name resolution.
For ex:- nslookup "server name or IP address or File server name"

3. If you still have issues
- Go to SQL Server Agent\Jobs and click properties
- Go to Steps section
- Highlight the Sub plan and click Edit
- In the “ package section” add a Backslash to the beginning of the path listed

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com

Add or Remove a node in SQLServer 2005 Failover Cluster

You need to follow the steps in case of adding or removing a node in a cluster or recover a failed node in a failover cluster environment. In case of node1 failure, the SQL Server failover cluster fails over to node2. Use Cluster administrator to evict node from cluster.

You may receive following error while adding or removing a node to the existing cluster.

Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.

Error message from event log:-

"SQL Server Remote Setup .job" (setup.exe) 5/20/2011 10:15:00 PM ** ERROR **
Unable to start task.
The specific error is:
0×80070005: Access is denied.

Solution: You might be connected via Remote Desktop and you may have some active remote desktop session. Please note that you must close all your RDP sessions before proceeding to installation.

To add or remove a node you must be a local administration on all the nodes of the failover cluster.

Step 1. Add a node to the Cluster
• Assign the quorum
• Run Add node wizard in Custer administrator from new node
• Provide Cluster service account password to add the node
• On the New Node assign the clustered drives used by SQLServer and verify the node recognizes the cluster drives
• The drive where you install SQLServer should have same driver letter as other nodes

Step 2. Add or remove a node in SQL Server 2005 Cluster
• Login to the surviving Node or active node of the SQLServer Cluster
• Click Add or Remove Programs in Control Panel
• Select the Microsoft SQL Server 2005 and click change
• The SQL Server 2005 Installation Wizard starts
• On the Component Selection page, select the SQL Server 2005 instance name to change and click next
• On Feature Maintenance page select a Database Engine component to change and click next
• SQL Server 2005 System Configuration Checker starts
• On the Microsoft SQL 2005 Welcome screen click next
• The System Configuration Checker scans the existing configuration on the computer
• Review the warnings and error messages, correct the errors as needed and click next
• On the Change or Remove Instance page, click Maintain the Virtual Server.
• On the Cluster Node Configuration page select the node from the available nodes list and click add . To remove a node from the failover cluster, select the node and click remove.
• In Remote Account Information, provide the password of the user currently logged in(This user should be a local admin on all the nodes) and click next
• Enter the service account username/password for each listed service and click next.
• On Error and Usage Report page, do not select anything and click next
• On the Ready to Install page, click install
• On the Completing the Microsoft SQL Server Installation Wizard page, you can review the summary log by clicking on the link provided. To exit from Installation wizard click Finish.

Step 3.Install the Workstation Components and Service packs
• Run the setup.exe from Installation CD or software dump
• Install only workstation components (note that these are not part of the cluster)
• All nodes in a failover cluster must be at same service pack version, install the service packs as same as servicing node or active node.

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com