Deadlocks - solving

Great blog here on how to investigate the SQL causing deadlocks. Pretty comprehensive, clear steps concentrating on the information presented by SQL 2005.

Moving logins when migrating a server

This is how MS recommends migrating logins when moving a database. Very useful, and removes the need to run sp_change_users_login .

2007 best scifi stories - for free

Links to the 2007 nominated sci-fi books - all to read for free. Some good reads there and worth a visit.

Data Table Shrinking

One the the most annoying features of SQL Server is that the GUI based shrink commands rarely work. In a development environment we do not always have the space to store a database and it's predicted growth, so shrinking is a necessity.

The following script will shrink all the files in a database - data and log, and free up that much needed space. Just edit it and replace the logical file names. It will shrink any number of files - just add/remove the commands as required.

use master
go
BACKUP LOG [BSE] with TRUNCATE_ONLY
GO
DBCC SHRINKDATABASE(N'BSE')
GO
USE BSE
GO
DBCC SHRINKFILE(BSE_dat, 10)
GO
DBCC SHRINKFILE(BSE_dat2, 10)
GO
DBCC SHRINKFILE(BSE_dat3, 10)
GO
DBCC SHRINKFILE(BSE_dat4, 10)
GO
DBCC SHRINKFILE(BSE_Log, 3)
GO

Tally Table - loop replacement

Excellent article here on using Tally Tables instead of a loop. The performance increases are amazing. In essence the Tally Table is used to replace the loop, and using set based logic rather than a traditional loop gives high performance gains. Worth a read at least.