Instance Information : set linesize 300 select name||’ – ‘||instance_name||’@’||host_name||’ – ‘||DB_UNIQUE_NAME||’ – ‘||version||’ – ‘||open_mode||’ – ‘||to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) || ‘ – ‘|| database_role from v$database, v$instance; FRA Usage : set linesize 300 col file_type for a40 select * from V$FLASH_RECOVERY_AREA_USAGE; Check Restore Point :… Read More
Quickly Check SQL Server CPU Pressure Following query results how many threads are waiting to be executed on the server, if there are more than two threads waiting to be executed on a server, then that server can be assumed to have a CPU bottleneck… Read More
Live Insert Counts Without Disturbing Running Transaction There are times long running insert transactions are annoying and we don’t get direct details of running transaction like how many rows inserted and how many are pending. Following query gets estimated rows inserted for the specific table mentioned. SELECT… Read More
Check Active Queries and Related Details Many times DBAs need to check currently active queries on the DB servers and other details like estimated completion time, blockings, query plan, query text, host name, user login details etc. Following query generate such detail for active queries.… Read More
Expensive Queries of a database Following Query will find the expensive queries on specific database on SQL Instance. select DB_NAME(s.dbid) DBNAME , t.Text, spid from sysprocesses s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t where DB_NAME(s.dbid) like ‘Database_Name%’ Thank you for giving your valuable time… Read More
AlwaysOn SQL Agent Alerts Please find below script to create SQL Server Agent Alerts. Please note that operator name should be changed according to your SQL operator. The below script will provide one output, you will have to execute the out again to create the… Read More
Capture SQL Server Important Details Please find the below script to capture SQL Server Important Details before upgrade, migration etc. PRINT ‘***************** Server Name ********************’ SET NOCOUNT ON select @@SERVERNAME go PRINT ‘***************** Version ********************’ SET NOCOUNT ON select @@version go PRINT ‘***************** License Information********************’… Read More
Find the Database Restore History On SQL Instance, at times we should know what DB’s got restored on specific instance or want to know when specific DB got restored. Below query will help to achieve that. SELECT * FROM MSDB..RESTOREHISTORY ORDER BY RESTORE_DATE DESC … Read More
Find Percent Completion of Process This script is to find the percent of completion of backup, restore, DBCC commands etc. SELECT req.session_id, sqltext.TEXT, req.status, req.command, req.wait_type, req.wait_time, (req.estimated_completion_time/1000/60) as est_min, req.percent_complete FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext join sys.dm_exec_sessions ses on ses.session_id =… Read More
Find Objects and Row count of each table in Database create table #tablesize (name nvarchar(120),rows char(11),reserved varchar(18), data varchar(18),index_size varchar(18),unused varchar(18)) go insert into #tablesize exec sp_msforeachtable @command1= ‘exec sp_spaceused ”?”’ select * from #tablesize go drop table #tablesize Thank you for giving your… Read More
