Running all scripts in a directory against a particular database

At some point, you may have the need to rapidly apply a bunch of scripts on a particular database.  Rather than manually opening each in Management Studio and executing them, you can use sqlcmd to run all scripts in a particular directory, in alphabetical order.  Should save you some time:

for %f in (“c:\path\to\dir\*.sql”) do sqlcmd -S [SERVER_NAME] -d [DATABASE_NAME] -i “%f” -b

Negative Process ID (spid -2) in SQL Server

You may find a negative spid blocking another process on your server. The source of the negative spid (SQL Server Process ID), specifically a spid of -2, was the Microsoft Distributed Transaction Coordinator (MSDTC). Most applications probably never go through MSDTC, but if you want your application to modify tables in multiple databases as a single transaction, then MSDTC is the developer’s friend because it makes the transaction easy to code. The trade-off is that a little bit of transaction management resides outside of SQL Server in the MSDTC. If the application fails to commit or rollback the transaction, the MSDTC transaction becomes orphaned and is assigned a spid -2.

Getting rid of this database connection is not as easy as issuing a kill for spid -2, but you just need to perform one additional step. To kill the orphaned transaction:

1) Use this query to get the UOW (a GUID) of the offending transaction:

use master
select distinct req_transactionUOW from syslockinfo

Note: Ignore the UOW records that are all zeros. “00000000-0000-0000-0000-000000000000”

2) Use the Kill command, replacing the GUID below with the req_transactionUOW obtained from the query above, to kill the offending transaction:

KILL ‘D5499C66-E398-45CA-BF7E-DC9C194B48CF’

Repeat for each orphaned transaction.

Limiting connections to your SQL Server to only certain IPs

While SQL Server does not have any inherent ability for IP filtering, it can be accomplished via DLL Logon triggers.

Read more: http://sql-articles.com/blogs/ip-address-blocking-or-restriction-in-sql-server/

Recovering a database with a missing ldf files and no backups

Hopefully, you would never find yourself in this position to begin with.  But if so, here are the steps to get a working database back with a viable log:
1. Detach database and move your mdf to save location.
2. Create new databse of same name, same files, same file location and same file size.
3. Stop SQL server.
4. Swap mdf file of just created DB to your save one.
5. Start SQL. DB will go suspect.
6. ALTER DATABASE <your db> SET EMERGENCY
ALTER DATABASE <your db> SET SINGLE_USER
7. DBCC CHECKDB (<your db>, REPAIR_ALLOW_DATA_LOSS)
8. ALTER DATABASE <your db> SET MULTI_USER
ALTER DATABASE <your db> SET ONLINE

In SQL 2000, this could be accomplished using the DBCC REBUILD_LOG command, but that is unsupported in SQL 2005.

Enabling AWE on SQL Server 2005 for servers with > 4GB RAM

Here are the steps to enable AWE so that Sql Server 2005 can use additional memory, if your physical memory is more than 4 GB on 32-bit Windows 2003 box.

1. Add /pae at the end of last line inside c:\boot.ini
2. Run gpedit.msc. On the left hand pane, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, select User Rights Assignment
3. On the right hand pane, find Lock pages in memory and double click, then add your Sql Server startup account into Local Security Policy Setting tab;
4. In Sql Server Management Studio, run:
sp_configure ’show advanced’, 1
reconfigure
sp_configure ‘awe enabled’, 1
reconfigure
Ignore the error message below, if you have it.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.
5. Reboot.

Find all tables without triggers

In our production environment, every user table has an auto-generated trigger that facilitates auditing.  Since trigger generation is an automated process, it occasionally fails to run for a plethora a reasons; this it is a good practice to periodically scan for tables that are missing triggers.  The following query will return tables that fit this condition by using a LEFT JOIN between sysobjects and itself.

SELECT   sys1.name
FROM     sysobjects sys1
LEFT JOIN sysobjects sys2
ON sys1.id = sys2.parent_obj
AND sys2.xtype = ‘TR’
WHERE    sys2.name IS NULL
AND sys1.xtype = ‘U’
ORDER BY sys1.name

SQL Server Interview Questions

In case you’re looking for some quick questions to screen a prospective candidate working with SQL Server, here is a link to some SQL Server Interview Questions (from SQLAuthority.com).

Text to Binary Translator

…because you never know when you’ll need one.

Link

Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO