October 27, 2008 at 7:13 am
Hi all, Application team just reported a problem they are getting that 'The log file for database 'mbt' is full. Back up the transaction log for the database to free up some log space. I'm not sure if it is the mbt_log.LDF. I see the file in D:\ on the server. There is only 632MB free space for D:\. Do I just have to clear up some space on D:\?? Please help me if you have any suggestions.
Thanks a lot.
October 27, 2008 at 7:25 am
psangeetha (10/27/2008)
Hi all, Application team just reported a problem they are getting that 'The log file for database 'mbt' is full. Back up the transaction log for the database to free up some log space. I'm not sure if it is the mbt_log.LDF. I see the file in D:\ on the server. There is only 632MB free space for D:\. Do I just have to clear up some space on D:\?? Please help me if you have any suggestions.Thanks a lot.
My guess is that your database is set to full model, but you don’t backup the log at all. This causes the log to continue growing constantly. To fix this problem you can start backing up your log or modify the database’s recovery model to simple (Pleas read about recovery model in Books On Line, so you’ll be able to decide which action to take). After you do one of those actions, the log file’s size will be the same, but the file will be empty. In order to shrink the file, so you’ll have more space on your disk, you’ll have to run dbcc shrinkfile command (again, you can read about it on Books On Line).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 27, 2008 at 7:27 am
DISCLAIMER: It's very important you'll know the concepts of a logfile. Start reading Books on Line, or search this site. My answers to your question are very basic. How you use your logfile depends on the backup/recovery policy of your company. Make sure you know what you're doing.
If your logfile is full, you'll have to empty it! How, depends on the recoverymodel of the related database.
- If it's full recovery, make a transaction log backup.
- If it's simple, try truncate the log.
Note: both operations releases some space inside the log (so you can reuse is) , it's NOT shrinking the logfile.
If SQL says a logfile is full and you still have free diskspace, that's because it's probably increasing by 10% (1000 mb becomes 1100MB, in that case you'll need 100 MB free diskspace).
Wilfred
The best things in life are the simple things
October 27, 2008 at 7:50 am
Check the properties for the database and see if your files are restricted in size or set to autogrow.
Check the recovery model in the same place (is it full or simple).
If the log is full, you will have to run a backup log with the no_log option (look this up in Books Online) to clear the log. IMMEDIATELY after this run a full backup.
Now if you need to recover your database between full backups, use full mode and set up regular transaction log backups. I'd recommend every hour just because it's simple. Once you have those going, you can see how large your log is (look at used log space in the database) and how large each backup is. That will help you determine if you have enough space.
If you get to that point and are confused, come ask more questions.
October 27, 2008 at 9:13 am
Thanks everyone for your help. I ran backup online log and also cleared up some space on the drives. Its fine now.
October 27, 2008 at 9:16 am
Great. Now make sure that you have regular log backups scheduled so that this doesn't happen again.
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
October 27, 2008 at 11:20 am
choose the correct recovery model for your database and backup accordingly
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply