So our SQL Server 2000 is giving me the error, 'The log file for database is full. Back up the transaction log for the database to free up some log space.'
A file name is the complete title of a file and file extension. For example, 'readme.txt' is a complete or full file name. A file name may also only describe the first portion of the file, for example, 'readme' is the name of the file and '.txt' is the file name extension of the file.
How do I go about fixing this without deleting the log like some other sites have mentioned?
Additional Info: Enable AutoGrowth is enabled growing by 10% and is restricted to 40MB.
Scott, as you guessed: truncating the log is a bad move if you care about your data.
The following, free, videos will help you see exactly what's going on and will show you how to fix the problem without truncating the logs. (These videos also explain why that's such a dangerous hack and why you are right to look for another solution.)
Together these videos will help you understand exactly what's going on and will show you whether you want to switch to SIMPLE recovery, or look into actually changing your backup routines. There are also some additional 'how-to' videos that will show you exactly how to set up your backups to ensure availability while managing log file sizing and growth.
Michael K. CampbellMichael K. Campbell
To just empty it:
To save it somewhere:
If you dont really need transactional history, try setting the database recovery mode to simple.
TrevorDTrevorD
ether backup your database logs regularly if you need to recover up to the minute or do other fun stuff like log shipping in the future, or set the database to simple mode and shrink the data file.
DO NOT copy, rename, or delete the .ldf file this will break your database and after you recover from this you may have data in an inconsistent state making it invalid.
Wes Brown
I don't think renaming or moving the log file will work while the database is online.
Easiest thing to do, IMO, is to open the properties for the database and switch it to Simple Recovery Model. then shrink the database and then go back and set the DB to Full Recoery Model (or whatever model you need).
Changing the logging mode forces SQL Server to set a checkpoint in the database, after which shrinking the database will free up the excess space.
My friend who faced this error in the past recommends:
Try
Backing up the DB. The maintenance plan includes truncation of these files.
Also try changing the 'recovery mode' for the DB to Simple (instead of Full for instance)
Cause:The transaction log swells up due to events being logged (Maybe you have a number of transactions failing and being rolled back.. or a sudden peaking in transactions on the server )
You have the answer in your question: Backup the log, then it will be shrunk.Make a maintenance plan to regularly backup the database and don't forget to select 'Backup the transaction log'. That way you'll keep it small.
Once this has completed shrink the log file to free up disk space. Finally switch database recovery mode to simple.
Jim Prince
As soon as you take a full backup of the database, and the database is not using the Simple recovery model, SQL Server keeps a complete record of all transactions ever performed on the database. It does this so that in the event of a catastrophic failure where you lose the data file, you can restore to the point of failure by backing up the log and, once you have restored an old data backup, restore the log to replay the lost transactions.
To prevent this building up, you must back up the transaction log. Or, you can break the chain at the current point using the TRUNCATE_ONLY or NO_LOG options of BACKUP LOG.
If you don't need this feature, set the recovery model to Simple.
My dear friend it is vey important for a DBA to check his log file quite frequently. Because if you don't give much attention towards it some day it is going to give this error.
For this purpose you have to periodically take back up so that the logs file would not faced such error.
Other then this the above given suggestion are quite right.
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2000 or ask your own question.
Hi Katy,
Thank you for posting in Community.
We understand your concern and we will provide the assistance required to help you resolve the issue.
Before we proceed, I would like you to provide the information stated below:
Can you provide the entire error message that you received?
I would like to inform that the error that you receive indicates that the internal storage capacity for File History is almost full. To rectify this, I would suggest that you perform the methods mentioned below:
If your File History drive is full or almost full, but you can free up some space by changing how often File History saves your files and how long it keeps older versions of your files.
To change how long File History keeps files
Open Control panel, locate and select File History
Tap or click Advanced settings.
Under Versions:
Next to Save copies of files, tap or click the list, and choose a time interval.
Next to Keep saved versions, tap or click the list, and choose a time interval.