Tag Archives: SQL Server

SQL Server Integration Services – Packages, Datasources and Connections

In this article we will see how we can create a SSIS project via visual studio and then create a package and linking the packages to data sources along with connections. For this demo i will be using SQL Server 2008. The steps remain same for SQL Server 2008 R2 and SQL Server 2012.

creating a SSIS project…

Step 1: Open SQL Server Business Intelligence Development Studio(BIDS). Go to File -> New -> Project








Step 2 : ON the new Project dialog box choose Integration Services Project. Give the Project a name and location as shown below. Click on OK. The  Project will appear on the Solution explorer. By default a package is already created named Package.dtsx

Step 3: let us rename this package as MyPackage1. Right click on the Package and select rename. Change the name to MyPackage.dtsx. Click Yes on the confirmation dialog box.
Step 4: We will create two more packages named DC.dtsx and DP.dtsx. Right click on SSIS package on solution explorer and click on New SSIS Package. A package would be automatically added with default name Package(n).dtsx. We will rename the package as DC.dtsx. Repeat the same steps and create another package named DP.dtsx. Click on save all button to save all the changes made.
IS6 IS7So this is how we will create a SSIS project with packages. In the next section we will create data sources for the project.

 creating data sources..

Step 1: Open the Project in BIDS. Right click on Data Sources and select New Data Sources. On the Data source wizard click on new and select a server name and database name. In this case we will choose AdventureWorks as our database.
IS8 IS9 IS10Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
IS11 IS12 In the next section we will create new package connections from the Data Sources we created.

Creating Connections

Step1 : Click any one of the packages. Right Click on the connection managers window and select New Connection from Data Source. Select the Data source we created name AdventureWorks. Click on OK.
IS13 IS14The new connection connection will now appear in the connection managers window for the package. We need to create the same for the rest of the two packages.



Extended Events in SQL Server 2012 – Part 1

In SQL Server 2008, Microsoft introduced a feature called Extended Events. Extended Events helped in collecting and analyzing event driven data about the SQL Server instance. Extended Events introduced lesser load than trace or profiler events on the server. But in SQL Server 2008 there was no GUI that allowed direct communication with the events. Complex T-SQL had to be written to fetch the information which was returned in xml format.

In SQL Server 2012, MS has introduced a GUI in the Management Studio (SSMS)  that allows working with Extended Events and viewing event related data a much simpler process. In this article we will understand and see step by step how we can create and manage Extended events via the GUI that is built in the SSMS.

Step 1: Connect to SQL Server 2012 instance -> Open Management Tab -> Open Extended Events -> Right Click on Sessions -> New Session
Step 2 -> In the new session dialog box type in a name of the session. In this case we will use ‘Session1’. For now we will not choose any template.

Step 3 -> Click on Events tab -> On the below window you will find many Events listed from which we need to pick an event or multiple events that we want to capture. Once selected we need to click on the button to add it. For this article we will use the Events
a) query_post_execution_showplan and b) sql_statement_completed
EE3AEE4Step 4 -> Click on Data Storage -> Here you specify how you can capture data so you can view it later. Here we will save it as Event File. Mention the path where we would save the file -> Click on OK.
EE5Step 5 -> Go to management studio -> Under sessions you will find New session listed. It would be in a stopped state as this is the default behavior. Right Click on the session and click on ‘Start Session
EE6EE7EE8Step 6 -> Now right click on the created session and select ‘Watch Live Data’. If there is no activity on the instance you will not see any data.
EE9EE10Step 7 -> Let us fire few queries on the AdventureWorks2012 database and watch the window. Following are queries that we would run on the database for testing. After running the queries go back and open the ‘Watch Live Data’ window again. Click on the Event data and observe the below.

USE AdventureWorks2012
SELECT * FROM Production.Product pp
WHERE pp.ProductID > 40
SELECT * FROM Production.BillOfMaterials Pb
WHERE pb.BillOfMaterialsID > 100

EE11EE12EE13In this article we have seen how we can start a new event trace from SSMS. In my next article we will explore more into the new GUI feature that MS has added to SQL Server 2012.

Resources in a SQL Server Failover Cluster

In this article I will discuss about the resources that are part of a SQL Server failover cluster.

A SQL Server failover cluster instance consists of 2 kinds of resources.

1)Local Resources: Local resources are those components that are installed on each possible owner node. They remain on that node and do not move over during a failover.
2)The shared resources: Those resources that fail over between the owner nodes.

Let us now go through the resources that are part of a resource group in a SQL Server failover cluster.
a) SQL Server Database Engine\Agent service : The database engine and SQL Server agent resources are always part of any SQL Server failover cluster’s resource group where the database engine has been installed. If SQL server analysis service is installed, then it will be a part of the resource group. From an availability and performance perspective it is better if both are installed on separate resource groups.

b) IP Address and Network name: The IP address and network name component ensures an interface that clients can use to connect to the SQL Server instance  irrespective of the fact that which node is currently serving the cluster. During a failover event,  the network name and IP addresses is registered to redirect to the new node that is serving the SQL Server failover cluster instance. To the clients that are connecting to the SQL Server, this is a absolutely transparent process.

c) Shared disks:A SQL Server shared disk contains all of the system and user data including databases, logs for the SQL Server failover cluster instance. When a failover occurs, the disks are mounted on the new node. When SQL Server instance is started on the new node, it goes through recovery as part of the SQL Server startup and maintains access to the same database files that existed when it was running on the previous node.The shared disks are the single point of failure for a failover cluster instance. Windows Server and SQL Server failover clustering provides redundancy for machines, operating systems, and SQL Server binaries, but requires reliable storage to assure availability of the shared storage.

How to restore a table from a Full backup using Quest Litespeed without restoring the entire database

In this article I will demonstrate how we can restore a table from a SQL Server database backup file which has been taken by Quest Litespeed tool. This feature called Object level recovery in the tool is extremely helpful as you need not restore the entire database and yet retrieve data from the backup. Follow the below mentioned steps and understand how this can be accomplished. I will be using a trial copy of Quest Litespeed along with SQL server 2012 and the database we would use is Adventureworks2012. However you can use SQL server 2008 also.

Step 1: Create a database and take a full backup of the database
Let us create a database for this purpose

USE mydb
INSERT INTO Mytable1 VALUES (1,'Sanchayan')
INSERT INTO Mytable1 VALUES (2,'Abhishek')
INSERT INTO Mytable2 VALUES (1,'Pandit')
INSERT INTO Mytable2 VALUES (2,'Agrawal')

Now take a Full Backup of the database.

exec master.dbo.xp_backup_database
@database = N'Mydb',
@compressionlevel = 3,
@filename = N'D:\MSSQL\SQLBackup\Mydb.bak',
@init = 0,
@OLRMAP = 1--This is important as this marks the backup for object level recovery

Step 2: Now run the following code to find the Tables available in the backup

Use Master
exec xp_objectrecovery_viewcontents
@FileName = N'D:\MSSQL\SQLBackup\mydb.bak',
@FileNumber = 1,
@Type = 'All',
@DisableLogProcessing = 1

Step 3: Now select the table that you wish to restore. In this demo we would restore MyTable2.
We would need the below predefined:
a) The database where we would restore the table.
b) A temp directory location which should be large enough for the table to be restored initially.

Use master
exec xp_objectrecovery
@FileName = N'D:\MSSQL\SQLBackup\Mydb.bak',
@FileNumber = 1,
@ObjectName = N'dbo.Mytable2',
@DestinationTable = N'[dbo].[Mytable2]',
@DestinationDatabase = N'MyTempDB',
@DestinationServer = N'GN-A-MOOKBO-7L\sql2012',
@TempDirectory = N'D:\MSSQL\SQLBackup',
@DisableLogProcessing = 1

The output of the above code would be as below
olr2To verify if the data you can query the table that we restored on mytempdb database
olr3I hope this article was helpful in understanding how we can use ‘Object level recovery’ to fetch data from a full backup file that has been taken using Quest Litespeed for SQL Server tool.

Technical reference: http://www.quest.com/litespeed-for-sql-server/

How to increase the number of SQL Server logs

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
EL5Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
EL6We can also run the below query to attain the same:

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
EL1In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

USE msdb
EXEC dbo.sp_cycle_agent_errorlog

This will re-initialize the current log and start a new log with the current date time stamp
2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:
b) Click on OK

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

How to Partition a table in SQL Server

In this article we will see how we can partition a table in a database using T-SQL. We will first understand the basics of table partitioning in SQL Server and then go through the scripts that we will use to build a partitioned table. Let us start with the basics of partitioning.

What is table partitioning in SQL Server?
Partitioning is dividing a large table and its indexes into smaller fragments called partitions.

Some of the Benefits?
Its helps performing maintenance operation fragment by fragment basis rather that performing on the entire table. Apart from that SQL queries can be redirected to proper partitions directly rather than scanning the entire table.SQL queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism. Archiving data is another major benefit of partitioning. We will not deep dive in this article on the benefits of partitioning.

Components of Partitioning:
1) Partition Function(PF): PF defines which rows goes into what partition in a partitioned table based on a range.
2) Partition Scheme(PS):The partition scheme defines how partitions will be stored on filegroups. Creating a partition scheme assumes that your database already has filegroups.

Let us now understand with an example how we can partition a table in SQL server.
1) Creating a database:

USE master
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'

2) Adding 4 new filegroups to the PartitionDB database.


3) Adding files to the database filegroups.

        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
    TO FILEGROUP PartitionFG1;
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
    TO FILEGROUP PartitionFG2;
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
    TO FILEGROUP PartitionFG3;


        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
    TO FILEGROUP PartitionFG4;

4) Creating a Partition Function(PF):

    AS RANGE LEFT FOR VALUES (10, 20, 30);

5) Creating a Partition Scheme(PS):

    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);

6) Create a Table:–

USE [PartitionDB]
CREATE TABLE PartitionTable
	MyDate datetime NULL,
	Name varchar(50) NULL
	)  ON PartScheme1(MyID)

7) Create Index on Partitioned Table

USE PartitionDB
ON PartitionTable(MyID)
ON PartScheme1 (MyID);

8) Insert Data into the Table

USE PartitionDB
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');

9) Verify data in the table

SELECT * FROM dbo.partitiontable

10) Verify Rows Inserted in Partitions

USE PartitionDB
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';

11) Run the below code to see the details of the partitioned table

USE PartitionDB
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id < 2
ORDER BY TableName,part.partition_number;

P2 I hope this article was helpful in understanding how we can create table partitions via T-SQL.

Script to find the size of all Indexes in a database

Continuing with my interest with Indexes, i wrote this script that finds the size of all indexes in a database along with the table and the filegroup on which the index resides. I have used the AdventureWorks2012 database as an example. Please replace the DB name for which you want to find the information.

USE AdventureWorks2012
IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata

@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')

OPEN Indexloop

INTO @ObjectID, @IndexID, @NameOfIndex,

SELECT @SizeofIndex = sum(avg_record_size_in_bytes * record_count)
FROM sys.dm_db_index_physical_stats(DB_ID(),@ObjectID,
@IndexID, NULL, 'detailed')

insert into #Indexdata(name, IndexID, IndexName, SizeOfIndex,
SELECT TableName = OBJECT_NAME(@ObjectID),
IndexID = @IndexID,
IndexName = @NameOfIndex,
SizeOfIndex = CONVERT(DECIMAL(16,1),(@SizeofIndex/(1024.0 * 1024))),
IndexType = @TypeOfIndex,
IsPrimaryKey = @IsPrimaryKey,
FGroup = @FGroup

INTO @ObjectID, @IndexID, @NameOfIndex,
CLOSE Indexloop

select name as TableName, IndexName, IndexType,
SizeOfIndex AS [Size of index(MB)],
case when IsPrimaryKey = 1 then 'Yes' else 'No' End as [IsPrimaryKey]
,FGroup AS [File Group]
from #Indexdata order by SizeOfIndex DESC

Snapshot of the output below:

SQL Server 2012 – How to enable the “Lock Pages in Memory” Option

In this article I will discuss the feature “lock pages in memory” and demonstrate how we can enable the “Lock pages in Memory” option for SQL Server 2012 on the OS. The OS decides which accounts can utilize a process to retain data in the RAM. This would not allow the system to page out data to virtual memory on the physical disk. In SQL Server 2012, the memory architecture has been simplified for the usage of locked pages across all editions including processor. In SQL 2012 we enable this option by allowing the account which runs the SQL Server service to have this right. The below illustration shows the requirements for different editions of SQL server for Locking pages in memory. LP1If you have migrated your SQL server to SQL 2012 from SQL 2005 or SQL 2008 and if you have been using this option then you can remove the trace flag 845 from the start up parameters.

We will now see how to enable this option.

        1. Click Start, click on Run. Type gpedit.msc. Click on OK and you will find the Local group policy editor opening.
          LP2         LP3
        2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
          LP4 LP5
        3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
        4. Once you open the User Rights Assignment folder all the policies will be displayed. Locate “Lock pages in Memory”. Double Click on “Lock pages in memory”
        5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
          We can verify whether SQL Server 2012 is using “locked pages” by the following ways:
          1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

LP10              2) DBCC MEMORYSTATUS will show the following:
LP11 I hope this article was helpful in understanding how we can enable this option for SQL Server 2012.

Find Top 5 expensive Queries from a Read IO perspective

The below written query helps us find the most expensive queries from a read IO perspective. The DMV that we have used in this script is sys.dm_exec_query_stats and the function that we have used is sys.dm_exec_sql_text.

SELECT TOP 5 qt.text AS ‘SQL’, qstats.total_physical_reads AS ‘Total Physical Reads’, qstats.total_physical_reads/qstats.execution_count AS ‘Average Physical Reads’,    qstats.execution_count AS ‘Execution Count’,
qstats.total_worker_time/qstats.execution_count AS ‘Average Worker Time’,
qstats.total_worker_time AS ‘Total Worker Time’,
DATEDIFF(Hour, qstats.creation_time, GetDate()) AS ‘AgeInCache In Hours’,  db_name(qt.dbid) AS ‘Database Name’
FROM sys.dm_exec_query_stats AS qstats
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qstats.total_physical_reads DESC

SQL Server 2008 -How to troubleshoot error: Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856

A few days ago i encountered the below mentioned error message in SQL error logs in one of our production environments
Process 0:0:0 (0x1ebc) Worker 0x00000005393F61A0 appears to be non-yielding on Scheduler 15. Thread creation time: 13022573770856.

I found the following KB Article on MSDN to throw light on this issue. As per the article there was a stack dump that we found in the server.
StackWhat caused the schedulers to be non-responsive:
As per the article the cause of the issue is:
“This issue occurs because the profiling session is not closed correctly. The profiling session is used to calculate the cost of an object to be cached, and is based on memory usage and CPU usage”.

Resolution as Provided by MS in the Kb article for SQL server 2008 is:
Apply CU6 for SP 3.

Our patch level in the environment is beyond that and still this issue happened, so literally there is no solution provided by MS. We would wait and see if this error comes back too often and open a case with MS support.

SQL server 2008 – How to troubleshooting error: The fulltext filter daemon host (FDHost) process has stopped abnormally.

A few days ago i came across the below message in our SQL Server logs:
Error: 30089, Severity: 17, State: 1.
The fulltext filter daemon host (FDHost) process has stopped abnormally. This can occur if an incorrectly configured or malfunctioning linguistic component, such as a wordbreaker, stemmer or filter has caused an irrecoverable error during full-text indexing or query processing. The process will be restarted automatically.

Full-text search is a key component in our SQL environment as we support a database that consists of millions of documents. I started scanning the FT crawl log and also found the below error:
Error ‘0x80004005’ occurred during full-text index population for table or indexed view ‘[DBNAME].[dbo].[TABLENAMe]’ (table or indexed view ID ‘10045673’, database ID ‘6’), full-text key value ‘4’. Attempt will be made to reindex it.

In case you are not aware as to where to find the crwal log then go the following location:
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG folder- this is where log folder would be for SQL logs. Under this folder you will find files with following pattern:

Where DatabaseID is
The ID of a database. <dbid> is a five digit number with leading zeros.<FullTextCatalogID>
Full-text catalog ID. <catid> is a five digit number with leading zeros.
n an integer that indicates one or more crawl logs of the same full-text catalog exist.

For example, SQLFT00006000010.5 is the crawl log file for a database with database ID = 5, and full-text catalog ID = 10. The 5 at the end of the file name indicates that there are two crawl log files for this database/catalog pair.

I searched the msdn site with error message and this is what i found as the cause of the error:
This problem occurs because of a bug in the communication between the Sqlservr.exe process and the Fdhost.exe process. When batches are sent from the Sqlservr.exe process to the Fdhost.exe process, sometimes the outbound shared memory (OSM), which is the shared memory from Sqlservr.exe to Fdhost.exe, has insufficient space to fit the next column of a document. When this occurs, the Sqlservr.exe process sends a message to notify the Fdhost.exe process to process all the existing data in the OSM. While the Fdhost.exe process is processing data, the inbound shared memory (ISM), which is the shared memory from Fdhost.exe to Sqlservr.exe, may become full. In this situation, the Fdhost.exe process sends a “rewind” message to the Sqlservr.exe process. This message requests that the Sqlservr.exe process resend the last document from the first column. However, when the Sqlservr.exe process starts to send the document again, it starts from column 2 instead of column 1.

Apply Cumulative update package 3 for SQL Server 2008 R2 Service Pack 2 and Cumulative update package 4 for SQL Server 2008

MS also suggests an workaround for the same which is increasing the ism size value:

What is ISM. Well ISM is the internal shared memory used by the FDHost.exe process.

To show the current value:
sp_fulltext_service ‘ism_size’

To change the value to a new value:
sp_fulltext_service ‘ism_size’,@value=NewValue

Useful tech articles of Full-text search:

Buffer cache hit ratio – How useful it is?

Buffer cache hit ratio(BCHR) : Well every dba who has ever done any Performance tuning work would know about this particular counter. So what is BCHR: Percentage of pages that were found in the buffer pool without having to incur a read from disk.

And how much guidance does it give us….

Well to know that i had to share this great article from . I read this article on http://www.simple-talk.com. Read the article and challenge your understanding of BCHR once again.

Find jobs that are least run or never run

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
FROM    msdb.dbo.sysjobactivity
GROUP    BY Job_Id
) Jact
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.

Unused Jobs

Find all unused indexes in a database

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 +
%d bloggers like this: