December 18, 2001 at 9:55 am
I am running SQL 2000 s/p 1. When my users leave at the end of the day, they have permissions to INSERT, UPDATE and DELETE records as necessary by using Stored procedures. When they arrive in the morning, They can only view data. Any attempt to INSERT, UPDATE, or DELETE results in a timeout error. When I stop SQL Server and restart it, they are ok to do their business again. We are running Backup Exec at midnight but see no problems with it. Any help would be appreciated.
December 18, 2001 at 10:02 am
Never seen anything like this. Are you r users members of some role? Have you checked the error logs for SQL and BakcupExec?
does this happen with a normal SQL backup to disk?
Steve Jones
December 18, 2001 at 10:04 am
No special situations with the users. they are all members of public. Backup Exec shows nothing unusual and has been running nightly for months.
December 18, 2001 at 10:21 am
Sorry, didn't read the timeout error. My guess is that there is a lock being held for some reason. Does the backup complete with no errors?
run sp_lock before and after an insert attempt. Look for a block.
Steve Jones
December 18, 2001 at 10:26 am
The user's ability to SELECT and view anything leads me to believe it is not a lock. It's almost as if their INSERT, UPDATE, DELETE permissions have been revoked.
December 18, 2001 at 10:57 am
Even if they had been revoked, restarting the service would not fix it. Easy enough to check anyway, right? I like Steve's idea about blocking - worth checking it out. Are you doing index rebuilds at night? Maybe something not finishing? How about profiling all activity from 6pm on, see if you can spot anything bizarre.
Andy
December 18, 2001 at 11:00 am
I ran a profiler last night. Last user signed off at 6:30 P.M. Backup happened at 10 and completed. No activity until 6:58 A.M. with the first user signing on. At that time he got a timeout when trying to update a production note. I am going to drop SQL from Backup Exec tonight and see if it helps.
December 18, 2001 at 4:47 pm
Is the db set to auto-close? If no activity and it shuts down, you'd see a fair delay while it reopened - plus there would be no data cached.
Andy
December 18, 2001 at 4:48 pm
That's a good point.
check all db settings before backup and after. See if something changed.
Steve Jones
December 19, 2001 at 6:24 am
Well after turning off the "Auto-Shrink" function on the database, I came in this morning and the users were working fine. No permossion lockout at all. Any ideas as to why this would happen?
December 19, 2001 at 10:26 am
December 19, 2001 at 10:28 am
Disk is cheap. Let the db expand and don't worry about shrinking. thanks for your help guys.
Edited by - gajajojo on 12/19/2001 10:28:48 AM
December 19, 2001 at 11:24 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply