December 28, 2005 at 2:12 am
I have a simple batch job that uses OSQL to restore a database and then setup some stored procedures. The batch job then uses BCP to extract data from the database using those stored procedures.
The restore usually took around 10 minutes however when I came in this morning I found that none of the jobs on this database had run because the database was stuck (IN LOAD).
How can I find out why it is stuck?
How can I fix it?
December 28, 2005 at 5:07 am
USE master GO restore database <YourDBName> with recovery
try it out
December 28, 2005 at 5:10 am
USE master
GO
RESTORE DATABASE <DBNAme> WITH RECOVERY
December 28, 2005 at 6:49 am
My SQL Script File is as follows
use master
go
------------------------------------------------------------------------------------
-- If the backup device already exists then drop it and recreate it
------------------------------------------------------------------------------------
IF EXISTS(SELECT 1 FROM sysdevices WHERE name='epos_Backup')
exec sp_dropdevice 'epos_Backup'
GO
exec sp_addumpdevice 'disk','epos_Backup','D:\Epos\BACKUP\DW_EPOS.BAK'
GO
-- ----------------------------------------------------------------------------------------
-- -- Restore the database from the newly created backup device
-- ----------------------------------------------------------------------------------------
RESTORE DATABASE EPOSDW
FROM epos_Backup
WITH MOVE 'EPOS_Data' TO 'D:\SQLUSERDB\EPOS_DATA.mdf',
MOVE 'EPOS_Log' TO 'D:\SQLUSERLOG\EPOS_LOG.ldf',
RECOVERY
GO
WAITFOR DELAY '00:05:00'
GO
I know that the script works and I suspect that something is wrong with the backup set.
What I am not sure of is how to get out of the situation where the database is in the "IN LOAD" state without restarting the server?
I should also be grateful if someone could give me some tips for diagnosing what is wrong with the backup set.
December 29, 2005 at 10:18 am
Give this a try:
kill the spid for the script (if it is still active)
exec sp_resetstatus @DBName='your_loading_database'
drop database your_loading_database
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply