February 2, 2011 at 4:37 pm
i came across a strange situation yesterday. The restore transaction log job started failing with error message as Database is in use and cannot restore the log. Inspite of checking and killing all the active sessions before the logs are restored, this error message appeared.
Then i tried to have a look at all the sessions on the db server using sp_who2 and activity monitor and i was shocked to see that there are NO sessions at all connected to the database. This kept on happening for almost an hour and to my surprise, the agent job started restoring logs (as it is scheduled to run every 5 mins) without any changes made to database and agent job.
has anyone experienced this problem before. this is the second time i am facing this issue in last two weeks.
sql server version: sql server r2 (10.50.1600)
February 2, 2011 at 9:50 pm
set db to single user mode w/ immediate roll back and then set it back to multi_user, then start your tran restore.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 2, 2011 at 9:55 pm
thanks for the reply, i am aware of the alter database command and it will ensure that all connections have been dropped.
my point is, if SQL Server is saying database is in use and due to this reason it cant restore tran logs, then why the connection information is not displayed in sp_who2 and activity monitor. also when i ran a query to find SPIDs with database name, query returned no records.
is there a bug in SQL Server R2?
February 2, 2011 at 9:57 pm
what else is your db being used for? are there any .net or web application accessing the db?
did you take a look in profiler and fliter for the db to see if there are any activities going on?
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 2, 2011 at 10:32 pm
i havent run the profiler. i tried restoring tran log manually in SSMS but it threw same error. by the time i wrote the query to alter db with single user, dont know what happened and sql server started restoring logs on the next scheduled time. therefore, nothing was changed.
if there would have been any application accessing the database, the connection must have appeared in sp_who2 or activity monitor where as in my case, there was nothing in both of them.
February 2, 2011 at 11:33 pm
in my environment, .net application that testers/devs have hooked into the system sometimes doesn't show up in sp_who2.
i would try to drop a db and sp_who2 doesn't show anyone being connected, yet, i can't get an exclusive lock on the db to drop it.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
February 2, 2011 at 11:38 pm
I have faced this situation in SQL Server 2000.
Processes didn't show up in sp_who2 but showed up in sysprocesses. I eventually killed the processes in saw in
sysprocesses and the restored the log/database.
M&M
February 3, 2011 at 3:49 am
Were you restoring from the query window in SSMS with selected database as the database you were restoring to?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply