July 11, 2008 at 3:34 am
hello
have a SQL 2005 DB that ran out of transaction log space so became read only..
Increased the size of the log and seemed to start working again.
Can log in to it, read, even delete records but inserts seem to not take. Not getting any errors though! Trace shows sp's executing fine and and no errors in SQL log or application logs.
Server has been restarted since to apply windows updates.
I'm a bit at a loss so any ideas very welcome!
Thanks
July 11, 2008 at 6:52 am
As your database seems to be in Full or Bulk-Logged mode, you should be backing up the transaction log with some regularity. That'll stop it from filling up and halting processing. Not sure why inserts wouldn't work but deletes would as they are both logged transactions and apparently you have free log space again. I'd take a long look at the code or SQL logs to see if they'll shed some light on that issue (some permission issue where inserts aren't permitted on a particular table(s)?).
-- You can't be late until you show up.
July 11, 2008 at 7:00 am
Are the database files full? That might allow deletes but not inserts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 7:34 am
GSquared (7/11/2008)
Are the database files full? That might allow deletes but not inserts.
Good point. I didn't think about that - it's still early and my brain isn't fully functional yet!
-- You can't be late until you show up.
July 11, 2008 at 7:49 am
I hope your issue is solved by now, but if it is not, then RUN this Query and post back the Results:
select database_id, log_reuse_wait, log_reuse_wait_desc from sys.databases
Where database_id= your_databaseID_Here
Maninder
www.dbanation.com
July 11, 2008 at 7:52 am
Again some Pointers for Responding to full transaction log is:
Backing up the log.
Freeing disk space so that the log can automatically grow.
Moving the log file to a disk drive with sufficient space.
Increasing the size of a log file.
Adding a log file on a different disk.
Completing or killing a long-running transaction.
Again a Microsoft Warning:
Note:
Forcing log truncation breaks the log chain and leaves your database vulnerable until the next full database backup. For this reason, the TRUNCATE_ONLY option will be removed from the BACKUP statement in a future version of SQL Server. Avoid using this option in new development work, and plan to modify applications that currently use it.
Maninder
www.dbanation.com
July 11, 2008 at 8:05 am
Hi all
thanks for your thoughts 🙂
The DB files are not full so guess thats not the problem.
The DB's were being backed up with Backup Exec which should have been truncating the logs after the backup but wasn't (apparently well documented)
Oddity is that I'm not sure why the log files became size restricted, not sure that changing something else on the DB didn't change that (?) I was truncating them and they got a lot bigger than the 1GB they finally stopped working at.
I have looked at the code that is trying to insert and can't find anything, no errors result on the insert and the SP's doing it execute seemingly!
will get the answer to that SQL that was posted.
thanks
Matt
July 11, 2008 at 8:08 am
here 'tis 🙂
8logging2LOG_BACKUP
July 11, 2008 at 8:21 am
Check out http://www.sqlskills.com and Paul Randals Blogs, he explains about SHRINK operation and how they cna cause fragmentation, which in return can take us LOG SPACE. etc etc
Maninder
www.dbanation.com
July 11, 2008 at 8:29 am
Gail also has a good blog about shrinking as well (which in turn, also references Paul's). Check it out here: http://sqlinthewild.co.za/
In addition, truncating/shrinking the logs are not as big an issue as shrinking the data file, although still not a best practice (search this site for volumes of reasons why). Just be fully aware, truncating the log breaks your restore chain so ALWAYS do a full backup of the database immediately afterwards.
-- You can't be late until you show up.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply