What is Server-side tracing?
Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.
Here is how you view the number of traces currently in the system and the details of the trace:
Number of traces(active and stopped) currently in the system:
select * from sys.traces
Here is how you view the active(trace is running) traces in the system:
SELECT * FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1
You can stop and terminate a trace by executing the following:
EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.
I had to find all the jobs that either least run(one in 4 months) or never executed on all our SQL environments. i wrote the below script that pulls up that information.
SELECT Jb.Name AS [Job Name], CASE WHEN Jb.[Enabled] = 1 THEN ‘Yes’ ELSE ‘No’ END AS [Enabled], Jact.LastRun AS [Last Run Date], Jact.NextRun AS [Next Run Date]
FROM msdb.dbo.sysjobs Jb
JOIN msdb.dbo.sysjobs_view Jv
ON Jb.Job_ID = Jv.Job_ID
SELECT Job_ID, MAX(Last_Executed_Step_Date) LastRun, MAX(Next_Scheduled_Run_Date) NextRun
GROUP BY Job_Id
ON Jb.Job_ID = Jact.Job_ID
JOIN msdb.dbo.syscategories JC
ON Jb.Category_ID = JC.category_id
WHERE DATEDIFF(day, ISNULL(LastRun, ‘1900-01-01’), GETDATE()) > 120
OR NextRun < GETDATE()
Snapshot of the outcome below.
While doing an index analysis activity i had to find the indexes are hardly being used in our production DB’s. I wrote this script to acheive the same
SELECT case when os.is_published = 1 then 'Replicated' else 'Not Replicated'
end as ReplicatedorNot,
OBJECT_NAME(idx.object_id) AS TableName ,
idx.name as Index_name,
FROM sys.dm_db_index_usage_stats AS istat
JOIN sys.indexes AS idx ON idx.index_id = istat.index_id
join sys.objects os
on idx.object_id = os.object_id
AND idx.object_id = istat.object_id
istat.database_id = DB_ID('databasename') -- type the name of the Db
and object_name(idx.object_id) not like 'Ms%'
and object_name(idx.object_id) not like 'sys%'
and object_name(idx.object_id) not like 'id_%'
and object_name(idx.object_id) not like 'conflict%'
and object_name(idx.object_id) not like 'Any other tables that you like to avoid%'
AND idx.is_unique_constraint = 0 -- no unique indexes
AND idx.is_primary_key = 0
AND idx.is_disabled = 0
AND idx.type > 1 -- don't consider heaps/clustered index
AND ( ( istat.user_seeks + istat.user_scans +istat.user_lookups ) < istat.user_updates
or (istat.user_seeks = 0 AND istat.user_scans = 0 AND istat.user_lookups = 0))
order by (istat.user_seeks + istat.user_scans +
Recently i was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed the below script which will help us to locate the primary keys in the database which are not clustered index.
SELECT idx.name AS IndexName
, fg.name AS Filegroup
, object_name(idx.object_id) AS TableName
WHEN index_id = 1 THEN
WHEN index_id = 0 THEN
END AS TypeOfIndex
WHEN idx.is_primary_key = 1 AND index_id > 1 THEN
‘Primary key as Unique Non Clustered Index’
WHEN idx.is_primary_key = 1 AND index_id = 1 THEN
‘PK and Clustered’
END AS PrimaryKeyAsNonCLIndex
JOIN sys.indexes idx
ON fg.data_space_id = idx.data_space_id
JOIN sys.objects O
ON object_name(idx.object_id) = O.name
object_name(idx.object_id) NOT LIKE ‘sys%’
AND idx.is_primary_key = 1
AND index_id > 1
SELECT IDENT_SEED(TABLE_NAME) AS SeedValue,
IDENT_INCR(TABLE_NAME) AS IncrementValue,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity_Value,
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’
The below script helps us to find all the foreign keys in a database and there details. i have used AdventureWorks2012 db as example.
SELECT fk.name AS ForeignKey,
IsNotForReplication = case when fk.is_not_for_replication = 0 then ‘No’ else ‘yes’
OBJECT_NAME(fk.parent_object_id) AS TableName,
fkc.parent_column_id) AS ColumnName,
OBJECT_NAME (fk.referenced_object_id) AS ReferenceTableName,
fkc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fk.OBJECT_ID = fkc.constraint_object_id
order by IsNotForReplication
A snapshot of the outcome below:
In this article I will share the script that I use while dropping a table from an existing publication.
Below is the script that helps us achieve the same.
use [databasename]--Name of the DB in replication
exec sp_dropsubscription @publication=N'NameOfPublication',
@article=N'TestTable',--name of the table to be dropped
exec sp_droparticle @publication = N'NameOfPublication',
@article = N'TestTable',--name of the table to be dropped
@force_invalidate_snapshot = 1
The details for the stored procedure sp_dropsubscription can be found in the below link