What would make a MS SQL Server 2000 randomly decide that performing write operations is too daunting a task? And instead of telling people that it doesn’t want to write things anymore, it just fails to respond while the query times out.
Nothing in the SQL Server error logs indicates any problems. Nothing in the SQL Server process list included in the email I get for every database error indicates any problems. Neither the event logs nor the IIS access log indicate any problems. The disk has plenty of free space. The server is not stressed in any way. It delivers results from reading just fine.
Still it just fails when writing. I am glad I am not a server administrator - oh wait, I am meant to be one in this case, doh.
I'm at home right now and don't have access to SQL Manager but, as I recall, free space in the database isn't a function of diskspace, it's a function of how much space was allowed the schema when it was created. Thus my wild-ass guess would be it's having to compact the archive logs before it can write the data which results in a timeout. Alternately, there might be an issue with the disk where the archive logs are stored or something. Perhaps this'll help - if you want, I can vpn to work and see if I can dig up where those are set.
I think the transaction log had enough free space, but I admit I didn't think about it before restarting the machine last time. Anyways, I seem to recall the database returning an error message instead of timing out when the transaction log runs full.
You might be right, perhaps it's the autogrowing of the log/database files that causes problems. Hm, how to check that...