Disk drive full-Troubleshooting

1. We will purge the old data or files

2. We will raise request to Wintel Team to add more space

3. If database log file occupied more space will shrink the log file process

Performance issues

1. CPU Utilization We will check the performance of CPU usage of the server in Task Manager

2. Memory We will check memory manager counters

3. Disk IO We will check Disk IO counters

4. Blocking

i. We will identify if blocking is really present or not? 

Select * from sys.sysprocesses where blocked<>0

ii. After identifying the SPID's, find out which SPID is doing what task 

DBCC INPUTBUFFER (SPID1) 

DBCC INPUTBUFFER (SPID2)

iii. Identify which SPID belongs to which application/login/network address 

Select * from sys.sysprocesses where spid=<SPID1>

iv. Priority should be chosen based on Time started, CPU Time, Memory usage, IO Usage 

sp_who2 

v. If confused on the priority immediately escalate to respective application Team Lead/Application Contact Person. Also approach any escalation in your team.

5. Deadlock

1. As per the request we will enable traces and find the deadlock issues. 

DBCC traceon(1204) 

DBCC traceon(1222)

2. Please stop the trace, once monitoring is completed. Else it would lead to performance issues. 

DBCC traceoff(1204) DBCC traceoff(1222)

3. Enable Traces or Profiler so that the deadlock occurrence reason can be found Runàprofiler (2000)/profiler 90 (2005)

4. DBCC TRACEON(1204) – will write error to SQL Server Log`s

5. DBCC TRACEON (1222,-1)- will write error to XML file

6. Finding details through Profiler Profileràlocksàdeadlock chain àdeadlock graphàsel batch completedàsel batch stared àRPC completed. Inform Application team that they have to run the query again so that you can enable the trace and then find the reason for deadlock (if it occurs again). Explain application team that DBA Team can troubleshoot only when trace/profiler are enabled.

7. If trace 1204 is enabled information is written into SQL Server logs. · SPID of both processes Sp_who sp_who2 select *From sys.sysprocess · what queries they were running dbcc inputbuffer(spid) · For how long that deadlock occurred By default 5 seconds detected the deadlock · Which process was killed Dead loc priorities

8. Finally once the information is handy work with Application team in modifying/code enhancements in the queries used for the application.

9. Additional option is to set event "Deadlock Graph" under Locks segment in Profiler and monitor the process, object, query and much more additional information about the deadlock. Profiler -àlocksàdeadlock chainàdeadlock Graph

10. Additional Points: DBA/App Team/Developers can control the deadlock priority using SET DEADLOCK_PRIORITY [LOW|NORMAL|HIGH]

How is the instance performing Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”. 7. How many logins/sessions are connected SELECT login_name, count(session_id) as session_count FROM sys.dm_exec_sessions GROUP BY login_name 8. What is the current load on the system Open Task Manager, switch to Performance tab, you will find your entire four cores are listed.

1. Run a single task; watch the changes of the CPU Usage History of every core.

 
2. You can find the load may switch between cores.

 
3. Switch to Processes tab, right-click the task’s process, chooses Set Affinity. You will find all four cores are selected.

 
4. You can uncheck three cores, click Ok.

 
5. Switch back to Performance tab, check if the task load switches between cores. You may close and rerun the task to watch the changes.

Sql server stopped

1. Ping the server and check the status

2. Check whether services are running well - services.msc

3. Make sure the TCP end points are configured properly 

4. Firewall could be one of the reasons.

5. Check with the authentication & user privileges.

6. Restart the instance after confirming the Trace Flags.-t3608 (optional)

7. This can have many reasons and right reason can be found only when we look into Event viewer and what has caused SQL Server down.  

SQL_Login_Expired_script (T-SQL Script to find When will a SQL login password expire?)

-- When will a SQL login password expire?

SELECT SL.name AS LoginName
      ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
      ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration
        ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration'))
                   , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration
      ,SL.is_policy_checked AS IsPolicyChecked
      ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired
      ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange
      ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked
      ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime
      ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount
      ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime
      ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC

To change all databases to Simple Recovery except system databases

USE MASTER

go
declare
      @isql varchar(2000),
      @dbname varchar(64)
     
      declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
      open c1
      fetch next from c1 into @dbname
      While @@fetch_status <> -1
            begin     
            select @isql = 'ALTER DATABASE @dbname SET RECOVERY simple'
            select @isql = replace(@isql,'@dbname',@dbname)
            print @isql
            exec(@isql)
           
            fetch next from c1 into @dbname
            end
      close c1
      deallocate c1