Monitoring Auditing Active Current Running Processes, TSQL queries in SQL Server using SYSPROCESSES and storing results in table

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.

Configure Alert in SQL Server for Severity Levels

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

Configure Default Operator in SQL Server for sending alerts via Database Email

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.

Default Port for Database Mirroring in SQL Server

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.

 

Cannot generate SSPI context. (Microsoft SQL Server)

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)"

How to Open/Configure Windows firewall port for SSAS(SQL Server Analysis Services)

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.

DBCC SHRINKFILE: Page : could not be moved because it is a work table page Cannot move all contents of file "" to other places to complete the emptyfile operation.

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.

Schedule SQL Server Agent Job from SSIS Package Stored in MSDB/Integration Services

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.

Cannot open backup device ''. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.

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.

Pages