So, we all know what sysprocesses and sp_who2 do.  These gives us the current transactions/processes running on SQL Server Instance at a given moment of time.

Now, what if on a regular interval bases, I want to capture what all processes are running and what is the Query/Batch being running.

Ever wondered what each error is associated with what severity level in SQL Server?

Every error which is logged in SQL Server Error log has a severity level assocoated with it. What level of severity needs to be taken on priority and what can be ignored can be found here in Microsoft Link

Ever wondered how to create a Default Operator in SQL Server for Sending Alerts configured in your Jobs using Database Emails? Well you can definately do it using SQL Server Management Studio, but what if you have 100 new servers installed and want to create operators on all servers. Lengthy process right? Not actually.

You can configure operator on multiple servers using CMS and below is the script which you need to run on a CMS.

Recently I was asked by a friend what is the default port for Database Mirroring.

Well, Generally the Default Port(End Point) for Database Mirroring is 5022 when you do it through GUI(Management Studio), But when you do it through T-SQL you can always assign any free port the database Mirroring.

This is one of the most common error which we come across while connecting to SQL Server Instance. This generally happens when you are running the SQL Server Service under a Domain Service Account and No SPN Is registered for this Service Account for this SQL Server Instance.

"Cannot generate SSPI context. (Microsoft SQL Server)"

What is the Default Port listening to SQL Server Analysis Services, How to Open/Configure Windows firewall port for SSAS(SQL Server Analysis Services)

Stuck up with above questions? Well below mentioned are the Step by Step Instructions to Open/Configure Windows firewall port for SSAS(SQL Server Analysis Services).

1. Go to Run and type WF.msc to Open Windows Firewall Console.

It was all good day and all was fine. Then there was something we were discussing. On one of my Server we had four datafiles in TempDB. Due to some reasons we needed to remove two datafiles from my TempDB. All fine. You cannot do this just like that when it is running as there might be some data in that datafiles. So what we need to do is first empty the file by migrating the data in this file to other data files in the same filegroup. I ran the below query to Empty the file and then Remove the File.

In my previous post, I had written How to add SSIS Package to MSDB. In this post we will learn how to use this stored package and Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services. Below mentioned is the step by step intructions.

1. Connect to SQL Server Instance where you want to schedule a Job, Expand SQL Server Agent, Right Click on Jobs and Select New Job as shown below.

How many of you know that you can add a developed SSIS package in MSDB? What are the advantages of this.

You are trying to take a SQL Server Database backup and end up error saying:

"Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ''. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally."

There are couple of Scenarios under which this Error Can Occur.