SQL Server Performance Forum – Threads Archive

Allow select but can’t update/insert for whole DB

Hi all, Yesterday my company database occurred the strange incident. All the applications and SQL Server query analyzer only allowed to select the records from any tables of the Database. However, when I tried to insert or update the record to each table in the database, It can’t allow and return error "timeout expired". Did anyone also have this problem? What is the suggestion of this issue? Thank you very much. Cheers, Eddie[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Check whether READ ONLY option is set on the database.
Run SP_DBOPTION from Query analyzer for information. Refer to SQL server error log for more information.
Have you tried the insert/update record from Query analyzer? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What are your daily and week manteinance plan?.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Update stats. Also run profiler to check locking.

I tried in the query analyzer, it also failed and the error is same "timeout expired". Besides, we found that the size of transaction log is 23 GB with respect to the size of Database of 4GB, is it abnormal? We don’t deploy any maintenance plan yet, does it improve such issue? Thanks.

If you are not doing Transaction Log backups, you need to set the recovery mode on your database to Simple. You can do this by right-clicking on the database in Enterprise Manager and changing the Recovery Mode. You then need to right-click on it again and shrink the database. This really shouldn’t affect performance too much unless you’re out of space. Is the harddrive filled up? Do you run anything else on this server now? Have you looked at performance monitor to see where you are at on memory and processor? Also run: select * from master.dbo.sysprocesses where blocked <> 0 and tell us if that returns anything. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I suspect you may be hitting space issues on the drive where Transaciton log is located. The best option would be to table full backup and run BACKUP LOG … WITH NO_TRUNCATE and then use DBCC SHRINKFILE in order to shrink the transaction log file size. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Have you checked out the rights for your sql server login?.It login does not have previllages then you can not do data manipulation on enitre database. You are getting timeout expired error.You try it out following command in RUN option of Statup menu "TELNET ipaddress 1433 ( mentioned port no)" Mahesh Paranjpe
SQL DBA

Thanks a lot. Under checking on block process, there is no any blocked process in the server. I also suspect the data size of transaction log. I would like to ask that how I should do to control the transaction log to optimal size.
What should I do in the daily and weekly maintanence plan?
Please advice. Thanks. Eddie

If you maintain regular backups for Transaction logs then you may not be having issues of increase in Tlog file. If the current recovery model is set to SIMPLE then ensure to maintain database backups regularly. As of now you can use DBCC SHRINKFILE to shrink the Tlog file size and set a optimum size, after scheduling all database maintenance tasks using Database Maintenance Plan wizard you can define the size for Tlog. Refer to books online for more information on DBCC SHRINKFILE and database maintenance plan topics. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also, to keep performance, defrag index and update statistics before shrink.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

]]>