Category Archives: Troubleshooting

Example of Deadlock | SQL Server Deadlock Script

IS there a way I can stimulate a deadlock in SQL Server, so that I can use that as an example of deadlock Let’s take an example, where we will result / cause a dead lock to SQL Serve? To stimulate the Deadlock, let’s create two tables which we are going to use as an [...]

How to TEST SQL Server Storage drives ?

How will you check, how your SQL Server Drives are performing ? What is the throughput of the Disk Drive / Storage where your Database file resides ? What is SQL Server Storage speed ? is that fast on write operations or fast or read operations ? All this questions can be easily answered by [...]

What Performance counters will you use to set up SQL Server baseline ?

What Performance counters will you use to set up SQL Server baseline ? Setting up SQL Server baseline is primary objective for every DBA. This baseline will be key during troubleshooting performance issues. A DBA should setup up a server base by using the following performance counters. S. No Counter Name Description 1 Memory\Available Bytes [...]

SQL Script to kill problematic session from SQL Server

SQL command / script to exclude/terminate/kill a problematic sessions from a SQL server. — SQL Script to kill problematic session KILL 54 WITH STATUSONLY; This script can be used if you wanted to kill a particular process from the server.  But prior to kill a particular session / process from the server, we need to [...]

Agent XPs Component is turned off

Cannot show requested dialog. Additional information: unable to execute requested command. ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. In Microsoft SQL Server Management Studio i get the following errors when trying to create a [...]

SQL Server, Error: 547 Maintenance Plan Error

I was ‘trying’ to delete an obselete SQL Server Agent job until this nice error popped up: The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column ‘job_id’. The statement has been terminated. (Microsoft SQL Server, Error: 547)   Drop failed for Job ‘MYMAINTENANCEPLAN.Subplan_1′.  (Microsoft.SqlServer.Smo) —————————— ADDITIONAL [...]

How to check when was SQL Server installed ?

I am using Evaluation edition for testing and writing blog. Evaluation Edition works for 180 days from the date of installation. Thus, it’s very import for me to know When was my SQL Server Instance Installed when it is going to stop working Script to get this Information SELECT create_date as ‘SQL Server Install Date’, [...]