Tuesday, 30 October 2012

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 :)
set @DBName = 'eIntuit_DW_X'
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName
 ,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
FROM @ErrorLog ORDER BY [LogDate] DESC
Courtesy: http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

Thursday, 25 October 2012

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(
[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 !!!!