Script to backup a single table:
select * into newtablename from oldtablename
No comments
Author: Enrique
SQL Server – Always-ON Availability Groups Monitoring Script
Below is the script I use to monitor Always-ON
;WITH AG_Stats AS (
SELECT AGS.name AS AGGroupName,
AR.replica_server_name AS InstanceName,
HARS.role_desc,
Db_name(DRS.database_id) AS DBName,
DRS.database_id,
AR.availability_mode_desc AS SyncMode,
DRS.synchronization_state_desc AS SyncState,
DRS.last_hardened_lsn,
DRS.end_of_log_lsn,
DRS.last_redone_lsn,
DRS.last_hardened_time, -- On a secondary database, time of the log-block identifier for the last hardened LSN (last_hardened_lsn).
DRS.last_redone_time, -- Time when the last log record was redone on the secondary database.
DRS.log_send_queue_size,
DRS.redo_queue_size,
--Time corresponding to the last commit record.
--On the secondary database, this time is the same as on the primary database.
--On the primary replica, each secondary database row displays the time that the secondary replica that hosts that secondary database
-- has reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database
-- row represents approximately the recovery time objective (RPO), assuming that the redo process is caught up and that the progress
-- has been reported back to the primary replica by the secondary replica.
DRS.last_commit_time
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR
ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS
ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
),
Pri_CommitTime AS
(
SELECT DBName
, last_commit_time
FROM AG_Stats
WHERE role_desc = 'PRIMARY'
),
Rpt_CommitTime AS
(
SELECT DBName, last_commit_time
FROM AG_Stats
WHERE role_desc = 'SECONDARY' AND [InstanceName] = 'InstanceNameB-PrimaryDataCenter'
),
FO_CommitTime AS
(
SELECT DBName, last_commit_time
FROM AG_Stats
WHERE role_desc = 'SECONDARY' AND ([InstanceName] = 'InstanceNameC-SecondaryDataCenter' OR [InstanceName] = 'InstanceNameD-SecondaryDataCenter')
)
SELECT p.[DBName] AS [DatabaseName], p.last_commit_time AS [Primary_Last_Commit_Time]
, r.last_commit_time AS [Reporting_Last_Commit_Time]
, DATEDIFF(ss,r.last_commit_time,p.last_commit_time) AS [Reporting_Sync_Lag_(secs)]
, f.last_commit_time AS [FailOver_Last_Commit_Time]
, DATEDIFF(ss,f.last_commit_time,p.last_commit_time) AS [FailOver_Sync_Lag_(secs)]
FROM Pri_CommitTime p
LEFT JOIN Rpt_CommitTime r ON [r].[DBName] = [p].[DBName]
LEFT JOIN FO_CommitTime f ON [f].[DBName] = [p].[DBName]
SQL Server – Check Always-ON replica States
Below is the script to check Always-ON replica states
SELECT
GETDATE() AS DateTimeCaptured
,r.replica_server_name
,s.role_desc
,d.synchronization_state_desc
,d.log_send_queue_size
,d.log_send_rate
,d.redo_queue_size
,d.redo_rate
,d.truncation_lsn
,d.last_sent_lsn
,d.last_sent_time
,d.last_received_lsn
,d.last_received_time
,d.last_hardened_lsn
,d.last_hardened_time
,d.last_redone_time
,d.last_commit_time
,d.low_water_mark_for_ghosts
FROM sys.availability_replicas r INNER JOIN
sys.dm_hadr_availability_replica_states s
ON r.replica_id=s.replica_id INNER JOIN
sys.dm_hadr_database_replica_states d
ON s.replica_id=d.replica_id
ORDER BY s.role_desc, r.replica_server_name DESC
SQL Server – Check Disk Usage
Below is the script I use to check disk usage.
USE [master]
begin
set nocount on
/*******************************************************/
/* Enabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 1
RECONFIGURE with override
/*******************************************************/
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
drive, cast(TotalSize/1024.0 as decimal(8,2)) as 'Total(GB)', cast(FreeSpace/1024.0 as decimal(8,2)) as 'Free(GB)',cast(TotalSize/1024.0-FreeSpace/1024.0 as decimal(8,2)) as 'Used(GB)',cast((TotalSize/1024.0-FreeSpace/1024.0)/(TotalSize/1024.0)*100 as decimal (8,2)) as 'PercentUsed'
FROM #drives
ORDER BY drive
DROP TABLE #drives
/*******************************************************/
/* Disabling Ole Automation Procedures */
exec sp_configure 'show advanced options', 1
RECONFIGURE with override
exec sp_configure 'Ole Automation Procedures', 0
RECONFIGURE with override
/*******************************************************/
end
SQL Server – Identifying Current Running Queries with Database Name
Use the script below to get the current running queries on your server.
SELECT sqltext.TEXT ,DB_Name(req.database_id) as DatabaseName ,sess.last_request_start_time ,req.total_elapsed_time/1000 as total_elapsed_time ,sess.session_id AS SessionID ,req.blocking_session_id AS BlockedBy ,req.command ,req.cpu_time AS CPU_Time ,sess.HOST_NAME as RunningFrom ,sess.login_name ,wait_type ,wait_time ,sess.status ,req.reads ,req.writes ,req.logical_reads ,sess.program_name FROM sys.dm_exec_requests req INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id AND sess.is_user_process = 1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext WHERE DB_Name(req.database_id) NOT IN ('master','tempdb') ORDER BY total_elapsed_time DESC