drop the Default constraints in MSSQL code.

when there is a need to drop the default constraint on a column in MSSQL code, please check whether you have created a name for Default constraint.

please not that i had not created a name for default constraint, instead i just created a value for that constraint. so dropping that
was a tedious task. so came across couple of websites/blogs that suggested that MSSQL server itself will create a name for default constraint id not created manually.

use [tsql_Prog_testing]
 select * from sys.sysobjects where xtype='U';

-- create a new table CUSTOMERS
create table customers

ID int not null,
Name varchar(20)not null,
Age int not null,
address varchar(25),
salary decimal(18,2),
primary key (ID)

— make salary column not to have null values. i.e alter the table

ALTER TABLE CUSTOMERS alter column salary decimal(18,2) not null;

— add default value to Salary coulmn

alter table customers add default 5000.00 for Salary;

— drop the DEFAULT constraint
when there is a need to drop the default constraint on a column in MSSQL code, please check whether you have created a name for Default constraint. please not that i had not created a name for default constraint, instead i just created a value for that constarint. so dropping that was a tedious task. so came accross couple of websites/blogs that suggested that SQL server itself will create a name for default constraint id not created manually.
In general, when you don’t name a default constraint, SQL Server will assign one.
you can check by executing the command
ref – https://technet.microsoft.com/en-us/library/aa175912(v=sql.80).aspx

-- sp_help customers;

–we can create a default constraint name for column as shown below

so finally checked the table description using

 sp_help customers 

which gave me the details of default column name as – [DF__customers__salar__014935CB]



so use the drop constraint as shown below
— drop the DEFAULT constraint

 alter table customer drop constraint [DF__customers__salar__014935CB]; 



Posted in Uncategorized | Leave a comment

Locate Outlook Files from CMD prompt!!!

Steps to Locate Outlook Files from CMD prompt!!!

step1 – open CMD prompt in windows server/system.

step2 – type the below command

C:\Users\Local_user1> dir /s *.pst


Posted in Tech General | Tagged | Leave a comment

List MSSQL instances installed in server using COmmand Prompt

use below command in CMD prompt to get the list of MSSQL instances in server.

C:\Users\Test_user1>sqlcmd -L 

Note – ‘L’ is capital letter in above Command.

Posted in Uncategorized | Leave a comment

MSSQL Replication – Interview Questions

MSSQL Interview Questions


Where will you find the SQL Server installation related logs?

Installation related logs are stored under the shared feature directory folder which was selected at the time of first SQL Server instance installation.  E.g. If Shared Features were selected to be placed on the

“C:\Program Files\Microsoft SQL Server” then logs will be created under.

What is a service account?

Based on the selected components while doing the installation we will find respective service to each component in the Windows Services. e.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each and every service through which each service will run. That use is called Service Account of that service.

Mainly we categorize the Service account as below:

Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.

Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.

Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.

Do we need to grant Administrator permissions on the Windows server to SQL Service account to run the services or not, why?

No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.

What are Shared Features Directory and its usages?

This directory contains the common files used by all instances on a single computer e.g. SSMS, sqlcmd, bcp, DTExec etc. These are installed in the folder :\Program Files\Microsoft SQL Server\110\ ,

where <drive> is the drive letter where components are installed. The default is usually drive C.

Type of Instance and maximum no. of instances which can be installed on a server.

There are two types of Instances.

Default instance

Named Instance

Each computer can run maximum of 50 instances of the Database Engine.  One instance can be the default instance.

The default instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance.

A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.

What is a collation and what is the default collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Default collation:  SQL_Latin1_General_CP1_CI_AS

What is an RTM setup of SQL Server?

RTM stands for release to manufacturing.

What is a slip stream installation and its usages?

SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation. Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.

What is a silent installation and how can we use this feature?

The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in quite mode is known as Silent installation.

Can we change the default port of SQL Server, How?

Yes, it is possible to change the Default port on which SQL Server is listening.

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port

Service pack/Patch/Hotfixes

What’s the practical approach of installing Service Pack?

Steps to install Service pack in Production environments:

– Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.

– List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.

– Install the service pack on SQL Servers.

– Verify all the SQL Services are up and running as expected.

Can we uninstall/rollback a service packs from SQL Server 2005?

  1. No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it.
  2. For Sql Server 2008 you can uninstall a SP from Add/Remove programs.

                                                        DB Maintenance

Is it mandatory to restart the Windows server after installing SQL server service pack?

No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.

What is the port numbers used for SQL Server services?

The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number.

The browser service runs on port UDP 1434.

Reporting services is a web service – so it’s port 80, or 443 if it’s SSL enabled.

Analysis service is on 2382 but only if it’s a default install. Named instances get a random port number.

How to recover a database that is in suspect stage?


After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects, but the database will be in read-only mode. Any attempt to modify data will result in an error similar to the following:

Msg 3908, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database ‘test’ …..etc


If DBCC CHECKDB statement above succeeds the database is brought back online (but you’ll have to place it in multi-user mode before your users can connect to it). Before you turn the database over to your users you should run other statements to ensure its transactional consistency. If DBCC CHECKDB fails then there is no way to repair the database – you must restore it from a backup.

Why Shrink file/ Shrink DB/ Auto Shrink is really bad?

In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file.

The data becomes fragmented, potentially up to 100% fragmentation, this is a performance killer for your database;

The operation is slow – all pointers to / from the page / rows being moved have to be fixed up, and the SHRINKFILE operation is single-threaded, so it can be really slow (the single-threaded nature of SHRINKFILE is not going to change any time soon)


Shrink the file by using Truncate Only: First it removes the inactive part of the log and then perform shrink operation

Rebuild / Reorganize the indexes once the shrink is done so the Fragmentation level is decreased

Can we track no of transactions / inserts / updates / deletes a Day (Without using profiler)? If yes how?

You could use capture data change or change tracking.

How many datafiles I can put in Tempdb? What is the effect of adding multiple data files.

By far, the most effective configuration is to set tempdb on its own separate fast drive away from the user databases. I would set the number of files based on # of cpu’s divided by 2. So, if you have 8 cpu’s, then set 4 tempdb files. Set the tempdb large enough with 10% data growth.

I would start at a general size of 10 GB for each size. I also would not create more than 4 files for each mdf/ldf even if there were more than 8 cpu’s. you can always add more later.

Let’s say a user is performing a transaction on a clustered server and failover has occurred. What will happen to the Transaction?

If it is active/passive, there is a good chance the transaction died, but active/passive is considered by some the better as it is not as difficult to administer. I believe that is what we have on active. Still, active/active may be best depending on what the requirements are for the system.

What are Page Splits?

Pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as “Page Split”.

In which Files does SQL Server Actually Store Data?

Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf*.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.

What is Collation in SQL Server?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

What are the different types of database compression introduced in SQL Server 2008?

Row compression and Page compression.

Resource Database

Do you know about Resource Database?

All sys objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades.

Really does resource faster the upgrades? Can you justify?

Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file. We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.

I have my PROD sql server all system db’s are located on E drive and I need my resource db on H drive how can you move it?

No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.

Can we take the backup for Resource DB?

No way. The only way if you want to get a backup is use windows backup for option resource mdf and ldf files.

Indexes maintenance

What is Fill Factor?

Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.

High Availability Questions:

What is the difference between the 2 operating modes of Database Mirroring (mentioned in above answer)?

High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.

High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored

Database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.

What type of locking occurs during the Snapshot generation?

Locking depends on the type of replication used: In snapshot replication, the snapshot agent locks the object during the entire snapshot generation process. In transactional replication, locks are acquired initially for a very brief time and then released. Normal operations on a database can continue after that. In merge replication, no locks are acquired during the snapshot generation process.

What options are there to delete rows on the publisher and not on the subscriber?

One option is to replicate stored procedure execution instead of the actual DELETE command. You can create two different versions of the stored procedures one on the publisher that does the delete and the other on the subscriber that does not do the delete. Another option is to not replicate DELETE commands.

Data is not being delivered to Subscribers, what can be the possible reasons?

There are a number of possible causes for data not being delivered to Subscribers: The table is filtered, and there are no changes to deliver to a given Subscriber. One or more agents are not running or are failing with an error. Data is deleted by a trigger, or a trigger includes a ROLLBACK statement. A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created. Replication of stored procedure execution for a transactional publication produces different results at the Subscriber. The INSERT stored procedure used by a transactional article includes a condition that is not met. Data is deleted by a user, a replication script, or another application.

Explain what stored procedure sp_replcounters is used for?

Sp_replcounters is a system stored procedure that returns information about the transaction rate, latency, and first and last log sequence number (LSN) for each publication on a server. This is run on the publishing server. Running this stored procedure on a server that is acting as the distributor or subscribing to publications from another server will not return any data

Is it possible to replicate data from SQL Server to Oracle?

Yes this can be done using heterogeneous replication.

How will you monitor replication latency in transactional replication? Tracer tokens were introduced with SQL Server 2005 transactional replication as a way to monitor the latency of delivering transactions from the publisher to the distributor and from the distributor to the subscriber(s). For details, please refer tip to this tip: Monitor SQL Server replication latency using tracer tokens

Can you tell me some of the common replication DMV’s and their use? s

sys.dm_repl_articles – Contains information about each article being published. It returns data from the database being published and returns a row for each object being published in each article. sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published sys.dm_repl_traninfo – Contains information about each transaction in a transactional replication

How to determine if the data at Subscribers is synchronized with data at the Publisher?

Use validation. Validation reports on whether a given Subscriber is synchronized with the Publisher. For more information, see Validate Replicated Data. Validation does not provide information on which rows if any are not synchronized correctly, but the tablediff utility does.

Posted in Interview | Leave a comment

T-SQL to start/Stop SQL services.

--See below example to check the status of SQL Services
EXEC xp_servicecontrol N'querystate',N'MSSQLServer'
EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
EXEC xp_servicecontrol N'querystate',N'msdtc'
EXEC xp_servicecontrol N'querystate',N'sqlbrowser'
EXEC xp_servicecontrol N'querystate',N'MSSQLServerOLAPService'
EXEC xp_servicecontrol N'querystate',N'ReportServer'

— to start/stop service using SSMS

EXEC xp_servicecontrol N'stop',N'SQLServerAGENT'
EXEC xp_servicecontrol N'start',N'SQLServerAGENT'

–See below example to check non-SQL Service

EXEC xp_servicecontrol querystate, DHCPServer

excellent article by author here!!


Posted in MSSQL | Leave a comment

Job failure E-Mail Notification Automation

Configuring the notification for Job failure alerts.

 Assumption – we assume that Database Mail is configured. If not familiar with the DB mail procedure. In the beginning of the article its given. Please refer the below link.


Follow the below steps for configuring notification on job failures.

documented in link – Notification_Job_failures_Doc1

Step1) Click on Alert System under Select a page.

  • Under Mail session, select the option to Enable mail profile. Ensure the correct Mail profile is selected.
  • Under Token replacement, enable Replace tokens for all job responses to alerts.

Click OK. Restart the SQL Server Agent service.

Step2) make sure the operator is created with email id. for our convenience, we have created a alert by name – DBA_Alert

/****** Object:  Operator [DBA_Alert]    Script Date: 06/20/2015 11:32:15 ******/

EXEC msdb.dbo.sp_add_operator @name=N'DBA_Alert',/* can give any name*/
@enabled=1, @weekday_pager_start_time=90000, @weekday_pager_end_time=180000,
@saturday_pager_start_time=90000, @saturday_pager_end_time=180000,
@sunday_pager_start_time=90000, @sunday_pager_end_time=180000,


Step3) exec script to check job name & job_id which is required to change the notification. 

select name,job_id from msdb.dbo.[sysjobs]
where name like '%[job_name]%'

Step 4) execute the below query with job_id fetched from above query. 

EXEC msdb.dbo.sp_update_job @job_id=N'',

Note – if you are trying to update job failure notification for more than 1 job. You can use Excel for automating the script as shown below.


Excel Formula used at query column :

="EXEC msdb.dbo.sp_update_job @job_id=N'"&column number of Job_id&"',
@notify_level_email=2, @notify_level_netsend=2,
@notify_level_page=2, @notify_email_operator_name=N'DBA_Alert';"

Ref –

Excel formula used – http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/

email notification steps –http://www.orcsweb.com/blog/desiree/how-to-set-up-database-mail-for-sql-server-job-failures/

Posted in MSSQL | Leave a comment

Create Blocking alerts & testing it..!!

below is the doc that has step by step instruction to

1) configure db mail in MSSQL. please check the doc attached in below link

try t-sql to configure DB mail in this link – t-sql_DB_Mail_creation

db_mail_config steps

or refer below links for better understanding.


2) configure the db blocing alerts. please refer the below link. superb article,great work!! thanks to the author!!


3) test the alerts.

Testing the blocking alerts

— step 1 : create a  sample database and table in DBA Monitor

Use master


Create database sample_db


use sample_db


create table test_sample_table (emp_id int ,emp_name varchar(30))

-- step 2 : insert data into table, execute ‘commit transaction’ after blocking threshold time i.e if blocking is set tp 30 secs,60 secs(1min),900 sec(15 mins) then execute the commit command as given below

use sample_db




insert into test_sample_table (emp_id,emp_name) values (1,'aaa'),(2,'bbb'),(3,'ccc')



-- step 3 : open new query window & execute below query to check the total records in sample table

use sample_db


select * from test_sample_table

if all the above steps are done correctly then you will get a mail.


Posted in Uncategorized | Leave a comment

Simple way to generate count of rows of all tables in MSSQL Database

If you intend to get the count of rows of all tables in MSDB database below is the query.

 Use msdb


Select 'Select count(*) From' +name from sys.sysobjects

Where type = 'U' 

once this query is executed in MSSQL query analyser you will see below result.


please refer for more details – http://blogs.msdn.com/b/chrissk/archive/2010/06/27/example-code-executing-tsql-for-each-table-in-a-database.aspx

You can add conditions to the query as shown below –

Use msdb


Select 'Select count(*) From ' +name from sys.sysobjects

Where type = 'U' and name like '%sysm%'

Posted in Uncategorized | Leave a comment

Hello world!

This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!

| Leave a comment