SELECT*

DECLARE @name sysname, @ sql nvarchar( 4000 ), @maxlen1 smallint , @maxlen2 smallint , @maxlen3 smallint DECLARE @ Table TABLE (DBName VARCHAR ( 1000 ),UserName VARCHAR ( 1000 ),RoleName VARCHAR ( 1000 )) IF EXISTS ( SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%' ) DROP TABLE #tmpTable CREATE TABLE #tmpTable ( DBName sysname NOT NULL , UserName sysname NOT NULL , RoleName sysname NOT NULL ) DECLARE c1 CURSOR for SELECT name FROM master.sys.databases OPEN c1 FETCH c1 INTO @name WHILE @@FETCH_STATUS >= 0 BEGIN SELECT @ sql = 'INSERT INTO #tmpTable SELECT N''' + @name + ''', a.name, c.name FROM [' + @name + '].sys.database_principals a JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id WHERE a.name != ''dbo...

The below query get the information from listed servers and all the databases that are existing under that particular instance. cls Import-Module sqlps -DisableNameChecking $Query = "SELECT @@servername as ServerName,DBName=DB_NAME(),DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' and DP2.name='NameHere' ORDER BY DP1.name" $instanceNames = @ ( "SERVER1" , "SERVER2" , "SERVER3" , "SERVER4" ) $instanceNames | ForEach -Object{ $IndInstance = $_ #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") $Server = New-object ` -TypeName "Microsoft.SQLserver.Management.Smo.Server...

Usually we will encounter this error when we are trying to place the backup files(.BAK) in another windows server or in a network shared folder. This issue comes up because of permission issues.  operating system error 5(access is denied.) We need add below two accounts to that particular folder or drive(to where we are placing .BAK files) security path and give FULL permissions. SQL Server Database Engine Service Account SQL Server Agent Service Account Sometimes even above approach will not work, in that case run SSMS in "run as Administrator mode" and run the command.

One day in one of my Active-Passive cluster environments while patching SQL Server 2012 environment I found the below Error. Here are the observations 1)   Failover cluster Health is good, all the roles, drives and nodes are running fine. Both the nodes are running pinging one to another.      Though the Error message says cluster is disabled or needs to start, cluster service is running fine in the services.msc Below are the error screenshots. The below command did the trick in my environment. Seems to be issue with MOF files. mofcomp C:\Windows\System32\WBEM\cimwin32.mof

The below query will provide us the database data and log file growth options on the disk. select DB_NAME(mf.database_id) database_name , mf.name logical_name , CONVERT ( DECIMAL ( 20 , 2 ) , ( CONVERT ( DECIMAL , size ) /128 )) [file_size_MB] , CASE mf.is_percent_growth WHEN 1 THEN 'Yes' ELSE 'No' END AS [is_percent_growth] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT ( VARCHAR , mf.growth) + '%' WHEN 0 THEN CONVERT ( VARCHAR , mf.growth /128 ) + ' MB' END AS [growth_in_increment_of] , CASE mf.is_percent_growth WHEN 1 THEN CONVERT ( DECIMAL ( 20 , 2 ), ((( CONVERT ( DECIMAL , size ) * growth) /100 ) *8 ) /1024 ) WHEN 0 THEN CONVERT ( DECIMAL ( 20 , 2 ), ( CONVERT ( DECIMAL , growth) /128 )) END AS [next_auto_growth_size_MB] , CASE mf.max_size WHEN 0 THEN 'No growth is allowed' WHEN -1 THEN 'File will grow until the disk is full' ELSE CONVERT ( VARCHAR , mf.max_size) END AS [max_size] , phy...

 The below script extract information about Cluster resource status in  Windows Cluster cls #ClusterResource $GetClusterInfo = Get-ClusterResource $GetClusterInfo | ForEach -Object{ $GetClustInfo = $_ if ( $GetClustInfo .State -ne 'Online' ) { $GetClustInfo | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Red } else { $GetClustInfo | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Green } } ; #Cluster Netwok $GetClusterNetwork = Get-ClusterNetwork $GetClusterNetwork | ForEach -Object{ $GetClustNet = $_ if ( $GetClustNet .State -ne 'UP' ) { $GetClustNet | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor Red } else { $GetClustNet | Format-Table -AutoSize| Out-String | Write-Host -ForegroundColor DarkYellow } } ; #Cluster NetworkInterface $GetClustNetInterface = Get-ClusterNetworkInterface $GetClustNetInterface | ForEach -Object{ $GetClustNetINter = $_ if ( $GetClustNetINter .State -ne 'U...

We can use the below query to get the job step information of SQL Server Agent jobs.

cls $servers = @( "RAMESH" , "RAMESH\TEST" , "RAMESH\UAT" ) $servers | ForEach-Object { $IndServer = $_ $IndServers = New-Object ` -TypeName Microsoft.SqlServer.Management.Smo.Server ` -ArgumentList $IndServer $IndServers . Databases | ForEach-Object { $Status = $_ if ( $Status . Status -ne 'Normal' ) {   Write-Host $IndServer , ',' $Status . Name , ',' $Status . Status -ForegroundColor red } <# else { $Status } #>   } }

Database status showing as "Synchronizing" though Availablity mode is "SynchronousCommit" and  Failovermode is "Automatic". If availability mode and Failovermode is as mentioned below AvailabilityMode: "SynchronousCommit" Failover mode:  Automatic The database should be in a Synchronized mode instead of Synchronizing. But in one of my client's environment, I saw the situation where all the databases went into Synchronizing state though the availability mode is "Synchronous Commit" and Failover Mode is "Automatic". I check the below points 1) The network is good in both the replicas, both nodes are accessible from one to another But the issue that happened due to LOGFILE OF DATABASES Has grown drastically. This is also caused to the Synchronizing state. So if the issue comes up like this shrink the log file of the database in Primary replica. And if we go through the SQL Server error log we could see the error message like...

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

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