SQL Server AlwaysON Database Refresh

 AlwaysON Database Refresh :

SQL Server AlwaysOn is very good functionality introduced by Microsoft as you can achieve high availability solution with actual copy of database with real time synch mode. Now as part of maintenance we might have to do Database refresh . I have tried to consolidate all points & steps included in doing such kind of activity. Hope below points will help :

Step 1 : Take a backup or Copy the backup from source to destination
Step 2 : Backup the Users in pre prod Database
Step 3 : Remove the Database from Availability Group in Pre-Prod.
Step 4 : Refresh the Database in Pre-Prod using the copied backup file from Source server
Step 5 : Drop the Users after DB restoration is completed in Pre-Prod DB.
Step 6 : Create the Users using the script from Step 2
Step 7 : Sync the Users
Step 8 : Update Stats
Step 9 : Reconfigure the Alwayson AG setup on pre-prod
Step 10 : Once configuration is completed check the AG status and make sure DB is Synchronized .

Script to Find Blocking,Headblocker,Long Running Queries,CPU Usage of queries,WaitType

Today I am going to discuss about how to find opentransactions , blockings , headblocker , runtime of a query, Long Running queries , cpu usage of a particular database or for all databases..etc.,  in a current situation

In a simple way we can call it as Activity Monitor Script which can show us current situation of SQL Instance :

I am sharing this as it helps me a lot in so many situations and Hope it will help you also .

First we need to create views in master :

USE [master]
GO

/****** Object:  View [dbo].[all_task_usage]    Script Date: 12/21/2016 6:14:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--display sessions with unfreed objects/pages
CREATE VIEW [dbo].[all_task_usage]
AS
    SELECT session_id,
SUM(user_objects_alloc_page_count) AS task_user_objects_alloc_page_count,
SUM(user_objects_dealloc_page_count) AS task_user_objects_dealloc_page_count,
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
    FROM sys.dm_db_task_space_usage
    GROUP BY session_id;

GO


USE [master]
GO

/****** Object:  View [dbo].[all_session_usage]    Script Date: 12/21/2016 6:15:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[all_session_usage]
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
,R1.user_objects_alloc_page_count
        + R2.task_user_objects_alloc_page_count AS session_user_objects_alloc_page_count,
        R1.user_objects_dealloc_page_count
        + R2.task_user_objects_dealloc_page_count AS session_user_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;

GO


USE [master]
GO

/****** Object:  View [dbo].[all_request_usage]    Script Date: 12/21/2016 6:15:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[all_request_usage]
AS
  SELECT session_id, request_id,
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count
  FROM sys.dm_db_task_space_usage
  GROUP BY session_id, request_id;

GO


USE [master]
GO

/****** Object:  View [dbo].[all_query_usage]    Script Date: 12/21/2016 6:16:06 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[all_query_usage]
AS
  SELECT R1.session_id, R1.request_id,
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;

GO


After creating above views we need to create a Stored Procedure called getactivity :

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_getactivity]    Script Date: 11/06/2014 00:31:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_getactivity](
@login varchar(130)='%',
@dbname varchar(130)='%',
@tsql varchar(130)='%',
@spidgtr int=0,
@showalltext int=-1,
@groupbysession bit=1,
@lightactive bit=0,
@default bit=1
)
as
set nocount on
set @dbname=lower(@dbname)
set @login=lower(@login)
print '--show default activity monitor and text for what is running:'
print 'exec master..sp_getactivity'
print '@dbname=''%'''
print ',@login=''%'''
print ',@default=1'
print ''
print '--show active tasks in detail:'
print 'exec master..sp_getactivity'
print ',@default=0'
print ''
print '--show default activity monitor and text for what is running in master db:'
print 'exec master..sp_getactivity'
print '@dbname=''master'''
print ',@login=''%'''
print ',@default=1'
print ''
print '--show default activity monitor and text for what is running under sa login:'
print 'exec master..sp_getactivity'
print '@dbname=''%'''
print ',@login=''sa'''
print ',@default=1'
print ''
if @default=1
begin
----------------------------------------------------------------------
--original act mon:
------------------------------------------------------------------
if object_id('tempdb.dbo.#defaultactmonitor') is not null
drop table #defaultactmonitor
SELECT
   [killsid]    = 'kill '+convert(varchar,s.session_id),
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [login]         = s.login_name, 
   [database]      = ISNULL(db_name(p.dbid), N''),
   [Task State]    = ISNULL(t.task_state, N''),
   [Command]       = ISNULL(r.command, N''),
   [Application]   = ISNULL(s.program_name, N''),
   [tsql]=convert(varchar(max),ltrim(replace(replace(replace(replace(REPLACE(REPLACE(replace(replace(replace(isnull((select text from sys.dm_exec_sql_text(p.sql_handle)),''),CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '),'*',''),'----','-'),'==',''))),
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''),
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
   [Total CPU (ms)] = s.cpu_time,
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,
   [Memory Use (KB)]  = s.memory_usage * 8192 / 1024,
   [Open Transactions] = ISNULL(r.open_transaction_count,0),
   [Login Time]    = s.login_time,
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''),
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = N'',
   [sid]    = convert(varchar,s.session_id)
into #defaultactmonitor
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads.  This will cause that thread to show up in multiple rows
-- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
where s.session_id>@spidgtr
and lower(convert(varchar(130),s.login_name)) like @login
and lower(convert(varchar(130),ISNULL(db_name(p.dbid), N''))) like @dbname
ORDER BY s.session_id desc
select * from #defaultactmonitor
where [sid]>@spidgtr
and lower(convert(varchar(130),[login])) like @login
and lower(convert(varchar(130),[database])) like @dbname
and lower(convert(varchar(130),[tsql])) like @tsql
ORDER BY [sid] desc
--lightweight:
select 'lightweight',db_name(database_id) 'dbname',session_id,command,status,wait_type,cpu_time,reads,writes,logical_reads,
sqltext.text 'batchtext'
from  sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
where req.session_id>@spidgtr and (req.session_id<>@@spid)
order by req.session_id desc
--lightweight.
end
else
--------------if @ligthactive=0:
begin
print 'Info: @login and @dbname ignored when @default=0'
--end deletesqlobjects.sql
--declare @showalltext int,@groupbysession bit
--select @retval = instid from instance where guid = @guid
--set statistics profile off
--set @groupbysession=0  -- 0=show all tasks per spid
--set @groupbysession=1  -- 1=show 1 line per spid
--set @showalltext=-1 -- -1=current txt 
--set @showalltext=0  --  0=curr,input_buffer
--set @showalltext=1  --  1=curr,input_buffer,offset,next
--set @showalltext=2  --  2=curr,offset,next,prev ,input_buffer
--set @showalltext=3  --  3=curr,offset,next,prev,fullbatch,input_buffer
--set @showalltext=4  --  4=everything
--SELECT * FROM sys.dm_os_schedulers
--select * from sysprocesses where spid=135
--select * from sys.dm_exec_requests req where session_id>50
--if @showalltext is null set @showalltext=0
--if @groupbysession is null set @groupbysession=1
--note:the max degree of parallelism is per STEP in the query, NOT per query.
--So a given query could have many more threads than the MAXDOP setting.
--MAXDOP setting is used to limit the number of threads per operation in the execution plan (e.g, scan,seek) and
--does not limit the number of threads used to execute the query.
--So it is normal to see  threads per SPID in sysprocesses greater than MAXDOP setting
if object_id('tempdb.dbo.#tempactivitymonitor') is not null
drop table #tempactivitymonitor
if object_id('tempdb.dbo.#tmpDBCCinputbuffer') is not null
drop table #tmpDBCCinputbuffer
SELECT
top 100
IDENTITY(int,1,1) 'id',
convert(varchar(max),'') inbuff,
sqltext.text 'batchtext',
qpx.query_plan 'queryplan',
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, 0,(case
when req.statement_end_offset=0
then charindex(left(req.command,charindex(' ',req.command+' ')),sqltext.text)
else (req.statement_start_offset/2) + 1 end)))
else 'encrypted' end as [prev],
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, (case
when req.statement_end_offset=0
then charindex(left(req.command,charindex(' ',req.command+' ')),sqltext.text)
else (req.statement_start_offset/2) + 1 end),
((CASE --req.statement_end_offset
  WHEN req.statement_end_offset < 1 THEN DATALENGTH(sqltext.text)
  ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1))
else 'encrypted' end as [current],
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, (CASE
  WHEN req.statement_end_offset < 1 THEN DATALENGTH(sqltext.text)
  ELSE req.statement_end_offset END/2+1)
,DATALENGTH(sqltext.text)/2+1))
else 'encrypted' end as [next],
convert(varchar(50),'DBCC OUTPUTBUFFER('+convert(varchar,req.session_id)+')') outbuffcmd,
sqltext.encrypted 'encrypted',
case when sqltext.encrypted=0 then
convert(varchar,req.statement_start_offset/2)+'-'+
convert(varchar,(case when req.statement_end_offset<1
then DATALENGTH(sqltext.text)/2 else req.statement_end_offset/2 end))+
case when req.statement_end_offset>0 --if > 0 then there is more in batch
then ' '+convert(varchar,DATALENGTH(sqltext.text)/2) else '' end  else 'encrypted' end 'offset',
'kill '+convert(varchar,req.session_id) 'sessid',
hdblkr=CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (req.blocking_session_id = 0 OR req.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
convert(varchar,req.session_id) 'spid',
req.request_id 'reqid',
req.status 'status', --s.status,
req.command 'command',
--req.cpu_time 'cpu_ms', --commented this because next one totals all from multiple processes and same session:
--(select sum(convert(bigint,p.cpu_time))/1000 from sys.dm_exec_sessions p where (req.session_id = p.session_id)) 'cpu_ss',
(select sum(convert(bigint,p.cpu))/1000 from sys.sysprocesses p where (req.session_id = p.spid)) 'cpu_ss',
task_internal_objects_alloc_page_count-task_internal_objects_dealloc_page_count 'tskipgs',
session_internal_objects_alloc_page_count-session_internal_objects_dealloc_page_count 'sesipgs',
task_user_objects_alloc_page_count-task_user_objects_dealloc_page_count 'tskupgs',
session_user_objects_alloc_page_count-session_user_objects_dealloc_page_count 'sesupgs',
(task_internal_objects_alloc_page_count+task_user_objects_alloc_page_count)-(task_internal_objects_dealloc_page_count+task_user_objects_dealloc_page_count) 'tskpgs',
(session_internal_objects_alloc_page_count+session_user_objects_alloc_page_count)-(session_internal_objects_dealloc_page_count+session_user_objects_dealloc_page_count) 'sespgs',
ru.request_internal_objects_alloc_page_count-ru.request_internal_objects_dealloc_page_count 'reqpgs',
qu.request_internal_objects_alloc_page_count-qu.request_internal_objects_dealloc_page_count 'qpgs',
req.granted_query_memory 'mpgs',
req.row_count 'rows',
(select count(*) from sys.sysprocesses p where (req.session_id = p.spid)) 'thds', --threads
  CAST(((DATEDIFF(s,req.start_time,GetDate()))/3600) as varchar) + 'h'
  + CAST((DATEDIFF(s,req.start_time,GetDate())%3600)/60 as varchar) + 'm'
  + CAST((DATEDIFF(s,req.start_time,GetDate())%60) as varchar) + 's' as run_time,
--case
--when req.total_elapsed_time<(1000*60*2) then convert(varchar,req.total_elapsed_time/1000)+'s'
--else convert(varchar,convert(decimal(10,1),req.total_elapsed_time/1000.0/60.0))+'m'
--end [elaps],
--req.total_elapsed_time/1000 'elaps_ss',-- commented this for elaps_mi cuz it's smaller value:
--convert(decimal(10,2),req.total_elapsed_time/1000.0/60.0) 'elaps_mi', Sie_018810
--next is showing more physical io count that the Wr Re below..so added:
(select sum(convert(bigint,p.physical_io)) from sys.sysprocesses p where (req.session_id = p.spid)) 'physio',
db_name(req.database_id) 'db',
case when len(s.login_name)>0 then s.login_name else s.original_login_name end as 'login',
s.host_name 'host_name',
s.writes 'Wr',
s.reads 'Re',
s.logical_reads 'logical Re',
req.prev_error 'error',
(req.wait_time/1000) 'wait_ss',
(wait_duration_ms/1000) 'wait_dur_task_ss',
wt.wait_type  'wait_type',
exec_context_id 'exec_context_id',
blocking_exec_context_id 'blocking_exec_context_id',
case
when wt.wait_type like 'PAGE%LATCH[_]%' and (wt.resource_description like '2:%:1' or wt.resource_description like '2:%:2' or wt.resource_description like '2:%:3') then 'tempdb:fileid:page contention in the allocation structures that track allocation information: 1=PFS, 2=GAM, 3=SGAM'
when wt.wait_type like 'PAGE%LATCH[_]%' and (wt.resource_description like '2:%:%') then 'tempdb:fileid:page'
when wt.wait_type like 'PAGE%' then 'exec ['+db_name(req.database_id)+']..[sp_pagetoobject] '''+case when len(resource_description)>0 then resource_description else req.wait_resource end+''''
when wt.wait_type='ASYNC_NETWORK_IO' then 'client disconnect or huge select resultset?'
when wt.wait_type='CXPACKET' then 'MAXDOP too high?'
when wt.wait_type='SOS_SCHEDULER_YIELD' then 'voluntarily yielded'
when wt.wait_type='OLEDB' or req.wait_resource like '%(SPID=%)' then 'using linked server?'
when req.wait_resource like '%:%:%' then 'exec ['+db_name(req.database_id)+']..[sp_pagetoobject] '''+replace(replace(req.wait_resource,' ',''),'PAGE:','')+''''
else ''
end [misc notes],
req.wait_resource 'wait_resource',
wt.resource_description 'resource_description',
req.last_wait_type 'last_wait_type',
case when req.blocking_session_id<>0 then 'kill '+convert(varchar,req.blocking_session_id)
else convert(varchar,req.blocking_session_id) end 'blocker',
req.executing_managed_code 'exe',
req.open_resultset_count 'opresultset',
req.open_transaction_count 'optrn',
req.percent_complete 'percComp',
  CAST(((DATEDIFF(s,req.start_time,GetDate()))/3600) as varchar) + ' hour(s), '
  + CAST((DATEDIFF(s,req.start_time,GetDate())%3600)/60 as varchar) + 'min, '
  + CAST((DATEDIFF(s,req.start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((req.estimated_completion_time/3600000) as varchar) + ' hour(s), '
  + CAST((req.estimated_completion_time %3600000)/60000 as varchar) + 'min, '
  + CAST((req.estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
convert(smalldatetime,dateadd(second,req.estimated_completion_time/1000, getdate())) as est_completion_time
,s.client_interface_name 'cli'
into #tempactivitymonitor
FROM sys.dm_exec_requests req
left join sys.dm_exec_sessions s  ON (s.session_id = req.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
left join all_task_usage tu  ON (tu.session_id = req.session_id)
left join all_session_usage su  ON (su.session_id = req.session_id)
left join all_request_usage ru  ON (ru.session_id = req.session_id and req.request_id=ru.request_id)
left join all_query_usage qu  ON (qu.session_id = req.session_id  and req.request_id=qu.request_id)
left join sys.dm_os_waiting_tasks wt on (req.session_id=wt.session_id)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) as qpx
where (req.session_id<>@@spid and req.session_id>@spidgtr)
order by db_name(req.database_id), req.session_id
-----------------------
if @groupbysession=1
delete from #tempactivitymonitor WHERE isnull(exec_context_id,0)<>0
----------
create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(max))
declare @session_id int,@reqid int
declare c cursor for select distinct spid,reqid from #tempactivitymonitor;
open c
fetch next from c into @session_id,@reqid
while @@FETCH_STATUS = 0
begin
begin try
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER('+@session_id+','+@reqid+') WITH NO_INFOMSGS')
update #tempactivitymonitor
set inbuff=(select ltrim([Event Info]) from #tmpDBCCinputbuffer)
where spid=@session_id and reqid=@reqid
end try
begin catch
-- exec usp_geterrorinfo
end catch
truncate table #tmpDBCCinputbuffer
fetch next from c into @session_id,@reqid
end
close c
deallocate c
--remove more than one space, and CRLF and tab to space:
update #tempactivitymonitor
set batchtext=ltrim(replace(replace(replace(replace(replace(replace(batchtext,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set inbuff=ltrim(replace(replace(replace(replace(replace(replace(inbuff,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set prev=ltrim(replace(replace(replace(replace(replace(replace(prev,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set [current]=ltrim(replace(replace(replace(replace(replace(replace([current],CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set next=ltrim(replace(replace(replace(replace(replace(replace(next,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
if @showalltext=-1
select
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
sessid,status,blocker,hdblkr,command,cpu_ss,sespgs,--tskipgs,sesipgs,tskupgs,sesupgs,sespgs,tskpgs,reqpgs,
qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,wait_ss,wait_type,resource_description,[misc notes],wait_resource,last_wait_type,exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,
cli,spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
------------------------------------
if @showalltext=0
select
case
when len(rtrim(prev))<1
then inbuff
else prev end 'prev/inbuff',
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
sessid,status,blocker,hdblkr,command,cpu_ss,sespgs,--tskipgs,sesipgs,tskupgs,sesupgs,sespgs,tskpgs,
--reqpgs,
qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,
cli,spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
----
----------------------
if @showalltext=1
select
case
when len(rtrim(prev))<1
then inbuff
else prev end 'prev/inbuff',
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss,--tskipgs,sesipgs,tskupgs,sesupgs
sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,
Wr,Re,[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,
exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
-----
if @showalltext=2
select
inbuff,
[prev],
case when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss--tskipgs,sesipgs,tskupgs,sesupgs
,sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,
wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,optrn,percComp,
running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
---
if @showalltext=3
select  inbuff,batchtext,
[prev],
case when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss--tskipgs,sesipgs,tskupgs,sesupgs
,sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,
[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,
optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
---
if @showalltext=4
select * from #tempactivitymonitor order by db,spid
Declare @sqlversion char(1),@cmd varchar(2000)
Select @sqlversion=convert(char(1),convert(varchar(10),@@microsoftVersion/16))
if @sqlversion<>8 and (isnull((select count(*) from #tempactivitymonitor where blocker like 'kill%'),0)>0)
begin
set @cmd='declare @date varchar(50),@i int;set @i=0
while (@i<1)
begin
set @date=convert(varchar(50),convert(varchar,cast(getdate() as smalldatetime),102)+'' ''+convert(varchar,cast(getdate() as datetime),108))
SELECT convert(varchar(100),db_name(sp.dbid))
,convert(varchar(10),sp.spid) blockerSPID
,convert(varchar(50),sp.status) blockerStatus
,convert(varchar(50),sp.cmd) blockerCmd
,convert(varchar(100),sp.[program_name]) blockerPgmnm
,convert(varchar(50),sp.loginame) blockerLogin
,convert(varchar(50),sp.hostname) blockerHost
,convert(varchar(50),sp.physical_io) physio
,convert(varchar(50),convert(varchar,cast(sp.login_time as smalldatetime),102)+'' ''+convert(varchar,cast(sp.login_time as datetime),108)) logintime
,convert(varchar(50),@date) reportdate
,convert(varchar(50),sp.lastwaittype) waittyp
,convert(varchar(50),sp.waitresource) waitres
,convert(varchar(50),convert(varchar,cast(sp.last_batch as smalldatetime),102)+'' ''+convert(varchar,cast(sp.last_batch as datetime),108)) ''lastbatch''
,convert(varchar(50),sp.memusage) ''memusage''
,convert(varchar(10),sp.open_tran) ''opentran''
,convert(varchar(1000),ltrim(replace(replace(replace(replace(REPLACE(REPLACE(replace(replace(replace(isnull((select text from sys.dm_exec_sql_text(sp.sql_handle)),''''),CHAR(10),'' ''),CHAR(13),'' ''),char(9),'' ''),'' '',''<>''),''><'',''''),''<>'','' ''),''*'',''''),''----'',''-''),''=='',''''))) text
,convert(varchar(50),serverproperty(''SERVERNAME'')) servername
FROM master..sysprocesses sp
LEFT OUTER JOIN sys.dm_exec_requests r ON (sp.spid = r.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (sp.spid = r2.blocking_session_id)
where sp.spid > 0
and r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)
--cross apply sys.dm_exec_sql_text(sp.sql_handle) sqltext
set @i=@i+1
--waitfor delay ''00:00:01''
end'
end
exec(@cmd)
end --@lightactive=0

After creating above views and stored procedure execute below script as per your requirement :

use master --dbname and login : case insensitive:

exec master..sp_getactivity 

@dbname='%%' -- Provide DB name to see what is going on for particular DB.If you leave it empty it will show for all DB's.

,@login='%%' -- provide the loginame to see what is going on with particular login.If you leave it empty it will show for all Logins.

,@default=0

,@spidgtr=50

,@showalltext=-1

,@groupbysession=1

go

Note : This script will be used like Activity Monitor and it will show only current situation of sql instance or database.

Sync logins/users between Principal/ Mirror/ Alwayon Servers

There are many ways to transfer SQL logins between the SQL Server instances and also I believe all of the transfer logins methods have some issues . 

But I used below Script which worked fine without any issues.

1. First create Stored Procedure Hexadecimal on your sql instance.

If there is a Mirroring create linked Servers between principle and Mirror Instance.
If there is Always on create linked Servers between Primary and Secondary Instances .

The script depends on sp_hexadecimal.  Please find the script below :

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[sp_hexadecimal]    Script Date: 1/22/2018 10:50:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_hexadecimal]
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue

GO


2 . Put below script in SQL Job :


SET NOCOUNT ON
declare @samesids bit,@printonly bit
set @samesids=1
set @printonly=0

CREATE TABLE #Logins
(
    loginId int IDENTITY(1, 1) NOT NULL,
    loginName nvarchar(128) NOT NULL,
    passwordHash varbinary(256) NULL,
    sid varbinary(85) NOT NULL
)
-- openquery is used so that loginproperty function runs on the remote server,
-- otherwise we get back null
INSERT INTO #Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([DESKTOP\SQL1], '--Change this to your source Instance Name
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE type = ''S''
AND name NOT IN (''sa'', ''guest'')
AND create_date >= ''12/31/1900''
ORDER BY name')
DECLARE
    @count int, @loginId int, @loginName nvarchar(128),
    @passwordHashOld varbinary(256), @passwordHashNew varbinary(256),
    @sid varbinary(85), @sql nvarchar(4000), @password varchar(514),@sidparm varchar(100),@sidh varbinary(85)
--select * from #logins
SELECT @loginId = 1, @count = COUNT(*) FROM #Logins
print '--sql login count:'+convert(varchar,@count)
WHILE @loginId <= @count
BEGIN
    SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
    FROM #Logins
    WHERE loginId = @loginId
if @samesids=1
set @sidparm=',SID = ' + master.sys.fn_varbintohexstr(@SID)
else
set @sidparm=''
EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT
-- if the account doesn't exist, then we need to create it
    IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name=@loginName)
    BEGIN
set @sql='-- login does not exist on target:'+char(13)
        SET @sql = @sql + 'CREATE LOGIN [' + @loginName + '] WITH PASSWORD = '
        SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
        SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'+@sidparm
print @sql
if @printonly=0
exec (@sql)
    END
    -- if the account does exist, then we need to drop/create to sync the password;
    -- can't alter as hashed isn't supported
    ELSE
    BEGIN
        SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))
        -- only update if the password has changed since the last sync or sid is different:
        IF (@passwordHashOld <> @passwordHashNew)
OR EXISTS (SELECT * FROM master.sys.server_principals WHERE name=@loginName
and sid<>@sid and @samesids=1)
        BEGIN
if (@passwordHashOld <> @passwordHashNew)
set @sql='-- exists on target, but password mismatch:'+char(13)
else
set @sql='-- exists on target, but sid mismatch:'+char(13)
            SET @sql = @sql+'DROP LOGIN [' + @loginName+']'
            print @sql
if @printonly=0
exec (@sql)
            SET @sql = 'CREATE LOGIN [' + @loginName + '] WITH PASSWORD = '
            SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
            SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF'+@sidparm
print @sql
if @printonly=0
exec (@sql)
        END
    END
    SET @loginId = @loginId + 1
END
DROP TABLE #Logins


Note :
"LinkedServerName ( DESKTOP\SQL1 )" in the OPENQUERY statement needs to be changed to the linked server that points to the source server in above script .

How to recover SA password on Microsoft SQL Server

When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password

1. Person who installed the SQL Server knows the password but has left the building.
2. You did not write down the password in your password file
3. Password file is lost

Steps to recover the SA password

Start SQL Server Configuration Manager

Stop the SQL services

Edit the properties of the SQL Service

Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters

Start the SQL services. These are now running in Single User Mode. 

Start CMD on tthe SQL server

Start the SQLCMD command. Now you will see following screen

Now we create a new user. Enter following commands

CREATE LOGIN newlogin WITH PASSWORD = ‘XXXXXX′(Remember SQL server has default strong password policy

Go

Now this user is created

Now we grant the user a SYSADMIN roles using the same SQLCMD window.

sp_addsrvrolemember ‘newlogin’, ‘sysadmin’

go

Stop the SQL service again

Change the SQL service properties back to the default settings

Start the SQL service again and use the new created login (newlogin in my above example)

Go via the security panel to the properties and change the password of the SA account.

Now write down the new SA password.

How to transfer logins and passwords between instances of SQL Server:

http://support.microsoft.com/kb/246133

http://support.microsoft.com/kb/918992