Something about deadlock

This is a nice article that talks about how to find more details about deadlock.

Finding and Extracting deadlock information using Extended Events

http://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

Using TRY/CATCH to handle the deadlock

https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx

Code sharing

I've been thinking of sharing my codes for many years and finally I decided to do it.

I have uploaded the table structures, views, stored procedures and functions into Github. You may download and use it freely. I will update the contents if I encounter the useful script and table design.

  https://github.com/lauhw/MSSQLProc

Let me know if you have anything to share with me. ;)

Dynamic SQL usage

One of the usefulness about dynamic SQL is for database maintenance. For example, you want to run the maintenance process in the selected databases.

Below is the flow of using dynamic SQL to ease the maintenance:

• Create a table that stores the database names for your system.
• Declare a cursor and loop through all your database names and match the process number (explain later).
• Generate the dynamic SQL statement to be executed against the selected databases.
• Finally, execute the dynamic SQL.

The reason to have a table to stores your database names:

• You are not interested in all databases stored in sys.databases. You just want to run the script against the selected databases.
• The database name did not follow any convention before you take over the maintenance. In you are handling the database server, then, you should follow a standard naming convention for the database name. This allows you to query the database name from sys.databases.
• You don't want to execute the SQL statement against all your application databases. Only certain databases are going to run script 1 and other databases are going to run script 2, etc.

The table design to stores the database name:

• db_name nvarchar(255) - this is the database name
• process_1 int - if '1' means run the maintenance stored procedure #1. '0' to ignore.
• process_2 int - if '1' means run the maintenance stored procedure #2.'0' to ignore.
• (other process flag).

Getting the list of parameter for stored procedure

There are 2 ways to list down the parameters for stored procedure.

(1) In SQL2000 or above, you may do this:

  select *
  from syscolumns
  where id = object_id('pr_get_sales_data')

(2) In SQL2005 or above, you may do this:

  select *
  from information_schema.parameters
  where specific_name = 'pr_get_sales_data'