Helpful scripts for SQL Server

There is nothing new or revolutionary about the following scripts but I am compiling them here for anyone that these may help.

SQL Server database marked 'suspect'

There are various reasons that the DBMS may mark your database as suspect . Some of these are

• The database could have been corrupted
• Not enough space for the DBMS to run a recovery operation.
• OS locks on the files
• Unexpected sql server shut down.

To resolve this copy and paste the following script into SSMS. Replace [MyDatabaseName] to the actual database that you are trying to recover.

use master
go
EXEC sp_resetstatus [MyDatabaseName];
ALTER DATABASE [MyDatabaseName] SET EMERGENCY
DBCC checkdb(MyDatabaseName)
ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (MyDatabaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MyDatabaseName SET MULTI_USER

Get a list of all indexes on all tables in your database

There are instances when you need a list of all the tables, with index description and keys in your database. The following script lets you do just that.

DECLARE @IndexInfoTemp  TABLE (index_name         varchar(250)
                              ,index_description  varchar(250)
                              ,index_keys         varchar(250)
                              )

DECLARE @IndexInfo  TABLE (table_name         sysname
                          ,index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

DECLARE @Tables Table (RowID       int not null identity(1,1)
                      ,TableName   sysname 
                      )
DECLARE @MaxRow       int
DECLARE @CurrentRow   int
DECLARE @CurrentTable sysname

INSERT INTO @Tables
    SELECT
        DISTINCT t.name
        FROM sys.indexes i
            INNER JOIN sys.tables t ON i.object_id = t.object_id
        WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1

WHILE @CurrentRow<=@MaxRow
BEGIN

    SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow

    INSERT INTO @IndexInfoTemp
    exec sp_helpindex @CurrentTable

    INSERT INTO @IndexInfo
            (table_name   , index_name , index_description , index_keys)
        SELECT
            @CurrentTable , index_name , index_description , index_keys
        FROM @IndexInfoTemp

    DELETE FROM @IndexInfoTemp

    SET @CurrentRow=@CurrentRow+1

END --WHILE
SELECT * from @IndexInfo

Kill all sleeping processes in SQL Server

There may be occasions where you may want to get a list and subsequently kill all sleeping processes. Here is a script that lets you do that.

DECLARE @v_spid INT

DECLARE c_Users CURSOR

   FAST_FORWARD FOR

   SELECT SPID

   FROM master..sysprocesses (NOLOCK)

   WHERE spid>50 

   AND status='sleeping' 

   AND DATEDIFF(mi,last_batch,GETDATE())>=60

   AND spid<>@@spid

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid

WHILE (@@FETCH_STATUS=0)

BEGIN

  PRINT 'Exterminating '+CONVERT(VARCHAR,@v_spid)+'...'

  EXEC('KILL '+@v_spid)

  FETCH NEXT FROM c_Users INTO @v_spid

END

CLOSE c_Users

DEALLOCATE c_Users