Understanding the SQL Server error Logs

Error Logs name itself defines as it will logs the error events raised by SQL Server database engine or SQL Server Agent.

 Error Logs are the main source to troubleshooting the SQL Server problems.

 SQL Server supports 2 types of error logs

                * SQL Server Logs

                * SQL Agent Logs

What is recorded exactly 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 per instance.

Things about Error Logs everyone should know

How to View SQL Server Error Log file location

EXEC xp_readerrorlog 0,1,"Logging SQL Server messages in file";

You can change the default location of SQL Server error logs by using Startup parameter -e
“-e C:\Logs\NewLogs”

How to see the error logs location without SSMS
Open SQL Server Configuration Manager
Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008) (or 2008 R2) > Configuration Tools > SQL Server Configuration Manager

How to view the SQL Server error log using management studio –
Connect to SQL Server > In Object Explorer > Expand a server > Expand Management > and then Expand SQL Server Logs. Right-click a log and click View SQL Server Log.

By Default SQL Server maintains 6 Error log files only. Default settings can be changed to any number between 6-99.

To change the default settings
Connect to SQL Server > In Object Explorer > Expand a server > Expand Management > and Right-click > Configure > Check the Limit box and change.

There are problems with the size of error logs. One error log file can grow up to any limit if SQL server is not restarted since a long time. On the other hand, a file can be very small if SQL server is restarted frequently.

As a best practice – SQL Server error logs can be recycled by creating a SQL Job which runs at a regular interval. That’s how you can prevent the problems of loading or reading the error log.

SQL Server error logs can give you information about machine type. Use the script below.

 EXEC xp_readerrorlog 0 ,1 ,"Manufacturer";

 SQL Server error logs can give you the port number which is used by SQL Server

 EXEC xp_readerrorlog 0 ,1 ,"Server is listening on";

SQL Server error logs can give you information about Startup Parameter that are used by SQL Server

EXEC xp_readerrorlog 0,1 ,"Registry startup parameters";

SQL Server error logs can give you information about Dedicated admin connection is used by SQL Server

 EXEC xp_readerrorlog 0 ,1 ,"Dedicated admin connection support";

SQL Server error logs can give you information about OS Process ID used by SQL Server

EXEC xp_readerrorlog 0,1 ,"This instance of SQL Server last reported using a process ID";

SQL Server error logs can give you information about SQL Server authentication Mode

EXEC xp_readerrorlog 0,1 ,"Authentication mode";

Grant User Access to All SQL Server Databases at a time

Problem :

Need to grant user access to all SQL Server databases where  large number of databases exist

Solution :

Use a simple cursor with the LOCAL and FAST_FORWARD options to optimize its performance. 

LOCAL - Specifies that the cursor can be available only in the batch in which the cursor was created, and will be de-allocated once the batch terminates.

FAST_FORWARD - Specifies that the cursor produces the least amount of overhead on SQL Server by choosing the lowest cost static or dynamic plan.

STEP 1: Create the Login

USE [master]

GO
CREATE LOGIN [DemoUser]

      WITH PASSWORD = N'DemoUser@123'

            ,DEFAULT_DATABASE = [master]

            ,CHECK_EXPIRATION = OFF

            ,CHECK_POLICY = ON

GO

STEP 2 - Get a list of all user databases in SQL Server instance, excluding the system databases (master, model, msdb, tempdb and distribution) from the sysdatabases table.

STEP 3 - Once the databases list is ready, loop through thes database to create a user for that login created in STEP 1 and grant it read and write access on each database.

USE master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT NAME FROM MASTER.dbo.sysdatabases
WHERE NAME NOT IN ('master','model','msdb','tempdb','distribution')
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @statement = 'use ' + @dbname + ';' + 'CREATE USER [DemoUser] 
FOR LOGIN [DemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'
      EXEC sp_executesql @statement
      FETCH NEXT
      FROM db_cursor
      INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

STEP 4 - Execute the scripts that was generated to create the user and grant permissions in new query window.

Now you can cross check the access for 1 or 2 databases.

SQL Server Performance Tuning Part 5

                                                   LOCKS

Locks:

 SQL Server holds a specific object (tables, database, pages, rows, instance, extent, key……etc) by using this locking concept.

 Locks are very useful to provide consistence data or right data or correct data to the end user

 SQL Server cannot lock the resources.

Note: Lock internally managed by lock manager and takes the decision depend on the transaction what lock to be applied.

LOCK RESOURCES:

ROW LEVEL:  Row identifier used to lock a single row with in a table.

PAGE LEVEL:  8 kilo bytes (kb) data pages or index pages.

(The page is nothing but the fundamental unit of data storage in SQL Server where actual data present)

EXTENT LEVEL: Contiguous group of eight data pages or index pages.

( An extent is a collection of eight physical contiguous pages. )

TABLE LEVEL:  Entire table including all data index.

DATABASE LEVEL: Database

KEY LEVEL: row lock with in an index used to product key ranges in serializable transaction.

How to find locks:

SP_LOCK

       

OR

SELECT * FROM SYS.DM_TRAN_LOCKS

               

Output Columns:

Resource type [Database or Page or Object or Row or Extentor table]

Request mode [Lock type]

Request type

Request status [Grant or Wait]

Request Session id

How will u find out which session is doing what work?

SP_WHO2

TYPES OF LOCKS:

1. Shared lock[S]: Multiple users can able to read the data on specific resource. No transaction or query need to wait.

 When transaction starts internally lock manager applies shared lock and once reading completed lock revoked automatically.

2. Exclusive Lock[X]: When we perform any insert and delete operations then an exclusive lock (X) will be placed on the resource.

Note: Always lock manager gives the priority to DML operations compare to any select queries.

3. Update Lock [U]: Whenever we perform any update operations then update lock placed in SQL Server.

Update lock calls most of the time exclusive lock (X) by lock manager.

4. Schema Lock (SCH-L): When performing any locks at schema table then lock manager raise Schema level lock.

5. Bulk Update [BU]: Bulk update lock generally placed by lock manager when there are any bulk transactions.

Ex: Insert into, bulk into, select into

6. Intent lock: Indented to apply desired lock on a  particular lock.

3 Types:

1. Intent Shared [IS] -- Indented to read the data

2. Intent Exclusive [IX] -- Intended to write the data

3. Shared with Intent Exclusive [IS] --

Lock Escalation: Process of converting a lot of low level locks such as row level , page level locks into higher level locks such as table level of row level is called as Lock Escalation i.e .

 Instead of multiple row level locks better is table level lock. Which reduces number of locking types and improves the performance by escalating lock

 Instead of multiple page level of locks better is database level lock.

 This decision of Escalation is taken by SQL Server Engine.

 SQL Server supports escalating the locks to the table level. The locks can only be escalated from rows to the table or pages to the table level.

RID --> Pages --> Tables --> DB

Note: In SQL Server locks  can be maintain by lock manger Users or DBA does not have any consoling locking system .