SQL tables not accepting Inserts, Updates or Deletes by any means.

Question

SQL tables not accepting Inserts, Updates or Deletes by any means.

Thumperfla

Grasshopper

Points: 10
More actions
June 26, 2009 at 9:05 am

#223088

At least twice a week I'm running into a situation where the SQL tables will not accept Inserts, Updates or Deletes. When this occurs all applications tied to SQL timeout and within Enterprise Manager attempted data changes directly into the tables hangs with Windows Task Manager saying No Response. I can view, open and query the tables but nothing else.
Also, there are no errors logged into SQL Logs or Windows Event Logs.
To recover, a reboot has to been done to the server. Simply ending the service just hangs as well.
Currently, we are only backing up the DB using a maintenance plan but not the transaction log. Could this have any ties to this issue?
Has anyone ran into the same issue?
Any help will be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DNA_DBA SSCrazy Eights Points: 9132 More actions · Answer 1

Could this be due to blocking? Have a look in Activity Monitor or run sp_who2 and sp_lock to see if there are transactions blocking which can cause timeouts.

ps. One Orange Chip Points: 29423 More actions · Answer 2

Thumperfla (6/26/2009)
To recover, a reboot has to been done to the server. Simply ending the service just hangs as well.
Currently, we are only backing up the DB using a maintenance plan but not the transaction log. Could this have any ties to this issue?

Is your database in Full recovery model? If yes, you'll need to backup ur logs to prevent it from filling up disk. But this doesn't look like the case here as restarting the services is helping solve the issue. As suggested in previous post check if there is some long running query that is blocking other transactions from happening.

Pradeep Singh

Thumperfla Grasshopper Points: 10 More actions · Answer 3

Thanks for the reply. There are no locks at the time of the occurrance of this issue. However, after finding that the Transaction Log file is over 66 gig for a 5 gig db and the disk size has 300 gig of free space, I have wonder if when SQL is writting to such a large log file that other jobs are getting affected by SQL's timeout parameter.

We have turned on a nightly Transaction Log Database Maintenance Plan and will see if that takes care of this issue.

Again, thank you for your input.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read through this - Managing Transaction Logs[/url]

If it happens again, query sys.processes and see what the wait_types are.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass