DeadLock Finding – SQL Script

Posted Leave a commentPosted in DBA Handy Tools, Uncategorized

DeadLock On SQL:

Occasionally you may find deadlock on the SQL Server. If you have enabled the trace, it will appear in the error log. Alternatively you can use the below script to see. Tested on SQL Server 2012

 

 

 

SQL Server Information

SQL Server Instance and DB Information – SQL 2005

Posted Leave a commentPosted in Uncategorized
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;
 
SQL Server Information

SQL Server Instance and DB Information – SQL 2008

Posted Leave a commentPosted in Uncategorized
SQL Server 2008:
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.
 
/*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,
  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 @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,    
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(@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,
 @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’) 
 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 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 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;
 
SQL Server Information

SQL Server Instance and DB Information – SQL 2012

Posted Leave a commentPosted in Uncategorized
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;
 

Oracle 11g RAC – Two Node Cluster Installation

Posted Leave a commentPosted in Uncategorized
Step by Step Oracle RAC 11g Installation:

(Includes setting up of two node Linux servers using VirtualBox, Installing Grid and Database)
 ——————————————————————————————————————

Install Oracle 11g 11.2.0.3 Grid Infrastructure and Database Installation
 —————————————————————————

Step by Step installation practical approach is in the above video. However, I kept the below screenshot for the Grid and Database Installation

1. Go to the installer mount directory. Then execute

#./runInstaller

(Note: Please note that there will be an issue while starting. I kept the information to fix, in case if you face it)


Checking monitor: must be configured to display at least 256 colors

>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.    Failed <<<<

Solution:

[root@rac1 ~]# DISPLAY=:0.0
[root@rac1 ~]# export DISPLAY
[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su – oracle
[oracle@rac1 ~]$ DISPLAY=:0.0
[oracle@rac1 ~]$ export DISPLAY

The above solution is more than enough to sort it out.

Optional:

Install the below packages if not there:

yum install xorg-x11-apps
yum install xorg-x11-xauth
ssh -X oracle@rac1.localdomain







Click Skip software updates and press Next


Choose “Install and Configure Oracle Grid Infrastructure for a Cluster” and Press Next..

Click the below screenshot option Advanced Installation and press Next

Choose the language as English and press Next

Name like below screenshot and deselect the Confgure GNS and press Next

Add rac2 and setup SSH connectivity and press Next

Press OK as per below screenshot and press Next

Leave the default like below and press Next

Select Oracle ASM and Press Next

Choose Change Discover Path and keep this screen like below two screenshots and press Next

Choose “Use same password for the accounts” and enter oracle as a password and press Next

Click Yes for this and continue by pressing Next..

Select “Do not use Intelligent Platform Management Interface (PMI) ” and Press Next…

Keep the below screenshot default and press Next

Click Yes to this and Press Next..

Keep the below default Oracle Base and Software Location and Press Next…

Keep the inventory directory as below and press Next..

Select Ignore All option for this and press Next..

Select Yes to this and continue by pressing Next..

Press Install button and proceed further.

Execute the below two scripts in rac1 and then followed by rac2

# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/11.2.0/grid/root.sh

Once the above two scripts executed successfully , please press Next..

Please note that if the error is like below and should be ignored and press Next..

Once installed, please verify as per below screenshot using crsctl command…



Oracle Database Installation:

Install the database using oracle software usually comes from the folder database. See the screen shot below.

#./runInstaller

Uncheck the below and click Next – It will prompt, select Yes to proceed..

Select Create and Configure database option and Press Next..

Select Server Class and Press Next..

Select both rac1 and rac2 and choose the Oracle Real Application Cluster database installation

Select English language and press Next …

Select the Enterprise Edition and press Next..

Select the default path and press Next..

Select the below General Purpose / Transaction processing option and press Next..

Name as per your choice. The database name here is orcl and press Next..

Choose the memory and press Next..

Choose the below option and press Next..

Choose the Oracle Automatic Storage Management Option and press Next..

Select Do not enable automation backups and press Next..

Select the Disk Group Name and press Next..

Select the “Use the same password for all accounts”, enter oracle as a password and Press Next

Choose the below Group and press Next..

Select “Ignore All” option for the below screenshot checks and press Next..

Execute the below scripts in both nodes rac1 and rac2 and press OK

Follow the below screenshot command to check the database installation status.:

BCP Issues and Solutions

Posted Leave a commentPosted in Uncategorized
BCP Issues – A way to sort it out.

Reference:

http://sqlfool.com/2012/04/bcp-script-generator/