September 19, 2014 at 2:42 am
Hi all ,
SQL server 2005[Standard]
Database goes into Restoring mode it is Production server standard edition
Database size is more than 2tb
Thanks
Naga.RohitKumar
Thanks
Naga.Rohitkumar
September 19, 2014 at 3:09 am
If a database goes into restoring mode it implies a database restore action is started. Run the query below see information about the active backup/restore actions.
SELECT
convert(NVARCHAR(60), db_name(database_id)) AS [database]
,CASE command
WHEN 'BACKUP DATABASE'
THEN 'DB'
WHEN 'RESTORE DATABASE'
THEN 'RESTORE'
ELSE 'LOG BACKUP'
END AS [type]
,command
,start_time AS [started]
,dateadd(mi, estimated_completion_time / 60000, getdate()) AS [finishing]
,datediff(mi, start_time, (dateadd(mi, estimated_completion_time / 60000, getdate()))) - wait_time / 60000 AS [mins left]
,datediff(mi, start_time, (dateadd(mi, estimated_completion_time / 60000, getdate()))) AS [total wait mins (est)]
,convert(VARCHAR(5), cast((percent_complete) AS DECIMAL(4, 1))) AS [% complete]
,getdate() AS [current time]
, Loginame
, Hostname
, program_name
FROM
sys.sysprocesses sp
left outer join sys.dm_exec_requests der
on sp.spid = der.session_id
WHERE
command IN (
'BACKUP DATABASE'
,'BACKUP LOG'
,'RESTORE DATABASE'
)
September 19, 2014 at 3:17 am
Or someone has taken a tail-log backup. (BACKUP LOG ...WITH NORECOVERY)
Check the log backup jobs, make sure someone hasn't mistakenly checked the 'backup tail of the log' option'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2014 at 3:22 am
Hi Gail
There is no Other DBA or no one have Access
no one take tail-log backup it just simply went in to restoring mode in that case how can we make the database in to online dbsize is 2TB
if we have to restore the tail log backup wht is the order for
full sun 10am
diff daily
tran 1hour
Thanks
Naga.Rohitkumar
September 19, 2014 at 3:27 am
To just bring the database out of restoring state (when no restore action is active) you only need to specify this with a restore command:
RESTORE DATABASE [db_name] WITH RECOVERY
September 19, 2014 at 3:30 am
naga.rohitkumar (9/19/2014)
...no one take tail-log backup it just simply went in to restoring mode ...
And btw: a database doesn't go "just in to restoring mode". An action is required to do so. It could be you are not aware of such action because it's scheduled or it's by an external (third) party tool or....
September 19, 2014 at 3:40 am
naga.rohitkumar (9/19/2014)
no one take tail-log backup it just simply went in to restoring mode
Databases don't 'just go into restoring mode'. If the DB is RESTORING (not RECOVERING, that's different), then someone ran a restore, ran a backup with the NORECOVERY option or failed over a database mirroring setup.
And no, you don't just go restoring backups at random. Figure out what happened first, backups and restores are logged, if it's a mirroring pair you would, I assume, know that and check the partner.
Edit: Is this also a scenario which the previous DBA has given you to solve?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply