Get data from mirrored database

CREATE DATABASE NewDatabase
ON (NAME = 'Your Database Name', FILENAME = 'C:\Backups\NewDatabase.SNP')
   AS SNAPSHOT OF YourDatabaseName
GO
Select * from NewDatabase.dbo.test
GO

Get Port Number for all Instance

Set NoCount On
Declare @CurrID int,@ExistValue int, @MaxID int, @SQL nvarchar(1000)
Declare @TCPPorts Table (PortType nvarchar(180), Port int)
Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,
InstName nvarchar(180),
Folder nvarchar(50),
StaticPort int null,
DynamicPort int null,
Platform int null);
Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))
Declare @Platform varchar(100)
Insert into @Plat exec xp_msver platform
select @Platform = (select 1 from @plat where charactervalue like ‘%86%’)
If @Platform is NULL
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances set Platform=64
End
else
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances Set Platform=32
End

Declare @Keyexist Table (Keyexist int)
Insert into @Keyexist
Exec xp_regread’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances Set Platform =32 where Platform is NULL

Select @MaxID = MAX(InstanceID), @CurrID = 1
From @SQLInstances
While @CurrID <= @MaxID
Begin
Delete From @TCPPorts

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPDynamicPorts”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPPort”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPDynamicPorts”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPPort”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Update SI
Set StaticPort = P.Port,
DynamicPort = DP.Port
From @SQLInstances SI
Inner Join @TCPPorts DP On DP.PortType = ‘TCPDynamicPorts’
Inner Join @TCPPorts P On P.PortType = ‘TCPPort’
Where InstanceID = @CurrID;

Set @CurrID = @CurrID + 1
End

Select serverproperty(‘ComputerNamePhysicalNetBIOS’) as ServerName, InstName, StaticPort, DynamicPort,Platform
From @SQLInstances
Set NoCount Off

Get Next Schedule Time for Job

SELECT
JOBS.Name AS JobName,
CAST(
CONVERT(CHAR(8), next_run_date, 112) + ‘ ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ‘:’), 3, 0, ‘:’)
AS DateTime) AS NextRunTime
FROM Msdb.dbo.SysJobs JOBS
INNER JOIN Msdb.dbo.SysJobSchedules SCHED ON JOBS.Job_Id = SCHED.Job_Id
AND JOBS.Enabled = 1 and next_run_date <> 0

Get List of Compressed Tables

–List Compressed Tables
SELECT * FROM sys.partitions WHERE data_compression <> 0

SELECT OBJECT_NAME(OBJECT_ID) AS ObjName,data_compression_desc AS CompressionType
FROM sys.partitions
WHERE OBJECTPROPERTY(OBJECT_ID,’ismsshipped’) <> 0

Direct Insert from Stored Procedure

Drop table #MyTempTable
SELECT * INTO #MyTempTable FROM OPENROWSET(‘SQLNCLI’, ‘Server=InstanceName;Trusted_Connection=yes;’,
‘EXEC sp_who’)

SELECT * FROM #MyTempTable

Find Job Name From Command

use msdb
go
select sj.name as job_name, st.command
from sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like ‘%Job Name%’

Type Wise Backup Count

BEGIN
SET NOCOUNT ON;
Declare @LinkedServer varchar(500)
Declare @DBName varchar(500)
Declare @InstanceName nvarchar(500)
Declare @DatabasePathMDF nvarchar(500)
Declare @DatabasePathLDF nvarchar(500)
Declare @DBSize nvarchar(500)
Declare @DBBackupSize nvarchar(500)
Declare @FirstDate DATETIME
set @FirstDate = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))

Declare @DB_Details Table (
Instance_Name varchar(50),
Database_Name varchar(50),
Full_Backup int,
Diff_Backup int,
Trn_Backup int
)

DECLARE server_name_cursor Cursor READ_ONLY For
SELECT name from sys.servers where name like ‘LKDS[_]%’

Declare @cur_stat_Server smallint
select @cur_stat_Server = CURSOR_STATUS(‘global’,’server_name_cursor’)
if @cur_stat_Server = -1
OPEN server_name_cursor
Try_ServerName:
FETCH Next From server_name_cursor Into @LinkedServer
Begin Try

WHILE @@Fetch_status =0
Begin
Declare @Task varchar(1000)
set @Task = ‘select
(select * from openquery(‘+ @LinkedServer +’,”select @@ServerName”)) as ServerName,
bs.database_name as DBname ,
count (case type when ”D” Then 1 End)as FullBackup,
count (case type when ”I” Then 1 End)as DiffBackup,
count (case type when ”L” Then 1 End)as LogBackup
from ‘+@LinkedServer+’.msdb.dbo.backupset as bs
where DATEADD(DD,0,DATEDIFF(DD,0,backup_start_date)) = DATEADD(DD,-1,DATEDIFF(DD,0,GETDATE()))
Group by bs.database_name’
Print @Task
insert into @DB_Details
Exec (@Task)

Fetch Next from server_name_cursor Into @LinkedServer
End
End Try
Begin Catch
GoTo Try_ServerName
End Catch
CLOSE server_name_cursor
DEALLOCATE server_name_cursor

select * from @DB_Details
END

Mail Count

select
@@SERVERNAME as ServerName,
Date as MailDate,
max(totalmails) as TotalMails,
MAX(SentMails) AS SentMails,
MAX(FailedMails) AS FailedMails
from
(SELECT convert(varchar,last_mod_date,101) AS Date,
count(*) AS TotalMails,
” AS SentMails,
” AS FailedMails
from msdb..sysmail_allitems
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
group by
convert(varchar,last_mod_date,101)
union all
SELECT convert(varchar,last_mod_date,101),
”,
sum(CASE When sent_status=’sent’ THEN 1 END ),
sum(CASE When sent_status=’failed’ THEN 1 END)
from msdb..sysmail_allitems
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
group by Convert(varchar,last_mod_date,101),sent_status)T
Group by Date

Mail Summary

with cte as
(SELECT CASE WHEN subject like ‘%DB Schema Updated%’ THEN ‘DB Schema Updated’
WHEN subject like ‘%Patient Import Notification%’ THEN ‘Patient Import Notification’
WHEN subject like ‘Schedule Import Status%’ THEN ‘Schedule Import Status’
WHEN subject like ‘SQL Server Job System%’ THEN ‘SQL Server Job System’
WHEN subject like ‘HHA Exchange: Request for New Placement%’ THEN ‘HHA Exchange: Request for New Placement’
WHEN subject like ‘HHA Exchange: Broadcast Request for New placement%’ THEN ‘HHA Exchange: Broadcast Request for New placement’
ELSE subject
END as Subject,
sent_status,
last_mod_date
FROM msdb..sysmail_allitems M1
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
),
cte1 as
(select Subject,
sum(CASE WHEN sent_status =’sent’ then 1 END) as TotalSentMails,
sum(CASE WHEN sent_status =’failed’ then 1 END) as TotalFailedMails
from CTE
group by SUBJECT,convert(varchar,last_mod_date,101),sent_status
)
select Subject,
isnull(sum(TotalSentMails),0) as TotalSentMails,
isnull(sum(TotalFailedMails),0) as TotalFailedMails
from cte1
group by subject
order by TotalSentMails desc

List all Failed Mail

SELECT
case profile_id
when 1 then ‘info@hhaexchange.com’
when 2 then ‘demo@hhaexchange.com’
when 3 then ‘feedback@hhaexchange.com’
when 4 then ‘ssis@hhaexchange.com’
when 5 then ‘info-noreply@hhaexchange.com’
when 6 then ‘info@hhaexchange.com’
end as From_Address,
recipients,
Subject,
Body,
case profile_id
when 1 then ‘Standard’
when 2 then ‘Demo’
when 3 then ‘feedback’
when 4 then ‘SSIS’
when 5 then ‘dnsexit’
when 6 then ‘Strandard’
end as Profile_Name,
body_format
into #FailedMail
FROM hhax10.msdb.dbo.sysmail_allitems M1
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
and sent_status = ‘Failed’

select * from #FailedMail

select
‘EXEC msdb.dbo.sp_send_dbmail
@from_address = ”’+From_Address+”’
, @recipients = ”’+recipients+”’
, @subject = ”’+subject+”’
, @body = ”’+Body+”’
, @profile_name = ”’+Profile_Name+”’
, @body_format = ”’+body_format+””
from #FailedMail

 

select sp.profile_id,sp.name,sa.email_address from hhax10.msdb.dbo.sysmail_profileaccount spa
join hhax10.msdb.dbo.sysmail_profile as sp on spa.profile_id = sp.profile_id
join hhax10.msdb.dbo.sysmail_account as sa on spa.account_id = sa.account_id