Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

T-SQL Tuesday #51: Doubledown disaster

Jason Brimhall is hosting the 51st T-SQL Tuesday this month with “Place your bets!.

It’s always a gamble when buying a new piece of software. You pay your money, you throw the dice and you hope that you didn’t just buy a POS (Piece Of non-functioning Software). However there are… Read more

3 comments, 780 reads

Posted in SQLStudies on 11 February 2014

Check your SQL Agent history settings before it’s too late!

A little while back I was doing some research into a failed job and ran into a slight problem. The Agent history settings were such that I was only seeing the last 2-3 runs of the job. This job is run “on demand” and I really wanted to see the… Read more

3 comments, 102 reads

Posted in SQLStudies on 10 February 2014

Transactions: What commands aren’t allowed?

Transactions are great tools that every DBA and developer should learn how to use. Unfortunately not everything can be put inside a transaction. There are a handful of commands that won’t work inside a transaction. CREATE, ALTER and DROP DATABASE for example. The full list of commands can be… Read more

0 comments, 2,038 reads

Posted in SQLStudies on 5 February 2014

Transactions: Creating a single restore point across multiple databases.

This is a disaster and recovery trick I’ve found to be useful for developers with batch processes that hit multiple databases. If you have read up much on either the BEGIN TRANSACTION or RESTORE statements you will probably have noticed the MARK option. If you mark a transaction in the… Read more

0 comments, 365 reads

Posted in SQLStudies on 2 February 2014

Using sys.dm_exec_sql_text() to figure out blocking is sometimes flawed.

I frequently rely on joining sys.dm_exec_requests and sys.dm_exec_sql_text() to know what queries are running on a system and when I have a blocking situation I like to look and see what query is running that is blocking everything else. I’ve mentioned recently that you can also use sys.dm_exec_connections.most_recent_sql_handle to see… Read more

0 comments, 123 reads

Posted in SQLStudies on 29 January 2014

Negative session_ids

I probably had the most fun all week when a query I was running came up blocked. Sounds strange right? Well the blocking_session_id was a negative 2 (-2)! I’ve never seen anything like it before. Once I had resolved my problem (see below). I started doing some research on negative… Read more

2 comments, 955 reads

Posted in SQLStudies on 27 January 2014

The “most_recent_sql_handle” column

While researching my last post I ran across an interesting column I hadn’t noticed before, sys.dm_exec_connections.most_recent_sql_handle. I mentioned it in my previous post but I felt it was interesting enough that I would point it out specifically. Here is the BOL definition:

The SQL handle of the last request…

Read more

1 comments, 119 reads

Posted in SQLStudies on 22 January 2014

Transactions: Who, What and Where

Recently we had a scenario where we had a handful of queries being blocked. Nothing unusual there but when I looked into sys.dm_exec_requests I could see all of the blocked requests, but could not find a request with a session_id matching the blocking_session_id. The session showed up in sys.dm_exec_sessions but… Read more

0 comments, 2,310 reads

Posted in SQLStudies on 20 January 2014

sp_SrvPermissions & sp_DBPermissions V4.0

These are a couple of stored procedures I wrote to help me with security research. Each sp returns three data sets.

  1. A list of principals and some basic properties about them.
  2. Role membership
  3. Object/Database/Server level permissions

Each row of each dataset has not only the appropriate properties but a set… Read more

0 comments, 355 reads

Posted in SQLStudies on 15 January 2014

Guid vs Identity columns (Ints)

I came across an interesting question on SE last week. Guid vs INT – Which is better as a primary key? In addition to the quite good accepted answer I thought I would throw in my own take.

  • Size
    • GUIDs are 16 bytes and hold more values you then could…

Read more

7 comments, 2,631 reads

Posted in SQLStudies on 13 January 2014

Transactions: What are they?

I’ve done a couple of posts now talking about how rolling back a transaction works. I thought this time I would back up a bit and talk about what exactly a transaction is and why we have them. A transaction is simply a unit of work. A unit of work… Read more

4 comments, 485 reads

Posted in SQLStudies on 8 January 2014

Transactions: Rolling back a transaction inside a stored procedure.

So over the last couple of posts I’ve talked about the fact that the ROLLBACK command will roll back an entire transaction no matter how many layers down the ROLLBACK is executed. Well this has an interesting implication with a stored procedure. If a ROLLBACK command is issued inside of… Read more

4 comments, 2,348 reads

Posted in SQLStudies on 6 January 2014

Happy new year!

Happy New Years! It’s the first day of the year and it’s a day known for setting goals. I had several goals last year and did pretty well over all. At least in my opinion which is really the only one that counts for this particular type of thing.

First… Read more

2 comments, 92 reads

Posted in SQLStudies on 1 January 2014

The 12 days after release

On the first day after release my developer gave to me

  • a performance problem on a crucial query

 

On the second day after release my developer gave to me

  • 2 deadlocked queries
  • and a performance problem on a crucial query

 

On the third day after release my developer… Read more

3 comments, 242 reads

Posted in SQLStudies on 24 December 2013

SSMS Shortcuts and more

I went and voted for #tribalawards and when I was finished they offer you links to 6 different free PDFs. Not sure if they are the same for everyone but for me it included Grant Fritchey’s SQL Server Execution Plans 2nd Edition which I highly recommend. It also included a… Read more

0 comments, 152 reads

Posted in SQLStudies on 23 December 2013

Transactions: Rolling back part of a transaction.

In my previous post I mentioned the fact that the ROLLBACK command rolls back the entire transaction all the way to the top level. If that is the case then can we roll back an inner transaction and still maintain and commit the rest of the transaction? Yes as… Read more

0 comments, 172 reads

Posted in SQLStudies on 19 December 2013

Transactions: Rolling back a nested transaction

Transactions are great and wonderful things. They make sure that our work stays atomic, consistent, isolated and durable (yes ACID). And if we use an explicit transaction (created by BEGIN TRANSACTION) and we make a mistake we can always roll the transaction back. Unfortunately rolling back a transaction and committing… Read more

0 comments, 680 reads

Posted in SQLStudies on 17 December 2013

T-SQL Tuesday #49: Wait for it

It’s T-SQL Tuesday again and this time it’s being hosted by the SQL Soldier. He’s picked the subject of Waits. As in, why am I having to wait for my query to finish. Or more specifically what is my query waiting for. Waits are a popular subject. You can… Read more

3 comments, 170 reads

Posted in SQLStudies on 10 December 2013

Undocumented functionality in FORMATMESSAGE

I was reading a rather interesting post on stackexchange “Why is % a forbidden char in a THROW message?” (which is an interesting read in and of itself) and noticed something interesting in the OP’s example.

FORMATMESSAGE('Procedure input %s is wrong', @ProcParam)

That’s pretty cool. I remember using… Read more

0 comments, 331 reads

Posted in SQLStudies on 9 December 2013

Insert into a table using only default values

A while back I talked about the DEFAULT keyword and using it to tell SQL to use the default value without having to specify an actual value. Well along that same note, what do you do if you want all of the columns to use their defaults? Or if you… Read more

3 comments, 128 reads

Posted in SQLStudies on 4 December 2013

Newer posts

Older posts