I have a VB application that uses SQL Server 2000. I am adapting it to support both 2000 and 2005. I connect to the database using ODBC. My application has the typical backup/restore functionality. Before I do a backup or restore, I check to see if the database is currently being used by another application. In SQL Server 2000, I did this by connecting to the 'master database' and running the following query:
SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'
If I get a count greater than 0, someone else is using the database. This of course does not work on SQL Server 2005. I have come up with an alternative. For SQL Server 2005, I connect to the database I want to backup or restore and run the following query:
SELECT COUNT(*) FROM sys.dm_tran_locks
If I get a count greater than 1 (1 because I had to connect to the database myself to run the query), someone else is using the database. The problem is, it isn't terribly reliable. I sometimes run the query and get a count greater than one, then try again a few seconds later and get a count of 1. Having the Studio Enterprise manager open to the point that you can see all database in the database tree also has an impact. The problem must be more complicated than my simply solution can handle. Trouble is I am having a hard time finding any docs that discusses the issue. I am probably just no looking in the right place. Does anyone have a better way to determine if another process is using the database you want to backup or restore?
Why worry for backups, the operation is online, i.e. people can be accessing the system whilst the backup is running.
Not the case for restores but they should be few and far between. You can kick everyone off, by using the ALTER DATABASE command in SQL 2005.
If you do need to find connections go to sysprocesses in SQL 2000 and in sql 2005 sys.dm_exec_requests
|||I figured that might be be someone's response, but I wanted to keep my post short so I did not explain any further. While backups can be done on-line, in my case it would not be appropriate. I am doing a backup and restore as part of my database upgrade process. A backup is done just before the data upgrade (adding and deleting tables, fields, moving data around, etc). The restore is used to recover from any failure during the upgrade process. So, allowing the users to be in the system for a few more moment's just postpones the problem. I cannot have someone in the database while I am making structure changes.
That said, kicking them off unexpectedly isn't very elegant either. I would much prefer to put a check before going through this process to tell the user that someone is connected to the database and I cannot proceed until they are out. I am running this business application in locations across the country. It is used by "normal" users who may have few skills other than a knowledge of how to run the business application.
I do appreciate the suggestion to use the sys.dm_exec_requests stored procedure. I'll look into this now.
Thank you very much!
|||You could issue aalter database <database> set single_user
And just wait. If it timesout then someone is still in the db.
|||That also stops new users from connecting.|||This looked like a really good idea. I tried using it by connecting to the database via my application, then going to Management Studio and entering the alter database as you suggested. The result was that it waited indefinitely for the lock to be removed (it never timed out). I searched around for how to set the timeout period. The only thing I could find is 'set lock_timeout=n' but it is documented that alter database ignores this setting (as does create database and drop database). I could not find an alternative.
I'll go back and try to work with your first suggestion.
Thanks!
Andy
|||I went back and found the problem with my original solution. The SQL Server 2000 approach and the corresponding SQL Server 2005 approach are more similar than I thought. In SQL Server 2000, I ran the following query to see if anyone was using the database called 'MyDatabase':
SELECT COUNT(*) FROM SYSDATABASES WHERE DBID IN (SELECT DBID FROM SYSLOCKS) AND NAME = 'MyDatabase'
In SQL Server 2005, the following seems to work the same:
SELECT COUNT(*) FROM sys.dm_tran_locks WHERE resource_database_id IN (SELECT dbid from sys.sysdatabases where name='MyDatabase')
Thank you for you other suggestions. Setting the access to single user is probably better, because it prevents people using the database part way through the upgrade process. In order to use it, I just need to figure out how to make it timeout in a reasonable period of time.
Andy
|||You can set the timeout in your application. If using ADO the command object has a command timeout.
As for your second set of code. you can simplify it by using db_id('your database name') to get the database_id of your database
|||Thanks for the info. I am not using ADO, but I'll keep that in mind if I ever switch. The biggest problem that prevents me from using ADO is the lack of support for cursors. Not everything works well with a disconnected recordset. I keep hearing that support will be added back in... but I do not think it has happened so far.|||Why do you need cursors?
What do you use instead of ADO?
What are you programming in?
|||I only have one need for cursors (but it is big). In many placed in an application, I have to manage a collection of items where that is very large (several thousand to as many as 10 million). It isn't practical to load that many things in a collection (it takes too long) or display that many things in a listbox or spreadsheet. The way I solve this problem know is by paging the data into and out of the collection.
While getting a page worth of data doesn't require a cursor, navigating from page to page without it has its problems. For example, just bringing up a vertical scrollbar is a problem. There isn't a way to tell with a disconnected recordset what page you are on relatively to the entire collection. Going to the next and previous page is terribly innefficient (especially if you are not near the top of the collection) because ther isn't a good way to get the 'next' or previous page. Disconneted recordsets involve rerunning queries to go from one page to the next (which does not perform well). These are problems I can solve with a cursor.
Of course the applications that I am writing are desktop applications with a relatively small number of users. A lot of folks say cursors are expensive and do not scale well. In my case, that is a good trade off. Most of those folks are writing web pages which is a very different animal. I would rather have a highly function and high performing interface than be concerned about scalability to a number of users because my applications are typically used by less than 100 users at at time.
Anyway, all of my applications are written in VB 6.0. I use the ODBC APIs for database access. Most of them work against more than one database (e.g. SQL Server, Access, Sybase). I will begin converting them to .NET next year (I am working on a prototype of the .NET archicture I will use and a prototype of how to convert them). I will probably leave the data access alone, since the existing data access works will in .NET. This also allows me to focus my efforts on just getting the applications working again.
No comments:
Post a Comment