September 22, 2011 at 5:12 am
I have two servers. One belongs to a user AOR, one is my QC server. The user server has linked servers that connect to the QC server. Lately, our "prodcopy" restores to QC have been failing because users are still connected to the database and the KILL Users proc I have isn't resetting the connections. Yet, there never appear to be open connections on the QC server to be causing this problem.
When I go to the user server and start killing processes, suddenly the restore works. So I've narrowed down the cause to the linked servers.
Today I noticed their data drive (where the database files are kept) is full up, and probably has been for several days. Oddly enough, probably as long as we've been having our restore issues.
Is it reasonable for me to assume that the full disk drive means that whatever queries they are running get hung in the middle of execution, as do the connections, and do not properly drop the linked server connection to our QC server? Or am I just blowing smoke and making the wrong call with this one?
September 22, 2011 at 3:14 pm
Instead of killing the sessions one by one, try doing this:
ALTER DATABASE [dbname] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
-- this will kill all connections to the db, except for those with sa or dbo access
GO
--do restore:
RESTORE DATABASE [dbname] ...
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
It's possible that after killing sessions and before starting the restore, more connections are initiated against the database, possibly link-server connections.
BTW, what is the error you are getting?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 23, 2011 at 4:24 am
There are no additional connections rejoining because this restore happens at 5:00 a.m. when no users are here. I know this because I'm in the office when the restore fails and there isn't a single soul here when I get here.
The error is the standard one for when users are connected to the db.
I know you're trying to help with suggestions on how to kill the users, but that's not what I'm asking.
My question is "Is it reasonable to assume that the full disk drive is what is causing the user query to freeze and not properly disconnect?"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply