Our company is running MS 2000 Server with Client Access Licenses with MS SQL Server v8.0 Enterprise edition. Currently, the SQL server is only taking SELECT statements while rejecting all INSERT/UPDATE/DELETE procedures (even with Admin access)
We believe it is an issue with the licensing aspect of the SQL Server, but we own processor license... We are trying everything at the moment, and I thought I'd come and ask experts here while we are attempting to solve the problem ourselve.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I don't want to say the server freezes... rather, the client interface connecting to the server stops responding. It doesn't even give a timeout error.
We use task manager to close out the Server Enterprise Manager.
Currently, it is telling us that our Database is "recovering" but we haven't really done anything to it to resolve the problem.
Heh, now it is working. We simply let the SQL server finish "recovering."
But, before the server entered into the self-recovery phase, we simply couldn't INSERT/UPDATE/DELETE. It didn't even mention it was recovering.
Since we didn't do anything to fix the problem, we are trying to find out what caused it and how to resolve the problem in the future.
Heh, now it is working. We simply let the SQL server finish "recovering."
But, before the server entered into the self-recovery phase, we simply couldn't INSERT/UPDATE/DELETE. It didn't even mention it was recovering.
Since we didn't do anything to fix the problem, we are trying to find out what caused it and how to resolve the problem in the future.
Heh, now it is working. We simply let the SQL server finish "recovering."
But, before the server entered into the self-recovery phase, we simply couldn't INSERT/UPDATE/DELETE. It didn't even mention it was recovering.
Since we didn't do anything to fix the problem, we are trying to find out what caused it and how to resolve the problem in the future.
Here is one error that could cause the problem...
Error: 9002, Severity: 17, State: 6
The log file for database 'Lazarus' is full. Back up the transaction log for the database to free up some log space.
However, we have the SQL Server to automatically increase the size of the partition to let the transaction log grow (it's at 40 gigs at the moment). I personally doubt that is the problem we are facing... but, would the server crap out if it cannot create anymore transaction log?
Here is one error that could cause the problem...
Error: 9002, Severity: 17, State: 6
The log file for database 'Lazarus' is full. Back up the transaction log for the database to free up some log space.
However, we have the SQL Server to automatically increase the size of the partition to let the transaction log grow (it's at 40 gigs at the moment). I personally doubt that is the problem we are facing... but, would the server crap out if it cannot create anymore transaction log?
Yep. Sure would. By running out of space the Server would not be able to ensure a good recovery of data changes. For data integrety purposes, it would not allow any modifications of data.
I'm a little intersted in the fact that you have a 40 gig transaction log. Do you perform database backups and truncate the log? I mean 40 gig may not seem like alot, but I'd hate to think about the recovery time...
Sorry about the f5 / refresh. I'm new at asking questions. I will use the 'reload this question' at the top left corner. Usually, I find the answers via browsing the topics.
The transaction log is important to our database since we are in the business of selling. That's why it is so large. We have now discounted the error as the source of the problem for a few reasons --
1) The allocation disk space for the log grows in chunks of 10% each time it reaches a specific limit automatically, the error message should never had happened. There were plenty of space for the log to grow into.
2) The problem gone away after the db finished the recovery process -- however, recovery does not shrink the transaction log, nor create more space for the transaction log.
3) The error occurred at 9:35 AM during the 1st SQL Server reboot. The problem with the SQL Server didn't go away until the 5th reboot finishes at 11:21 AM.
We are running out of errors to examine in all the different error logs. We are probably gonna get pounded into the ground soon.
I'd have to spend more time doing research, but during the grow of the transaction log, the database might not be able to accept the requests, so even though you discounted this as the reason, I'm not sure you should have been so quick to move on. So my question might be did the log just "grow" or does it have about 10% free space at the moment? If it does... then your error could likely be that during a state of "growth" the db is unable to process update requests.
As far as the Log size... Again, if you backup the db, and the transaction log to say, tape backup, you can safely SHRINK the transaction log.
here's the concept:
I have a good backup of a database at 12:00am on 1/1/2003 and my transaction log has been shrunk.
Now, its 1/2/2003 at 1:00pm... My DB crashes. I restore the db from backup, and then apply as much as the CURRENT transaction log as I can to the database. Isolating the transaction, if it is a transaction, that causes the corruption. Then take steps to prevent the transaction from causing the error again.
At my next backup, I backup the Database and the transaction log, shrinking the log again. If I need to retain the transaction logs for business reasons, I archive them off to a different space/server/media for storage/retrevial.
I guess my question would be how often have you had need to use your transaction log for business? I'm guessing is is not often, so archiving off to a different location does a few things for you; makes recovery faster incase of db failure, provides the history of your transactions(for whatever reason) and decrease the effort of the os to upkeep a 40 gig file. Sometimes it is beneficial to set a transaction log to a SET file size. Say You know the file grows by 10 meg / day and you do backups every night. You could easily set the log db to a 100 meg size, on a shrink and your log should never need to "GROW" unless you exceed 10 Times your normal day's growth. Alternatively, you could import your transaction logs into a seperate DB and maintain your "history" that way.
Growth takes time, and DOES effect resources.
I'm not sure if growth takes into account fill factor and possibly reorganization of indexes... If it does, this could easily explain your inability to modify data during this period.
Good Luck!
It happened again. Came in this morning and everything was working fine for about an hour. All of the sudden, it came crashing down.
Currently, we can perform SELECT statements but not INSERTS/DELETES. We can't even look at table properties in the Server Enterprise Manager. Yesterday, we made absolutely sure the transaction log and DB had room to grow into. There is no way that overnight, both could grow another few gigabytes.
When doing any query that the Server doesn't like, it simply stops. Not even a timeout error. At the SQL Server Log, here are all the errors occurred.
--
10:07:25.01 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
10:07:25.01 Error: 15457, Severity: 0, State: 1
10:10:40.29 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
10:10:40.29 Error: 15457, Severity: 0, State: 1
10:11:38.42 Waiting for type 0x4, current count 0xa, current owning EC/PSS 0x6D22B4F8/0x6D22B1D0.
10:11:38.42 WARNING: EC 6cee7518, 0 waited 300 sec. on latch 42c8ba70. Not a BUF latch.
--
That's all.
Our manager decided the transaction log is the one causing the delay. Since the SQL server brought the company to a grinding halt, he decided to truncate the transaction log. From 49 gigabytes, to 11 megabytes.
Everything works smoothly now. I'd keep the topic open for another day and see if anyone else have comments that might be helpful in the future!
The article at:
http://www.sqlmag.com/Foru
seems to be about the same topic...
but they don't have an answer yet either.
One last link...
http://www.it-faq.pl/msKno
Seems they have uncovered a variety of potential errors for this problem. This might be something to investigate.
Business Accounts
Answer for Membership
by: acperkinsPosted on 2003-04-29 at 08:11:35ID: 8420671
>>while rejecting all INSERT/UPDATE/DELETE procedures <<
What is the error message?
Anthony