|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
best way to log out when running the jobHi,
When I run the job, I am not sure whether it could be done at the table level to log users out. I would not want to keep them out of the whole database...only the tables I are updating. Also, once the job is running, I would like to prevent any users from accessing the tables I am updating until the end of the job. Is there anyway to do that? Thanks Ed You can take an Exclusive lock on the table and hold it until you are done.
That will not allow anyone to access the table as they will be blocked. Not sure if they will like you for doing that though<g>. -- Show quoteAndrew J. Kelly SQL MVP "Ed" <E*@discussions.microsoft.com> wrote in message news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@microsoft.com... > Hi, > When I run the job, I am not sure whether it could be done at the table > level to log users out. I would not want to keep them out of the whole > database...only the tables I are updating. Also, once the job is running, > I > would like to prevent any users from accessing the tables I am updating > until > the end of the job. > > Is there anyway to do that? > > Thanks > Ed Andrew,
Thanks for your answer. I would also like to know what is the best way to log users out before the job is running. Again, I only need to log them out of the tables that I will be updating not the whole database. Thanks again Ed Show quote "Andrew J. Kelly" wrote: > You can take an Exclusive lock on the table and hold it until you are done. > That will not allow anyone to access the table as they will be blocked. Not > sure if they will like you for doing that though<g>. > > -- > Andrew J. Kelly SQL MVP > > > "Ed" <E*@discussions.microsoft.com> wrote in message > news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@microsoft.com... > > Hi, > > When I run the job, I am not sure whether it could be done at the table > > level to log users out. I would not want to keep them out of the whole > > database...only the tables I are updating. Also, once the job is running, > > I > > would like to prevent any users from accessing the tables I am updating > > until > > the end of the job. > > > > Is there anyway to do that? > > > > Thanks > > Ed > > > You can't "Log out" users from a table. They log in to the server and
potentially have access to one or more db's in that server. A user only takes out locks while they are accessing the data. So any locks they may have on any specific table are released once they are done with the query or the transaction is committed or rolled back. Why do you care if they are in the table or not? Maybe if you explain in more detail what you are attempting to do we can suggest an appropriate action. -- Show quoteAndrew J. Kelly SQL MVP "Ed" <E*@discussions.microsoft.com> wrote in message news:3937FA07-1F29-497C-99AA-608E89D0E807@microsoft.com... > Andrew, > Thanks for your answer. I would also like to know what is the best way > to > log users out before the job is running. Again, I only need to log them > out > of the tables that I will be updating not the whole database. > > Thanks again > > Ed > > "Andrew J. Kelly" wrote: > >> You can take an Exclusive lock on the table and hold it until you are >> done. >> That will not allow anyone to access the table as they will be blocked. >> Not >> sure if they will like you for doing that though<g>. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Ed" <E*@discussions.microsoft.com> wrote in message >> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@microsoft.com... >> > Hi, >> > When I run the job, I am not sure whether it could be done at the >> > table >> > level to log users out. I would not want to keep them out of the whole >> > database...only the tables I are updating. Also, once the job is >> > running, >> > I >> > would like to prevent any users from accessing the tables I am updating >> > until >> > the end of the job. >> > >> > Is there anyway to do that? >> > >> > Thanks >> > Ed >> >> >> The major reason why I would like to "Log Out" users is because the job has
to be run in the daytime and the job has to be finished as quick as possible. with so many users using the resources of the server (hugh data warehouse to be accessed by so many people), I would like to "Log Out" all users and block the access of all updating tables except the one running by the job. any suggestion Thanks Ed Show quote "Andrew J. Kelly" wrote: > You can't "Log out" users from a table. They log in to the server and > potentially have access to one or more db's in that server. A user only > takes out locks while they are accessing the data. So any locks they may > have on any specific table are released once they are done with the query or > the transaction is committed or rolled back. Why do you care if they are in > the table or not? Maybe if you explain in more detail what you are > attempting to do we can suggest an appropriate action. > > -- > Andrew J. Kelly SQL MVP > > > "Ed" <E*@discussions.microsoft.com> wrote in message > news:3937FA07-1F29-497C-99AA-608E89D0E807@microsoft.com... > > Andrew, > > Thanks for your answer. I would also like to know what is the best way > > to > > log users out before the job is running. Again, I only need to log them > > out > > of the tables that I will be updating not the whole database. > > > > Thanks again > > > > Ed > > > > "Andrew J. Kelly" wrote: > > > >> You can take an Exclusive lock on the table and hold it until you are > >> done. > >> That will not allow anyone to access the table as they will be blocked. > >> Not > >> sure if they will like you for doing that though<g>. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Ed" <E*@discussions.microsoft.com> wrote in message > >> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@microsoft.com... > >> > Hi, > >> > When I run the job, I am not sure whether it could be done at the > >> > table > >> > level to log users out. I would not want to keep them out of the whole > >> > database...only the tables I are updating. Also, once the job is > >> > running, > >> > I > >> > would like to prevent any users from accessing the tables I am updating > >> > until > >> > the end of the job. > >> > > >> > Is there anyway to do that? > >> > > >> > Thanks > >> > Ed > >> > >> > >> > > > Well you can KILL a connection that has a lock in the table you want but
that obviously ruins their day. Other than that there is no way to remove a users locks on a table until that user has finished accessing it and committed or rolled back their transaction if they had one. As I stated earlier the only real way to prevent them from accessing the table is to take out an exclusive lock. You may have to wait a while until all current users in that table have finished before the lock takes hold. But no new users can access it either. It almost sounds like you need to place the db in a single user mode, do the work and return it as soon as possible. You can specify a termination option to kick out the current users after a period of time with the alter database command. -- Show quoteAndrew J. Kelly SQL MVP "Ed" <E*@discussions.microsoft.com> wrote in message news:5C6CCABD-739B-472F-892B-4F3B3F53DB31@microsoft.com... > The major reason why I would like to "Log Out" users is because the job > has > to be run in the daytime and the job has to be finished as quick as > possible. with so many users using the resources of the server (hugh data > warehouse to be accessed by so many people), I would like to "Log Out" all > users and block the access of all updating tables except the one running > by > the job. > > any suggestion > Thanks > Ed > > "Andrew J. Kelly" wrote: > >> You can't "Log out" users from a table. They log in to the server and >> potentially have access to one or more db's in that server. A user only >> takes out locks while they are accessing the data. So any locks they may >> have on any specific table are released once they are done with the query >> or >> the transaction is committed or rolled back. Why do you care if they are >> in >> the table or not? Maybe if you explain in more detail what you are >> attempting to do we can suggest an appropriate action. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Ed" <E*@discussions.microsoft.com> wrote in message >> news:3937FA07-1F29-497C-99AA-608E89D0E807@microsoft.com... >> > Andrew, >> > Thanks for your answer. I would also like to know what is the best >> > way >> > to >> > log users out before the job is running. Again, I only need to log >> > them >> > out >> > of the tables that I will be updating not the whole database. >> > >> > Thanks again >> > >> > Ed >> > >> > "Andrew J. Kelly" wrote: >> > >> >> You can take an Exclusive lock on the table and hold it until you are >> >> done. >> >> That will not allow anyone to access the table as they will be >> >> blocked. >> >> Not >> >> sure if they will like you for doing that though<g>. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Ed" <E*@discussions.microsoft.com> wrote in message >> >> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@microsoft.com... >> >> > Hi, >> >> > When I run the job, I am not sure whether it could be done at the >> >> > table >> >> > level to log users out. I would not want to keep them out of the >> >> > whole >> >> > database...only the tables I are updating. Also, once the job is >> >> > running, >> >> > I >> >> > would like to prevent any users from accessing the tables I am >> >> > updating >> >> > until >> >> > the end of the job. >> >> > >> >> > Is there anyway to do that? >> >> > >> >> > Thanks >> >> > Ed >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics