How do I find what queries were executing in a SQL memory dump?

NOTE:  This post has been updated in a new post due to an issue found with the steps in this post.  The procedure is the same, but the steps here may only work with 32 bit dumps.  Please read the post located at the address below: http://blogs.msdn.com/b/askjay/archive/2011/02/18/finding-which-queries-were-executing-from-a-sql-memory-dump-revisited.aspx —————————————————————————————————- In this post, we’ll see how to…

8

Catching who dropped SQL Server logins

I recently had a situation where SQL server logins were getting deleted from the system and nobody could figure out why or by whom.   So in this post, we present a few ways to catch this. First, if you are using SQL Server 2008, the easiest method is to capture this via the new Auditing…

1

The parameters of xp_readerrorlog

  I find that few people know xp_readerrorlog accepts parameters to help you mine information out of the SQL Error log (as well as the SQL Agent error log).  The SQL Error log has a wealth of information.   In this post, we’ll look at some of those parameters. Running xp_readerrorlog without any parameters returns the…

2

How do I track this in my Stored Procedure? (Using a custom trace event)

  It’s been a while since I’ve posted an entry as I’ve been busy with moving to a different position that has me working dedicated to one customer here in my PFE role at MS.  I’ve been keeping up with the topics I want to post about, so there are plenty more posts to come….

0

Cool Windows 7 Feature…

I’d have to say that one of my favorite new features from Windows 7 – that doesn’t seem to be all that well known – is the new Problem Steps Recorder.  It’s a great way for someone to show you the steps they’ve taken when encountering an issue, or for you to document the steps…

0

Creating a custom performance monitor counter for SQL Server

If you have ever needed to monitor a value in SQL Server, or follow the trend of anything that can be expressed in a numerical value – then creating a custom performance monitor counter could be just what you need…. For this example, I am going to use a query that calculates the size of…

3

How do I set a breakpoint in a CLR Stored Procedure already deployed and running on a live SQL Server and debug is using Visual Studio?

For information on setup and debugging a CLR stored procedure in a direct-debug scenario by “stepping-through” the code from within Visual Studio, OR by using a test TSQL script from with Visual Studio, refer to the following MSDN article: http://msdn.microsoft.com/en-us/library/ms165051.aspx Now, suppose you have a stored procedure already deployed to a remote SQL Server that…

3

How can I create a dump of SQL Server?

You can create a memory dump of the SQL Server process space in several ways.  There are many external tools that can help you accomplish this such as userdump.exe, debugdiag.exe, and ADPlus.exe.  In this post, I’ll cover 3 common ways to accomplish this for SQL Server:  The most common way (sqldumper), using the debugger, and…

0

What do I need to know to be good at debugging?

From time to time I will get this question from people learning to debug application code.  Debugging is an unusual skill and is a combination of art and science.   However, IMHO the following 3 items or characteristics are super important for being successful with your debugging: You don’t necessarily need to be a C or…

0

Basic Debugging Concepts and Setup – Part 2 (Basic & Common Commands)

  So if you read my previous post on Basic Debugging Concepts and Setup, you are familiar with how to open a SQL Server dump or attach to the SQL Server process (sqlservr.exe).  You are also familiar with what symbols are and why we need them.  If you missed it, you can read it here:…

0