-
Important DMVs for Database Mirroring…
Below are few important DMVs for Database mirroring…. select * from sys.dm_db_mirroring_connectionsselect connection_id,* from sys.dm_exec_connectionsselect * from sys.database_mirroringselect * from sys.database_mirroring_endpointsselect * from sys.database_mirroring_witnesses
//
-
SQL Server AlwaysON Health Check…
–Below SQL Script can we use for check SQL Server AG HEALTH USE [TempDB] SET NOCOUNT ON GO SELECT CurrentTime_UTC = SYSUTCDATETIME() DECLARE @XELTarget VARCHAR(MAX); DECLARE @XELPath VARCHAR(MAX); DECLARE @XELFile VARCHAR(max); IF EXISTS(SELECT name FROM sys.dm_xe_sessions WHERE name = ‘AlwaysOn_Health’) BEGIN SELECT @XELTarget = cast(xet.target_data AS XML).value(‘(EventFileTarget/File/@name)[1]’, ‘VARCHAR(MAX)’) FROM sys.dm_xe_sessions xes INNER JOIN sys.dm_xe_session_targets xet ON xes.address = xet.event_session_address WHERE xet.target_name = ‘event_file’ and xes.name = ‘AlwaysOn_Health’…
//
-
SQL Server tempDB usage…
–Below script is for check tempDB usage ——————————————————————————– use tempdb go select * from Sys.dm_db_file_space_usage –Is the version store taking up a lot of space? SELECT version_store_Gb = (SUM(version_store_reserved_page_count)*8/1024./1024.) , total_tempdb_Gb = sum(total_page_count)*8/1024./1024. , pct_of_tempdb_dedicated_to_version_store = round((sum(version_store_reserved_page_count)*1./sum(total_page_count)*1.)*100.,2) FROM sys.dm_db_file_space_usage;…
//
-
SQL Server Installation Type on a Server
There are basically two types of installation in SQL Server… In Stand alone Installation again there are two different ways… SQL Server Services Configuration Manager Tool view…
//
-
-
Database Backup and its types
Hey guys, In this blog I am going to explain you about Database backup and its types. Backup- A backup of the SQL Server data that…
//
-
Cluster node was removed from the active failover cluster membership
Below was the cluster setup where the issue came… Error messages which logged in Cluster error log… Cluster node ‘ABCD-xyz05’ was removed from the active failover…
//
-
SQL Server Security Check List
Data is very crucial aspect of any organization or entity. Loosing, stole or destroy no one want and most of time can not effort. Below…
//
-
Powershell Script to Drop Logins across multile servers.
[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $Error.Clear() cls $servers = Get-Content E\LoginNames\Servers.txt $logins = @(login name to be dropped) foreach($server in $servers) { $srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $server #drop database users foreach($database in $srv.Databases) { foreach($login in $logins) { if($database.Users.Contains($login)) { $database.Users[$login].Drop(); } } } #drop server logins foreach($login in $logins)…
//
-
Get SQL Server version information installed on multiple servers
$start = get-date write-host “Start: ” $start [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null ## Specify path of output file $FilePath = “D:\Version\” $OutFile = Join-Path -path $FilePath -childPath (“SQLVersions_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”) ## Output file name will be like “X:\Monitoring_Automation\ SQLVersions_20130906_1248AM.log” # Below is loop…
//
-
Make delete operation fast using CTE.
–drop table t1 create table t1 (a int primary key, b char (100)) declare @i int set @i=1 SET NOCOUNT ON while (@i<100000) begin insert into t1 values (@i,’x’); set @i=@i+1 end ———————————————————- ———————————————————– set statistics io on set statistics time on delete from t1 where a in (select top (10000) a from t1 order by a); set statistics time off set statistics io off ———————————————————– set statistics io on set statistics time on ;with d as ( select top (10000) a from t1 order by a…
//
-
Why are spids in suspended state.
SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, ”) AS login_name ,COALESCE(es.host_name,”) AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,’MISCELLANEOUS’) AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,’MISCELLANEOUS’) AS lastwaittype ,COALESCE(er.wait_resource,”) AS waitresource ,coalesce(db_name(er.database_id),’No Info’) as dbid ,COALESCE(er.command,’AWAITING COMMAND’) AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN ‘Unspecified’ WHEN 1 THEN ‘Read Uncommitted’ WHEN 2 THEN ‘Read Committed’ WHEN 3 THEN ‘Repeatable’ WHEN 4 THEN ‘Serializable’ WHEN 5 THEN ‘Snapshot’…
//