What is TRACE and How to configure, Use and Read Trace with Examples in SQL Server?

What is TRACE?

               A Trace allows you to track the specific and particular actions and event that performed against a SQL Server databases. A TRACE provide vital and valuable information and details for troubleshooting and monitor database issues, problems and tuning database engine performance.

Why we use SQL Trace?

·  Has someone deleted a table?

·  Are you trying to track auto grow events?  Problem scenarios such as Database auto grow and slow database recovery

·  When did SQL memory usage change? Read SQL Server Performance, memory pressure and memory usage for SQL Server memory analysis

·  SQL Server security changes?

The default trace has loads of information.

WHAT TYPE OF DATA IS AVAILABLE FROM THE DEFAULT TRACE?

 Object creation, object deletion, error events, auditing events, full text events

WHAT SORT OF EVENTS DOES THE DEFAULT TRACE FILE CAPTURE?

--returns full list of events

SELECT *  FROM sys.trace_events

--returns a full list of categories

SELECT * FROM sys.trace_categories

--returns a full list of subclass values

SELECT * FROM sys.trace_subclass_values

How many types of TRACE categories?

1.      Cursors

2.      Database

3.      Errors and Warnings

4.      Locks

5.      Objects

6.      Performance

7.      Scans

8.      Security Audit

9.      Server

10.  Sessions

11.  Stored Procedures

12.  Transactions

13.  TSQL

14.  User configurable

15.  OLEDB

16.  Broker

17.  Full text

18.  Deprecation

19.  Progress Report

20.  CLR

21.  Query Notifications

HOW DO I CHECK DEFAULT TRACE IS ON?

SELECT * FROM SYS.CONFIGURATIONS WHERE CONFIGURATION_ID = 1568

HOW DO I ENABLE DEFAULT TRACE?

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'default trace enabled', 1;

GO

RECONFIGURE;

GO

HOW DO I FIND THE DEFAULT TRACE FILE?

SELECT * FROM ::FN_TRACE_GETINFO(0)

HOW CAN I LIST OBJECTS DELETED IN THE LAST 24 HRS FROM A SPECIFIC DATABASE?

SELECT *

FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_154.trc',0) tf

INNER JOIN sys.trace_events te

ON eventclass = trace_event_id

INNER JOIN sys.trace_categories AS tc

ON te.category_id = tc.category_id

WHERE databasename = 'AdventureWorks' AND

objectname IS NOT NULL AND

te.category_id = 5 AND

te.trace_event_id = 47 

--SQL server- 47 trace event it is for Oject Deleted.

How to Create a Trace (SQL Server Profiler)

This topic describes how to use SQL Server Profiler to create a trace.

1.      Profiler -> File menu, clicks New Trace, and connects to an instance of SQL Server.

2.      In the Trace name box, type a name for the trace.

3.      In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.

4.      To save the trace results, do one of the following:

o        Click Save to file to capture the trace to a file. Specify a value for Set maximum file size. The default value is 5 megabytes (MB).

Optionally, select Enable file rollover to automatically create new files when the maximum file size is reached. You can also optionally select Server processes trace data, which causes the service that is running the trace to process trace data instead of the client application. When the server processes trace data, no events are skipped even under stress conditions, but server performance may be affected.

o        Click Save to table to capture the trace to a database table.

Optionally, click Set maximum rows, and specify a value.

When you do not save the trace results to a file or table, you can view the trace while SQL Server Profiler is open. However, you lose the trace results after you stop the trace and close SQL Server Profiler. To avoid losing the trace results in this way, click Save on the File menu to save the results before you close SQL Server Profiler.

5.      Optionally, select the Enable trace stop time check box, and specify a stop date and time.

6.      To add or remove events, data columns or filters, click the Events Selection tab.

7.      Click Run to start the trace.


Please provide your input if anything missing here- Jainendra Verma

SQL SERVER – How to create Linked Server in SQL Server and its important Commands.

 Linked Server provide option to access / connect data to other Data source like MS Access, Oracle, My SQL etc. To access other data source into SQL server instance. We can use Linked server option. 

Linked Server Information stored into Master Database.

To create Linked Server we have to follow below steps-

Using User Interface-

1.       Open SSMS(SQL Server Management Studio) and open server object(If not found this option then find it in ‘View’ menu )

2.       Then Right click on the Linked server and click on the ‘New Linked server’

3.       Then we need to provide required Stuff(Server name, Instance Name, Default database name, Data source type etc) and we can access data from newly connected Data Source.

Using SQL Query –

SQL Server has provided below stored procedure to createvLinked server.

SP Name - Sp_AddLinkedServer

Example-

EXEC sp_addlinkedserver

   @server = N'Jai_Linked_Server_Test1',

   @provider = N'Microsoft.ACE.OLEDB.12.0',

   @srvproduct = N'OLE DB Provider for ACE',

   @datasrc = N'C:\MSOffice\Access\Samples\MS_ACCESS_DATABASE.accdb';

GO

Important commands-

Tests the connection to a linked server: 

   sp_testlinkedserver Jai_linked_server_test1

       To know linked servers information:

            Select * from sys.servers

      To access table from different server:

          Select * from  Linkedservername.Databasename.dbo.Tablename

      To Delete created linked server:

     IF OBJECT_ID('Jai_linked_server_test1') IS NOT NULL

     EXEC master.sys.sp_dropserver 'Jai_linked_server_test1','droplogins'

SQL Server: 6 very Important DMV scripts to troubleshoot the issue in SQL Server

Please find the below important DMV scripts to troubleshoot the issue in SQL Server.

sys.dm_exec_requests

sys.dm_exec_sql_text

sys.dm_os_waiting_task

sys.dm_os_wait_stats

sys.dm_exec_sessions 

sys.dm_tran_locks

--Here is a sample script that shows wait information and the T-SQL currently running in each session where available:

SELECT      er.session_id,

            er.database_id,

            er.blocking_session_id,

            er.wait_type,

            er.wait_time,

            er.wait_resource,

            st.text

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

--Here is a sample script that shows all the information for waiting tasks with the T-SQL currently running where there is a session_id available:

SELECT      wt.*, st.text

FROM sys.dm_os_waiting_tasks wt LEFT JOIN sys.dm_exec_requests er

ON wt.waiting_task_address = er.task_address

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

ORDER BY wt.session_id

--The following sample script from Microsoft is a great way to check for cpu pressure by comparing signal wait times (cpu wait) with resource wait times:

Select signalWaitTimeMs=sum(signal_wait_time_ms)

      ,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum(wait_time_ms) as numeric(20,2))

      ,resourceWaitTimeMs=sum(wait_time_ms - signal_wait_time_ms)

      ,'%resource waits'= cast(100.0 * sum(wait_time_ms -signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2))

from sys.dm_os_wait_stats

-- wait stats workload script

DBCC sqlperf ('sys.dm_os_wait_stats',clear)

GO

exec usp_loopmarriageupdate

GO

SELECT * FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC

GO

SELECT session_id,cpu_time,total_elapsed_time

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID

--Move your t.log

USE master;

GO

ALTER DATABASE people

MODIFY FILE(NAME = people_log,FILENAME = N'h:\people_log.ldf')

GO

ALTER DATABASE people SET OFFLINE

GO

ALTER DATABASE people SET ONLINE

--Locking demo

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

GO

BEGIN TRANSACTION

SELECT * FROM people

WHERE personid = 'B95212DB-D246-DC11-9225-000E7B82B6DD'

--view the locks

SELECT request_session_id AS Session,

       resource_database_id AS DBID,

       Resource_Type,

       resource_description AS Resource,

       request_type AS Type,

       request_mode AS Mode,

       request_status AS Status

FROM sys.dm_tran_locks

--open a new window

UPDATE people SET dob = 0

-- check the locks again

-- check sys.dm_os_waiting_tasks

SELECT session_id,wait_duration_ms,wait_type, blocking_session_id, resource_description

FROM sys.dm_os_waiting_tasks

WHERE session_id = 54

SQL Server - How to resolve TempDB get FULL issue.

Please find the complete details to troubleshooting and resolving If the TempDB database get full:-

 Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
 Back up the TRANSACTION LOG FOR the DATABASE TO free
 up SOME LOG SPACE

 Reason for temp DB full.
 1. Heavy transaction activity
 2. Due to maintenance job (Index fragmentation etc)
 3. Due to inventory closing or any other such type activity 
 4. Mount Drive\ Volume does not have sufficient space to grow temp db log files.
 5. Auto growth is not enable in tempdb
 6. Bulk Operation

 How to check if TempDB database get FULL :-

  

--Log space usage

dbcc sqlperf(logspace)

 --Open Tran

dbcc opentran(tempdb)

-- VLF (Virtual Log File)

  use tempdb

  go

  dbcc loginfo()

 Status - 2 (active log)
 Status - 0 (Inactive Log)

•  To truncate or shrink the log file there should be continuous inactive log.

 Resolution –
1. Simple and effective solution is to re-start the SQL server but in production environment we do not have privilege to restart the SQL services.
 2. We can use below quires to shrink the log file -
  

    dbcc shrinkfile (templog, 0)

 3. Create new log file in some other volume and cab the existing one to stop the auto growth.
 4. Perform failover if it’s in cluster if shrinking will not resolve the issue and we have necessary approvals from business.

SQL Server – Delete the duplicate record (data) form table

 It is very easy to delete duplicate record from table in sql server. SQL Server always stores each tupple (Row) as unique into the table.

To see duplicate record, we can use the count function with group by clause with having in the condition.

 To delete the record to Max function with NOT IN keyword.

Just execute and see how it work to delete duplicate record  into table.

USE tempdb

GO

CREATE TABLE Jainendra_TestTable (My_ID INT, Rank_Col VARCHAR(50))

Go

INSERT INTO Jainendra_TestTable (My_ID, Rank_Col)

SELECT 1, 'First'

UNION ALL

SELECT 2, 'No Rank'

UNION ALL

SELECT 3, 'Second'

UNION ALL

SELECT 4, 'Second'

UNION ALL

SELECT 5, 'Second'

UNION ALL

SELECT 6, 'Third'

UNION ALL

SELECT 7, 'Five'

UNION ALL

SELECT 8, 'Second'

UNION ALL

SELECT 9, 'Five'

UNION ALL

SELECT 10, 'Nine'

UNION ALL

SELECT 11, 'Third'

GO

-- See the inserted data in create table

SELECT *

FROM Jainendra_TestTable

GO

-- Now below query is detecting duplicate records into table

SELECT Rank_Col, COUNT(*) TotalCount

FROM Jainendra_TestTable GROUP BY Rank_Col

HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

GO

-- Now below query is deleting the duplicate record into table

DELETE FROM Jainendra_TestTable

WHERE My_ID NOT IN

( SELECT MAX(My_ID) FROM Jainendra_TestTable GROUP BY Rank_Col)

GO

-- Selecting Data

SELECT *

FROM Jainendra_TestTable

GO

DROP TABLE Jainendra_TestTable

GO

If it is useful than please like and share it to other SQL Server learners