Daily Archives: February 27, 2019

Standard

Posted by

Posted on

February 27, 2019

Posted under

Tsql

Comments

Leave a comment

Script to list size of all tables in a SQL Server database

-> Below script can be used to get the size of all tables in a SQL Server database.

SELECT

a3.name +'.'+a2.name AS [TableName],
a1.rows as [# Records],
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [Reserved KB],
a1.data * 8 AS [Data KB],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [Indexes KB],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [Unused KB],
convert(decimal(18,2),(((a1.reserved + ISNULL(a4.reserved,0))* 8) - ((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8))/1024.0/1024.0) as [Table_used_Space GB]
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type != N'S' and a2.type != N'IT'
--ORDER BY a3.name, a2.name
--order by a1.rows desc
order by [Table_used_Space GB] desc

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

February 27, 2019

Posted under

AlwaysON

Comments

Leave a comment

Always ON Availability group automatic failover to a particular node

Environment

Blog25_1

-> One of my client had an Alwayson setup as depicted above. They advised me that all their Availability group should reside only on server JBSERVER1.JB.NET which is in Primary datacentre. Even if it fails over to server JBSERVER2.JB.NET in secondary datacentre, it should be immediately failed back to server JBSERVER1.JB.NET and they are not worried about the downtime during the fail back.

-> I felt it to be a strange requirement and requested them for reason. They advised me that Server JBSERVER1.JB.NET hosts SSD drives which performs many times better than the SAS drives hosted on server JBSERVER2.JB.NET.

-> I configured a SQL Server agent job to execute below query and that took care of my Client’s requirement.

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'SECONDARY'
BEGIN
ALTER AVAILABILITY GROUP [JBAG] FAILOVER;
END

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

February 27, 2019

Posted under

Tsql

Comments

Leave a comment

TSQL to query commandlog table to check Alter Index duration

-> Below TSQL can be used to query commandlog table created by Ola Hallengren’s Index Maintenance script to check Alter Index duration.

select
[DatabaseName]
,[SchemaName]
,[ObjectName]
,[ObjectType]
,[IndexName]
,[IndexType]
,[CommandType]
,ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') as Page_Count,convert(decimal(5,2),ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'Float')) as Fragmentation
, CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 / 24 / 7 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 / 24 % 7 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 / 60 % 24 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) / 60 % 60 AS NVARCHAR(50)) + ':'
+ CAST(DATEDIFF(second, StartTime, EndTime) % 60 AS NVARCHAR(50)) + '' as "w:d:h:m:s"
, DATEDIFF(second, StartTime, EndTime) [Duration in Sec]
,[StartTime]
,[EndTime]
from [dbo].[Commandlog]
order by DATEDIFF(second, StartTime, EndTime) desc

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.