SQL SERVER - FIX - REPLICATION ERROR: Cannot execute as the database principal because the principal "dbo" does not exist

I was dealing with a replication issue, the Log Reader Agent was failing with the following messages:

Error messages:

The process could not execute 'sp_replcmds' on 'ServerName'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)

Get help: http://help/15517

The process could not execute 'sp_replcmds' on 'ServerName'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: http://help/MSSQL_REPL22037

In several forums you will see a discussions about it, but most of them didn't provide a final solution for it.

The solution for it is really simple:

ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO sa 

The reason is, the DB was restored from another environment or was restored from an old SQL Version.

Script to generate T-SQL sentence to backup all DBs in SQL Server

You can use the following script to generate an output that will help you to backup all existing databases in SQL Server. For it follow the following steps.

1. Connect to your instance using SQL Server Managment Studio
2. Click in New Query
3. Select the option to have the results in a text.
4. In the new query window please paste the following script:

/*----------------------------------------------------------------

-  Author: Ruben Carrillo Siañez                                --

-  Description: Script to generate backup script for all DB's   --

-  Version for SQL Server 2005 & 2008                           --

-  Created: 12/07/2010      Modified: 01/23/2012                --

----------------------------------------------------------------*/

SET NOCOUNT ON

DECLARE @BackupPath varchar(300)

DECLARE @name sysname,

      @devicename sysname,

      @msg varchar(300),

      @newbackuppath varchar(300),

      @retstatus int,

      @checkbackuppath varchar(100),

      @length smallint

--Change the variable value with the path that you going to use

SET @BackupPath = 'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup'

DECLARE dbs_cursor CURSOR FOR

SELECT  name

FROM master..sysdatabases

WHERE name not in ('tempdb')

ORDER BY name

OPEN dbs_cursor

FETCH NEXT FROM dbs_cursor INTO @name

WHILE       @@fetch_status = 0

      BEGIN

        SET @devicename = rtrim(@name) + '_dmp'

        SET @newbackuppath = @backuppath

        PRINT 'backup database ' + @name + ' to disk = N''' + @backuppath + '\' + @name + '.bak'' with init, skip

GO

Print ''Database backed up was ' + @name + '''

GO'

        FETCH NEXT FROM dbs_cursor INTO @name

      END

CLOSE dbs_cursor

DEALLOCATE  dbs_cursor

You will see an output like this one:

backup database master to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\master.bak' with init, skip

GO

Print 'Database backed up was master'

GO

backup database model to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\model.bak' with init, skip

GO

Print 'Database backed up was model'

GO

backup database msdb to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\msdb.bak' with init, skip

GO

Print 'Database backed up was msdb'

GO

backup database ReportServer to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\ReportServer.bak' with init, skip

GO

Print 'Database backed up was ReportServer'

GO

backup database ReportServerTempDB to disk = N'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL.1\Backup\ReportServerTempDB.bak' with init, skip

GO

Print 'Database backed up was ReportServerTempDB'

GO

To use it you just need to copy the output and paste it in a new query window.

You can run it directly using sp_executesql but the idea is you can prevent any failure reviewing the code first.

Log Shipping 'skipping' all logs and not restoring

After you configured log shipping in SQL Server, you verify and saw all the jobs are working fine: LSBackup, LSCopy and LSRestore.  But after several hours you saw a message indicating "log shipping is out of Sync". 

When you go and review the job history all of them indicate were running fine, but in the restore job you found something like this:

"Skipped Log Backup File ....."
"Could not find a log backup file that could be applied to secondary Database"

It happen for an old issue reported since version 2000 and still happen in the new SQL Server version 2008.

To force the sync you can run the following queries:

1. You need to check the current configuration in the secondary node.

--This query will show you the current restore delay per database

Select secondary_database, restore_delay from dbo.log_shipping_secondary_databases

secondary_database                   restore_delay

-------------------------------- --------------

Adventures                               360

Testing                                    360

2. After you have the current values run the following query in msdb.

use [msdb];

update

dbo.log_shipping_secondary_databases

set [restore_delay] = -2000000000

3. Start LSRestore job.

You will see the report with sync information after some minutes; please keep in mind, all depends of the size of your database if you are restoring large transaction log files it can take hours.

4. Finally return the values to the previous values with the following query:

use [msdb];

update

dbo.log_shipping_secondary_databases

set [restore_delay] = 360

Any question please contact me at: sqlexpertsrucs@gmail.com

How to know what SQL instance is using more CPU?

Suposing you have several SQL Server instances running in same cluster, on this particular case, in the same node and the application team is reporting to you peformance issues. You review the server and see the CPU usage is highter. 

First you need to identify what is the instance causing the high CPU usage. 

We have 2 ways to check what SQL instance is consuming high CPU in the server.

The Easy and fast way.

Your need to identifity the process ID for any instance running in the server for it you can do some of the following actions:

• Take a Look in each instance at SQL server log, read the error log and find the line that indicates “Server Process ID is 6404.”
• Run the following query on each sql instance:

     SELECT serverproperty('ServerName') AS Instance, serverproperty('ProcessID') 

Now follow the next steps:

1. Connect to the cluster node by RDP
2. Go to task manager
3. Go to processes tab
4. From Menu select View-> Select Columns
5. From the list of columns highlight PID (Select Process identifier)

The hard way. (Be careful with this option because can affect server performance)   

1.      Open Windows Performance Monitor
2.      Add Counters
3.      Select Process from Performance Object
4.      Select % Processor Time from the Counters
5.      Select ID Process
6.      And select sql server instances

Any question please contact me at: sqlexpertsrucs@gmail.com