Clota Blog

  • 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; –If version store is very large, need to find out…

    Read More

    Abhishek Yadav

    //

  • 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 for different installation…

    Read More

    Abhishek Yadav

    //

  • Basic Tools in SQL Server

    Basic Tools in SQL Server

    Read More

    Abhishek Yadav

    //

  • 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 can be utilized to repair a failure and recover the…

    Read More

    Abhishek Yadav

    //

  • 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…

    Read More

    Abhishek Yadav

    //

  • 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…

    Read More

    Abhishek Yadav

    //

  • 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”)

    Read More

    Abhishek Yadav

    //

  • 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…

    Read More

    Abhishek Yadav

    //

  • 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

    Read More

    Abhishek Yadav

    //

  • 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…

    Read More

    Abhishek Yadav

    //

  • 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”,…

    Read More

    Abhishek Yadav

    //

  • 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,…

    Read More

    Abhishek Yadav

    //

Search

Categories

Recent Posts