March 27, 2011 at 12:51 am
We have a maintenance plan to run weekend. It does 1. check db integrity 2. rebuild index
3. update statistcis. when the job runs to rebuild index, it failed.
the error is: Excuting the query "ALTER INDEX [PK_appStudentSnapshot] ON [dbo].[appS..." failed with the following error: "The transaction log for database 'StudentApp' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
The database is setup using full recovery mode. After I got the error, I did a transaction log backup,
then I use DBCC SQLPERF (LOGSPACE) to see the log space used, it reduces to 6%.
Then I ran the same maintenance plan again, but it failed again with the same error above.
I see the log used changed to 100% . It seems it is the alter index made it full.
What could be the problem, and how can I fix this?
Thanks
March 27, 2011 at 3:03 am
Chances are your log file cannot be extended within the timeout sqlserver waits for the os to complete the task.
SQL will then raise the error file full, but the os will finally complete the file extend, unless the disk runs out of space.
Try to figure out what size your log file will need to complete a full maintenance and pre-size it to be able to cope with that task.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 27, 2011 at 3:15 am
Read the Gail's article Why is my transaction log full?[/url]
This too
http://www.sqlservercentral.com/articles/64582/
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 27, 2011 at 3:22 am
An index rebuild is a single transaction. In full recovery, you will need log space at least 1.2* the index size to rebuild the index. Either increase the size of the log or switch to bulk-logged recovery for the duration of the index rebuilds. (read up on bulk-logged recovery first)
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
March 27, 2011 at 10:59 pm
Thank you, so you said to change to bulk copy recovery mode then do the rebuild index. Can I also change to simple mode recovery to do this? For this, what's the difference?
Also after this is done, I change it back to full recovery mode, does this break back up chain?
Do I need to do a full back up immediately after the change of recovery mode?
and why?
Thank you!
March 27, 2011 at 11:34 pm
annasql (3/27/2011)
Thank you, so you said to change to bulk copy recovery mode then do the rebuild index. Can I also change to simple mode recovery to do this? For this, what's the difference?
You can, but...
Simple recovery breaks the log chain, meaning you will not be able to take log backups until another full/diff backup is taken. For most production systems that risk is too high. Also if you break the log chain and you have a corrupt full backup, then you have no chance of going back to a previous full backup because you don't have an intact chain of log backups
Do I need to do a full back up immediately after the change of recovery mode?
and why?
If you switch to simple, yes, because the switch to simple broke the log chain. If you switch to bulk-logged, no.
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
March 29, 2011 at 9:16 am
Thank you, it's very helpful. and you explained so easy to understand.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply