SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent.

* Error Logs are main source for troubleshooting SQL Server problems.

* SQL Server supports 2 types of error logs

                * SQL Server Logs

                * SQL Agent Logs

FAQ : - What is recorded in error logs?

1.       SQL Server start up events including database recovery.

2.       Backup and restore details.

3.       Any failed SQL Server jobs

4.       User defined error message which has WITH LOG clause.

5.       Maintenance related DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC.

6.       Turning trace flags on or off.

7.       SQL Servers usage of a particular session for a long period of time.

8.       Starting and stopping Profiler traces

* By default SQL Server supports

                1 - Current Log

                6 - Archieve Logs

* Error logs are present in LOG folder of respective instance.

* We can read error logs using

                sp_readerrorlog

                xp_readerrorlog

* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using

                sp_cycle_errorlog

* We can configure up to 99 error logs

* How to Configure?

                * Go to Object Explorer

                * Management

                * R.C on SQL Server Logs

                * Configure

                * Select checkbox " Limit the no of error logs..................."

                                Max no of logs= 20

                * OK

* To filter the events from error log

                sp_readerrorlog 0,1,'Error',null

Value of error log file you want to read:

                                0 = current, 1 = Archive #1, 2 = Archive #2, etc...

Log file type: 1 or NULL = error log, 2 = SQL Agent log

Search string 1: String one you want to search for

Search string 2: String two you want to search for to  further refine the results

                5th         : Start Date

                     6th         : End Date

Scripts

WMI Error Solution

Copy the following script in notepad and save with "wmi.bat"
Double click on the file, WMI service is installed then continue setup.

@echo on 
cd /d c:\temp 
if not exist %windir%\system32\wbem goto TryInstall 
cd /d %windir%\system32\wbem 
net stop winmgmt 
winmgmt /kill 
if exist Rep_bak rd Rep_bak /s /q 
rename Repository Rep_bak 
for %%i in (*.dll) do RegSvr32 -s %%i 
for %%i in (*.exe) do call :FixSrv %%i 
for %%i in (*.mof,*.mfl) do Mofcomp %%i 
net start winmgmt 
goto End 

:FixSrv 
if /I (%1) == (wbemcntl.exe) goto SkipSrv 
if /I (%1) == (wbemtest.exe) goto SkipSrv 
if /I (%1) == (mofcomp.exe) goto SkipSrv 
%1 /RegServer 

:SkipSrv 
goto End 

:TryInstall 
if not exist wmicore.exe goto End 
wmicore /s 
net start winmgmt 
:End

Installing and Configuring SQL Server 2008

System Requirements

* O/S                      : Windows2003 with sp2/ 2008 Server

* Memory               : 1GB

* CPU                    : >1GHz

* HDD                    : 2684MB

                               : 611MB for database server

* SQL Native Client

* .Net Framework 3.5 with sp1

* Windows Services

                :WMI (Windows Management Instrumentation)

                :RPC (Remote Procedural Call)

                :DTC (Distributed Transaction Coordinator)

Installing SQL Server

* SQL Server supports 2 types of installations

                1. Stand Alone Environment

                2. Cluster based ,,

1. Stand Alone installation

Steps

                * Creating service account

                                * R.C on My Computer --> Manage

                                * Local users and groups

                                * R.C on Users

                                * New User

                                                Name: SQLDBEngine_user

                                                pwd   hyd@123

                                   Confirm p  hyd@123

                                                Uncheck "User must change...."

                                                Click on Create button

                * Go to SQL Server dump or use DVD

                * Double click on Setup

                * Click on "Installation"

                * Click on "New Stand Alone ........"

                * OK

                * Under Setup Support Files --> Click Install

FAQ: - I am installing SQL Server in d: drive where 20GB free space is available. But in system drive (c:)       

            there is 100MB free space. Setup continue or fail?

Ans:

                * Setup support files are installed in system drive where 118MB free space is required.

                 Setup fails.

                * Next

                * Under Installation Type select

                                Perform a new ............. option

                                Next

                * Next

                * Select "I Accept........"

                * Next

                * Under Feature Selection

                                select

                                                Database Engine Services

                                                SQL Server Replication

                                                Full Text Search....

                * Next

Note:

                1. First time, select shared features also

                2. Install SQL Server always in NTFS not in FAT32. NTFS provides high security and high      

                  performance.

               

                * Select "Named Instance and enter: TEST

                                Instance Root Directory: d:\SQLSERVER

                                Next

                * Next

                * Under "Server Configuration" Click on button

                                Use Same account..........

                                Enter

                                Account Name: SQLDBEngine_user

                                password      hyd@123

                                OK

                * Next

 FAQ:- Once I install SQL Server what are the logins created automatically?

Ans:

                SS 2005

                                * sa

                                * BUILTIN\Administrators

                SS 2008

                                * sa

                                * Login for service account

                                * Logins for added users in wizard

               

                * Under Database Engine Configuration

                                * Click on "Add Current User"

                * Next

                * Next

                * Next

                * Install

               

FAQ: - What are the differences in the installation of SS 2005 and SS 2008?

Ans

                * In SQL Server 2005 there is no

                                * Installation Center

                * Upgrading, edition change, rebuilding system databases etc we have to perform

                  by running setup from command prompt.

                                * There is no "ConfigurationFile.ini"

                                

                * Next

                * Close

Post Installation Steps

1. Verifying installation

                * We can verify installation process using Summary.txt file, present in

                                C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

 

2. Verifying instance folders

* For every instance the following folders are created in installation directory.

                MSSQL10.<instanceName>

                                MSSQL

                                                * BACKUP

                                                * Binn     - Consists of .exe and .dlls

                                                * DATA   - data and T.log files

                                                * FTData

                                                * Install - consists of .sql files

                                                * Jobs     - jobs and maintenance plans

                                                * Log       - Error logs

                                                * repldata- Replication snapshot folder

                                                * Upgrade (2008)

 3. Configuring Error logs

* Error logs maintains events raised by SQL Server.

* Consists of both errors and information.

* Error logs are present in LOG folder of respective instance.

* SS supports

                1 Current Log

                6 Archieve logs by default.

* Current Log has all the events from server restart.

* Once the server is restarted all the events are flushed from   current log into Archieve1 and from  

   Archieve1 to Archieve2 etc. This process is called recycling error logs.

* We can recycle error logs explicitly without server restart   using

                sp_cycle_errorlog

* We can read error logs using   sp_readerrorlog

* SS supports min 6 and max 99 error logs

* How to configure?

                * Go to SSMS --> Object Explorer --> Management

                * R.C on SQL Server Logs --> Configure

                                select checkbox "Limit no of ............"

                                Maximum logs..........: 20

                * OK

FAQ: - When I am starting SQL Server it was not started successfully. What are the possible   

            scenarios? and how to troubleshoot it?

Possible Scenarios

               

                1. Logon Failure

                                * Problem with service account.

                2. 17113

                                * Master files are moved or corrupted

                3. 3417

                                * After moving master files we have not granted read/write permissions on new folder

                                * Or any one startup parameter is missing.

How to troubleshoot?

               

                * Check windows event log

                                * start --> run --> eventvwr

                                * System

                                * In the right pane double click on Error

                * Using SQL Server error log

                                * Go to LOG folder of respective instance

                                * Open ERRORLOG in notepad and check for errors

Dead Locks in SQL Server

One thing that will you most certainly face at some time as a DBA is dealing with deadlocks.   A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL Server selects one of the processes as a deadlock victim and rolls back the statements.

You have two sessions that are updating the same data, session 1 starts a transaction updates table A and then session 2 starts a transaction and updates table B and then updates the same records in table A. 

Session 1 then tries to update the same records in table B.  At this point it is impossible for the transactions to be committed, because the data was updated in a different order and SQL Server selects one of the processes as a deadlock victim.

To further illustrate how deadlocks work you can run the following code in the Northwind database.

To create a deadlock you can issue commands similar to the commands below.
Step	Commands
1	--open a query window (1) and run these commands begin tran update products set supplierid = 2
2	-- open another query window (2) and run these commands begin tran update employees set firstname = 'Bob' update products set supplierid = 1
3	-- go back to query window (1) and run these commands update employees set firstname = 'Greg' At this point SQL Server will select one of the process as a deadlock victim and roll back the statement
4	--issue this command in query window (1) to undo all of the changes Rollback
5	--go back to query window (2) and run these commands to undo changes Rollback

Steps to capture dead lock information into error log file

1.       Enable trace flag 1204

            DBCC TRACEON (1204)

2.       Create the event alert for the error number 1205 so that it should send response to required operator.

3.    

          Capturing deadlocks with Profiler

a.    Start à Run à Profiler

b.    Go to File menu à New Trace

c.    Select Server Name

d.    Click on Options

e.    Connect to a database = Browse Server

f.     Yes

g.    Select Northwind (required database)

h.    OK

i.      Connect

j.     Enter Trace Name: Northwind_DeadLocks_Trace

k.    Use the Template : Tuning

l.      Select checkbox – Save to File à Save

m.  Select “Events Selection” tab

n.    Select checkbox – Show all events

o.    Under Locks node select DeadLock graph and DeadLock chain

p.    Run

q.    Go to SSMS à Run the above queries

--open a query window (1) and run these commands begin tran update products set supplierid = 2

-- open another query window (2) and run these commands begin tran update employees set firstname = 'Bob' update products set supplierid = 1

-- go back to query window (1) and run these commands update employees set firstname = 'Greg' At this point SQL Server will select one of the process as a deadlock victim and roll back the statement

r.     Stop trace in Profiler

s.    Under Event Class click on Dead Lock graph

Resources That Can Deadlock

---

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

• Locks. Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.
• Worker threads. A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result. For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. This results in a deadlock.
• Memory. When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.
• Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.
• Multiple Active Result Sets (MARS) resources. These resources are used to control interleaving of multiple active requests under MARS  
• User resource. When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.
• Session mutex. The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Before the task can run, it must have exclusive access to the session mutex.
• Transaction mutex. All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Before the task can run, it must have exclusive access to the transaction mutex.

Deadlock Detection

All of the resources listed in the section above participate in the Database Engine deadlock detection scheme. Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process:

• The default interval is 5 seconds.
• If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
• If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.
• If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

To help minimize deadlocks:

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
• Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
• Use snapshot isolation.
• Use bound connections.