SELECT*

How to transfer logins and passwords between instances of SQL Server And if we want to migrate server level role permissions too then we can use below script. SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''' ' AS [-- Server Roles the Logins Need to be Added --] FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U') AND SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name <> ('sa'); SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END     + ' ' + SrvPerm.permissio...

We can use this query to check when the last log has been committed in Secondary replica in Alwasy on. SELECT AGS.NAME AS AGGroupName     ,AR.replica_server_name AS InstanceName     ,HARS.role_desc     ,DRS.synchronization_state_desc AS SyncState     ,DRS.last_hardened_time     ,DRS.last_redone_time     ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB ,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes FROM sys.dm_hadr_database_replica_states DRS LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id     AND AR.replica_id = HARS.replica_id ---One more query for  Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica: select ...

Whenever there is any active transactions or REBUILD INDEXES activities are going there is a chance of log file growth in that particular principal database where that database has been participated in Mirroring. It means whenever Mirror database falls behind Principal please do the below steps.And if the amount of ACTIVE LOG is growing abnormally we can do the below steps 1) Stop Database Mirroring 2) Take the log backup that truncates the log and restore that in Mirroring WITH NORECOVERY option. 3)And RESTART the mirroing ---How to find how many databases are in mirroring state. SELECT A. name , CASE      WHEN B.mirroring_state is NULL THEN 'Mirroring not configured'      ELSE 'Mirroring configured' END as MirroringState FROM sys.databases A INNER JOIN sys.database_mirroring B ON A.database_id=B.database_id WHERE a.database_id > 4 ORDER BY A. NAME --How to check...

1) Go to SQL Server configuration Manager and click on respective instance. In my case it is SQL Server default instance. 2)Right click on Properties go to "Start Up Parameters" and "Specify a Startup Parameter" and in that window type "-m" and click on Add button.Then that parameter will appear under "Existing Parameters" as shown below. 3) Click on "Ok" and message will pop up as restart the services here. Ensure that "SQL Server Agent is also stopeed before you are connectin to SQL Server instance. 4) After restarting the sql server service through stop and start try to connect "SQL Server Management Studio". Here please observe few points carefully a) Go to run and give value "ssms" and " SQL Server Management studio will appear like below. And follow the below instructions b) DONT CLICK ON CONNECT button now. Instead click on "CANCEL" button. After that click on "New...