SQL Server 2008:
/*Virtual Machine Check*/
declare @virtual_machine_type_desc varchar(50)
declare @Server_type varchar(50)
select
@virtual_machine_type_desc = dosi.virtual_machine_type_desc
,@Server_type =
CASE
WHEN dosi.virtual_machine_type
= 1
THEN 'Virtual'
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi
select @virtual_machine_type_desc as virtual_machine_type_desc,@Server_type
as Server_type
set nocount on;
/* Logshipping Check */
DECLARE @LOGSHIPPING_PRIMARYSECONDARY_TABLE TABLE (LOGSHIPPING_PRIMARYSECONDARY
XML)
DECLARE @LOGSHIPPING_PRIMARYSECONDARY XML
INSERT INTO
@LOGSHIPPING_PRIMARYSECONDARY_TABLE (LOGSHIPPING_PRIMARYSECONDARY)
SELECT
(
select LP.primary_server,LS.secondary_server, SD.name,
case
when LP.primary_database
IS null then 'No' else 'Yes' end LSPConfigured,
case
when LS.secondary_database
IS null then 'No' else 'Yes' end LSSConfigured
From
master.dbo.sysdatabases SD
left
join msdb.dbo.log_shipping_monitor_primary LP on LP.primary_database=SD.name
left
join msdb.dbo.log_shipping_monitor_secondary LS on LS.secondary_database=SD.name
where (LP.primary_server is not null or LS.secondary_server
is not null)
FOR XML PATH('')
)
SELECT @LOGSHIPPING_PRIMARYSECONDARY =LOGSHIPPING_PRIMARYSECONDARY FROM
@LOGSHIPPING_PRIMARYSECONDARY_TABLE
--SELECT @LOGSHIPPING_PRIMARYSECONDARY AS
LOGSHIPPING_PRIMARYSECONDARY
/*Mirroring Status:*/
DECLARE @MIRROING_STATUS INT
SELECT @MIRROING_STATUS =
COUNT(X.name) FROM
(
SELECT d.name
,f.physical_name
,f.type_desc
,DB_in_Mirror=COALESCE(m.mirroring_role_desc,'Not Part in Mirror') --database must not be part of mirror (neither as principal
nor mirror) in order to be detached
,[Size
(Gb)]=CAST(f.size/1024.00/1024.00 AS NUMERIC(18,2))
FROM
sys.databases d
INNER
JOIN sys.master_files f
ON d.database_id = f.database_id
LEFT
OUTER JOIN sys.database_mirroring m
ON d.database_id = m.database_id
where 1 = 1
AND
d.state = 0 -- online
)X
WHERE
X.DB_in_Mirror <>
'Not Part in Mirror'
/*Replication Publisher Status*/
DECLARE @REPLICATION_PUBLISHER_TABLE TABLE (REPLICATION_PUBLISHER
XML)
DECLARE @REPLICATION_PUBLISHER XML
INSERT INTO
@REPLICATION_PUBLISHER_TABLE (REPLICATION_PUBLISHER)
SELECT
(
SELECT
X.name,
is_published
FROM
(
SELECT
DISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROM
sys.databases d
INNER
JOIN sys.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 -- online
)X
WHERE
X.is_published =
1
GROUP
BY X.name,X.is_published
ORDER
BY 1,2
FOR XML PATH('')
)
SELECT @REPLICATION_PUBLISHER =
REPLICATION_PUBLISHER FROM
@REPLICATION_PUBLISHER_TABLE
/*Replication Subscriber Status*/
DECLARE @REPLICATION_SUBSCRIBER_TABLE TABLE (REPLICATION_SUBSCRIBER
XML)
DECLARE @REPLICATION_SUBSCRIBER XML
INSERT INTO
@REPLICATION_SUBSCRIBER_TABLE (REPLICATION_SUBSCRIBER)
SELECT
(
SELECT
X.name,
is_subscribed
FROM
(
SELECT
DISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROM
sys.databases d
INNER
JOIN sys.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 -- online
)X
WHERE
X.is_subscribed =
1
GROUP
BY X.name,X.is_subscribed
ORDER
BY 1,2
FOR XML PATH('')
)
SELECT @REPLICATION_SUBSCRIBER = REPLICATION_SUBSCRIBER FROM
@REPLICATION_SUBSCRIBER_TABLE
/*Replication Merge Publisher Status*/
DECLARE @REPLICATION_MERGE_PUBLISHER_TABLE TABLE (REPLICATION_MERGE_PUBLISHER
XML)
DECLARE @REPLICATION_MERGE_PUBLISHER XML
INSERT INTO
@REPLICATION_MERGE_PUBLISHER_TABLE (REPLICATION_MERGE_PUBLISHER)
SELECT
(
SELECT
X.name,
is_merge_published
FROM
(
SELECT
DISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROM
sys.databases d
INNER
JOIN sys.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 -- online
)X
WHERE
X.is_merge_published =
1
GROUP
BY X.name,X.is_merge_published
ORDER
BY 1,2
FOR XML PATH('')
)
SELECT @REPLICATION_MERGE_PUBLISHER = REPLICATION_MERGE_PUBLISHER FROM @REPLICATION_MERGE_PUBLISHER_TABLE
/*Replication Distributor Status*/
DECLARE @REPLICATION_DISTRIBUTOR_TABLE TABLE (REPLICATION_DISTRIBUTOR
XML)
DECLARE @REPLICATION_DISTRIBUTOR XML
INSERT INTO
@REPLICATION_DISTRIBUTOR_TABLE (REPLICATION_DISTRIBUTOR)
SELECT
(
SELECT
X.name,
is_distributor
FROM
(
SELECT
DISTINCT d.name
,d.is_published
,d.is_subscribed
,d.is_merge_published
,d.is_distributor
FROM
sys.databases d
INNER
JOIN sys.master_files f
ON d.database_id = f.database_id
where 1 = 1
AND
d.state = 0 -- online
)X
WHERE
X.is_distributor =
1
GROUP
BY X.name,X.is_distributor
ORDER
BY 1,2
FOR XML PATH('')
)
SELECT @REPLICATION_DISTRIBUTOR = REPLICATION_DISTRIBUTOR FROM
@REPLICATION_DISTRIBUTOR_TABLE
/*CPU and Memory Status*/
Declare @PhysicalMemory_MB bigint,@PhysicalMemory_GB bigint,@virtualMemory_GB bigint
Declare @productversion nvarchar(20)
select @productversion =
cast(SERVERPROPERTY('productversion') as nvarchar(20))
DECLARE @Sockets_Physical_CPU_Count INT
DECLARE @Hyperthread_Ratio_Core INT
DECLARE @Logical_Processor_CPU_Count INT
DECLARE @sqlserver_start_time datetime
SELECT
@Sockets_Physical_CPU_Count = cpu_count/hyperthread_ratio -- AS
[Sockets/Physical_CPU_Count],
,@Hyperthread_Ratio_Core=
hyperthread_ratio ---AS [Hyperthread_Ratio/Core],
,@Logical_Processor_CPU_Count = cpu_count -- AS [Logical Processor/ CPU Count],
,@sqlserver_start_time=
sqlserver_start_time
FROM sys.dm_os_sys_info
/*
select @productversion -- = cast(@productversion as
varchar(20))
if
left(@productversion,2) = '11'
begin
SELECT
@PhysicalMemory_MB=CEILING(physical_memory_kb/1024.0)
-- as [Physical Memory_MB],
,@PhysicalMemory_GB =
CEILING(physical_memory_kb/1024/1024) --as [Physical Memory_GB],
,@virtualMemory_GB=CEILING(virtual_memory_kb/1024/1024)
--as [Virtual Memory GB]
FROM sys.dm_os_sys_info
goto escapefromsql2008
end
*/
if left(@productversion,2) = '10'
begin
SELECT
@PhysicalMemory_MB=CEILING(physical_memory_in_bytes/1024.0) -- as [Physical
Memory_MB],
,@PhysicalMemory_GB
= CEILING(physical_memory_in_bytes/1024/1024) --as [Physical Memory_GB],
,@virtualMemory_GB=CEILING(virtual_memory_in_bytes/1024/1024) --as [Virtual Memory GB]
FROM
sys.dm_os_sys_info
end
DECLARE @min_SQLServer_memory sql_variant
SELECT
@min_SQLServer_memory=value
FROM sys.configurations
WHERE name like '%min server memory (MB)%'
DECLARE @max_SQLServer_memory sql_variant
SELECT
@max_SQLServer_memory=value
FROM sys.configurations
WHERE name like '%max server memory (MB)%'
DECLARE @min_memory_per_query_kb sql_variant
SELECT
@min_memory_per_query_kb = value
FROM sys.configurations
WHERE name like '%min memory per query (KB)%'
Declare @SQLServerAuthentication varchar(40)
SELECT
@SQLServerAuthentication =
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN
1 THEN 'Windows
Authentication'
WHEN
0 THEN 'Windows and SQL
Server Authentication'
END
SELECT top 1
@@SERVICENAME AS INSTANCE,
SERVERPROPERTY('servername') as ServerName,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerName,
SERVERPROPERTY('productversion') as ProductVersion,
SERVERPROPERTY('productlevel') as [Prod.Level],
SERVERPROPERTY('edition') as Edition,
SERVERPROPERTY('IsClustered') as IsClustered,
SERVERPROPERTY('SqlCharSet') as SqlCharSet,
SERVERPROPERTY('SqlCharSetName') as SqlCharSetName,
SERVERPROPERTY('SqlSortOrder') as SqlSortOrder,
SERVERPROPERTY('SqlSortOrderName') as SqlSortOrderName,
SERVERPROPERTY('collation') AS SQLServerCollation,
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
--CONNECTIONPROPERTY('client_net_address') AS
client_net_address,
@SQLServerAuthentication as
SQLServerAuthentication,
@Sockets_Physical_CPU_Count AS [Sockets/Physical_CPU_Count],
@Hyperthread_Ratio_Core AS
[Hyperthread_Ratio/Core],
@Logical_Processor_CPU_Count AS [Logical
Processor/ CPU Count],
@sqlserver_start_time AS sqlserver_start_time,
@PhysicalMemory_MB as [Physical Memory_MB],
@PhysicalMemory_GB as [Physical Memory_GB],
--@virtualMemory_GB as [Virtual Memory GB],
@min_SQLServer_memory as min_SQLServer_memory_MB,
@max_SQLServer_memory as
max_SQLServer_memory_MB,
@min_memory_per_query_kb as
min_memory_per_query_kb,
COALESCE(@MIRROING_STATUS,'No Mirroring') as MIRROING_STATUS,
COALESCE(@REPLICATION_PUBLISHER,'No Publisher') AS
REPLICATION_PUBLISHER,
COALESCE(@REPLICATION_SUBSCRIBER,'No Subscriber') AS REPLICATION_SUBSCRIBER,
COALESCE(@REPLICATION_MERGE_PUBLISHER,'No Merge Publisher') AS REPLICATION_MERGE_PUBLISHER,
COALESCE(@REPLICATION_DISTRIBUTOR,'No Distributor') AS REPLICATION_DISTRIBUTOR,
COALESCE(@LOGSHIPPING_PRIMARYSECONDARY,'No Logshipping') AS LOGSHIPPING_PRIMARYSECONDARY,
COALESCE(SERVERPROPERTY ('IsHadrEnabled'),'0') as AlwaysOnEnable,
'No AlwaysOn' AS
AlwaysOnInfo,
@virtual_machine_type_desc as virtual_machine_type_desc,@Server_type
as Server_type,
OSVersion =RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ',@@VERSION))
GO
/*
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
AGC.name --
Availability Group
,
RCS.replica_server_name -- SQL cluster node name
,
ARS.role_desc -- Replica Role
,
AGL.dns_name -- Listener Name
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN
sys.dm_hadr_availability_replica_cluster_states AS RCS
ON
RCS.group_id =
AGC.group_id
INNER JOIN
sys.dm_hadr_availability_replica_states AS ARS
ON
ARS.replica_id
= RCS.replica_id
INNER JOIN
sys.availability_group_listeners AS AGL
ON
AGL.group_id =
ARS.group_id
WHERE
ARS.role_desc =
'PRIMARY'
END
*/
/*
DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
FROM
sys.database_mirroring
WHERE
DB_NAME(database_id) = N'DB_X'); --
your database name here
IF @MirroringRole = 2 -- Mirror
-- connect to
the failover partner server, using your database
ELSE IF @MirroringRole = 1 -- Principal
-- connect to
this server
END IF
Reference:
http://dba.stackexchange.com/questions/36755/how-do-i-determine-if-a-database-is-the-principal-in-a-mirroring-setup
USE MASTER
GO
--===========================================================
-- before detaching the database
-- see what files it has and where they are located
-- checks mirror and replication
-- database must not be part of mirror (neither as
principal nor mirror) in order to be detached
--===========================================================
*/
set nocount on;
--To find database information:
Declare @SQLServerAuthentication varchar(40)
SELECT
@SQLServerAuthentication =
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN
1 THEN 'Windows
Authentication'
WHEN
0 THEN 'Windows and SQL
Server Authentication'
END
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP
TABLE #temp
Create
Table #temp
(
Database_ID INT,
DatabaseName sysname,
Name sysname,
physical_name nvarchar(500),
size_MB decimal
(18,2),
FreeSpace_MB decimal (18,2)--,
--DBStatus
INT,
--is_read_only
BIT
)
Exec sp_MSforeachdb '
Use [?];
Insert Into #temp (Database_ID,DatabaseName, Name, physical_name, size_MB,
FreeSpace_MB)
Select DB_ID(DB_NAME()) AS
Database_ID,DB_NAME() AS [DatabaseName], Name,
physical_name,
Cast(Cast(Round(cast(size as
decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) AS size_MB,
Cast(Cast(Round(cast(size as
decimal) * 8.0/1024.0,2) as decimal(18,2)) -
Cast(FILEPROPERTY(name,
''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace_MB
FROM
sys.database_files
'
Select
T.DatabaseName,
@@SERVICENAME AS INSTANCE,
SERVERPROPERTY('servername') as ServerName,
'' AS Service,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerName,
SERVERPROPERTY('productversion') as ProductVersion,
DATABASEPROPERTYEX(DatabaseName, 'Status') AS DBStatus,
Read_Write_Status =
case when D.is_read_only = 0 then 'Read/Write'
else 'Read'
End,
T.Name as
[FileName],T.physical_name,
T.size_MB AS
Actual_Size_MB,
T.FreeSpace_MB,
@SQLServerAuthentication as
SQLServerAuthentication
From
#temp T
inner join sys.databases D
on T.Database_ID = D.database_id
where T.Database_ID not in (1,2,3,4)
set nocount off;
No comments:
Post a Comment