Interesting facts about Sql Server Startup Parameter -f

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:

SQL Server Startup Parameter
Error: Insufficient Memory or Exhausted Maximum Allowed Connections

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

SQL Server Startup Parameter
Error: Insufficient Memory while executing 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:

SQL Server Startup Parameter
Max Server Memory

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

SQL Server Startup Parameter
Showing Run Value

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/sql-server-startup-parameter/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

PowerShell Script to find SQL Instances on remote server

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 serverOnce 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:PowerShell Script to find SQL Instances on remote server

Execute the Script from Windows PowerShell Command Prompt:PowerShell Script to find SQL Instances on remote server

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/powershell-script-find-sql-instances-remote-server/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

Script To List SQL Agent Jobs and Schedules

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/list-sql-agent-jobs-schedules/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Script to Change SQL Agent Job Schedule

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.

Script to Change SQL Agent Job Schedule
Table_2

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/script-change-agent-job-schedule/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Event id 133 from source report server cannot be found

<<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:

Event id 133 from source report server cannot be found
SSRS Error log

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:
Event id 133 from source report server cannot be found
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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/event-id-133-from-source-report-server-cannot-be-found/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

SQL Server Reporting Services Not Starting

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 intimely 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:

Reporting Services Not Starting
Reporting Services 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:
Reporting Services Not Starting
Reporting Services Error in Event Log

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/reporting-services-not-starting/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

OS Patching in SQL Server Always 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 Failover Mode
AG Dashboard Properties

Change the Failover Mode to Manual and click OK:

Change Failover Mode
Change Failover Mode

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:

AlwaysOn Failover
AlwaysOn Failover

Click Next:

AlwaysOn Failover
AlwaysOn Failover

Select the new Primary Replica and click Next:

AlwaysOn Failover
AlwaysOn Failover

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:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/ag_os_patching_part2/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Steps in SQL Server Always On during OS patching

This article is about detailed Steps in SQL Server Always On during OS patching. Any minor mistakes for Always On Availability Group (AG) failover during OS patching can cause huge downtime. And this is because the database can go to recovery pending state and application will not be able to connect.
Therefore it is very critical to follow proper steps. In addition to this, the article will help non DBAs or accidental DBAs as well.
One of the main idea behind implementing SQL Server Always On is to minimize downtime during any kind of patching activity.

Steps to Follow in SQL Server Always On During OS Patching
  1. Backup Confirmation:
    Please make sure we have good full/diff backups of all the databases. This is to avoid any unwanted situation. Like in worst case you may need to restore from database backup and re-establish Always On.
  2. Validate Synchronization State: Make sure that synchronization state for all databases is SYNCHRONIZED and Failover Readiness shows No Data Loss in Availability Group (AG) Dashboard.
  • Right Click on AG from Primary Server and go to Dashboard
Open Always On Dashboard
Open Always On Dashboard
  • You need to check Synchronization State is Synchronized, and Failover Readiness is No Data Loss.
    Also make sure there is no error shows in the AG Dashboard as shown below:
AlwaysOn Dashboard: Synchronization status
AlwaysOn Dashboard: Synchronization status

The above step is for you to validate that you do not loose any data during failover of Always On AG. Other synchronization State can be “Synchronizing” and Failover Readiness is “Data Loss”. In that case you got to do some work before failover.

3. Change Failover Mode:

Change the failover mode of AG group to manual. This step is to make sure to have control during Node reboot after patching. Obviously you don’t want the AG to failover automatically during the activity.

Continue Reading>>

 

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/patching-ag/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Tempdb growth due to Version Store in AlwaysOn

<<Previous

This article about fixing tempdb growth due to Version Store in Always On Secondary replica. This is in continuation of  the article Tempdb space issue in AlwaysOn Secondary Replica. Hence please go through the first article and then comeback here so that it make more sense to you.

To summarize till now we have found that version store is consuming most of the space in tempdb. Hence we tried to find out the transaction consuming version store and detected an old system session id having lastwaittype as “REDO_THREAD_PENDING_WORK”.

As I mentioned in the first article this is a hint of some transaction in primary causing this. Furthermore you can check the below performance counters:

  • Longest transaction that’s currently running: SQLServer:Transactions\Longest Transaction Running Time.
  • The current size of all the version stores: SQLServer:Transactions\Version Store Size (KB) .
  • Rate of version store cleanup in tempdb: SQLServer:Transactions\Version Cleanup rate (KB/s).
  • Rate of version store generation: SQLServer:Transactions\Version Generation rate (KB/s).

Conversely you can use the below query in secondary replica facing the issue.

Query to check the above mentioned performance counters
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ( 'Longest Transaction Running Time' ,'Version Store Size (KB)' ,'Version Cleanup rate (KB/s)' ,'Version Generation rate (KB/s)' )
Sample output:
Result set of Longest Running Transaction
Fig1: Longest Running Transaction time

As you can see the “Longest Transaction Running Time” counter has a very high Cntr_Value. This is another key point and compelling evidence that there is long running transaction in primary replica.
Additionally the other three counters related to Version store shows that the the long running transaction is consuming the version store.

Now execute the below query again. This time in Primary Replica.

Query to find the transaction utilizing version store:
SELECT GETDATE() AS runtime
	,a.*
	,b.kpid
	,b.blocked
	,b.lastwaittype
	,b.waitresource
	,db_name(b.dbid) AS database_name
	,b.cpu
	,b.physical_io
	,b.memusage
	,b.login_time
	,b.last_batch
	,b.open_tran
	,b.STATUS
	,b.hostname
	,b.program_name
	,b.cmd
	,b.loginame
	,request_id
FROM sys.dm_tran_active_snapshot_database_transactions a
INNER JOIN sys.sysprocesses b ON a.session_id = b.spid
Sample Output:
Transactions In Primary Using Version Store In Secondary Replica
Fig2: Transactions In Primary Using Version Store In Secondary Replica

In the above sample output the most important columns are marked in red. If you have noticed, the “login_time and “last_batch” time is very old. Additionally the status is “sleeping”. Now you need to kill the corresponding session ids one by one and monitor the version store utilization as shown in the first article.

Points often overlooked is to kill one session at a time and then monitor the version store utilization in secondary replica. The reason behind this is, next time the version clean up process runs it will be able to clear the version store if you have killed the right one. This way you will be able to find the culprit transaction as well.
Also not to mention that you may need to contact the application team before killing any spid depending upon your environment.

Hope this helps. If you find any new scenario please put that in the comment section.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/tempdb-growth-due-to-version-store-in-alwayson/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Tempdb space issue in AlwaysOn Secondary Replica

In this article I am going to discuss about how to troubleshoot and fix the tempdb space issue in AlwaysOn Secondary Replica.
Recently I had faced an issue when tempdb in AlwaysOn secondary replica had grown to the disk space limit. It was SQL Server 2014 Enterprise Edition.
As you all know tempdb is used by server operations in a SQL Instance to store the following objects:
user objects, internal objects, and the version store, used primarily for snapshot isolation. Therefore we start the troubleshooting just like we do in case of regular tempdb space issue.

First of all we need to check what is consuming space in tempdb. We need to query sys.dm_db_file_space_usage which provides the details.

Query to find the tempdb space usage:
SELECT CAST(SUM((total_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS Total_Size_MB
	,--Works on SQL 2012 and future editions
	CAST(SUM((unallocated_extent_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS Free_Space_MB
	,CAST(SUM((user_object_reserved_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS User_Objects_MB
	,CAST(SUM((internal_object_reserved_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS Internal_Objects_MB
	,CAST(SUM((version_store_reserved_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS Version_Store_MB
	,CAST(SUM((mixed_extent_page_count) * 8) / 1024. AS DECIMAL(25, 2)) AS Mixed_Extent_MB
FROM tempdb.sys.dm_db_file_space_usage
GO
The output was as follows:
Tempdb Space Issue on AlwaysOn Secondary Replica
Objects consuming space in tempdb

As you can see Version Store is the highest consumer. The next step is to identify the transactions on Secondary replica maintaining an active version store. Execute the below query in secondary replica.

Query to find the transaction utilizing version store:
SELECT GETDATE() AS runtime
	,a.*
	,b.kpid
	,b.blocked
	,b.lastwaittype
	,b.waitresource
	,db_name(b.dbid) AS database_name
	,b.cpu
	,b.physical_io
	,b.memusage
	,b.login_time
	,b.last_batch
	,b.open_tran
	,b.STATUS
	,b.hostname
	,b.program_name
	,b.cmd
	,b.loginame
	,request_id
FROM sys.dm_tran_active_snapshot_database_transactions a
INNER JOIN sys.sysprocesses b ON a.session_id = b.spid
Sample Output:
Tempdb space issue on Always On Secondary Replica
Result showing transactions utilizing version store

Most important columns in the above result set to notice are “session_id “, “lastwaittype”, “database_name”, “login_time” and “last_batch”.

In addition you need to remember that we ran the query in Secondary Replica. Therefore you can see lastwaitype as “REDO_THREAD_PENDING_WORK”. Which means the redo thread was trying to catch up with the primary and was maintaining multiple versions of the rows updated to achieve read consistency.

In addition to that you can see “login_time” and “last_batch” of the session_id 37 was the oldest among all. If it is recent we can ignore but in my case it was two months back. Hence I could sense that there must my some transaction in Primary causing this.  Besides that the session_id 37 is < 50 which means it is a system session. Now let’s see how to find the culprit in primary. >>Continue Reading

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/tempdb_alwayson/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }