SQL database (In Recovery) mode

You are doing some transaction and suddenly there is blackout due to power failure. When everything resumes then the database shows “In Recovery” mode refusing to entertain any connection. You don’t know what to do. Yes you can do, you can wait and watch the recovery time :)

DECLARE @DBName VARCHAR(64) 
set @DBName = 'eIntuit_DW_X'
 
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 
SELECT TOP 5
  [LogDate]
 ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
 ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
 ,[TEXT]
 
FROM @ErrorLog ORDER BY [LogDate] DESC

Courtesy: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

Update SQL Table column with number range

There might be scenario you might want to update the column with number range. Here is the simple solution for doing so. 

For example from this (Figure 1) to this (Figure 2)

Figure 1

 

Figure 2

Below is the SQL code for above result.

--//PURPOSE: This sample is to demonstrate the trick 

--// of updating the SQL table column with sequence numbering.

--//Decalre the table variable

declare @TempTable as table(

[ID] INT,

[Name] varchar(50),

[Range] INT

)

--//Insert rows in the Table variable

insert into @TempTable values(1, 'Mumbai', null)

insert into @TempTable values(2, 'Navi Mumbai', null)

insert into @TempTable values(3, 'Delhi', null)

insert into @TempTable values(4, 'Kolkata', null)

insert into @TempTable values(5, 'Chennai', null)

insert into @TempTable values(6, 'Assam', null)

--//Do select to check the result of table variable with range column equals to null.

select * from @TempTable

--//Decalre the variable

declare @cnt int 

--//Set the variable to the value you want to keep as start.

set @cnt = 100

--//HERE IS TRICK. The single update statement to update entire table

update @TempTable set @cnt = [Range] = (@cnt + 1)

--//Check the result of the updated column [Range]

select * from @TempTable

Happy SQL Scripting !!!!