SQLGeeksPro

SQL Database Restore Stuck at 100 Percent

by Dev on

Hello there, today in this article we are going to find out how to check the reason for database restore Stuck at 100 Percent. DBAs often get the question “how much time it will take for the database to restore? ”

Generally DBAs provide a rough estimation based on the size of the database or by checking prior restore time. But while restoring, it may take more time due to different reasons. You as a DBA need to know how to find out the exact reason and let customer know.

Best way to trace it using Extended event session. There are multiple ways of creating extended event sessions. I have used “Backup_restore_progress_trace” which documents the internal steps of the restore process. You can use the below query to create the extended event.

Query to Create Restore Extended Event

CREATE EVENT SESSION [Restore Trace] ON SERVER 
ADD EVENT sqlserver.backup_restore_progress_trace(
    ACTION(sqlos.task_time,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\Restore Trace.xel') --Change the path as required
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Database Restore Stuck at 100 Percent — Fig:1

Now navigate to Extended Events > Expand Sessions > Right Click on the newly created Ex event and click on Start Session:

After this you are ready to start database restore. Once the restore is started you can come back and click on “View Target Data” as shown below. Straightaway to the right you will be able to see the default view.

Now in order to analyze the data, you need to add few columns to the view. Especially “database_Name” “operation_type” and “trace_message”.

For example refer the below screenshot to add “trace_message” to the table, highlight the row > Right Click > click “Show Column in Table”:

Now you can see the there are a number of steps in this process.

SQL Database Restore Stuck at 100 Percent — Fig: 4

The below screenshot shows 100% completion step. Important to realize here is 100% processed bytes is the same amount of data to be transferred from backup file as shown in Fig:4. Moreover you can see that VLF processing continues after that.

Now you know why restore status can stuck/take longer even though it shows 100% completion.

Please spread the word:

Tagged as: Database restore, Extended event, Restore

{ Add a Comment }

Powershell Script to Validate SQL Servers

by Dev on

This article is about a Powershell script to Check SQL Server Database Status and Services remotely. This will help in validating SQL Server after Windows patching.

To put in another way, DBAs are responsible for validating SQL Servers after server maintenance. Usually DBAs need to do a sanity check of the SQL Server after planned or unplanned maintenance.

The most common checks while doing a SQL Server validation:

Validate SQL Services are up and running
SQL Databases are online
There is no error in the error log
Authentication protocol after the restart (Kerberos Or NTLM)

Now imagine if you have a large number of servers, how much time you need to spend on such activity. The below simple yet effective powershell script will save a lot of time.

Powershell script to Check SQL Server Database Status and Services:

###########################################################################################################
#Objective: To Check SQL Service status, Database status, Errors in Errorlog and Authentication protocol  #
#Author: Tridib Dev                                                                                       #
#Source:https://sqlgeekspro.com/                                                                          #
#Parameter: SQL Instance Name                                                                             #
###########################################################################################################
param ([parameter(Mandatory = $true)] $SQLServer)
        
Write-host 'SQL Service Status for: ' $SQLServer
Write-host '---------------------------------------------'
Write-host " "
Get-WmiObject -Class Win32_Service -ComputerName $SQLServer | Where-Object {$_.Name -like "*SQL*"} | Format-List -Property Displayname, state, startName
Import-Module SQLPS -DisableNameChecking
$q1 = "select name as DatabaseName, state_desc as Status from sys.databases where state_desc <> 'online'"
$q2 = @"
CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC xp_readerrorlog 0,1, "Error"
select LogDate, ErrorMessage from #ErrorLog 
drop table #ErrorLog
"@
$q3 = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid"

$r1 = Invoke-Sqlcmd -ServerInstance $SQLServer $q1
$r2 = Invoke-Sqlcmd -ServerInstance $SQLServer $q2
$r3 = Invoke-Sqlcmd -ServerInstance $SQLServer $q3

write-host 'Database status'
Write-host '-----------------'
Write-host " "
Write-host $r1.itemArray
Write-host '-----------------'
write-host 'ErrorLog:-'
Write-host '-----------------'
Write-host " "
Write-host $r2.itemArray
Write-host '-----------------'
Write-host " "
Write-host 'Authentication Type'
Write-host '-----------------'
Write-Host $r3.itemArray

Sample Output:

Powershell to Check SQL Database Status — SQL Service Status

Note: You can run the script in PowerShell ISE or download it from ValidateSQLServerThe output will be in console. I have written this script keeping it simple and have not used advanced modules so that you don’t have to install any modules. You just need SQLPS module.
Hope this will help in saving lot of your time in validating SQL Servers after any maintenance. You may also like Powershell Script to find SQL Instance Name for a Remote Server

Please spread the word:

Tagged as: PowerShell, Validate SQL Server

{ Add a Comment }

Start SQL server using command line without local admin

by Dev on

Start SQL server using command line without local admin. As we all know you need to have local admin rights on the Server to start and stop SQL Services from Command line.

If you don’t know this easy trick, you may end up spending lot of time finding out how to get this done.

Now imagine a situation where you are a remote DBA and client is reluctant to provide you with the local admin access. Further WMI (Windows Management Instrumentation) is not working on the Server and you need to start SQL Server services in single user mode. As a result you are not able to launch SQL Server Configuration Manager.

At this point you want to convey to your customer that it is not possible to start or stop the SQL Services from Command Line without having local admin access.

The example SQL Instance is a default instance. The commands were discussed in this article.

Error if you try to Start/Stop SQL Server Services without starting cmd “Ran As Admin”

Start SQL Server using Command Line Without Local Admin — Error: Access Denied while Stopping SQL Service from Command Prompt

The client finally agreed to provide elevated permission on the Server. Now what? Is that enough to do your job. To find out you right clicked on the Command Prompt and see no option to open it with elevated permission.

The Options While You Do a right Click on cmd after having Elevated Permission

Instantly you used your favorite search engine and can’t find anything useful on this. The below simple steps will save big time. Believe me when I found it could not believe it was so easy.

Steps To Start SQL server using command line without local admin:

Type cmd in Search Box and Right Click >> Choose Open File Location

Right Click on the Command Prompt and now you get the option “Run Elevated” as shown below

The Command Prompt opens with elevated permission:

Important to realize the command prompt opens in Administrator mode as shown in the above screen shot. Same way you can open any tool like (SSMS, PowerShell etc.).

You might want to check other tips:

Interesting facts about SQL Server Startup Parameter -f

Steps in SQL Server Always On during OS Patching

Event id 133 from source report server cannot be found

Please spread the word:

Tagged as: Elevated Access, Local Admin

{ Add a Comment }

Interesting facts about Sql Server Startup Parameter -f

by Dev on

This article is about very interesting facts about SQL Server startup parameter “-f”. First let’s warm you up with the well known facts about the parameter.

The parameter -f starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.

Make sense? This is exactly what is written in Microsoft Documentation. And if you are not so experienced in SQL Server I could imagine your facial expression.

With this in mind I shall explain with example what it actually means. At the same time the text “Starting SQL Server in minimal configuration mode places SQL Server in single-user mode” is self explanatory. That means you don’t have to specify -m ( Single user Mode) explicitly when you are using -f.

Let me show you by starting SQL Server with -f parameter:

SQL Server Startup Parameter — -f puts SQL In single user mode

Not to mention the command is for a default SQL Instance. If you have a named instance then you need to use either of the below:

net start “SQL Server ( instancename )”

net start MSSQL$ instancename

As you can see I haven’t mentioned /m (Single User Mode) parameter but the error still says the SQL Instance is in Single User Mode.

Error message: Server is in single user mode. Only one administrator can connect at this time.

Now let me explain the very first sentence which says “minimal configuration”. What exactly is minimal configuration.

When SQL Server starts with -f parameter:

It remains in Single User mode as mentioned above
SQL Server does not execute the Checkpoint process which it normally does while starting SQL Services
It disables any startup Stored Procedures and Triggers

Now that you understood what is minimal configuration in this context, let me tell you when we need to use it.

When to use -f startup parameter to start SQL Sever:

You may get the following error message while connecting to SQL Instance:

Likewise you may get the below insufficient memory error while executing a query:

Interesting fact about -f Startup Parameter

Without any further delay let me show you how the parameter helps SQL Instance to start though it has insufficient memory.

For this demo I have configured the Max Server Memory to 128 MB:

Now let me stop and then start SQL Server service with -f startup parameter again:

Here I want you to pay attention to the “run_value” for ‘Max Server Memory’ which is the maximum default value though the config value is 128. This is how -f parameter allows a user to connect even though SQL has insufficient memory.
Now does the SQL Instance has that much memory? of course not, it is just configuring to the max possible value and utilizes the amount of memory it needs to login.
Hope you have learned something interesting today. Let me know if you have any questions.

Please spread the word:

Tagged as: SQL Server Startup Parameter, Startup Parameter

{ Add a Comment }

PowerShell Script to find SQL Instances on remote server

by Dev on

This article is about a PowerShell script to find sql instances on remote server. Not to mention the remote server can be standalone or Cluster.
When I got the requirement to develop the script, I thought it is not a big deal. Generally speaking, PowerShell will have some direct commands to do so and my script is few searches away.
But when I put my feet in, I got to know it was not as easy as I thought.
Especially the real challenge comes in when you have to list the SQL Instances for a SQL Cluster. The script should be intelligent enough to find out if the server is cluster or standalone and accordingly list the SQL Instances.

Note:The script should run from a server where FailOver Clustering Feature is installed.

PowerShell script to find sql instance name on remote server

Param(
    [Parameter(Mandatory=$true)]
     $ComputerName
)

if ((Get-WMIObject -Class MSCluster_ResourceGroup -ComputerName $ComputerName -Namespace root\mscluster -ErrorAction SilentlyContinue) -ne $null)
 {  
    Import-Module FailoverClusters
    get-clusterresource -Cluster $ComputerName  -ErrorAction SilentlyContinue|
    where-object {$_.ResourceType -like “SQL Server”} | 
    get-clusterparameter VirtualServerName,InstanceName | group-object ClusterObject | 
    select-object @{Name = “SQLInstance”;Expression = {[string]::join(“\”,($_.Group | select-object -expandproperty Value))}} 
    } 
else {
$SQLInstances = Invoke-Command -ComputerName $ComputerName {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
}
    foreach ($sql in $SQLInstances) {
       [PSCustomObject]@{
           ServerName = $sql.PSComputerName
           InstanceName = $sql
       }
   }  
   }

Not to mention, you can execute it from PowerShell ISE or from PowerShell Command prompt. Let’s see both examples.

Execute The Script Through Windows PowerShell ISE:

PowerShell Script to find SQL Instances on remote server Once you execute the script you’ll be prompted to provide the Server Name as you can see in the last line in above screen shot.
Sample OutPut:

Execute the Script from Windows PowerShell Command Prompt:

Only difference is to provide the server name while running the script as shown in the screen shot. Also the SQL Instance “PC01” is a cluster. Hence you don’t have to bother if the server is standalone or cluster. Hope this helps.
Let me know if you have any questions or comments.

Please spread the word:

Tagged as: PowerShell, SQL Instance

{ 2 Comments }

Script To List SQL Agent Jobs and Schedules

by Dev on

This article is to keep a handy script to list SQL Agent Jobs and Schedules. In the previous article I had provided a script to change the job schedule. This script may be very next one you need.

Once we changed the job schedules in all SQL Servers in the environment, we needed a script to verify. There are many scripts available in the net. Hence I kind of tweaked one of the script. It’s been long time and hence not able to provide the original source.

Note:Please change the Job Names in the Where clause of the query to verify the job schedule of the jobs you made changes to.

Query to get list of jobs in sql server with schedules

SELECT	 [JobName] = [jobs].[name]
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		where [jobs].[name] in ('1_DBA job Name', '11_DBA job Name') -- Change the job names or add/remove as applicable
GO

Again please change the values for Where clause. I know it is a long query hence mentioning it again.

The OutPut of the above query

List SQL Agent Jobs And Schedules
The query provides details on how the jobs are scheduled. If you want to to list the schedules of all the SQL Agent Jobs then just remove the where clause.

Please spread the word:

Tagged as: SQL Server Agent, T-SQL Script

{ Add a Comment }

Script to Change SQL Agent Job Schedule

by Dev on

This article is about a simple script to change sql agent job schedule. Now most you may be thinking why do you need a script while you can change it through SSMS.

But if you get an issue like sql server agent job is not running as per standard schedule. And the issue is on 100s of SQL Instances, you definitely prefer a query to do it on one shot.

Now let me tell you what made me develop this simple query which helped big time. Someone ran a script to create DBA maintenance job automatically.

Of course all went well until we realized two jobs were running thrice a week. The maintenance jobs should run on weekends only and hence the below query helps achieving the same.
You can use SQL Server’s Central Management Server (CMS) to execute the query in all SQL Instances at one shot.

Alter SQL Job Schedule Using Script

NOTE: Please don’t copy paste the query and run without reading the comments. You need to change few parameter values as per your requirements.

The below query is an example to change the job schedule of the SQL Agent Job “1_DBA job Name” to run Weekly Once on Saturday.

DECLARE @schid INT

SELECT @schid = sjc.schedule_id
FROM msdb..sysjobs AS sjob
INNER JOIN msdb..sysjobschedules AS sjc ON sjob.job_id = sjc.job_id
INNER JOIN msdb..sysschedules AS sch ON sjc.schedule_id = sch.schedule_id
WHERE sjob.name = '1_DBA job Name' -- Change The Job Name

EXEC msdb..sp_update_schedule @schedule_id = @schid
	,@freq_type = 8  --The value indicates when a job is to be executed. Refer Table_1
	,@freq_interval = 64 -- Refer Table_2 below and change it as per your requirement

freq_type: The value indicates frequency of a job is to be executed

Script to Change SQL Agent Job Schedule — Table_1

freq_interval: The days that a job will execute.

Please refer the Microsoft article Microsoft Documentation to understand and use different schedule options.
If you have some basic T-SQL knowledge you must be thinking this could have been achieved just by running sp_update_schedule. Well that is true if you have unique schedule name for the SQL Agent Job you are trying to change the schedule. If not this simple query will help.

The very next step would be to verify the changes you just made. Here is a script to achieve the same.>>Continue

Please spread the word:

Tagged as: SQL Server Agent, T-SQL Script

{ Add a Comment }

Event id 133 from source report server cannot be found

by Dev on

<<Previous

This is about a fix for the error “Event id 133 from source report server cannot be found”. This is in continuation of SQL Server Reporting Services Not Starting.

If you have landed here directly and you are getting the exact error in the Application event log, you are in right place. In summary I had explained the troubleshooting steps and showed the exact error message in the first article.

Further troubleshooting steps:

Once I had checked the Application Event log, next I checked the Reporting Services log. The location of the log files is your SSRS install directory. In case you have observed, the error in the application event log contains the install directory:

event id 133 from source report server cannot be found — Application Event Log Showing SSRS Install Directory

Hence you can find the SSRS logs in the following location: Considering the above example.
“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles”

Looked at the latest log file and could find the following error message:

The error indicates some issues with the SSRS Config file. Refer the first screen shot in this article to find the path of the file.

“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config”

The most compelling evidence is the following part in the error message:

“configmanager!DefaultDomain!20!03/27/2020-04:21:32:: e ERROR: Error loading configuration file: The ‘SemanticQuery’ start tag on line 220 does not match the end tag of ‘Extension’. Line 251, position 6.”

With this in mind I opened the config file in Visual Studio and searched for “SemanticQuery” and “Extension”.

Note: Please create a copy of the original RSReportServer.config file before making any changes.

Visual Studio View of RSReportServer.config file:

The arrow shows the <SemanticQuery> block. If you have observed, once I put the cursor in the </Extension> it showed the dialog box saying “Expecting end tag </SemanticQuery>.

The next step is to observe the difference of <Extension> blocks between “SemanticQuery” block. If you can see the red underlines does not have the ending ‘/’ except the one (second last red circle).

Where as the last <Extension> before the ending </SemanticQuery> has ‘/’. I removed the ‘/’ from the “Extension” block which was expecting end tag </SemanticQuery> and saved it.

Bingo! that worked and Reporting Services came online. This is how I fixed it, there may be multiple issues which you may need to fix.

If you have faced any other issues let me know by leaving your comments below.

Please spread the word:

Tagged as: SQL Server Reporting Services, SSRS

{ 2 Comments }

SQL Server Reporting Services Not Starting

by Dev on

This article is about troubleshooting SQL Server Reporting services not starting issue. It was a nice weekend until I got a call saying SSRS is not working. First thing I asked the on shift DBA if the services online and he says “No”. Of-course the DBA tried to bring it up and it failed saying “The service did not respond to the start or control request in a timely fashion‘.

Further he informed that Windows Team performed maintenance activity and as part of that they rebooted the server. After reboot all services came online except SSRS service.

Not to mention, you must have already guessed the situation. It required deeper troubleshooting and I am going to explain the steps in the following section.

The SQL Server version: SQL 2012 (SP4-GDR), Standard Edition.

Troubleshooting SQL Server Reporting Services Not Starting

First thing I tried to start the service from Services.msc to check if I get a different error. And It threw the following generic error:

The above error was of not much help but it will log the error at the top (mostly) in application event log. This is what I could find in the event log:

Log Name: Application
Source: Report Server
Date: 3/27/2020 6:40:30 AM
Event ID: 133
Task Category: (4)
Level: Error
Keywords: Classic
User: N/A
Computer: <<ServerName>>
Description:
The description for Event ID 133 from source Report Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config

The error image shows as follows:

The very first thing comes to mind after seeing the error is the SSRS service got corrupted and needs to be repaired/reinstalled. But that should always be the last resort. Continue to read to find the solution without reinstall. Next Page>>

Please spread the word:

{ Add a Comment }

OS Patching in SQL Server Always On

by Dev on

<<Previous

This article is about DBA steps during OS Patching in SQL Server Always On and in continuation of the Steps in SQL Server Always On during OS patching. Please read the first article if you have directly landed on this page.

Till now you have seen why database backup is important and how to Validate Synchronization State. Now let’s see how to change the failover mode. Why we need to change, you already know from first article.

How to change Failover Mode:

Right Click on AG Group on Primary > Properties:

Change the Failover Mode to Manual and click OK:

4. Now you can apply the OS patch on secondary Node. If you are DBA then request Intel to patch the secondary server. Do not failover the cluster resources to other node. You can reboot the secondary server after reboot.

5. After secondary server comes back online, wait until databases are in SYNCHRONIZED state and make sure Failover Readiness shows No Data Loss as described in Step 2) Validate Synchronization State, described in first article.

6. Availability Group (AG) Failover:

Fail over the Availability Group (AG) to the secondary replica using SSMS (SQL Server Management Studio) and not from Failover Cluster Manager.

Right Click on AG on Primary and Choose Failover:

Click Next:

Select the new Primary Replica and click Next:

Next step would be to connect to the Replica to failover.

7. Wait until all the database synchronization state becomes SYNCHRONIZED. Follow the instructions in Step 2) Validate Synchronization State. (Refer first article)

8. Once all the Db status become Synchronized, you can start OS patching activity on new Secondary.

9. Keep monitoring the log file growth and if it is about to get full then we may need to remove the database from Always ON AG to prevent it from going to suspect mode.

10. Once the activity completed monitor until the databases become SYNCHRONIZED. Follow the instructions in Step 2) Validate Synchronization State.

11. Failback the AG to the original node and wait until all databases become SYNCHRONIZED. This step is not mandatory and depends upon the application setup.

12. Lastly the node acting as the primary replica, change the failover mode back to Automatic. In fact you should not forget this step. Please follow step 3) Failover Mode.

Hope this helps and leave you comments or questions.

You may also like to read the following articles:

Tempdb space issue in AlwaysOn secondary Replica

SQL Database Restore using NetBackup

Please spread the word:

Tagged as: Always On, Patching

{ Add a Comment }