Uncategorized

SQL Server Instance and DB Information – SQL 2012

SQL Server Information
SQL Server 2012:
Many of the companies like to keep their database information as a report and that will be useful when it comes to upgrade or finding of the particular database or server information. This is very useful information which we believe that needs to be kept in the document folder. Anyone in the Infrastructure Team can use this useful information without searching through all of their databases instances.

 
set nocount on;
/*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
/* ALWAYSON Check */
DECLARE @ALWAYSON_INFO_TABLE TABLE (ALWAYSON_STATUS XML)
DECLARE @ALWAYSON_STATUS XML
INSERT INTO@ALWAYSON_INFO_TABLE (ALWAYSON_STATUS)
SELECT
(
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 ASAGC
  INNERJOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
   ON
    RCS.group_id = AGC.group_id
  INNERJOIN sys.dm_hadr_availability_replica_states AS ARS
   ON
    ARS.replica_id = RCS.replica_id
  INNERJOIN sys.availability_group_listeners AS AGL
   ON
    AGL.group_id = ARS.group_id
WHERE
 ARS.role_desc = ‘PRIMARY’
  ORDERBY 1,2
 FOR XML PATH()
)
 
SELECT @ALWAYSON_STATUS =ALWAYSON_STATUS FROM @ALWAYSON_INFO_TABLE
 
–SELECT @ALWAYSON_STATUS
/* 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,
  casewhen lp.primary_database IS null then ‘No’ else ‘Yes’ end LSPConfigured,
  casewhen ls.secondary_database IS null then ‘No’ else ‘Yes’ end LSSConfigured
From
       Master.dbo.sysDatabases SD
       leftjoin msdb.dbo.log_shipping_monitor_primary LP on lp.primary_database=SD.name
       leftjoin 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 @MIRRORING_STATUS_TABLE TABLE (MIRRORING_STATUS XML)
DECLARE @MIRRORING_STATUS XML
INSERT INTO@MIRRORING_STATUS_TABLE (MIRRORING_STATUS)
SELECT
(
 
 
SELECT
DISTINCT Name,DB_in_Mirror,Mirroring_Role
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))
         ,COALESCE(m.mirroring_role,0) AS Mirroring_Role
 FROMsys.databases d
      INNERJOIN SYS.master_files f
                 ON d.database_id = f.database_id
 
      LEFTOUTER JOIN sys.database_mirroring m
                      ON d.database_id = m.database_id           
where 1 = 1
  ANDd.state = 0 — online
 )X
 WHEREX.DB_in_Mirror <>‘Not Part in Mirror’
 
 
  ORDERBY 1,2
 FOR XML PATH()
)
 
SELECT @MIRRORING_STATUS =MIRRORING_STATUS FROM @MIRRORING_STATUS_TABLE
 
–SELECT @MIRRORING_STATUS
 
/*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
(     
 SELECTDISTINCT d.name
      ,d.is_published
      ,d.is_subscribed
      ,d.is_merge_published
      ,d.is_distributor
      FROMsys.databases d
      INNERJOIN SYS.master_files f
                 ON d.database_id = f.database_id
   
where 1 = 1
  AND
  d.state = 0 — online
  )X
   WHEREX.is_published =1
 GROUPBY X.NAME,X.is_published
  ORDERBY 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
(     
 SELECTDISTINCT d.name
      ,d.is_published
      ,d.is_subscribed
      ,d.is_merge_published
      ,d.is_distributor
      FROMsys.databases d
      INNERJOIN SYS.master_files f
                 ON d.database_id = f.database_id
   
where 1 = 1
  AND
  d.state = 0 — online
  )X
   WHEREX.is_subscribed =1
 GROUPBY X.NAME,X.is_subscribed
  ORDERBY 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
(     
 SELECTDISTINCT d.name
      ,d.is_published
      ,d.is_subscribed
      ,d.is_merge_published
      ,d.is_distributor
      FROMsys.databases d
      INNERJOIN SYS.master_files f
                 ON d.database_id = f.database_id
   
where 1 = 1
  AND
  d.state = 0 — online
  )X
   WHEREX.is_merge_published =1
 GROUPBY X.NAME,X.is_merge_published
  ORDERBY 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
(     
 SELECTDISTINCT d.name
      ,d.is_published
      ,d.is_subscribed
      ,d.is_merge_published
      ,d.is_distributor
      FROMsys.databases d
      INNERJOIN SYS.master_files f
                 ON d.database_id = f.database_id
   
where 1 = 1
  AND
  d.state = 0 — online
  )X
   WHEREX.is_distributor =1
 GROUPBY X.NAME,X.is_distributor
  ORDERBY 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]
 FROMsys.dm_os_sys_info
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’) 
 WHEN1 THEN ‘Windows Authentication’  
 WHEN0 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 asSQLServerAuthentication,
@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 asmax_SQLServer_memory_MB,
@min_memory_per_query_kb asmin_memory_per_query_kb,  
COALESCE(@MIRRORING_STATUS,‘No Mirroring’)  ASMIRROING_STATUS,
COALESCE(@REPLICATION_PUBLISHER,‘No Publisher’)  ASREPLICATION_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,
SERVERPROPERTY (‘IsHadrEnabled’) as AlwaysOnEnable,
COALESCE(@ALWAYSON_STATUS,‘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))
/*
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’) 
 WHEN1 THEN ‘Windows Authentication’  
 WHEN0 THEN ‘Windows and SQL Server Authentication’  
 END
 
IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL
    DROPTABLE #temp
 
 
    CreateTable #temp
(
    Database_ID INT,
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size_MB numeric (18,2),
    FreeSpace_MB numeric (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 int) * 8.0/1024.0,2) as int) as nvarchar) Size_MB ,
  cast(Cast(Round(cast(size as int) * 8.0/1024.0,2) as int) –
   Cast(FILEPROPERTY(name, ”SpaceUsed”) * 8.0/1024.0 as int) 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 ASActual_Size_MB,
T.FreeSpace_MB,
@SQLServerAuthentication asSQLServerAuthentication
 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;
 

Leave a Reply

Your email address will not be published. Required fields are marked *