There is nothing new or revolutionary about the following scripts but I am compiling them here for anyone that these may help.
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
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
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
