Monday, October 15, 2012

Sugru - Self setting rubber

Just saw video on this.

www.sugru.com

Looks like pretty amazing stuff and not much expensive either. I am going to find something at home that can be used with sugru.

Wonder how long it lasts in the package while it is stored.


Tuesday, August 21, 2012

How Can I Tell if My TempDB Database Has this Performance Problem?

Great Article about TempDB from BrentOzar

www.brentozar.com/go/tempDB

Monday, August 20, 2012

Kill all connections before restoring DB in SQL 2005, 2008, 2008R2

I usually run into issues while restoring DBs. Most of the time I cannot kill connections to the DB to start a restore. 

I have found two ways that it can be done




  1. Script to find all active connections and kill them. Make sure to set 
Set @dbname = 'databaseName' . Warning, this might kill all transactions that are currently running and may not rollback transactions.


Use Master
Go
Declare @dbname sysname
Set @dbname = 'databaseName'
Declare @spid intSelect @spid = min(spid) from master.dbo.sysprocesseswhere dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spidEnd


(Solution 1 was recommended by RagnaRock)


2. Secondly create a restore script (use SSMS to generate also) and add single user mode and mutli user beginning and at the end

USE [master]
ALTER DATABASE [DBname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE --// Kills all connections and starts restore process

RESTORE DATABASE [DBName] FROM  DISK = N'<backup location.BAK>'
WITH  FILE = 1
,NOUNLOAD
,REPLACE
,STATS = 5

ALTER DATABASE [DBName] --//Sets back to multiuser
SET MULTI_USER
GO