|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Setting database to single user mode allows connections from outsiI'm trying to use the following script as a job which should be running each first day of the month, at midnight, to repartition the database. This script suppose to disconnect all database users currently connected to the database, put the database into single user mode, and after doing re-partitioning work (within the same script), to put the database back into multi user mode... Unfortunately, it doesn't work as I planned: Since there is a load running when this jobs has to be executed, job disconnects all connections, but after setting database into single user mode, application successfully reconnects to the database and grabs that single connectionso that the rest of the job is not able to complete the work (since database is still in single user mode, and that connection is already taken by the application). Even worse, now PROD database is in single user mode, and I'm not able to return it to normal multi user mode!?!?! It is totally different (unfortunately worse) behaviour comparing to sql server 2000!!? Does anybody have an idea how to get out of this ridicilous situation? declare @sql nvarchar(max), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorProcedure nvarchar(126), @ErrorLine int, @ErrorMessage nvarchar(4000) -- seting database to single user mode: begin try alter database PROD set single_user with rollback immediate end try begin catch select @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state(), @ErrorProcedure = error_procedure(), @ErrorLine = error_line(), @ErrorMessage = error_message() rollback transaction goto ErrorHandler end catch print 'PROD database successfully set to single_user user mode.' --this is where all re-partitioning should be done --seting database back to unrestricted mode: begin try alter database PROD set multi_user with rollback immediate end try begin catch select @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state(), @ErrorProcedure = error_procedure(), @ErrorLine = error_line(), @ErrorMessage = error_message() goto ErrorHandler end catch print 'PROD database successfully set back to multi user mode.' ErrorHandler: select @ErrorNumber as ErrorNumber, @ErrorSeverity as ErrorSeverity, @ErrorState as ErrorState, @ErrorProcedure as ErrorProcedure, @ErrorLine as ErrorLine, @ErrorMessage as ErrorMessage go Pedja wrote:
Show quote > HI All, Try RESTRICTED_USER instead of SINGLE_USER. That will prevent any > I'm trying to use the following script as a job which should be running each > first day of the month, at midnight, to repartition the database. This script > suppose to disconnect all database users currently connected to the database, > put the database into single user mode, and after doing re-partitioning work > (within the same script), to put the database back into multi user mode... > Unfortunately, it doesn't work as I planned: > Since there is a load running when this jobs has to be executed, job > disconnects all connections, but after setting database into single user > mode, application successfully reconnects to the database and grabs that > single connectionso that the rest of the job is not able to complete the work > (since database is still in single user mode, and that connection is already > taken by the application). Even worse, now PROD database is in single user > mode, and I'm not able to return it to normal multi user mode!?!?! It is > totally different (unfortunately worse) behaviour comparing to sql server > 2000!!? Does anybody have an idea how to get out of this ridicilous situation? > > declare @sql nvarchar(max), > @ErrorNumber int, > @ErrorSeverity int, > @ErrorState int, > @ErrorProcedure nvarchar(126), > @ErrorLine int, > @ErrorMessage nvarchar(4000) > > -- seting database to single user mode: > begin try > alter database PROD set single_user with rollback immediate > end try > begin catch > select @ErrorNumber = error_number(), > @ErrorSeverity = error_severity(), > @ErrorState = error_state(), > @ErrorProcedure = error_procedure(), > @ErrorLine = error_line(), > @ErrorMessage = error_message() > rollback transaction > goto ErrorHandler > end catch > > print 'PROD database successfully set to single_user user mode.' > > --this is where all re-partitioning should be done > > --seting database back to unrestricted mode: > begin try > alter database PROD set multi_user with rollback immediate > end try > begin catch > select @ErrorNumber = error_number(), > @ErrorSeverity = error_severity(), > @ErrorState = error_state(), > @ErrorProcedure = error_procedure(), > @ErrorLine = error_line(), > @ErrorMessage = error_message() > goto ErrorHandler > end catch > > print 'PROD database successfully set back to multi user mode.' > > ErrorHandler: > select @ErrorNumber as ErrorNumber, > @ErrorSeverity as ErrorSeverity, > @ErrorState as ErrorState, > @ErrorProcedure as ErrorProcedure, > @ErrorLine as ErrorLine, > @ErrorMessage as ErrorMessage > go non-dbo or admin logins from connecting. Your application isn't connecting as an admin, right? Try setting RESTRICTED_USER instead of SINGLE_USER.
And, as far as I am aware, the SINGLE_USER behavior is the same in SQL 2005 as it was in SQL 2000. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Pedja" <Pe***@discussions.microsoft.com> wrote in message news:E447956B-2DD6-4F00-A2C9-15C4DAEF6A6C@microsoft.com... > HI All, > I'm trying to use the following script as a job which should be running > each > first day of the month, at midnight, to repartition the database. This > script > suppose to disconnect all database users currently connected to the > database, > put the database into single user mode, and after doing re-partitioning > work > (within the same script), to put the database back into multi user mode... > Unfortunately, it doesn't work as I planned: > Since there is a load running when this jobs has to be executed, job > disconnects all connections, but after setting database into single user > mode, application successfully reconnects to the database and grabs that > single connectionso that the rest of the job is not able to complete the > work > (since database is still in single user mode, and that connection is > already > taken by the application). Even worse, now PROD database is in single user > mode, and I'm not able to return it to normal multi user mode!?!?! It is > totally different (unfortunately worse) behaviour comparing to sql server > 2000!!? Does anybody have an idea how to get out of this ridicilous > situation? > > declare @sql nvarchar(max), > @ErrorNumber int, > @ErrorSeverity int, > @ErrorState int, > @ErrorProcedure nvarchar(126), > @ErrorLine int, > @ErrorMessage nvarchar(4000) > > -- seting database to single user mode: > begin try > alter database PROD set single_user with rollback immediate > end try > begin catch > select @ErrorNumber = error_number(), > @ErrorSeverity = error_severity(), > @ErrorState = error_state(), > @ErrorProcedure = error_procedure(), > @ErrorLine = error_line(), > @ErrorMessage = error_message() > rollback transaction > goto ErrorHandler > end catch > > print 'PROD database successfully set to single_user user mode.' > > --this is where all re-partitioning should be done > > --seting database back to unrestricted mode: > begin try > alter database PROD set multi_user with rollback immediate > end try > begin catch > select @ErrorNumber = error_number(), > @ErrorSeverity = error_severity(), > @ErrorState = error_state(), > @ErrorProcedure = error_procedure(), > @ErrorLine = error_line(), > @ErrorMessage = error_message() > goto ErrorHandler > end catch > > print 'PROD database successfully set back to multi user mode.' > > ErrorHandler: > select @ErrorNumber as ErrorNumber, > @ErrorSeverity as ErrorSeverity, > @ErrorState as ErrorState, > @ErrorProcedure as ErrorProcedure, > @ErrorLine as ErrorLine, > @ErrorMessage as ErrorMessage > go Thanks to both of you, I thought of changing that, and I will, but it is
still confusing and ridicilous. When I said that it is different than in sql server 2000, I meant that in sql server 2000 script stayed connected to the database. In sql server 2005, at least according to this case scenario, alter database ... set single_user...., from some reason disconnects itself as well, and when it tries to re-connect the only connection is already taken. RIDICILOUS!!!!! Show quote "Arnie Rowland" wrote: > Try setting RESTRICTED_USER instead of SINGLE_USER. > > And, as far as I am aware, the SINGLE_USER behavior is the same in SQL 2005 > as it was in SQL 2000. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Pedja" <Pe***@discussions.microsoft.com> wrote in message > news:E447956B-2DD6-4F00-A2C9-15C4DAEF6A6C@microsoft.com... > > HI All, > > I'm trying to use the following script as a job which should be running > > each > > first day of the month, at midnight, to repartition the database. This > > script > > suppose to disconnect all database users currently connected to the > > database, > > put the database into single user mode, and after doing re-partitioning > > work > > (within the same script), to put the database back into multi user mode... > > Unfortunately, it doesn't work as I planned: > > Since there is a load running when this jobs has to be executed, job > > disconnects all connections, but after setting database into single user > > mode, application successfully reconnects to the database and grabs that > > single connectionso that the rest of the job is not able to complete the > > work > > (since database is still in single user mode, and that connection is > > already > > taken by the application). Even worse, now PROD database is in single user > > mode, and I'm not able to return it to normal multi user mode!?!?! It is > > totally different (unfortunately worse) behaviour comparing to sql server > > 2000!!? Does anybody have an idea how to get out of this ridicilous > > situation? > > > > declare @sql nvarchar(max), > > @ErrorNumber int, > > @ErrorSeverity int, > > @ErrorState int, > > @ErrorProcedure nvarchar(126), > > @ErrorLine int, > > @ErrorMessage nvarchar(4000) > > > > -- seting database to single user mode: > > begin try > > alter database PROD set single_user with rollback immediate > > end try > > begin catch > > select @ErrorNumber = error_number(), > > @ErrorSeverity = error_severity(), > > @ErrorState = error_state(), > > @ErrorProcedure = error_procedure(), > > @ErrorLine = error_line(), > > @ErrorMessage = error_message() > > rollback transaction > > goto ErrorHandler > > end catch > > > > print 'PROD database successfully set to single_user user mode.' > > > > --this is where all re-partitioning should be done > > > > --seting database back to unrestricted mode: > > begin try > > alter database PROD set multi_user with rollback immediate > > end try > > begin catch > > select @ErrorNumber = error_number(), > > @ErrorSeverity = error_severity(), > > @ErrorState = error_state(), > > @ErrorProcedure = error_procedure(), > > @ErrorLine = error_line(), > > @ErrorMessage = error_message() > > goto ErrorHandler > > end catch > > > > print 'PROD database successfully set back to multi user mode.' > > > > ErrorHandler: > > select @ErrorNumber as ErrorNumber, > > @ErrorSeverity as ErrorSeverity, > > @ErrorState as ErrorState, > > @ErrorProcedure as ErrorProcedure, > > @ErrorLine as ErrorLine, > > @ErrorMessage as ErrorMessage > > go > > > Hi again,
when I ran the trace (and actually I could have seen that from the job log file) I realized that the issue lies in the second alter database statement, the one which returns database into unrestricted, multi user mode. So it is not that sql server released connection after setting database to single user mode, but the second alter database statement failed, script lost connection and application took free connection to the database AFTER the script failed (MY BAD, I AM SORRY). However, it still remains question why second ALTER DATABASE statement fails? This is what I'm getting in the trace file: Exception Error: 5061, Severity: 16, State: 1 User Error Message ALTER DATABASE failed because a lock could not be placed on database 'PROD'. Try again later. Exception Error: 5069, Severity: 16, State: 1 User Error Message ALTER DATABASE statement failed. No further explanation. When I run this script when the server is idle, everything works fine. When I run it during application load, I'm getting this s**#. Does anybody have an idea what could be preventing database engine to put the lock on the database? Thanks in advance, Pedja Show quote "Pedja" wrote: > Thanks to both of you, I thought of changing that, and I will, but it is > still confusing and ridicilous. When I said that it is different than in sql > server 2000, I meant that in sql server 2000 script stayed connected to the > database. In sql server 2005, at least according to this case scenario, alter > database ... set single_user...., from some reason disconnects itself as > well, and when it tries to re-connect the only connection is already taken. > RIDICILOUS!!!!! > > "Arnie Rowland" wrote: > > > Try setting RESTRICTED_USER instead of SINGLE_USER. > > > > And, as far as I am aware, the SINGLE_USER behavior is the same in SQL 2005 > > as it was in SQL 2000. > > > > -- > > Arnie Rowland, Ph.D. > > Westwood Consulting, Inc > > > > Most good judgment comes from experience. > > Most experience comes from bad judgment. > > - Anonymous > > > > > > "Pedja" <Pe***@discussions.microsoft.com> wrote in message > > news:E447956B-2DD6-4F00-A2C9-15C4DAEF6A6C@microsoft.com... > > > HI All, > > > I'm trying to use the following script as a job which should be running > > > each > > > first day of the month, at midnight, to repartition the database. This > > > script > > > suppose to disconnect all database users currently connected to the > > > database, > > > put the database into single user mode, and after doing re-partitioning > > > work > > > (within the same script), to put the database back into multi user mode... > > > Unfortunately, it doesn't work as I planned: > > > Since there is a load running when this jobs has to be executed, job > > > disconnects all connections, but after setting database into single user > > > mode, application successfully reconnects to the database and grabs that > > > single connectionso that the rest of the job is not able to complete the > > > work > > > (since database is still in single user mode, and that connection is > > > already > > > taken by the application). Even worse, now PROD database is in single user > > > mode, and I'm not able to return it to normal multi user mode!?!?! It is > > > totally different (unfortunately worse) behaviour comparing to sql server > > > 2000!!? Does anybody have an idea how to get out of this ridicilous > > > situation? > > > > > > declare @sql nvarchar(max), > > > @ErrorNumber int, > > > @ErrorSeverity int, > > > @ErrorState int, > > > @ErrorProcedure nvarchar(126), > > > @ErrorLine int, > > > @ErrorMessage nvarchar(4000) > > > > > > -- seting database to single user mode: > > > begin try > > > alter database PROD set single_user with rollback immediate > > > end try > > > begin catch > > > select @ErrorNumber = error_number(), > > > @ErrorSeverity = error_severity(), > > > @ErrorState = error_state(), > > > @ErrorProcedure = error_procedure(), > > > @ErrorLine = error_line(), > > > @ErrorMessage = error_message() > > > rollback transaction > > > goto ErrorHandler > > > end catch > > > > > > print 'PROD database successfully set to single_user user mode.' > > > > > > --this is where all re-partitioning should be done > > > > > > --seting database back to unrestricted mode: > > > begin try > > > alter database PROD set multi_user with rollback immediate > > > end try > > > begin catch > > > select @ErrorNumber = error_number(), > > > @ErrorSeverity = error_severity(), > > > @ErrorState = error_state(), > > > @ErrorProcedure = error_procedure(), > > > @ErrorLine = error_line(), > > > @ErrorMessage = error_message() > > > goto ErrorHandler > > > end catch > > > > > > print 'PROD database successfully set back to multi user mode.' > > > > > > ErrorHandler: > > > select @ErrorNumber as ErrorNumber, > > > @ErrorSeverity as ErrorSeverity, > > > @ErrorState as ErrorState, > > > @ErrorProcedure as ErrorProcedure, > > > @ErrorLine as ErrorLine, > > > @ErrorMessage as ErrorMessage > > > go > > > > > >  |
|||||||||||||||||||||||
Other interesting topics