July 19, 2013 at 8:23 am
Hi there
I'm a novice at SQL so bear with me here. I'm getting the following message when I run a SP:
The transaction log for database '???db' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Recovery mode is simple and I have truncated the log using shrinkfile. The log_reuse_wait_desc column is NOTHING but I'm still getting he same error!
Thanks in advance
Dave.
July 19, 2013 at 8:42 am
What is that SP all about?
Run dbcc sqlperf(logspace) to find out the log file usage of dbs
Refer the Errorlog (xp_readerrorlog) to find the error logged.
July 19, 2013 at 8:49 am
Sounds like your stored procedure is making more modifications than there is room for the transaction log to record. If that's the case, rewrite the procedure so that it's more efficient. If that's not possible, you'll need to increase the size of your transaction log.
John
July 19, 2013 at 8:52 am
Yes, the transaction log seems to be limited to 99MB because it increases to this size from 10MB after truncation, how can I increase this or make it unlimited?
Thanks
July 19, 2013 at 8:55 am
ALTER DATABASE MODIFY FILE (....)
If you don't know the syntax, you can search for it. Or you can just use the GUI.
John
July 19, 2013 at 8:59 am
Super, that's sorted now through the GUI.
Thanks for your help.
Dave.
July 19, 2013 at 9:34 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply