October 2, 2002 at 6:59 am
Hi everyone,
I am running 2 jobs and 2 DTS packages(all of which are scheduled as jobs to run nightly). The first job calls a sproc to BACKUP the databases, the second sproc is called to clear the tables in a database, then the first DTS package runs a straight copy load to the database where the tables were cleared, and finally a DTS package is called to load the datawarehouse from the staging dB. This set up runs nightly and has been running well until the last 4 days.
All of a sudden I get "error 9002, The transaction log is full , backup the database to truncate the log then run the package."
Problem is I take care of this by backing up the dB before I even start the load. Then I also have the dB configured with 2 transaction log files. Finally, I haven't had any problems with this in the past.
Aurora
October 2, 2002 at 9:51 am
Has the data set been growing? This can contribute. Is the log not set to autogrow?
Steve Jones
October 2, 2002 at 11:03 am
Thanks for responding Steve. The data does grow but very little (a little less than 1%), and yes I have both the dB file and the log file set to auto grow. As a matter a fact I increased it (although I have a sneaky suspicion this is not the problem) after I saw the error the first time. Then it happened again. Any thoughts?
Aurora
October 8, 2002 at 9:05 am
Hi Christine!
To gain more space, you can free disk space on any disk drive containing the transaction log file for the related database. Freeing disk space allows the recovery system to enlarge the log file automatically. Or you can gain space by adding or enlarging a log file for the specified database.
You can free disk space on your local drive or on another disk drive. To free disk space on another drive:
-Move the transaction log files with an insufficient amount of free disk space to a different disk drive.
-Detach the database by executing sp_detach_db.
-Attach the database by executing sp_attach_db, pointing to the moved files.
Another solution is to add a log file to the specified database using the ADD FILE clause of the ALTER DATABASE statement. Or you can enlarge the log file using the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Adding an additional log file allows the existing log to grow.
I'll hope that it helps you.
Rosana
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply