SQL Server – Script to Backup a Single Table

Script to backup a single table:

 

select * into newtablename from oldtablename

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

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]

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

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

 

 

 

 

 

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

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

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter

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

 

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter