Script to find Index Details

--- Below query is used to find the Index Details in a Database

select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,

*

from

sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'LIMITED') As a

GO

--- Below query is used to find the Detailed Index Details in a Database

select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,i.name,

a.avg_fragmentation_in_percent,a.fragment_count,a.avg_fragment_size_in_pages,

a.page_count,a.record_count,a.avg_page_space_used_in_percent,

a.index_id,a.index_depth,a.index_level,

a.partition_number,a.index_type_desc,a.alloc_unit_type_desc,

a.min_record_size_in_bytes,a.max_record_size_in_bytes,

a.avg_record_size_in_bytes,a.compressed_page_count

from

sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'DETAILED') As a

JOIN sys.indexes AS i

ON a.object_id = i.object_id AND a.index_id = i.index_id

GO

--- DMV to check the Index Usage Statistics

select DB_NAME(database_id) DBName,OBJECT_NAME(object_id) Obj_Name,

* from sys.dm_db_index_usage_stats

where DB_NAME(database_id)='mywork'

order by last_user_scan desc

GO

TableSpace Utilization Report

Below  Script is used to find the Tablespace utilization details in a Oracle Database

select a.tablespace_name,a.Total_Size_MB,b.Free_Size_MB,

(a.Total_Size_MB-b.Free_Size_MB) Used_Size_MB,

round(((a.Total_Size_MB-b.Free_Size_MB)/a.Total_Size_MB)*100,2) Pct_Used,

round((b.Free_Size_MB/a.Total_Size_MB)*100,2) Pct_Free

--,b.tablespace_name

from

(select tablespace_name,sum(bytes/1024/1024) Total_Size_MB

from dba_data_files

group by tablespace_name) a,

(select  tablespace_name,sum(bytes/1024/1024) Free_Size_MB from dba_free_space

group by tablespace_name) b

where a.tablespace_name=b.tablespace_name

union all

select d.tablespace_name,

(d.free_mb+d.used_mb) total_mb,

d.free_mb,d.used_mb,

(d.used_mb/(d.free_mb+d.used_mb))*100,(d.free_mb/(d.free_mb+d.used_mb))*100

from

(select tablespace_name,round(sum(bytes_free/1024/1024),2) free_mb,

round(sum(bytes_used/1024/1024),2) used_mb

from v$temp_space_header

group by tablespace_name) d

 

TSQL to find Time lag between Principal and Mirror DB

---Below Script is used to find the time of oldest transaction log which was last sent to Mirroring DB (Time lag between Principal and Mirror DB)

--Below Script will append the Database mirroring to monitor table

--if the same is not updated in last 15 seconds (approx)

create table #dbmmonitor

(database_name varchar(100),role varchar(100),

mirroring_state varchar(100),witness_status varchar(100),

log_generation_rate int,unsent_log int,send_rate int,

unrestored_log int,recovery_rate int,transaction_delay int,transactions_per_sec int,

average_delay int,time_recorded datetime,time_behind datetime,local_time datetime)

insert into #dbmmonitor

Exec msdb..sp_dbmmonitorresults 'dbmir',0,1

declare @time_recorded datetime, @time_behind datetime

select @time_recorded=time_recorded,@time_behind=time_behind from #dbmmonitor

declare @hour int,@min int,@sec int,@day int

declare @old_unsent_trn varchar(100)

select @hour=(DATEDIFF(hh,@time_behind,@time_recorded))%24

select @min=(DATEDIFF(mi,@time_behind,@time_recorded))%60

select @sec=(DATEDIFF(ss,@time_behind,@time_recorded))%60

select @day=(DATEDIFF(ss,@time_behind,@time_recorded))/86400

select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)+':'+convert(varchar(20),@sec)

select @old_unsent_trn 'Oldest Unsent Transaction'

drop table #dbmmonitor

GO

---Using msdb.dbo.dbm_monitor_data table for results

exec msdb.dbo.sp_dbmmonitorresults 'dbmir',0,1

---

declare @local_time1 datetime,@end_of_log_lsn1 bigint,@failover_lsn1 bigint

declare @local_time2 datetime,@end_of_log_lsn2 bigint,@failover_lsn2 bigint

declare @min int,@hour int,@day int,@min1 int

declare @old_unsent_trn varchar(100)

select top(1) @local_time1=local_time,@end_of_log_lsn1=end_of_log_lsn,

@failover_lsn1=failover_lsn

from msdb.dbo.dbm_monitor_data

order by local_time desc

--select @local_time1 local_time1,@end_of_log_lsn1 end_of_log_lsn1

--,@failover_lsn1 failover_lsn1

select top(1) @local_time2=local_time,@end_of_log_lsn2=end_of_log_lsn,

@failover_lsn2=failover_lsn

from  msdb.dbo.dbm_monitor_data

where end_of_log_lsn=@failover_lsn1

order by local_time desc

--select @local_time2 local_time2,@end_of_log_lsn2 end_of_log_lsn2

--,@failover_lsn2 failover_lsn2

select @day=(DATEDIFF(ss,@local_time2,@local_time1))/86400

select @min1=DATEDIFF(mi,@local_time2,@local_time1)

select @hour=convert(int,@min1)/60

select @min=convert(int,@min1)%60

--select @day,@hour,@min1

select @old_unsent_trn=convert(varchar(20),@day)+':'+convert(varchar(20),@hour)+':'+convert(varchar(20),@min)

select @old_unsent_trn 'Oldest Unsent Transaction'

OpenDataSource OpenQuery OpenRowset

--- OpenDataSource Example for connectiong another SQL Server and collecting data

---(Provides ad hoc connection information as part of a four-part object name without using a linked server name)

select * from

opendatasource('SQLNCLI','Data Source=muralixp;Integrated Security=SSPI;Connect Timeout=10').master.sys.databases

select * from

opendatasource('SQLNCLI','Data Source=murali-HP;User ID=sa;Password=xxxxxxx;Connect Timeout=10').master.sys.databases

--- OpenDataSource Example for connectiong Excel Sheet and collecting data. Shall mention linked server if we get error

--- Provides ad hoc connection information as part of a four-part object name without using a linked server name

select * from

opendatasource('Microsoft.Jet.OLEDB.4.0',

'Data Source=I:\SQL Server Own Material\Testing\Stars_Info.xls;Extended Properties=EXCEL 12.0')...[Info$] ;

--- OpenRowSet Example for connection another SQL Server and collecting data

select a.* from

openrowset('SQLNCLI','Server=murali-hp;trusted_connection=yes;',

'select * from master.sys.databases') as a

select *

from openrowset('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=I:\SQL Server Own Material\Testing\Stars_Info.xlsx;',

'select * from [info$]')

--- OpenQuery Example, connecting to linked server and collecting data

select * from openquery([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid>1')

delete OPENQUERY ([MURALI-HP], 'SELECT employeeid FROM mssqltips.dbo.employee WHERE employeeid=6')

Scripts to Monitor Log Shipping

--- Useful Scripts for Log Shipping Monitor

select lmp.primary_server,lp.primary_database,lps.secondary_server,

lps.secondary_database,lp.backup_directory,

lp.backup_share,lp.monitor_server,lp.last_backup_date,

datediff(mi,lp.last_backup_date,getdate()) time_since_last_backup_min,

lp.last_backup_file,lp.backup_retention_period backup_retention_period_min,

lmp.backup_threshold backup_threshold_min,lmp.threshold_alert threshold_alert_min,

lmp.history_retention_period history_retention_period_min

from

msdb.dbo.log_shipping_primary_databases lp

join

msdb.dbo.log_shipping_monitor_primary lmp

on

lp.primary_id=lmp.primary_id

join

msdb..log_shipping_primary_secondaries lps

on

lmp.primary_id=lps.primary_id

select top 10 database_name,log_time,message,session_status

from msdb.dbo.log_shipping_monitor_history_detail

order by log_time desc

/*

Useful Scripts in Secondary Database Server

*/

select ls.primary_server,ls.primary_database,lsd.restore_delay,

DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,

lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,

lms.last_restored_file,

lsd.disconnect_users,ls.backup_source_directory,

ls.backup_destination_directory,ls.monitor_server

--,lsd.block_size,lsd.buffer_count,lsd.max_transfer_size

from

msdb.dbo.log_shipping_secondary ls

join

msdb.dbo.log_shipping_secondary_databases lsd

on lsd.secondary_id=ls.secondary_id

join

msdb.dbo.log_shipping_monitor_secondary lms

on lms.secondary_id=lsd.secondary_id

---Check Error Log of Log SHipping Databases

select led.database_name,led.log_time,led.message,led.sequence_number,

led.session_id,led.source

from msdb.dbo.log_shipping_monitor_error_detail led

select top 10 database_name,log_time,message,session_status

from msdb.dbo.log_shipping_monitor_history_detail

order by log_time desc

Database Backup Status Script

---Script to find the Database backup details in last 10 days

select bs.server_name, bs.database_name,convert(decimal(7,2),(bs.backup_size/1024/1024/1024)) backup_size_GB,

bmf.device_type ,

bs.backup_start_date,

bs.backup_finish_date,bmf.physical_device_name,

bs.first_lsn,bs.last_lsn,bs.checkpoint_lsn,bs.database_backup_lsn

from msdb.dbo.backupset bs join

msdb.dbo.backupmediafamily bmf

on bs.media_set_id=bmf.media_set_id

where bs.backup_start_date>=(GETDATE()-10)

--and database_name='Master'

order by bs.database_name,bs.backup_start_date

Useful Scripts in Database Mirroring

---Script to find which Databases are involved in Mirroring

select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,

mirroring_safety_level_desc,mirroring_partner_name,mirroring_partner_instance,

mirroring_witness_state,mirroring_witness_state_desc

from master.sys.database_mirroring

where mirroring_state is not null

---Detailed Script to find which Databases are involved in Mirroring

select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,

mirroring_safety_level_desc,mirroring_safety_sequence

mirroring_partner_name,mirroring_partner_instance,

mirroring_witness_state,mirroring_witness_state_desc,

mirroring_failover_lsn,mirroring_connection_timeout,mirroring_redo_queue,

mirroring_end_of_log_lsn,mirroring_replication_lsn

from master.sys.database_mirroring

where mirroring_state is not null

---Below script is used to check the connection details

---which are involed in Database Mirroring

select state_desc,connect_time,login_time,authentication_method,principal_name,

remote_user_name,last_activity_time,is_accept,login_state_desc,

receives_posted,sends_posted,total_bytes_sent,total_bytes_received,

total_sends,total_receives

from sys.dm_db_mirroring_connections

 

---Checking EndPoint Status involved in Database Mirroring

select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,

connection_auth_desc

from sys.database_mirroring_endpoints

---Run on Witness Server if the same exist to know the info abot Database

---involved in Mirroring

select database_name,principal_server_name,mirror_server_name,safety_level_desc,

partner_sync_state_desc,is_suspended,safety_sequence_number,role_sequence_number

from sys.database_mirroring_witnesses

---Below Stored Procedure returns status rows for a monitored database from the status table in

---which database mirroring monitoring history is stored (last 2 hours)

Exec msdb..sp_dbmmonitorresults 'dbmir',1,0

---Below Script returns status rows for a monitored database from the status table in

---which database mirroring monitoring history is stored (last 2 hours)

declare @dbmmonitor table

(database_name varchar(100),role varchar(100),

mirroring_state varchar(100),witness_status varchar(100),

log_generation_rate int,unsent_log int,send_rate int,

unrestored_log int,recovery_rate int,transaction_delay int,transactions_per_sec int,

average_delay int,time_recorded datetime,time_behind datetime,local_time datetime)

insert into @dbmmonitor

Exec msdb..sp_dbmmonitorresults 'dbmir',1,0

select database_name,

case role  when '1' then 'Principal' when '2' then 'Mirror' else role end role,

case mirroring_state  when '0' then 'Suspended' when '1' then 'Disconnected'

when '2' then 'Synchronizing' when '3' then 'Pending Failover'

when '4' then 'Synchronized' else role end mirroring_state,

witness_status,log_generation_rate,unsent_log,send_rate,unrestored_log,recovery_rate,

transaction_delay,transactions_per_sec,average_delay,time_recorded,time_behind,

local_time

from @dbmmonitor

order by local_time desc