October 22, 2007 at 3:36 am
Hi everyone,
i try to update my table but fail to do so due to an error message that says:
The log file for database 'fiftyplus' is full. Back up the transaction log for the database to free up some log space.
Can anyone show me how to backup a transaction log ?
Thanks
October 22, 2007 at 3:46 am
Check the disc space...i often used to get these log full messages..
--Ramesh
October 22, 2007 at 3:46 am
You can use a statement like:
BACKUP LOG foo2 TO DISK = 'G:\foo2.bak'
where foo2 is the name of your datebase.
You can read more about the backup at http://msdn2.microsoft.com/en-us/library/ms186865.aspx
Make sure you have enough space in the backup target location. Once you have backed it up, it may be worth investigating why this has happened. Have you specified a max size for your log file...
Regards,
Andras
October 22, 2007 at 4:16 am
I think u need to shrink the log file after taking backup...
Step 1 :
u can use the following command :
backup log with truncate_only
Step 2 :
Go to EntMgr and shrink the Log file for that particular dB OR use the DBCC shrinkfile command.....
Hope this will help u....
regards,
Ninad
October 22, 2007 at 4:30 am
Hi Mr or Mrs. 500
I used your advise, ran the query and got the following message:
Processed 66 pages for database 'fiftyplus', file 'fiftyplus_Log' on file 2.
BACKUP LOG successfully processed 66 pages in 0.292 seconds (1.839 MB/sec).
I ran the "update table" query again but the same error (transaction log ) appeared again...
Thanks you however.
And thank you Ramesh, The disk isnt full....
October 22, 2007 at 4:34 am
blahknow (10/22/2007)
Hi Mr or Mrs. 500I used your advise, ran the query and got the following message:
Processed 66 pages for database 'fiftyplus', file 'fiftyplus_Log' on file 2.
BACKUP LOG successfully processed 66 pages in 0.292 seconds (1.839 MB/sec).
I ran the "update table" query again but the same error (transaction log ) appeared again...
Thanks you however.
Can you check if you have limited the size of the logfile and what the current size is.
On 2005:
select size, maxsize from sys.sysfiles where fileid=2
On 2000:
select size, maxsize from sysfiles where fileid=2
Regards,
Andras
October 22, 2007 at 4:37 am
Sorry Andras for calling you "Mr or Mrs. 500" :blush: I got so confused with those errors.
Thanks connect_ninad. I ran
backup log with truncate_only
And got
Incorrect syntax near the keyword 'with'.
October 22, 2007 at 4:38 am
connect_ninad (10/22/2007)
I think u need to shrink the log file after taking backup...Step 1 :
u can use the following command :
backup log with truncate_only
Step 2 :
Go to EntMgr and shrink the Log file for that particular dB OR use the DBCC shrinkfile command.....
Hope this will help u....
regards,
Ninad
Hi Ninad,
By backing up the log the virtual log files in the live log can be reused, so the data modifications that were failing previously because of the the full log file will be able to succeed. There is no real need to truncate the log. Indeed, please never do this. It does mess up the log chain. However, if you do truncate the log file, always take a full backup after this (always means: if recovery is important for you :)) Note that in the future truncate_only will be removed from the backup command. You could instead just switch to simple recovery mode, and then back. This in my opinion is a better solution, as it does suggest you more that you are affecting recovery. You are right however, that in case the transaction log was growing due to the lack of its backups, and has grown to a size that it too big and there is no need for it to use up so much space, shrinkfile is the way to go.
Regards,
Andras
October 22, 2007 at 4:41 am
Hi Andras
Answer is:
size maxsize
128 384
October 22, 2007 at 4:46 am
Could you also check if filegrowth is set:
select name, size, maxsize, growth from sys.sysfiles where fileid=2
Andras
October 22, 2007 at 5:35 am
Hi,
select name, size, maxsize, growth from dbo.sysfiles where fileid=2
yields
fiftyplus_Log 128 384 0
Should i change it ?
Can you tell me how ?
Thanks
October 22, 2007 at 5:41 am
blahknow (10/22/2007)
Hi,select name, size, maxsize, growth from dbo.sysfiles where fileid=2
yields
fiftyplus_Log 128 384 0
Should i change it ?
Can you tell me how ?
Thanks
Excellent,
so the problem is that your filegrowth for the log file is not set.
You could either increase the size of your log file, or set autogrowth on.
You could do this in the UI, Management Studio, rightclick on the db, properties, files, ...
or:
ALTER DATABASE [mydatabase] MODIFY FILE ( NAME = N'mydatabase_log', FILEGROWTH = 10%)
To increase the filesize:
ALTER DATABASE [mydatabase] MODIFY FILE ( NAME = N'mydatabase_log', SIZE = 256MB )
Regards,
Andras
October 22, 2007 at 5:53 am
Excellent indeed !!!
That solved the problem !!!:D
Thank you v e r y much Andras !
Is there a book that can be helpful in incidents such as this ?
Thanks again Andras !
October 22, 2007 at 5:56 am
blahknow (10/22/2007)
Excellent indeed !!!That solved the problem !!!:D
Thank you v e r y much Andras !
Is there a book that can be helpful in incidents such as this ?
Thanks again Andras !
Books online (the documentation that comes with SQL Server) is probably the best source to learn all the details about SQL Server. As for unexpected things this forum is a good source 🙂
Regards,
Andras
October 22, 2007 at 6:40 am
If u r still getting the error change ur database recovery model to 'SIMPLE' then run the DBCC SHRINKFILE command
dbcc shrinkfile (log_file_name,size)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply