Clota Blog

Category: SQL Server

  • 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 cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster.…

  • 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 are the points which we need to consider if we are considering for Database security… 1. Physical Security2. OS Level Security3. SQL Instance Level Security4. Login Security 1. Physical Security …

  • 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) { if ($srv.Logins.Contains($login)) { $srv.Logins[$login].Drop(); } } } $FilePath = “E:\LoginNames\” $OutFile = Join-Path -path $FilePath -childPath (“logins_” + (get-date).toString(‘yyyyMMdd_hhmmtt’) + “.log”)

  • 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 to generate version of each server @(foreach ($svr in get-content “D:\Version\Monitoring_servers.txt”) { $s = New-Object “Microsoft.SqlServer.Management.Smo.Server” $svr $s | select Name, Version }) | export-csv -noType $OutFile $end = get-date  write-host “End: ” $end

  • 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 ) delete from d; set statistics time off set statistics io off

  • 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’ END ,COALESCE(es.cpu_time,0)     + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0)     + COALESCE(es.writes,0)     + COALESCE(er.reads,0)     + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,”) AS program_name ,es.login_time FROM sys.dm_exec_sessions es     LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id     LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id     LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid     LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id     LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address     CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1   and es.session_id <> @@spid ORDER BY es.session_id

  • SQL Server Black Box Analysis

    “SQL Server Black Box Analysis” script give you a MRI report of SQL Server. Haha ha… Script… create table #ServerStats (create_time datetime,component_type sysname,component_name sysname, state int,state_desc sysname, data xml) insert into #ServerStats execute sp_server_diagnostics — Overview — Note: this events component will always show a status of unknown select create_time as “Date”,        component_name as “Component”,   state_desc as “Status”   from #ServerStats — System select  ‘System’ as “System”, data.value(‘(/system/@systemCpuUtilization)[1]’,’bigint’) as “System CPU”,         data.value(‘(/system/@sqlCpuUtilization)[1]’,’bigint’) as “SQL CPU”,         data.value(‘(/system/@nonYieldingTasksReported)[1]’,’bigint’) as “Non-yielding Tasks”, data.value(‘(/system/@pageFaults)[1]’,’bigint’) as “Page Faults”, data.value(‘(/system/@latchWarnings)[1]’,’bigint’) as “LatchWarnings”   from #ServerStats  where component_name like ‘system’ — Memory select ‘Memory’ as “Memory”,        data.value(‘(/resource/memoryReport/entry[@description=”Working…

  • SQL Server Extended Events

    DROP EVENT SESSION (Transact-SQL) Drops an event session. SQL> DROP EVENT SESSION [Collect_Deadlocks_New] ON SERVER View all the event session that exists in SQL Server SQL> select * from sys.server_event_sessions Create a Dead Lock Ex. session in SQL Server 2008 use [master]; CREATE EVENT SESSION [Collect_Deadlocks_New] ON SERVER     ADD EVENT sqlserver.xml_deadlock_report(         ACTION (             package0.collect_system_time,             sqlos.task_time,             sqlserver.client_app_name,             sqlserver.client_hostname,             sqlserver.database_id,             sqlserver.is_system,             sqlserver.username               )                                             )     ADD TARGET package0.asynchronous_file_target(         SET filename           = N’C:\SQL\xE\Collect-Deadlocks_New\Collect-Deadlocks.xel’,             max_file_size      = (512),             max_rollover_files = (3)                                                   ) WITH (      MAX_MEMORY            = 4096 KB,      EVENT_RETENTION_MODE  = ALLOW_SINGLE_EVENT_LOSS,      MAX_DISPATCH_LATENCY  = 20 SECONDS,      MAX_EVENT_SIZE        = 0…

  • TDE in SQL Server

    Transparent Database Encryption (TDE) – It was developed with SQL Server 2008, and it is also available in Oracle database management systems. It is an encryption method that protects the core data in the database. The encryption method protects the data in the database by encrypting the underlying files of the database, and not the data itself.…