Checking for Locks/Blocks in SQL Server

OK, this is one that I will never forget, because I use it often enough that it stays fresh in my mind. I have read a lot if ways that people use to check on the server for blocks. Well, I like to keep things simple. So, I have 1 query that I run. Run it against the master database and you'll see who is your culprit causing slowness (in most cases).

use master
go

select * from sysprocesses where blocked <> 0

quite simply.. this query is going to return to you the connections that are being blocked. Now, the number that appears in the 'blocked' column is a spid (connection id) for the blocker. So, you can check the returned results and follow the block path.
Now, once you find the spid that appeared in others blocked column, but has a '0' in their blocked path, they are the ones causing all the problems.

In other words, spid 224 is blocked by 143, and 143 is blocked by 68, 68 is blocked by nobody(has 0 in their blocked column) .. 68 is your culprit!

So, say you see that 68 is your culprit. You naturally wonder to your pea brained self "I wonder what 68 is doing?"

Turns out MSFT Thought of that too.. created a DBCC function just for that. "dbcc inputbuffer" is it's name, returning query info is it's game.

Here is an example of execution that will match our purposes here

dbcc inputbuffer(68)

The query they are running, more accurately, the query that was last run by this spid will be returned in the "EventInfo" column of the results.
Please note.. You can only get a max of 256 characters returned from this process. Which, coincidently, causes me much frustration at times! Often, a query is > 256 characters!

Searching text of trigger Or SPROC

Often times, there is some code, or snippet that you cannot remember where it is, or what SPROC it's in. This will allow you to change all of that. Easy enough.. obviously, you change the search string parameter below to match your inquiry.

USE Northwind
GO

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'Employee', @notcontain = 'Order'

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 or CHARINDEX(@notcontain,syscomments.text)<>0))

Adding A foreign Key to existing table.

I know, it sounds easy.. but MSFT doesn't have any specific scripts for adding a foreign key to a table. You have to sift through BOL (Books online) and they refer you to the alter table statements. Long story short. Here is how to add Foreign Key To SQL Server table.


alter table [table Name] WITH CHECK
add constraint [fk_name] FOREIGN KEY ([column]) references [Reference table Name] ([column referenced])


in other words.. Here is how I did it.

alter table customer WITH CHECK
add constraint FK_Custid FOREIGN KEY (customer_type_id) references customer_type(id)

Restoring or Creating Mapped Drive Sql Server Database

OK, so our staging environment has been eclipsed in drive space by our production database. This creates a problem, because the database is too big for the Staging server, so now we can't restore. SO, in the interim, I restored it to a mapped drive on a different server on our network. What? Isn't that going to cause problems? Isn't it going to be slow?
Well, maybe.. but our staging environment doesn't get a lot of hits, it's just internal. No outside access, so we figured it's OK for now while we are going to re-build the box soon anyway. DON'T do this for a production environment.
So, here is how you do it.
1. Get the drive mapped and visible in Explorer (my computer)

2. Ok, drive is visible to the user. Now, can Sql server see that drive? Probably not, most likely NOT. Here is how I got the drive visible to sql server...

a. first test to see if sql server can see the drive.. run this in query analyzer
exec master..xp_cmdshell "dir P:" (whatever your drive letter is)
You will probably get an error like this : "The system cannot find the path specified."

b. Turn on the drive visibility for Sql server. Run this..
exec master..xp_cmdshell "net use P: \\ServerName\Directory"

if things went right, you get this message:
"The command completed successfully." Now, move onto the next step.

if they didn't go right.. Then, well, check the documentation for the "net use" command, and make sure you are in accordance.
ALSO, make sure that you are running the command while you are ON the server where the mapped drive is to be USED from in other words, don't do it remotely.


c. Test Connection to mapped drive.
exec master..xp_cmdshell "dir P:"
Hopefully tested out just fine..

3. Restore database to mapped drive.
Congrats, you have now gotten you sql server able to see the mapped drive, now you need to restore your database to the drive. But you get this error (Or something similar when you try to restore):

5110 "File 'file_name' is on a network device not supported for database files."

This is expected. Microsoft by default, doesn't want you to restore a database (or even create for that matter) on a mapped drive, "Don't do it" that's what they say. By default, you cannot create a SQL Server database on a network file share.
So, with all the work you have done .. MSFT won't let you finish the job.. Sorry. Well, that was fun! Who loves wasting time? I know I do!
OK, stop crying. Here is your solution. You ever played with trace flags? I know I have :). If you haven't, I guess you can sort of think of them as ports on a firewall (it's a stretch) . They are either on or they're off. You have to turn them on, or they are on by default and you have to turn them off.
For this instance, you need to turn on trace flag 1807. Trace flag 1807 bypasses the check and allows you to configure SQL Server with network-based database files. (Yes!)
OK, do this in Query Analyzer:

--BEGIN SCRIPT
use master
go

dbcc traceon(1807)

--END SCRIPT

Alright, now check to make sure that it's turned on:


--BEGIN SCRIPT

use master
go

dbcc tracestatus(1807)

--END SCRIPT

OK, the above should return a row that will show a status of "1". If the Status is "0". Then, maybe you somehow ran the first script incorrectly.

That's it.. You can now restore, or create your database as you normally would . Enjoy!

Changing in Mass the Default database for Users

Alright, this little quick and dirty script will mass assign all the users (with an exception of some listed below) to a specified default database. I ran this on SQL Server 2000 but it should work on SQL 2005.



--BEGIN SCRIPT--

declare @user varchar(30)

--create the cursor.. but take out of the list of users that I don't want to change the default
--database for. Hopefully you can fine tune this list down.
declare c_users cursor for
select name from syslogins
where name NOT IN('sa', ' 'readonly', 'BUILTIN\Administrators', 'QA\Administrator' )


open c_users
fetch next from c_users into
@user

while @@fetch_Status = 0
begin
print 'defaulting user '+@user
--PUT IN Default Database here below!
exec sp_defaultdb @user, MY_Default_Database -- <--put your default database right here
fetch next from c_users into
@user

end
close c_users
deallocate c_users



--END SCRIPT--