Trace Flags in SQL Server

Trace Flags in SQL Server


Trace flags plays an important role in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. For example trace flag 1222 is to detect the deadlock and returns the resources and types of locks pertaining in a deadlock

In SQL Server, there are three types of trace flags, query, session and global. Query trace flags are only active for the specific query. Session traces flags are active only for that particular session or connection. Global trace flags are set at the server level and are visible to every connection on the server.

There are several types of trace flags available. Here I’ll take mostly example of deadlock trace flags which is the most common for the DBA use.


Requires membership in the sysadmin fixed server role.



A global trace flag must be enabled globally, otherwise the trace flag has no effect. It is recommended to enable global trace flag at start up, by using the –T command line option. This ensures the trace flag remains active after a server restart.

The trace flag should be enabled with appropriate scope whether it is global, session or query scope. A trace flag which is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.

How to set Trace flags on or off



E.g. DBCC TRACEON (1222, -1)

The effect of enabling a global trace flag with DBCC TRACEON is lost on server restart.

To turn off a global trace flag, use

DBCC TRACEOFF with the -1 argument

DBCC TRACEOFF (1222, -1)


-T (T stands as Trace flag) start up option

The –T startup option enables a trace flag globally. We can’t enable a session-level trace flag by using a startup option. This ensures the trace flag remains active after a server restart. This can be done via SQL Server configuration manager.

Note: Here we can set the trace flag in Startup Parameters as “-T1222”.

How to start\stop traces:

Dbcc traceon (trace no) – for current session

Dbcc traceon (traceno, -1) – to enable globally

Dbcc traceon (no1, no2, -1) – enable multiple traces globally

How to check trace status:

Dbcc tracestatus

Dbcc tracestatus (-1) – display all globally enabled flags

Dbcc tracestatus () – display all trace flags enabled for current session


Note: Hi Friends,

Looking forward your thoughts on my blogs at least one liner email along with your name so that I can publish it with your name in coming blog. Kindly send it on my email id or using contact us here on the blog. Appreciate your time in advance.

Email Id:

Posted in Database Administration, Q & A, Sql Server | Tagged , , , , , , , , , , , | Leave a comment

SQL Server High Availability and Database Recovery Model Relationship

Recovery model

There are four types of high availability options available in Microsoft SQL server. All the options are different from each other. For example, Log shipping, Mirroring and Always On works on database level while Replication works on database objects level. The databases involved in these high availability feature must have a fixed type of recovery models else it is not possible to configure these options. Here I am briefing about the recovery models of the database which are involved in high availability.

HA & Recovery Models

How to check the recovery model of a database?

Method 1:  sp_helpdb

Method 2: select name as [Database Name], recovery_model_desc as [Recovery Model] from sys.databases

How to change the recovery model of a database?

Method: T-SQL

Alter database database name set recovery full/bulk logged/simple


alter database Mirza set recovery full



Earn Money

Posted in Always on, Database Administration, Replication, Sql Server, T-SQL | Leave a comment

DBA Daily Tasks

sql-server DBA Daily Tasks

This blog is especially for those people who are preparing to be a Database Administrators or who have just started their career in the database administration field.

As a production DBA, we are supposed to perform several tasks on the servers on a daily basis to make and keep the server and database healthy. Monitoring the server is a key to safe our databases from any risk which may occurs in future. I am mentioning here only basic but very important tasks.

  1. Health Check of critical Servers
  2. Alerts from the servers needs to be acknowledged
  3. All the tickets to be checked and follow up
  4. Emails follow up
  5. Attend the scheduled client call
  6. Project work follow up
  7. Document the database environment

1.Health Check

Health checkup of the serves includes several checks which we need to perform on the critical servers and prepare a complete report. Here we need to check for database status, disk space utilization, CPU and Memory utilization, all the backups’ jobs and other maintenance jobs, error logs etc.

Important T-SQL commands can be used:

  1. Sp_helpdb
  2. Sp_spaceused
  3. Xp_fixeddrives

2. Alerts Acknowledgement

Any alert receives from the server via database mail or through any tool, we need to acknowledge and need to work upon them.

  1. Ticket Follow up

Mostly all companies use some ticketing system to get the database related issue \ task through it. All the tickets come with a SLA (service level agreement) which needs to be adhere and finish it within stipulated time frame.

  1. Email Follow up

Sometimes we get email from the client which contains a long list of database related talks which we need to work upon. So we have to follow-up on all emails and keep the status up to date without any fail.

  1. Call Attendance

For different project work related call with the clients, are scheduled and invitation goes to every team member. Therefore it is necessary for the DBA to attend the call and make the points clear on any ongoing work\ issue.

  1. Project Work Follow-up

Apart from daily routine work, project work also there which takes time and team do work side by side in parallel e.g. any database migration, AOAG setup on new servers, Server setup etc. Therefore we have to sync our self with the project work and keep updated on every task which is going on. By this way a new DBA can enhance his\her knowledge as well.

  1. Documentation

DBA performs several task in a single day and sometimes the task is a strange and new to DBA. We need to document all task either new or any project work to make every member of the team be on the same platform and make the life easy.

Earn Money !!! Click 

Make money from your Website or Blog with BidVertiser


Posted in Database Administration, Q & A, Sql Server | Tagged , , , , , , , , , | Leave a comment

Introduction to Microsoft Azure

Microsoft Azure

Microsoft Azure is an open, flexible, enterprise-grade cloud computing platform. Azure was announced in October 2008 and released on February 1, 2010.

It provides

Software as a service (SaaS)

Platform as a service (PaaS)

Infrastructure as a service (IaaS)

Why is it different from other cloud computing?

  1. Microsoft cloud is global, trusted and hybrid.
  2. 42 Azure regions, more than any other cloud provider
  3. 90% of fortune 500 companies use the Microsoft cloud.
  4. 1,20,000 new Azure customer subscriptions per month
  5. 62 compliance offerings more than any other cloud provider

Microsoft Azure – Popular Services\Products

  1. Virtual Machines
  2. App Services
  3. SQL Databases
  4. Storage
  5. Cloud Services
  6. Azure Cosmos DB
  7. Azure Active Directory
  8. Backup
  9. SQL Data Warehouse
  10. Traffic Manager
  11. Azure Database for MySQL
  12. Azure Databases for PostgreSQL


Posted in Azure, Database Administration, T-SQL | Tagged , , , , , , , , , , | Leave a comment

How to add and remove a database to an availability group in Always On

How to add and remove a database to an availability group in Always On


  1. Connect to the server instance that hosts the primary replica.
  2. Use the T-SQL statement as below
  3. After you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica.

To add a database to an availability group


ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,…n]


Group Name = MirzaAG

Database Name= Mirza (Existing database in the instance)



How to start data synchronization after adding a database to an Always On availability group

  1. For each new primary replica, secondary databases must be prepared on the server instances that host the secondary replicas. Then each of these secondary databases must be manually joined to the availability group.
  2. If the file paths are identical on every server instance that hosts a replica for an availability group then the data synchronization starts automatically else we have to manually to do it as below.

 To start data synchronization manually follow these steps:

  1. Restore current backups of each primary database and its transaction log (using RESTORE WITH NORECOVERY). You can use either of the following alternative approaches:
    • Manually restore a recent database backup of the primary database using RESTORE WITH NORECOVERY, and then restore each subsequent log backup using RESTORE WITH NORECOVERY. Perform this restore sequence on every server instance that hosts a secondary replica for the availability group.
    • As soon as possible, join each newly prepared secondary database to the availability group.

 To remove an availability database,


  1. This task is supported only on primary replicas. You must be connected to the server instance that hosts the primary replica.
  1. Requires ALTER AVAILABILITY GROUP permission on the availability group,
  2. Use the Alter Availability Group T-SQL statement


ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name

e.g.  Group_Name = MirzaAG

Database Name= Mirza


 Alter availability group MirzaAG remove database Mirza


Removing an availability database from its availability group ends data synchronization between the former primary database and the corresponding secondary databases. The former primary database remains online. Every corresponding secondary database is placed in the RESTORING state.

At this point there are alternative ways of dealing with a removed secondary database:

  • If you no longer need a given secondary database, you can drop it.
  • If you want to access a removed secondary database after it has been removed from the availability group, you can recover the database. However, if you recover a removed secondary database, two divergent, independent databases that have the same name are online. You must make sure that clients can access only one of them, typically the most recent primary database.

Looking forward your valuable likes and comments!

Facebook Page:




Posted in Always on, Clustering, Database Administration, T-SQL | Tagged , , , , , , , , , , | Leave a comment

Always On in SQL Server 2017 (Read-Scale Feature)

Always On in SQL Server 2017 (Read-Scale Feature)

SQL server 2017 has introduced new feature and technology (read-scale availability group) with Always on. Now, Always On availability groups provide high availability, disaster recovery and read-scale balancing.

Read-Scale Availability Group: This architecture provides replicas for read-only workloads but no HA. There is a question why we need this new architecture:

In SQL Server 2016 and before, all availability groups required a cluster and cluster provides a business continuity HADR. SQL Server 2017 introduces read-scale availability groups without a cluster.

This feature is very useful in case if the business needs to keep the resources for mission-critical workloads running on the primary then users can route to readable secondary replicas without using any cluster technology.

CLUSTER_TYPE:  There are three different cluster types which are used while creating availability group as below

  • WSFCWinows server failover cluster. On Windows, it is the default value for CLUSTER_TYPE.
  • EXTERNALA cluster manager that is not Windows server failover cluster – for example, on Linux with Pacemaker.
  • NONENo cluster manager. Used for a read-scale availability group.

How to get the details for cluster type used in availability group:

DMV:  sys.availability_groups

Two new columns:  cluster_type and cluster_type_desc


Select name as ‘Availability Groups ’, cluster_type, cluster_type_desc from sys.availability_groups.

Thanks for reading!

Awaiting your valuable responses…



Posted in Always on, Clustering, Database Administration, T-SQL | Tagged , , , , , , , , , | Leave a comment

10 Most Important handy system stored Procedures for DBA

10 Most Important handy system stored Procedures for DBA

This blog contains the most important system stored procedures which are supported by SQL Server and are used on a daily basis by DBA. These stored procedures are used commonly for maintenance of the server instance.

  1. Sp_helpdb

It provides information about the databases as mentioned in picture below. If any specific database is specified with this command then the information will be displayed about only that database.


  1. Sp_who2 

It provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.


  1. Sp_spaceused

It displays the disk space reserved and used by the whole database (any specific database).


  1. Sp_databases

It shows the database information for all databases on an instance of  a server as below.


  1. Sp_server_info

This stored procedure returns a list of attribute names and matching values for SQL Server.


  1. Sp_tables

It displays the information about the tables, name and type etc.


  1. sp_lock

It reports information about locks.


  1. sp_monitor

It displays statistics about Microsoft SQL Server.


  1. sp_updatestats

It runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

  1. Sp_configure

It displays or changes global configuration settings for the current server.


Remarks: For more information and more stored procedures, please review Microsoft Blog. It is recommended to execute these stored procedures and see the results in detail which will be beneficial.



Posted in Database Administration, Sql Server | Tagged , , , , , , , , , , , , | Leave a comment

MS SQL Server Version History – Year Wise

MS SQL Server Version History – Year Wise

MS SQL Server is a RDBMS (Relational Database Management System) which is developed by Microsoft.

Here we will go through a series of different versions of SQL Server with specified year when it was launched\born.

Being a DBA we must know which version we are working on and what were the previous versions because this knowledge will help us to solve the compatibility issues in SQL Server.

SQL Server Release History

Release Name Version Year
SQL Server 1.0 (16-bit) 1.0 1989
SQL Server 1.1 (16-bit) 1.1 1991
SQL Server 4.2A (16-bit) 4.2A 1992
SQL Server 4.2B (16-bit) 4.2B 1993
SQL Server 6.0 6.0 1995
SQL Server 6.5 6.5 1996
SQL Server 7.0 7.0 1998
SQL Server 2000 8.0 2000
SQL Server 2005 9.0 2005
SQL Server 2008 10.0 2008
SQL Server 2008 R2 10.50 2010
SQL Server 2012 11.0 2012
SQL Server 2014 12.0 2014
SQL Server 2016 13.0 2016
SQL Server vNext 14.0 2017

Command to check version in SSMS-

Select @@version

Posted in Database Administration, Sql Server | Tagged , , , , , , , | Leave a comment

AlwaysOn Pre-Requisites

AlwaysOn Pre-Requisites

 1.Windows 2.SQL Server 3. Databases

Hotfix for .NET 3.5 SP1 adds support to SQL client for AlwaysOn.

Windows Requirement:

  1. The system should not be in a domain controller
  2. Windows server 2012 or later versions should be running on each computer.
  3. Each and every computer or node should be in WSFC (windows server failover clustering).
  4. Ensure that WSFC cluster contains sufficient nodes to support availability group configurations.
  5. 5.   The user must be a system administrator on every cluster node to administer a WSFC
  6. 6.To ensure about sufficient disk space, dedicated network adapter and coparable systesm before proceeding.

SQL Server Prerequisite:

  1. The host computer must be a windows server failover clustering (WSFC) node.
  2. Each and Every server instance must be running the Enterprise Edition of SQL Server 2016.
  3. All the server instances involved in availability group must use the same sql server collation.
  4. Make sure the Always on Availability groups feature be enable on each server instance.
  5. Each server instance requires a database mirroring endpoint.
  6. Sysadmin fixed server role requries to create endpint.

Database prerequisite:

  1. Only user databases are allowed in availability group , system databases doesn’t.
  2. The database should be a read-write database. Read-only databases can’t be added to the AG.
  3. The database must be a multi user database.
  4. Full recovery model is needed for all the databases.
  5. We need to have atleast one full database backup for each database.
  6. The database should not belong to any existing availability group.
  7. The database should be be configured already for database mirroring


Looking forward your inputs\suggestions\likes on the same!

Posted in Clustering, Database Administration | Tagged , , , , , , , , , , , , , | Leave a comment

Resolve Microsoft SQL Server Attach Database Error Code 5120

When the users try to attach a database to SQL server then sometimes it becomes difficult to do this due an error. This error is known as Microsoft SQL Server error 5120 access denied. The error message occurs on the user’s screen is mentioned-below:


Error code 5120 occurs, when the account that is running SQL server service does not have the permission to access MDF and LDF files of a database. Therefore, in this post, we are going to discuss the solution to fix the error message.

Fix Attach SQL Database Error 5120

Create two users account such as SQLAdminOne and SQLAdminTwo on the SQL server and then follow the below-mentioned steps to solve the error 5120 in SQL server:

  • Create A New Database: Follow the below-mentioned steps to create a new database such as Test:
    LOG ON

  • Check Test File Permission For MDF and LDF: Browse the location of MDF and LDF files and then right-click on the file >> Properties >> Security
  • error-5120-2

  • SQLAdminOne Detaches Test Database: Right-click on the Test database>> Tasks >> Detach
  • Check Test File Permission For MDF And LDF Again: Test database MDF and LDF files permissions have changed and full permission are granted to SQLAdminOne only.
  • error-5120-3

  • SQLAdminTwo Tries To Attach Test Database: When SQLAdminTwo tries to attach the Test database SQL error code 5120 occurs due to the limited permission on the database files
  • Grant Full Permission On Both MDF and LDF Files Of Test Database: Follow the below-mentioned steps to grant the full permission on the Test database file:
    • Right-click on the Test database file and then select Properties
    • Now, click on Security tab and then click on Edit button
    • Click on the Add button and then enter the object name to select as NT Service\MSSQL$SQL2013. After that, click on Check Names button and then click on OK
    • Now, give this account Full Control to the file and then click on OK button
    • error-5120-4

  • Reattach Test database: SQLAdminTwo become successful to attach the Test database


Sometimes, the users of SQL server faced MS SQL server error 5120 when they try to attach a database in SQL server. This error mainly arises when the user’s account does not have access permission on a database (MDF and LDF) files. To attach a database file into SQL server, the account that is running on MS SQL server service should have the full access permission on both MDF and LDF files of a database.

Posted in Sql Server | Tagged , , | Leave a comment