SQLServerLog.com

All the jobs information are stored in system tables of MSDB system database. Here we are going to use sysjobs and sysjobhistory tables to monitor information for each scheduled jobs execution. Below query will help you to monitor jobs name, time and status messages so that you can monitor your jobs easily with a single query and date time format can be seen perfectly with SQL function agent_date…

SQL Server provides very useful T-SQL to find out details about property information of SQL Server instance by querying SERVERPROPERTY. With this T-SQL you can find out information like Product Version, Server Name, Edition, Collation, License Type etc. You can refer below query for more details and execute on SQL Server Management Studio to get output.

SELECT SERVERPROPERTY('ProductVersion') AS…

In many tables we use Identity column to maintain the uniqueness and automatically increment the values when records are inserted. If you want to reset the identity column numbers in a table then we can use DBCC CHECKIDENT T-SQL command with RESEED.

Syntax:

DBCC CHECKIDENT (TableName, RESEED, ReseedValue)

Example:

DBCC CHECKIDENT (tblEmployee, RESEED, 100)

Suppose if you have 100 records with…

Microsoft is releasing new and exciting features with new versions over the time. SQL Server 2016 also has lots of new features for database users and it has been reformed and made database users life easier. Real time operational analytics is amazing which is upto 30x fatser transactions with in-memory OLTP which performs operations from minutes to seconds. Here I am covering 10 new features in…

SQL Server Interview Questions and Answers

1. What do mean by XML Datatype? XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.

2. What are the Magic Tables in SQL Server?

Insert and Delete tables are created when the trigger is fired for any DML command. Those tables are called Magic Tables in SQL Server. These magic tables are used inside…

SQL Server provides DMVs and system tables to find out when SQL Server was last restarted. We may need this information many times to troubleshoot or trace issues which occurs on database servers. So, here I am listing few simple and useful DMVs and tables which can provide you this information.

sys.dm_os_sys_info

DMV sys.dm_os_sys_infoprovides information about system and resources consumed by…

Identify when Statistics were last Updated in SQL Server Database

If you want to identify when last time statistics were updated on objects in database then you can use either DBCC SHOW_STATISTICS header information or sys.stats catalog view.

Below example displays all statistics information for the AK_Currency_Name index of the Sales.Currency table in AdventureWorks2012 database.

USE AdventureWorks2012; GO DBCC SHOW_STATISTICS ('Sales.Currency',…

Find All Tables Missing Clustered Index in Database

If your database contains hundreds or thousands of tables then it’s difficult to find which tables are missing clustered index in a database manually. You can go through this article which provides very simple query and analyses all the tables and provides you required result and you can check and tune your database accordingly.

You can find index type and description by using below query which…

Find Number of Connections on Databases and Instance Used By Logins

We have a query which is very useful for DBAs and database users to find total number of connections used by particular login on database instance for all databases or on particular database. Here I am using object sys.sysprocesses which will provide us this detail.

To Find All Connections on Database Instance, you can use below script.

SELECT loginame as LoginName, DB_NAME(dbid) as DBName,…

Find Queries Using Highest CPU Utilization in SQL Server

As a DBA, time to time we have to trace SQL queries which are consuming highest CPU utilization in SQL Server and need to tune those queries. You can refer below query which provides details for top 10 expensive queries based on CPU utilization. This query also provides details like last execution time, execution count, worker time, physical reads, logical reads, logical writes, elapsed time etc.

Find Jobs Name and Owners in SQL Server

If you want to find SQL Jobs Name and owners by which particular job is running then you can find the same by joining sysjobs and syslogins from msdb and master database respectively as given below.

SELECT SJ.name AS JobName,SL.name AS Owner FROM msdb..sysjobs SJ left join master.sys.syslogins SL ON SJ.owner_sid = SL.sid

It has returned following output from my test server.

Similarly, you can…

Find All Enabled and Running Jobs in SQL Server

As a DBA sometimes we need to verify certain details for currently running jobs, jobs steps, jobs description and commands and queries used in jobs etc. We can extract these details by joining tables sysjobs and sysjobsteps and can find all Enabled and Running Jobs in SQL Server with a simple query.

SELECT database_name, name AS JobName, step_name AS JobStepName, enabled AS IsEnabled,…

Understanding and Troubleshooting SQL Server Performance Issues

SQL Server Performance Tuning is a major topic for Database Administrators and it is also a challenge for them to trace the issues and tune it. We should know what are the major areas where SQL Server performance issues may reside.

      Here, I am listing major areas for database performance issues.

Poor Database Indexing

Poor Query Design

Old Statistics

Poor and Non-Reusable Execution…

Different Types of SQL Server Locks

Sometimes conflicts occurs in SQL Server due to concurrent transactions which can be resolved by using lock modes. Locking is a very common problem and its a logical issue which means you cannot solve it by adding extra hardware. SQL Server automatically manages and locks the resources at appropriate transaction level e.g. row level or data page level etc.

  Here is the list of different Lock…

SQL IDENTITY Column – Auto Increment Column Values

SQL IDENTITY column generates a unique number when a new record is inserted in a table and it is normally used with CREATE TABLE or ALTER TABLE statement.

Syntax

Following create table statement generates unique integer identity values on RollNumber column.

CREATE TABLE Students ( RollNumber int IDENTITY(1,1) PRIMARY KEY, Title varchar(8), FirstName varchar(50) NOT NULL, MiddleName varchar(50),…

SQL FOREIGN KEY Constraint

SQL FOREIGN KEY constraint represents relationship between tables. A Foreign Key is a column(s) whose values are derived from the PRIMARY KEY or UNIQUE KEY of some other table.

If you want to associate records of one table with records of another table then you can create relationship between two tables with FOREIGN KEY. A column level FOREIGN KEY constraint can list only one reference column and…