Calculating Always On Latency

http://sqlblogcasts.com/blogs/blakmk/archive/2012/12/18/calculating-estimated-data-loss-with-always-on.aspx
-- Slightly modified from above


WITH DR_CTE ( replica_server_name, database_name, last_commit_time)

AS

                (

                                select ar.replica_server_name, database_name, rs.last_commit_time

                                from master.sys.dm_hadr_database_replica_states  rs

                                inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

                                inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

                                where replica_server_name != @@servername

                )

select ar.replica_server_name as PrimaryServerName, dcs.database_name, DR_CTE.replica_server_name SecondaryServerName, rs.last_commit_time, DR_CTE.last_commit_time 'DR_commit_time', datediff(ss,  DR_CTE.last_commit_time, rs.last_commit_time) 'lag_in_seconds'

from master.sys.dm_hadr_database_replica_states  rs

inner join master.sys.availability_replicas ar on rs.replica_id = ar.replica_id

inner join sys.dm_hadr_database_replica_cluster_states dcs on dcs.group_database_id = rs.group_database_id and rs.replica_id = dcs.replica_id

inner join DR_CTE on DR_CTE.database_name = dcs.database_name

where ar.replica_server_name = @@servername

order by lag_in_seconds desc

Check Database files autogrowth

Check Database files autogrowth



SELECT
DB_name(S.database_id) AS [Database Name]
,S.[name] AS [Logical Name]
,S.[file_id] AS [File ID]
, S.[physical_name] AS [File Name]
, Left(S.[physical_name], 1) as Drive
,CAST(CAST(G.name AS VARBINARY(256)) AS sysname) AS [FileGroup_Name]
,CONVERT (varchar(10),(S.[size]*8)) + ' KB' AS [Size]


,CASE WHEN S.[max_size]=-1 THEN 'Unlimited' ELSE CONVERT(VARCHAR(10),CONVERT(bigint,S.[max_size])/128)  END AS [Max Size MB]
,CASE s.is_percent_growth WHEN 1 THEN CONVERT(VARCHAR(10),S.growth) +'%' ELSE Convert(VARCHAR(10),S.growth/128) END AS [Growth MB]




,Case WHEN S.[type]=0 THEN 'Data Only'
WHEN S.[type]=1 THEN 'Log Only'
WHEN S.[type]=2 THEN 'FILESTREAM Only'
WHEN S.[type]=3 THEN 'Informational purposes Only'
WHEN S.[type]=4 THEN 'Full-text '
END AS [usage]

   --S.is_media_read_only,
   --S.is_read_only,
--   S.is_sparse
   --S.is_percent_growth,
   --S.is_name_reserved,
   --S.create_lsn,
   --S.drop_lsn,
   --S.read_only_lsn,
   --S.read_write_lsn,
   --S.differential_base_lsn,
   --S.differential_base_guid,
   --S.differential_base_time,
   --S.redo_start_lsn,
   --S.redo_start_fork_guid,
   --S.redo_target_lsn,
   --S.redo_target_fork_guid,
   --S.backup_lsn

FROM sys.master_files AS S
LEFT JOIN sys.filegroups AS G ON ((S.type = 2 OR S.type = 0)
AND (S.drop_lsn IS NULL)) AND (S.data_space_id=G.data_space_id)
where database_id > 4 and DB_name(S.database_id) not in ('dbadmin', 'sysadmin')