Category Archives: Errors and Fixes

How to update system tables SQL 2008 R2 (FIX: “Ad hoc updates to system catalogs are not allowed”)

Posted by yrushka on April 24, 2013 None comments

I came across an old version update that was enabled for all SQL 2005 + versions, meaning: update on system tables. In older versions, there was the possibility to update system tables, thus allowing you to perform some administrative tasks/fixes without having to shut down the server.
I agree that updating system tables are not meant to be done by an usual user, and therefore hacking SQL should be done… Continue reading

msdb.dbo.sp_readrequest;1 – long running process

Posted by yrushka on October 23, 2012 None comments

When monitoring the new installed version of SQL 2008 R2 SP1, I have encountered that Database mail leaves a hung process few minutes. This doesn’t do any blockages or other trouble to your SQL installation, either that it is anoying to see over and over again this long running process. In order to get rid off it, just check the system parameter value for “DatabaseMailExeMinimumLifeTime” running the following query:
Use msdb… Continue reading

Restore SQL 2008 R2 Database on SQL 2008 SP1 instance

Posted by yrushka on October 24, 2011 12 comments

It has been a long time since I wrote a new post, but now I ‘m back after a very busy period and I would like to share with you a recent problem I had with a restore from a higher version of SQL Server – SQL 2008 R2 to a lower version – SQL 2008 SP1.

If you search the internet, you will be sad to find out… Continue reading

RangeX-X Key-Range Locks in SERIALIZABLE isolation level (part 3)

Posted by yrushka on June 10, 2011 None comments

I will continue in this part describing other types of Key – Range locks: RangeX-X combined with RangeS-U locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeX-X Locks

RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index. The locks are exclusive locks… Continue reading

RangeS-U – Key-Range Locks in SERIALIZABLE isolation level – (part 2)

Posted by yrushka on May 30, 2011 None comments

I will continue in this part describing other types of Key – Range locks.

As a reference I will use the same table created in part 1 – range_lock.

RangeS-U Locks

RangeS-U locks are locks acquired on Index key values from a specific range when the following conditions are met:

  • The transaction contains an UPDATE statement
  • WHERE clause is used to specify a condition
Continue reading

RangeS-S – Key-Range Locks in SERIALIZABLE isolation level (part 1)

Posted by yrushka on May 25, 2011 1 comments

Overview
 

Locking mechanism in SQL is by far one of the most complex mechanism and hard to explain. Blockages and Locks are a DBA’s daily problems when confronting with concurrent processes which access same records of the same table. In order to have a high – performance system, a very important aspect to consider is isolation of concurrent operations. In high-activity systems like web-sites, ERP’s or online shops… Continue reading

Handling SQL Deadlocks – One Resource Conflict

Posted by yrushka on April 1, 2011 2 comments

From a DBA point of view, Deadlocks occurred in Production environment are probably the trickiest and time consuming problems to fix. Every time I came across this, it took me a while to understand where it is coming from, why, and how to avoid a particular Deadlock. This blog will go through some types of deadlocks and how to deal with them.

Here I will analyze a Deadlock caused by an After… Continue reading

Read memory dump file after a BSOD event

Posted by yrushka on November 15, 2010 None comments

I decided to post about this problem since I saw many questions about this issue. There are several tools that can be used to read a memory dump like Windbg.exe or Dumpchk.exe. In this article I will explain the usage of the Windbg debugger.

First of all what is a memory dump and in what circumstances we have to deal with it?

The BSOD bluescreen or “blue screen… Continue reading

SQL ERROR Fix: sp_MS_marksystemobject: Invalid object name ‘[cdc].[cdc_TABLE1]‘

Posted by yrushka on November 11, 2010 1 comments

Hi, one of the new features in Microsoft SQL Server 2008 is the ability to track changes on a table. You can enable change tracking on a table using the Change Data Capture feature. It is extremely useful for BI (Business Intelligence) processes because it allows to track only the changes done on a table instead of importing the whole table each day for processing. Today me and a DEV… Continue reading

SQL ERROR Fix : Error : Msg 9514, Level 16, State 1, Line 1 Xml data type is not supported in distributed queries. Remote object ‘LINKED_SERVER.Database.dbo.Table’ has xml column(s).

Posted by yrushka on November 11, 2010 None comments

I ran into a rather senseless restriction put by MS when using INSERT INTO…SELECT FROM to transfer few rows through a Linked Server from a table that has a XML data type column even if that column is not listed in “insert into” statement. See detailed the reproduction of this error.

The insert statement from this example uses a variable for filtering options.

Statement:

DECLARE @maxid as nvarchar (max)… Continue reading