Posts

Showing posts from July, 2018

VNet Peering in Azure

Image
Please find VNetPeering image.

Powershell basic cmdlets

Image

TASK,WORKERS,THREAD,SCHEDULERS,SESSION,CONNECTIONS,REQUEST

https://blogs.msdn.microsoft.com/sqlmeditation/2012/12/13/tasks-workers-threads-scheduler-sessions-connections-requests-what-does-it-all-mean/

MAXDOP recommendation

Image
This topic describes how to configure the  max degree of parallelism (MAXDOP)  server configuration option in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the  max degree of parallelism  option to limit the number of processors to use in parallel plan execution. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and key-set-driven cursor population. To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors u...

Loading SQL Server Assembly into Powershell

The below script will get the log information of sql server named instance from power shell command. clear #Loading sql server SMO Assembly [ System.Reflection.Assembly ]:: LoadWithPartialName( "Microsoft.SQLServer.SMO" ) $SQLServer = "RAMESH\DEV" $MySQL = New-Object  ( "Microsoft.SQLServer.Management.Smo.Server" ) $SQLServer $MySQL . ReadErrorLog() | Select-Object   LogDate , ProcessInfo , Text , HasErrors   -First   10 | Format-Table   -AutoSize #Getting information of multiple sql server instances. Import-Module sqlps  clear #Loading SQL Server assembly into Powershell [ System.Reflection.Assembly ]:: LoadWithPartialName( "Microsoft.SQLServer.SMO" ) #The below names are sql server named instances $servername = @( "RAMESH\DEV" , "RAMESH\TEST" ) $servername | Foreach-Object { $computername = $_ $Getinfo = New-Object ( "Microsoft.SQLServer.Management.Smo.Server" ) $comput...

File Share Witness failed in Cluster

Image
One day in one of my SQL Server cluster environments where Always on Configured. File share witness has been failed with the below error message. But when i check the SQL Server environment AG. Always on does not get affect anyway. And the databases are up and running fine.

tempdb log file growing abnormally and unable to shrink log file.

Image
In my environment i came across these scenarios where tempdb log file is growing abnormally and unable to shrink, and we can run below queries to find that out. Sometimes we can find out active transaction value under log_reusage_wait description under sys.databases view. use tempdb go select log_reuse_wait , log_reuse_wait_desc , * from sys . databases /*   To find out is there any open transactions are existing */ DBCC OPENTRAN ( 'tempdb' ) /*   To find that out through 3rd party tool */ sp_whoisactive   And sometimes we can find out negative spid', with sort operation like info, that might be due to ORDER BY clause in a SELECT query,some times even this is causing to tempdb file growth and  we are unable to shrink the log file of growth of tempdb.    https://blogs.msdn.microsoft.com/psssql/2012/09/08/revisiting-inside-tempdb/