SQL Server Q&A

Symptoms
The following error message may appear in the SQL Server Log Shipping Monitor View Restore History window:

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3154: [Microsoft][ODBC SQL Server Driver][SQL Server]The backup set holds a backup of a database other than the existing ‘pubs_test’ database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormallyThe preceding error message occurs if: You configure multiple databases for log shipping.You back up the transaction logs for the databases to the same folder.The database names only differ by “_tlog” in the name. For example, pubs_test and pubs_test_tlog.
Resolution
The RESTORE job that runs on the secondary server incorrectly parses the transaction log backup file names and attempts to restore transaction logs that belong to a different database. This behavior does not cause any other problems on the primary or the secondary servers.

Symptoms
The following error message is returned when you attempt to configure publishing using a remote distributor if the password required for the publisher to establish an administrative link with the distributor isNULL:

SQL Server Enterprise Manager could not configure ‘<Distributor_name>’ as the Distributor for ‘<Publisher_name>’.
Error 18456: Login failed for user ‘distributor_admin’.
Resolution
Configure the publisher as a trusted publisher to the distributor so that a password is not required to establish the administrative link between the publisher and the distributor. To change the security settings for the publisher, follow these steps:On the Tools menu, select Replication.On the Publisher tab, highlight your publisher in the list, and then click the ellipsis (…) next to that publisher.In the Replication Agents on the Distribution Log into the Publisher section, select the By impersonating the SQL Server Agent account on ‘{ServerName}’ (trusted connection) check box.
-or-Do not use a login that has a NULL password for establishing the administrative link between the publisher and the distributor. The administrative link password can be set in Enterprise Manager. To do this, follow these steps: On the Tools menu, select Replication.On the Distributor tab, under the Administrative link password section, enter your password in the password and confirm password fields.
Note We recommend that you use a strong password in this step.

Symptoms
In Microsoft SQL Server 2008, the syspolicy_purge_history SQL Server Agent job may fail when you run the syspolicy_purge_history job on a clustered instance. You may receive an error message that resembles the following in the history log file for the syspolicy_purge_history job:

Date datetime
Log Job History (syspolicy_purge_history)
Step ID 3
Server SQLVirtualName\instancename
Job Name syspolicy_purge_history
Step Name Erase Phantom System Health Records.
Duration 00:00:33
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: user. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’.
Correct the script and reschedule the job. The error information returned by PowerShell is: ‘SQL Server PowerShell provider error: Could not connect to ‘SQLVirtualName\instancename’. [Failed to connect to server SQLVirtualName\instancename. --> An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] Failed to connect to server SQLVirtualName\instancename. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) ‘
A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find path ‘SQLSERVER:\SQLPolicy\SQLVirtualName\instancename’ because it does not exist. ‘ A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘(Get-Item SQLSERVER:\SQLPolicy\SQLVirtualName\instancename).EraseSystemHealthPhantomRecords()’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘You cannot call a method on a null-valued expression. ‘. Process Exit Code -1. The step failed.
Resolution
This problem may occur if the syspolicy_purge_history job uses the computer node name instead of the virtual server name for the cluster instance.