Archive

Monthly Archives: January 2015

When it comes to setting up a test environment, Microsoft has a free version of SQL Server that you can play with, SQL Server Express.  SQL Server express is a stripped down version of SQL Server that is meant for smaller, embedded applications.  There are some limitations to SQL express, including a maximum database size of 10 GB and being restricted to running on only a single, physical CPU.
Read More

Excellent article on how to add yourself to SQL roles if you don’t have access to SQL.  Note that this REQUIRES that your account is a member of the Local Administrators Group and your SQL Services will need to be restarted with an additional startup switch.  This WILL result in a 10-15 minute SQL outage.

http://sigkillit.com/2013/01/02/recover-sa-password-on-microsoft-sql-server/

If you have ever dealt with a transaction being blocked, but didn’t quite know how to fix the problem, this is for you.  We will go over how to find blocks using SP_WHO2.

First, we will create a database called BlockTest.  Then, I used the following to create an Employees table.

CREATE TABLE [dbo].[Employees](
       [EmployeeNo] [int] IDENTITY(1,1) NOT NULL,
       [FirstName] [varchar](20) NOT NULL,
       [LastName] [varchar](20) NOT NULL
) ON [PRIMARY]
GO

Next, we want to run a transaction that will create a lock on the database.  This transaction will create a transaction that will lock the DB for 5 minutes.

USE BLOCKTEST
GO
BEGIN TRANSACTION
SELECT * FROM dbo.employees WITH (TABLOCKX,HOLDLOCK)
WAITFOR DELAY '00:05:00'
ROLLBACK TRANSACTION

Now, run the following query to try and insert a row into dbo.employees.

USE BLOCKTEST
INSERT INTO dbo.employees values ('Test','User');

You will see that this query is taking forever to run.  This query won’t actually start until the previous transaction is complete.  To see what’s blocking this query, open another query window and use SP_WHO2.

SP_WHO2

You might have to scroll down a few times, but you will eventually see an entry that has a value in the BLKBY column.  This is the transaction that is blocked and the value in the BLKBY column is the blocking transaction.
block_image