SELECT*

Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Windows Admin te

In one of my clients environments, I got a task where they ask me to delete volume partitions(diskmgmt.msc). For some partitions I found the option clearly sayings "Delete Volume" but for other partitions, I am unable to find it. So, in this case, I use the tools called DISKPART.EXE. I did this task in Windows Server 2016 environment. Go run command and provide the value as DISKPART.EXE and click the enter button. You now u can popup below the screen and follow the steps to delete partitions. You can also use DISKPART. EXE for other task creates volumes, partition, and disks as well. Here my task is to delete the partition which I  am unable to do it through GUI. Please find the reference screenshot.

/* This query is useful when we see a particular stored procedure or query plans showing null, Then we can run the below query to finout the execution plan of that particular query */ SELECT ( SELECT TOP 1 SUBSTRING ( sql_text . text , statement_start_offset / 2 + 1 ,          (( CASE WHEN statement_end_offset = - 1            THEN ( LEN ( CONVERT ( nvarchar ( max ), sql_text . text )) * 2 )          ELSE statement_end_offset END )   - statement_start_offset ) / 2 + 1 ))   AS text ,         cast ( txt_query_plan . query_plan as xml ) query_plan FROM sys . dm_exec_query_stats AS Query_Stats CROSS APPLY sys . dm_exec_sql_text ( sql_handle ) AS sql_text cross apply sys . dm_exec_text_query_plan ( plan_handle , statement_start_offset , statement_end_offset ) txt_query_plan

Here NODE1 is primary replica and NODE2 is secondary In the primary replica write the below commands to move the files logically. DON’T EXECUTE this step  for nowjust write it down   USE master ; GO ALTER DATABASE A MODIFY FILE ( NAME = A , FILENAME = 'C:\DataAndLog\A.mdf' ); GO ALTER DATABASE A MODIFY FILE ( NAME = A_Log , FILENAME = 'C:\DataAndLog\A_log.ldf' ); GO ALTER DATABASE B MODIFY FILE ( NAME = B , FILENAME = 'C:\DataAndLog\B.mdf' ); GO ALTER DATABASE B MODIFY FILE ( NAME = B_Log , FILENAME = 'C:\DataAndLog\B_log.ldf' ); GO ALTER DATABASE C MODIFY FILE ( NAME = C , FILENAME = 'C:\DataAndLog\C.mdf' ); GO ALTER DATABASE C MODIFY FILE ( NAME = C_Log , FILENAME = 'C:\DataAndLog\C_log.ldf' ); GO ALTER DATABASE D MODIFY FILE ( NAME = D , FILENAME = 'C:\DataAndLog\D.mdf' ); GO ALTER DATABASE D MODIFY FILE ( NAME = D_

Recently my boss raised a question to me and asking for setup an Always-on Between SQL Server 2016 Enterprise Edition and SQL Server 2016 Standard Edition.  SQL Server 2016 Enterprise edition will act as Primary replica and SQL Server 2016 Standard Edition will act as Secondary Replica. When I tried to implement this scenario in my test environment it does not work and While I am trying to add databases to the availability group I got the below errors.  Also tried reverse scenario, SQL Server 2016 Standard Edition will act as Primary replica and SQL Server 2016 Enterprise edition will act as Secondary Replica even this got failed. So we cont setup Always on between two different Editions.

The below PowerShell query will tell you about the which cluster role belongs to which SQL Server availability group. If you have multiple SQL server named instances and you have multiple availability groups. #if the all the roles starting with same name text Get-ClusterResource -Name *ServerName* | Where-Object {( $_ . ResourceType -notin ( 'IP Address' , 'Network Name' ))} #if the all the roles are different names with one other you need to pass the individual name Get-ClusterResource -Name ServerName | Where-Object {( $_ . ResourceType -notin ( 'IP Address' , 'Network Name' ))}

Source from : https://gallery.technet.microsoft.com/scriptcenter/f1df9f50-9cd9-4c75-a8d9-e2faba6b8574 -- Transact-SQL script to analyse the database size growth using backup history.   DECLARE   @ endDate   datetime ,  @ months   smallint ;  SET   @ endDate  =  GetDate ();   -- Include in the statistic all backups from today   SET   @ months  =  6 ;            -- back to the last 6 months.     ; WITH   HIST   AS      ( SELECT   BS . database_name   AS   DatabaseName             , YEAR ( BS . backup_start_date ) *  100              +  MONTH ( BS . backup_start_date )  AS   YearMonth             , CONVERT ( numeric ( 10 ,  1 ),  MIN ( BF . file_size  /  1048576.0 ))  AS   MinSizeMB             , CONVERT ( numeric ( 10 ,  1 ),  MAX ( BF . file_size  /  1048576.0 ))  AS   MaxSizeMB             , CONVERT ( numeric ( 10 ,  1 ),  AVG ( BF . file_size  /  1048576.0 ))  AS   AvgSizeMB        FROM   msdb . dbo . backupset   as   BS             INNER   JOIN             msdb . dbo . backupfile

We can search error log with the below mentioned extended stored procedure the main thing we need to remember here is ""( double quotes ) and the date format should be YYYY-MM-DD The below powershell query will fetch the SQL Server error log information. It will read all error log files. cls Get-ChildItem -Path "D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log" -File -Name -Include ERRORLOG*| ForEach -Object { $IndFile = $_ $filters = @ ( "offline" , "Database1" , "Database2" , "Database3" ) $inputFile = "D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\$IndFile" Get-Content $inputFile | Select-String -pattern $filters -AllMatches }