SELECT*

One day, in one of my client's environment, the windows server failover cluster node status showing as  QUARANTINE, I have captured a couple cluster log events during the time. Though it has thrown these many errors in the Critical Events of the cluster. The script that is there in the first image made the trick. Start-ClusterNode -ClearQuarantine  The reasons for the QUARANTINE state is one of the NIC card or links got down.

I got a task from my manager where he asked me to sync logins from Primary to secondary. We can achieve this by using commands from dbatools  . We have one command called   Sync-DbaAvailabilityGroup with which we can sync logins from Primary replica to secondary replica. 1) Connect to the secondary replica 2) Run PowerShell ISE as administrator 3) Run the below command in PowerShell In the below script -SQLInstace should have primary replica. Get-DbaAvailabilityGroup -SqlInstance RAMESH\UAT | Sync-DbaAvailabilityGroup ` -ExcludeType LinkedServers , AgentJob , SpConfigure , CustomErrors , DatabaseMail , LinkedServers , SystemTriggers , DatabaseOwner , AgentCategory , AgentOperator , AgentAlert , AgentProxy , AgentSchedule   The above command will exclude all the objects that are included to  -ExcludeType parameter and will sync all the logins to the secondary replica. If logins and permissions already exist it will ignore and will synch those only which are not in secondary replic

We can use dbatools, which is a PowerShell tool with which we can do the migration on SQL Server instances, we can move databases from source to destination by using the below commands. As a prerequisite you need to install dbatools( https://dbatools.io/commands ).If that particular object already exists in the destination server script will skip that particular object and will push remaining objects. Start-DbaMigration -Source RAMESH -Destination RAMESH\TEST -BackupRestore -SharedPath "\\ramesh\Pradeep\"   Start-DbaMigration -Source RAMESH -Destination RAMESH\TEST -BackupRestore -NetworkShare "\\ramesh\Pradeep\"   If we notice script here there are two parameters which are compulsory -SharedPath or -NeworkShare in some environments you can found -SharedPath parameter and some other environments you can find -Networkshare Data and log files of databses would be stored in the default path of folders which would be created while we are installing SQL

/*   The below command will take the backup to disk NUL   Dont confuse with NULL, whatever we have writtne below is correct NUL: is correct   values only */ USE UserDatabaseName go BACKUP LOG UserDatabaseName TO DISK = 'NUL:' WITH NO_COMPRESSION , STATS = 5

We can use the below query to read extended information from the respective drive-in .XEL format. select     n . value ( '(@name)[1]' , 'varchar(50)' ) as event_name ,     n . value ( '(@package)[1]' , 'varchar(50)' ) AS package_name ,     n . value ( '(@timestamp)[1]' , 'datetime2' ) AS [utc_timestamp] ,     n . value ( '(data[@name="duration"]/value)[1]' , 'int' ) as duration ,     n . value ( '(data[@name="cpu_time"]/value)[1]' , 'int' ) as cpu ,     n . value ( '(data[@name="physical_reads"]/value)[1]' , 'int' ) as physical_reads ,     n . value ( '(data[@name="logical_reads"]/value)[1]' , 'int' ) as logical_reads ,     n . value ( '(data[@name="writes"]/value)[1]' , 'int' ) as writes ,     n . value ( '(data[@name="row_count"]/value)[1]' , 'int' ) as row

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.

SELECT F . name AS FolderName , P . name AS ProjectName , PKG . name AS PackageName FROM ssisdb . catalog . folders AS F INNER JOIN SSISDB . catalog . projects AS P ON P . folder_id = F . folder_id INNER JOIN SSISDB . catalog . packages AS PKG ON PKG . project_id = P . project_id ORDER BY F . name , P . name , PKG . name ;

In one of my Always on Environments, I used to get the below error daily at a particular time, and when I log in to the environment to check the status it is showing everything looks good. My always-on Environment is not multi-subnet and it is a normal environment. A connection for availability group 'ListerNameHere' from availability replica   'PrimaryReplicaNameHere' with id  [996F8E9F-1808-4606-9D69-7614F6E7CDE7]   to 'SecondaryReplicaNameHere' with id [417DDEB3-5F9A-42F2-9906-5E577AA3BA69]  has been successfully established .   This is an informational message only . No user action is required . And when i check the event viewer i used to get the below error on each database that are participating in Always on Availability groups. I am using Microsoft SQL Server 2014(SP3-CU-GDR), Microsoft Enterprise Edition. When I google this error I found very few blogs, and they are mentioned to check Network connectivity, port connectivity, the firewal

I got a scenario where my manager askeed me to find out databse names on which FULL BACKUP got failed on a particular days. Date and Times are mentioned below. Every week full backups on sunday at 10:00PM /* Finding out database name which are not happening on a particular day and time, in my scenario FULL backups are happeniNG at 10:00PM */ SELECT DISTINCT DATABASE_NAME + ', ' FROM msdb . dbo . backupset WHERE DATABASE_NAME NOT IN( SELECT DATABASE_NAME FROM msdb . dbo . backupset where year ( backup_start_date )= 2020 and month ( backup_start_date )= 5 and day ( backup_start_date )in ( 29 , 30 ) and type = 'D' AND CONVERT ( VARCHAR ( 10 ), CAST ( getdate () AS TIME ), 0 )> '10:00PM' )