Roles to be added while moving a Report Server Database

Penning down this today as I recently saw my friends struggling to connect with Reporting server after migrating the report server database.

Reporting Services uses a predefined DB role called RSExecRole to grant report server permissions to the report server DB. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.

After you create the roles, you can move the report server database to new SQL Server instance.

T-SQL for adding the roles explicitly within the new instance:

use [master]
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
use [msdb]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]

Happy learning!

Aman Kharbanda


The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.

Quite often we see such errors as these while moving the system DB’s across drives or the restore failing for system DB (Model). SQL Server won’t be starting for you at this point in time which could be frustrating. Lifeline for us? The errorlog to indicate what actually is happening at the backend, and this is where you shall be reading the error as subjected.

The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run.
2016-11-26 06:19:37.23 spid12s     Error: 927, Severity: 14, State: 2.
2016-11-26 06:19:37.23 spid12s     Database ‘model’ cannot be opened. It is in the middle of a restore. 

First things first, start SQL Server in a minimally configured mode-
“sqlservr.exe -sInstanceName /T3608 -m” and once service is started, establish a connection with SQL Server via SQLCMD – “sqlcmd -E -S servername/instancename,port”

“1>” means we are connected to SQL Instance and then execute the below command (hit enter at end of each line)

1>Restore database Model with recovery
RESTORE DATABASE successfully processed 0 pages in 0.0410 seconds (0.000MB/Sec)

Kabooom! You’re done. Startup your SQL service and proceed with your tasks.

Happy learning!

Aman Kharbanda

SQL2008R2 Cluster Installation Failure on WIN Server2012R2

Migrating a SQL2008R2 DB from a WIN2008R2 Server to a WIN2012R2 server can cause a few problems especially while rebuilding the cluster setup.
DB Cluster installation can throw away anything from a validation failure to a setup installation failure message.

Even if the Server/OS teams have shown you a green flag on the cluster health check/validation level, you may very likely end up scratching your heads figuring out these installation related errors.

When I attempt to run the SQL 2008 R2 installer to create a new Failover Cluster, the Setup Support Rules check gives me errors about the cluster, that the Cluster Service and Cluster Storage aren’t available.

It turns out that SQL 2008 R2 relies on some Windows cluster functionality that is not installed by default in Windows 2012 R2, specifically the Failover Cluster Automation Server.

You can open a PowerShell prompt in Administrator mode and execute the following:

Get-WindowsFeature RSAT-Cluster*

You’ll see that the Failover Cluster Automation Server is not checked off. To install it, execute the following:

Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

If you quit your SQL Installer and attempt to re-run it to create a Failover Cluster, you’ll now see that the setup support rules around the cluster pass.

Hope it helps you.

Happy Learning!

Aman Kharbanda

Backup/Restore IO Size

By default, SQL Server will use jumbo sized IOs (typically of 1MB) to perform backup and restore operations. These can adversely affect the IO latency for your array. Whenever possible, use MAXTRANSFERSIZE=65536 on your BACKUP or RESTORE statements. This could make your backups and restore take a tad longer, so test accordingly. If you are leveraging Transaction Log Shipping, you might have no way to tune this, as sqllogship.exe doesn’t offer a means to tune this parameter. If log shipping backups/restores are affecting your performance, considering rolling out scripts to perform log shipping manually.

If you are not achieving good throughput from your backup/restore operations after using the MAXTRANSFERSIZE=65536 parameter, considering bumping up the number of buffers available to the process by using the BUFFERCOUNT parameter. In our testing, a 1.5TB database backed up in 24 minutes, 38 seconds at default settings, and 25 minutes, 6 seconds with 64Kb IO block size and BUFFERCOUNT=2048.

Happy Learning.
Aman Kharbanda




Fixing SQL Server Log Shipping after new DB file has been added to Primary

If you are maintaining a standby copy of your PRD DB enabled with log shipping mechanism, and it breaks after a data file was added to your Primary database, refer to the below to overcome this kind of situation.

You might see restore errors like these on your secondary DB server-

Error: Could not apply log backup file ‘\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’ to secondary database ‘DBName’.(Microsoft.SqlServer.Management.LogShipping) ***  2015-11-12 13:30:11.98 *** Error: Directory lookup for the file “E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf” failed with the operating system error 3(The system cannot find the path specified.).  File ‘DBName_Data3’ cannot be restored to ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’. Use WITH MOVE to identify a valid location for the file.  Problems were identified while planning for the RESTORE statement. Previous messages provide details.  RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Check which transaction log has been last applied on Secondary DB (Via T-SQL or Log Shipping Status Report).
You may use this t-sql to get the status:

SELECT [rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
Now to validate which secondary file was lately added to your DB, use the below T-SQL:
RESTORE FILELISTONLY FROM DISK=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’

And the final step would be to restore the transaction log with norecovery and move option:
RESTORE log DBName FROM Disk=’\\File server Name\MSSQL\PRD\DBName_20151111199999.trn’
with norecovery,
MOVE ‘DBName_Data3’
TO ‘E:\Data\MSSQL10_50.PRD\MSSQL\DATA\DBName_2.ndf’

Once DB is restored, you should be able to restart your log shipping restore automated jobs.

Happy learning!

Aman Kharbanda

Avoid a reboot on your server while installing multiple updates or patches

Ever wondered how cool would it be to avoid server reboot prompts should you be installing multiple updates or deploying patches on your Windows Servers? I’m sure many of you must have encountered this error sometime before-

“This error indicates that a restart from a previous install or update is pending and the system must be restarted before setup can continue”

Here’s one trick up your sleeve which you may use to avoid such errors-HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

If PendingFileRenameOperations registry key has any value then we got to clean it, in order to overcome this issue.

To delete the PendingFileRenameOperations registry key value-

  1. Open a registry editor, such as Regedit.exe or Regedt32.exe.
  2. Navigate to HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\
  3. In the right navigation pane, right-click the PendingFileRenameOperations key and select Delete.
  4. Close Registry Editor.

Happy learning!!

Aman Kharbanda

Query processor ran out of internal resources and could not produce a query plan

If you happen to see the below error in your SQL Server log file, and not sure what’s it related to and why is it logged, here’s what you should be doing-

Error: 8623, Severity: 16, State: 1   The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.

If you look closely into the error, it states that the query processor ran out of internal resources and could not produce a query plan. When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever.

There are trace flags available which are meant to be used on a session level to avoid such issues.
But if such issue happens regularly, you should approach session owner, and advise them to fine tune the queries or use Database Tuning Advisor, and apply the recommendations.

Happy Learning!

Aman Kharbanda