Uncategorized

SQL Server Instance and DB Information – SQL 2005

SQL Server Information
SQL Server 2005:

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;
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
    FROMsys.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
 FROMsys.dm_exec_connections
WHERE net_transport ISNOT NULL
GROUP BYnet_transport
–SELECT @net_transport
 
 
DECLARE @protocol_type VARCHAR(8000)
SELECT @protocol_type =COALESCE(@protocol_type + ‘, ‘, ) + protocol_type
 FROMsys.dm_exec_connections
WHERE protocol_type ISNOT NULL
GROUP BYprotocol_type
–SELECT @protocol_type
 
 
DECLARE @auth_scheme VARCHAR(8000)
SELECT @auth_scheme = COALESCE(@auth_scheme + ‘, ‘, ) + auth_scheme
 FROMsys.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)
–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
 
 
/* 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 @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))
 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’
 
 
/*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 @sqlserver_start_time =login_time FROM sysprocessesWHERE spid =1
 
/*
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]
 FROMsys.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,    
@net_transport AS net_transport, 
@protocol_type AS protocol_type, 
@auth_scheme AS auth_scheme,     
@local_net_address AS local_net_address,
@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 asmin_SQLServer_memory_MB,
@max_SQLServer_memory asmax_SQLServer_memory_MB,
@min_memory_per_query_kb asmin_memory_per_query_kb,  
COALESCE(@MIRROING_STATUS,‘No Mirroring’) as MIRROING_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,
COALESCE(SERVERPROPERTY (‘IsHadrEnabled’),‘0’) as AlwaysOnEnable,
‘No AlwaysOn’ ASAlwaysOnInfo,
‘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
    DROPTABLE #temp
 
 
    CreateTable #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) 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 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 *