Printable View
I just came to know that we can gain exclusive access to a SQL Server Database. I am not sure about it, since I have not done it successfully. So thought that members over there can help me in this topic. Please tell me that can I gain such access? Also I want to know more about the Rollback and Shutdown that takes place in SQL Server Database. I hope that some of you must have understand what I want?! Please help me by providing appropriate solutions. :notworthy
You will have to code for getting an exclusive access to a SQL Server Database. Since, I am not having it, I have not posted it. Will post it soon as I get that. If you get the code, all you have to do is observe all current transactions are rolling back. That does not denote that you'll have the database to yourself any time soon though. Observe how big depending on the dealings is and how they are far along, it may be rolling back for more than a few hours. Take for occurrence if someone is running a great update or delete operation and it's been running for 6hrs. When you run the above command it'll start rolling it back without impediment, goal it will not be finished for at least 6hrs. This is why we say that can be ambiguous this parameter, because for those who do not know sufficient about it, it seems like your exclusive access is going to happen right away. This is one of the reasons database are always moralizing about small transactions. If you have to gain exclusive access to a database for any reason, you'll be far more likely to do it if your transactions are small and there's not much to rollback.
The additional options for this command and are subsequent to no_wait. After given is a numeric value in seconds. So if you desire SQL server to wait for 5 seconds before endeavoring to rollback any transaction, would you type rollback after 5. This can be helpful if you know a process is almost done and you want to give it time to complete as an alternative of forcing it to rollback. It's uncertain that you would give something only 5 seconds to complete so you would characteristically either wait pending you can see that it's done, gold give it a couple minutes or so. No_wait is another option that's misleading. It does not mean the identical thing that means no-wait in the section below. Here it means that if the transaction cannot be killed instantly, that is, if they will have to rollback, then cancel the alter database request. This is versatile if you want to know whether you'll get exclusive be able to access right away, and if you're not then you may prefer another method.
Now, if you're reporting system was where there will not be any genuine business transactions, and then you can still use the exceeding rollback method, to you may also want to consider using shutdown. All you have to do is open a query window in ssms and type: shutdown with nowait. That mentions that the SQL server to turn off the service the director. What is this in actuality does not because SQL server to perform a checkpoint there previous to they're the complete databases shutdown. If you do this on the incorrect type of system you will force SQL server to do a recovery process when you turn the service back on. Depending on what was occurring when it was shut down, it could be hours before your database. So be cautious with this command and type of system knows what you're working with. Unluckily by, it's not that easy because while you were able to shutdown SQL server, which absolutely kicked the entire user out of the database, you still have the problem of gaining exclusive access. Often times you have process running against your database and ounces SQL server comes back up, they straight away begin trying to connect again. So this leaves you with a freshly restarted SQL server, goal with copiousness of user connections attached. However, at this point it should not be too complicated to use the above because rollback command nothing has really had time to get lots of work done, the rollbacks should be fairly quick.