SQL Server 2000:
set nocount on;
/*
DECLARE @local_net_address varchar(48),@local_tcp_port
int
SELECT top 1 @local_tcp_port = local_tcp_port,
@local_net_address = local_net_address
FROM
sys.dm_exec_connections
WHERE
(local_tcp_port IS NOT NULL AND local_net_address IS NOT NULL )
DECLARE @net_transport VARCHAR(8000)
SELECT @net_transport = COALESCE(@net_transport + ', ',
'') + net_transport
FROM
sys.dm_exec_connections
WHERE net_transport IS NOT NULL
GROUP BY net_transport
--SELECT @net_transport
DECLARE @protocol_type VARCHAR(8000)
SELECT @protocol_type = COALESCE(@protocol_type + ', ',
'') + protocol_type
FROM
sys.dm_exec_connections
WHERE protocol_type IS NOT NULL
GROUP BY protocol_type
--SELECT @protocol_type
DECLARE @auth_scheme VARCHAR(8000)
SELECT @auth_scheme = COALESCE(@auth_scheme + ', ', '') +
auth_scheme
FROM sys.dm_exec_connections
WHERE auth_scheme IS NOT NULL
GROUP BY auth_scheme
--SELECT @auth_scheme
*/
/*Virtual Machine Check*/
DECLARE @result int
EXEC @result = xp_cmdshell 'SYSTEMINFO'
declare @virtual_machine_type_desc varchar(50)
declare @Server_type varchar(50)
/* 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 @sqlserver_start_time = login_time FROM
sysprocesses WHERE spid =1
if left(@productversion,2) = '8'
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,
'' AS
net_transport,
'' AS
protocol_type,
'' AS auth_scheme,
'' AS
local_net_address,
'' AS
local_tcp_port,
--CONNECTIONPROPERTY('client_net_address') AS
client_net_address,
@SQLServerAuthentication as
SQLServerAuthentication,
'' AS [Sockets/Physical_CPU_Count],
'' AS
[Hyperthread_Ratio/Core],
'' AS [Logical
Processor/ CPU Count],
'' AS sqlserver_start_time,
'' as [Physical
Memory_MB],
'' as [Physical Memory_GB],
--@virtualMemory_GB as [Virtual Memory GB],
'' as
min_SQLServer_memory_MB,
'' as
max_SQLServer_memory_MB,
'' as min_memory_per_query_kb,
'Refer Server' AS
MIRROING_STATUS,
'Refer Server' AS
REPLICATION_PUBLISHER,
'Refer Server' AS
REPLICATION_SUBSCRIBER,
'Refer Server' AS
REPLICATION_MERGE_PUBLISHER,
'Refer Server' AS
REPLICATION_DISTRIBUTOR,
'Refer Server' AS LOGSHIPPING_PRIMARYSECONDARY,
'Refer Server' AlwaysOnEnable,
/*
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,
'Refer systeminfo' virtual_machine_type_desc,'Refer systeminfo' Server_type,
OSVersion =RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ',@@VERSION))
/*
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;
--===========================================================
-- 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
--===========================================================
--To find database information:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP
TABLE #temp
Create
Table #temp
(
Database_ID smallint,
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 SAF.dbid AS
Database_ID,DB_NAME() AS [DatabaseName], SF.Name, SF.filename as physical_name,
Cast(Cast(Round(cast(SF.size as
decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size_MB,
Cast(Cast(Round(cast(SF.size as
decimal) * 8.0/1024.0,2) as decimal(18,2)) -
Cast(FILEPROPERTY(SF.name,
''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace_MB
FROM
dbo.sysfiles SF inner join master.dbo.sysaltfiles SAF
on SF.fileid = SAF.fileid and SF.name COLLATE DATABASE_DEFAULT = SAF.name
COLLATE DATABASE_DEFAULT
'
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,
DATABASEPROPERTYEX(DatabaseName, 'Updateability') AS Read_Write_Status,
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 master.dbo.sysdatabases D
on T.Database_ID = D.dbid
where T.Database_ID not in (1,2,3,4)
set nocount off;
go
No comments:
Post a Comment