T-SQL Script to Free Disk Space in SQL Server

To achieve our requirement we have to execute  two stored procedures given below :

1. StoredProcedure [dbo].[Srisp_configure]

2. CREATE procedure [dbo].[usp_freedrivespace]

Find the stored procedures script below :

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Srisp_configure]    Script Date: 6/29/2018 10:42:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[Srisp_configure]
@begin bit=0
AS
set nocount on
if (CONVERT(char(1), SERVERPROPERTY('ProductVersion'))='8')
return
set @begin=isnull(@begin,1)
print '/*'
if @begin=1
begin
if object_id('tempdb.dbo.##sp_configure') is not null
drop table ##sp_configure
select convert(int,value) 'value', config, comment
into ##sp_configure
from master..sysconfigures
end
declare @oleap varchar(50),@xpcs varchar(50),@adhocdq varchar(50),@showadv varchar(50)
print convert(varchar,@@servername)+', '+convert(varchar(40),@@version)
set @showadv=isnull((select value from ##sp_configure where lower(comment) like '%show advanced options%'),1)
set @oleap=isnull((select value from ##sp_configure where lower(comment) like '%ole automation procedures%'),1)
set @xpcs=isnull((select value from ##sp_configure where lower(comment) like '%command%shell%'),1)
set @adhocdq=isnull((select value from ##sp_configure where lower(comment) like '%ad hoc distributed queries%'),1)
if @begin=1
begin
if @showadv=0 
begin
EXEC sp_configure 'show advanced options', 1
RECONFIGURE with override
end
if @oleap=0 EXEC sp_configure 'Ole Automation Procedures', 1
if @xpcs=0 EXEC sp_configure 'xp_cmdshell', 1
if @adhocdq=0 EXEC sp_configure 'Ad Hoc Distributed Queries', 1
end
else
begin
if @oleap=0 EXEC sp_configure 'Ole Automation Procedures', 0
if @xpcs=0 EXEC sp_configure 'xp_cmdshell', 0
if @adhocdq=0 EXEC sp_configure 'Ad Hoc Distributed Queries', 0
end
RECONFIGURE with override
print '*/'

GO


Use master
GO

CREATE procedure [dbo].[usp_freedrivespace]
(@filedrive varchar(5)='f:\'  --e.g. 'c:\' or 'd:\' or 'e:\'  etc...  '%:\' for all
,@filefreespacemin int =1000 --any db files with > @filefreespacemin freespace(MB)(e.g.,1000 = 1GB)
,@filefreespacemax int=1000000000 -- and < @filefreespacemax freespace(MB)(e.g., 15000 = 15GB)
,@freespaceshrinkpct float=.90 --% to shrink freespace of each file(to .2 and run a few times)
,@bakdaysOld int=30  --delete @bakdaysOld .bak files on @filedrive (>999 to skip delete step)
,@filestodelete varchar(10)='*.bak'--
,@dbfiletype varchar(10)='%' --to 'data' or 'log'  or '%'.   '%'="shrink both log and data files"
,@dbnamelikethis varchar(100)='%' --used in 'like' "where clause" for dbnames to shrink
,@dbnamenotlikethis varchar(100)=' ' --used in 'not like' "where clause" for dbnames to shrink
,@printonly bit=0  --only print commands..don't execute them
)
as
declare @ssql nvarchar(4000),@reuse_wait varchar(100),@masterdbs varchar(100)
----------------- parms you set end.
-- shrinks all db files on a drive by a percentage of internal db freespace
-- change parms below where appropriate.
-- also deletes old files from drive (if @bakdaysOld set to < 999).
-- select * from  sys.databases
-- Run as many times as needed.  Does 2 things to free space:
-- 1. Delete all @filestodelete older than @bakdaysOld days on drive @filedrive (if @bakdaysOld set to < 999)
-- 2. On drive @filedrive, shrink every db file that has freespace between @filefreespacemin and @filefreespacemax by: @freespaceshrinkpct
if object_id('tempdb.dbo.#tabledir') is not null
drop table #tabledir
if object_id('tempdb.dbo.#spacetbl') is not null
drop table #spacetbl
if object_id('tempdb.dbo.##alldbfiles') is not null
drop table ##alldbfiles
if object_id('tempdb.dbo.##alldbfilesbefaft') is not null
drop table ##alldbfilesbefaft
if object_id('tempdb.dbo.##TMPFIXEDdriveS_shrink') is not null
DROP TABLE ##TMPFIXEDdriveS_shrink
CREATE TABLE ##TMPFIXEDdriveS_shrink (
  drive  CHAR(1),
  mbfree BIGINT)
INSERT INTO ##TMPFIXEDdriveS_shrink EXEC [master]..xp_fixeddrives
--select * from ##TMPFIXEDdriveS_shrink
create table #tabledir (ID INT IDENTITY(1,1) PRIMARY KEY, x varchar(300) null)
create table #spacetbl (ID INT IDENTITY(1,1) PRIMARY KEY, drivename varchar(10),bytesfree varchar(50),befaft varchar(25) null)
create table ##alldbfilesbefaft (dbname varchar(256),filename varchar(256),filesizemb bigint,freespacemb bigint,befaft varchar(25) null,log_reuse_wait_desc varchar(50) null)
create table ##alldbfiles (dbname varchar(256),name varchar(256),filename varchar(256),sizemb bigint,fileid int, freespacemb bigint, pctfree float,usage varchar(10))
set @filedrive=upper(@filedrive)
if (@freespaceshrinkpct>.8) set @freespaceshrinkpct=.8
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1)<>'8'
begin
set @masterdbs='sys.databases sdb'
set @reuse_wait='sdb.log_reuse_wait_desc'
end
else
begin
set @masterdbs='master..sysdatabases sdb'
set @reuse_wait='''n/a'''
end
declare @msg varchar(4000)
set @msg='/*Warning: Shrinking DB files is only meant as a !Temporary! relief of drive space pressure.
The need to free up drive space in the first place is telling you to add more drive space to SQL Server:

https://connections.cat.com/wikis/home?lang=en_US#/wiki/SQL%20Server%20Support%20WIKI/page/Request%20space%20for%20disk

If you absolutely have no choice and have to run a data file shrink operation, be aware that you
are going to cause index fragmentation and you should take steps to remove it afterwards if it is going to
cause performance problems. The only way to remove index fragmentation without causing data file growth again
is to use DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE. These commands only require a single 8KB page of
extra space, instead of needing to build a whole new index in the case of an index rebuild operation.

Bottom line - try to avoid running data file shrink at all costs!*/'
select @msg 'Warning'
print @msg
if @filedrive<>'%:\' --and exists (select top 1 value from dbo.sysconfigures where (config=16390 and value<>0))
begin
exec catsp_configure @begin=1
set @ssql='dir /d '+@filedrive
insert #tabledir exec master..xp_cmdshell @ssql
insert into #spacetbl
select @filedrive,convert(varchar,Right(x,len(x)-charindex(')',x))),'Before'  from
#tabledir where upper(x) like upper('%bytes free%')
truncate table #tabledir
if charindex('.bak',@filestodelete)=0 and charindex('.zip',@filestodelete)=0
and charindex('.ldf',@filestodelete)=0 and charindex('.mdf',@filestodelete)=0
and charindex('.ndf',@filestodelete)=0
set @filestodelete='*.bak'
set @ssql='forfiles /p '+@filedrive+' /s /m '+@filestodelete+' /d -'+convert(varchar(3),@bakdaysOld)+' /c "cmd /c echo @path ; size:@fsize ; date:@fdate"'
-- make sure to fully qualify cmdshell...  without master.. it gets forcibly close msg:
insert #tabledir exec master..xp_cmdshell @ssql
if not exists (select * from #tabledir where upper(x) like '%NO FILES FOUND%') and @bakdaysOld < 999
begin
select x 'Deleting these files' from #tabledir where upper(x) like upper('%'+@filedrive+'%'+replace(@filestodelete,'*','%'))+'%'
set @ssql='forfiles /p '+@filedrive+' /s /m '+@filestodelete+' /d -'+convert(varchar(3),@bakdaysOld)+' /c "cmd /c del /q @path"'
if @printonly=1
print @ssql
else
begin
insert #tabledir exec master..xp_cmdshell @ssql
end
end
exec catsp_configure @begin=0
truncate table #tabledir
end
else
begin
--select 'master..xp_cmdshell not allowed.  skipping show freespace step...' 'cmdshell'
insert into #spacetbl
select drive,convert(varchar,mbfree)+' MB free','Before' from
##TMPFIXEDdriveS_shrink where upper(drive) like left(@filedrive,1)
-- select @filedrive,convert(varchar,mbfree)+' MB free','Before' from
-- ##TMPFIXEDdriveS_shrink where left(@filedrive,1)=upper(drive)
end

set @ssql= 'if ''?'' not in (''master'') begin
        use [?];
        insert into ##alldbfiles
        select ''?'',name,filename,size/128,fileid,(size/128.0 - (CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0)),
((size/128.0 - (CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0))/(size/128.0)),
''usage'' = convert(varchar(25)
,(case status & 0x40
when 0x40 then ''log''
else ''data'' end))
 from [?]..sysfiles where upper(filename) like ('''+upper(@filedrive)+'%'')
--print convert(varchar,@@rowcount)+'' - ''+''?''
end;'
--print @ssql
exec sp_MSforeachdb @ssql
--select * from ##alldbfiles
declare @tsql nvarchar(4000); set @tsql = ''
declare @dbname varchar(256),@iLogFile int,@size bigint,@freespace bigint,@name varchar(133),@filename varchar(300),@pctfree float
declare alldbfiles cursor for
select top 30 dbname,name,filename,sizemb,fileid,freespacemb,pctfree
from ##alldbfiles where freespacemb between @filefreespacemin and @filefreespacemax
and usage like @dbfiletype and lower(dbname) like lower(@dbnamelikethis) order by pctfree desc
--select @@rowcount 'rows'
open alldbfiles
        fetch next from alldbfiles into @dbname,@name,@filename,@size,@iLogFile,@freespace,@pctfree
        while @@fetch_status = 0
        begin
-- select @dbname,@filename,(@freespace*1.0),(@freespace*@freespaceshrinkpct),@filefreespacemin,@filefreespacemax
if ((@freespace*1.0)>(@freespace*@freespaceshrinkpct)) and ((@freespace*1.0) between @filefreespacemin and @filefreespacemax) and lower(@dbname) not like lower(@dbnamenotlikethis)
begin
-- select @dbname,@filename,(@freespace*1.0),(@freespace*@freespaceshrinkpct),@filefreespacemin,@filefreespacemax
set @tsql='DBCC SHRINKFILE('+cast(@iLogFile as varchar(5))+', '+cast(cast(@size-(@freespace*@freespaceshrinkpct) as decimal(18,0)) as varchar)+') WITH NO_INFOMSGS;'
print char(13)+'-- '+@dbname+' file: '+@filename+', '+convert(varchar,@pctfree*100)+'% free,'+char(13)+'-- current size:'+convert(varchar,@size)+'MB, free:'+convert(varchar,@freespace)+'MB. shrinking freespace by ~'+convert(varchar,(@freespace*@freespaceshrinkpct))+'MB'+char(13)+'use ['+@dbname+']'
RAISERROR(@tsql, 0, 1) WITH NOWAIT
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1)<>'8'
set @tsql='begin try'+char(13)+@tsql+'end try'+char(13)+'begin catch'+char(13)+'exec master..usp_geterrorinfo @appinfo='''+'use ['+@dbname+'];'+@tsql+''';end catch;'
set @tsql='use ['+@dbname+']
insert into ##alldbfilesbefaft
SELECT '''+@dbname+''',sf.name,size/128,(size/128.0 - CAST(FILEPROPERTY(sf.name, ''SpaceUsed'') AS int)/128.0) AS AvailableSpaceInMB, ''Before'' befaft, '+@reuse_wait+'
FROM sysfiles sf left join '+@masterdbs+' on sdb.name='''+@dbname+''' where (has_dbaccess(sdb.name)=1) and upper(sf.filename) like ''%''+upper(left('''+@filename+''',len('''+@filename+''')))+''%'';'+@tsql+'
insert into ##alldbfilesbefaft
SELECT '' '','' '' ,size/128,(size/128.0 - CAST(FILEPROPERTY(sf.name, ''SpaceUsed'') AS int)/128.0) AS AvailableSpaceInMB, ''After'' befaft, '+@reuse_wait+'
FROM sysfiles sf left join '+@masterdbs+' on sdb.name='''+@dbname+''' where (has_dbaccess(sdb.name)=1) and upper(sf.filename) like ''%''+upper(left('''+@filename+''',len('''+@filename+''')))+''%'';'
if @printonly=0
exec(@tsql)
end
fetch next from alldbfiles into @dbname,@name,@filename,@size,@iLogFile,@freespace,@pctfree
        end
        close alldbfiles
        DEALLOCATE alldbfiles
truncate table #tabledir

if @filedrive<>'%:\' --and exists (select top 1 value from dbo.sysconfigures where (config=16390 and value<>0))
begin
exec catsp_configure @begin=1
set @ssql='dir /d '+@filedrive
insert #tabledir exec master..xp_cmdshell @ssql
exec catsp_configure @begin=0
insert into #spacetbl
select @filedrive,convert(varchar,Right(x,len(x)-charindex(')',x))),'After'  from
#tabledir where upper(x) like upper('%bytes free%')
end
else
begin
-- select 'master..xp_cmdshell not allowed.  skipping show freespace step...' 'cmdshell'
truncate table ##TMPFIXEDdriveS_shrink
INSERT INTO ##TMPFIXEDdriveS_shrink EXEC [master]..xp_fixeddrives
insert into #spacetbl
select drive,convert(varchar,mbfree)+' MB free','After' from
##TMPFIXEDdriveS_shrink where upper(drive) like left(@filedrive,1)
end
select drivename,bytesfree,befaft from #spacetbl
order by drivename,befaft desc
if (select count(*) from ##alldbfilesbefaft) >0
begin
if (select count(*) from ##alldbfilesbefaft)/2=20
select 'Limiting shrink to top 10 files by pct of freespace...'
else
select convert(varchar,(select count(*)/2 from ##alldbfilesbefaft))+' Files with freespace between '+
convert(varchar,@filefreespacemin)+'MB and '+convert(varchar,@filefreespacemax)+'MB' 'Files matching criteria'
select dbname,filename,filesizemb,freespacemb,befaft,log_reuse_wait_desc,case when lower(log_reuse_wait_desc) like 'log_backup' and lower(befaft) like 'before' then 'perform LOG BACKUP of '+[filename]+' then reissue DBCC SHRINKFILE' else '' end [info] from ##alldbfilesbefaft
end
else
select @filedrive 'drivename', 'No eligible DB files found' 'msg'
-- in emergency:
--USE dbname
--GO
--ALTER DATABASE dbname
--SET RECOVERY SIMPLE
--GO
--DBCC SHRINKFILE (dbname_Log, 10)
--GO
--ALTER DATABASE dbname
--SET RECOVERY FULL
--GO
/*
Be aware that the LSN chain is immediately broken when the recovery model is set to SIMPLE.

Restarting the Chain
To restart the LSN log chain, immediately perform a full or differential backup after resetting the recovery model to FULL, and then resume normal log backups:
*/

After executing above scripts to perform shrink opeartion on a database mdf,ldf files and also if you want delete old backup files from the drive to clear space we can use this below script :

use [master]

set nocount on

--waitfor delay '00:26:00'

----------------- parms you set:

execute [master].[dbo].[usp_freedrivespace] 

@filedrive='%:\'  --e.g. 'c:\' or 'd:\' or 'e:\'  etc...  '%:\' for all

,@filefreespacemin=1000 --any db files with > @filefreespacemin freespace(MB)(e.g.,1000 = 1GB)

,@filefreespacemax=1000000000 -- and < @filefreespacemax freespace(MB)(e.g., 15000 = 15GB)

,@freespaceshrinkpct=.90 --% to shrink freespace of each file(e.g., set to .2 and run a few times)

,@bakdaysOld=1  --delete @bakdaysOld .bak files on @filedrive (set >999 to skip delete step)

,@filestodelete='*.trn'

,@dbfiletype='log' --set to 'data' or 'log'  or '%'.   '%'="shrink both log and data files"

,@dbnamelikethis='%' --used in 'like' "where clause" for dbnames to shrink

,@dbnamenotlikethis=' ' --used in 'not like' "where clause" for dbnames not to shrink

,@printonly=0  --only print shrink commands..don't execute them

--,@reorgindex='print' --comment or set to NULL or 0 or '' to skip reorgs. values: 'print,execute' - performs a reorg if a db file had a shrink performed on data file

GO

NOTE : I have been using this script without any issues .But still please do some testing of this script before executing it in your Production.

Refresh A SQL Server Database Automatically from PROD to TEST\DEV

Refresh A SQL Server Database Automatically from PROD to TEST\DEV 

As a DBA, we are often asked to refresh a database, which means to overwrite an existing database using a different database's backup.  If you are rarely asked to do this, you may decide to do it manually.  If you are asked to do this on a regular and perhaps scheduled basis, then you'd want to automate it.

To do the Refresh Manually, you would probably need to follow below steps :

Step-1 : Copy Backup files(here I took Spilt backup-2 files) from Source(Prod) to Destination(Test\Dev)

Step-2 : Take Destination (Test\Dev)Database Users Backup Before Starting the Restore.

Step-3 : Refresh the Destination Database from the copied PROD Backup File( from step-1 )

Step-4 : Drop Users from the Restored Database

Step-5 : Create Users on database from Step-2

Step-6 : Sync the Users.

Step-7 : Update the Statistics 

To automate this process, we need to write code that does the above steps and then to schedule that code to run via the SQL Server Agent Job.

As we have to Refresh the TEST\Dev Database from Last night backup on PROD based on this I have Automated DBRefresh Task using below Script and it works Fine in my Environment. You  have to schedule the job on Destination Server. 

USE [msdb]

GO

/****** Object:  Job [DBA - Refresh TEST from PRD]    Script Date: 3/4/2018 5:00:53 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [REPL-Snapshot]    Script Date: 3/4/2018 5:00:53 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Snapshot' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Snapshot'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA - Refresh TEST from PRD', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'Version 2.0

', 

@category_name=N'REPL-Snapshot', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Copy Backups]    Script Date: 3/4/2018 5:00:53 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Copy Backups', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'EXEC master..xp_cmdshell ''DEL C:\Restore\*.* /F /Q''

EXEC master..xp_cmdshell ''robocopy \\NODE1\Backup C:\Restore *TEST*.bak''', 

@database_name=N'master', 

@output_file_name=N'C:\Restore\output.txt', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Backup Users]    Script Date: 3/4/2018 5:00:53 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Users', 

@step_id=2, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'CmdExec', 

@command=N'SQLCMD -h -1 -W -S NODE2 -E -d TEST1 -i "C:\Scripts\script_users_for_db_refresh.sql" -o "C:\Scripts\TEST1_users.sql"', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Refresh TEST1]    Script Date: 3/4/2018 5:00:53 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh TEST1', 

@step_id=3, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'DECLARE @Sql NVARCHAR(2000)

DECLARE @BuName1 NVARCHAR (500)

DECLARE @BuName2 NVARCHAR (500)

CREATE TABLE #Temp (Col1 VARCHAR(500))

INSERT  INTO #Temp

EXEC master.dbo.xp_cmdshell ''dir C:\Restore''

DELETE  #Temp

WHERE   Col1 IS NULL

DELETE  #Temp

WHERE   Col1 NOT LIKE ''%TEST1%''

SELECT @BuName1 = SUBSTRING(Col1,40,75)

FROM    #Temp

WHERE SUBSTRING(Col1,40,75) LIKE ''%1.bak''

SELECT @BuName2 = SUBSTRING(Col1,40,75)

FROM    #Temp

WHERE SUBSTRING(Col1,40,75) LIKE ''%2.bak''

PRINT @BuName1

PRINT @BuName2

ALTER DATABASE [TEST1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [TEST1]

SET @SQL = ''RESTORE DATABASE [TEST1] FROM  DISK = N''''C:\Restore\''+@BuName1+''''''

,DISK = N''''C:\Restore\''+@BuName2+''''''

WITH  FILE = 1, 

MOVE N''''TEST1'''' TO N''''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST1.mdf'''',

MOVE N''''TEST1_log'''' TO N''''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TEST1_log.ldf'''',

NOUNLOAD,  REPLACE,  STATS = 5''

PRINT @SQL

EXEC (@SQL)

ALTER DATABASE [TEST1] SET MULTI_USER

DROP TABLE #Temp

USE [TEST1]

GO

EXEC sp_changedbowner ''sa''

GO

', 

@database_name=N'master', 

@output_file_name=N'C:\Restore\output.txt', 

@flags=2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Drop Users]    Script Date: 3/4/2018 5:00:53 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Drop Users', 

@step_id=4, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'USE [TEST1]

GO

DECLARE @Sql VARCHAR(1000)

DECLARE @User VARCHAR(25)

DECLARE UserCursor CURSOR

FOR SELECT  DISTINCT

name

FROM    sys.schemas

WHERE  name NOT IN (''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',

''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'',''dbo'',''guest'',

''INFORMATION_SCHEMA'',''sys'',''ID_User'', ''AdsReporting'', ''globalUser'')

OPEN UserCursor

FETCH NEXT FROM UserCursor INTO @User

WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @Sql = ''IF  EXISTS (SELECT * FROM sys.schemas WHERE name = ''''''

            + @User + '''''')

                     DROP SCHEMA ['' + @User + '']''

        PRINT @Sql

        EXEC (@Sql)

        FETCH NEXT FROM UserCursor INTO @User

    END

CLOSE UserCursor

DEALLOCATE UserCursor

DECLARE UserCursor CURSOR

FOR SELECT name FROM sys.database_principals

WHERE  type <> ''R''

AND name NOT IN ('''')

AND principal_id BETWEEN 5 AND  16383

OPEN UserCursor

FETCH NEXT FROM UserCursor INTO @User

WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @Sql = ''IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = ''''''

            + @User + '''''')

              DROP USER ['' + @User + '']''

        PRINT @Sql

        EXEC (@Sql)

        FETCH NEXT FROM UserCursor INTO @User

    END

CLOSE UserCursor

DEALLOCATE UserCursor

GO 

', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Creat Users]    Script Date: 3/4/2018 5:00:54 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Creat Users', 

@step_id=5, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'DECLARE @Exists INT 

EXEC master.dbo.xp_fileexist "C:\Scripts\TEST1_users.sql",

    @Exists OUT

IF @Exists = 1

    BEGIN

        EXEC master.dbo.xp_cmdshell ''SQLCMD -S NODE2 -d TEST1 -E -n -i "C:\Scripts\TEST1_users.sql"''

    END', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Sync Users]    Script Date: 3/4/2018 5:00:54 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Sync Users', 

@step_id=6, 

@cmdexec_success_code=0, 

@on_success_action=3, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'USE [TEST1]

GO

DECLARE @UserName nvarchar(255)

DECLARE orphanuser_cur cursor for

SELECT UserName = name

FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and

suser_sname(sid) is null

and name <> ''dbo''

ORDER BY name

OPEN orphanuser_cur

FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + '' user name being resynced''

EXEC sp_change_users_login ''Update_one'', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName

END

CLOSE orphanuser_cur

DEALLOCATE orphanuser_cur', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Update Stats]    Script Date: 3/4/2018 5:00:54 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Stats', 

@step_id=7, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'USE TEST1

GO

EXEC sp_updatestats', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

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.