SQL Server Performance Forum – Threads Archive

Problems with log file

How can i avoid or turn off the log file of a certain database in MSSQL 2000. The scenario is like this: I created a DTS and scheduled it to execute every hour. In this DTS i create temprorary file, truncate a certain table and transport a text file to the tables. My scheduled task runs smoothly for a week then suddenly I noticed that my DTS returned an error. I try to back up my database but it says that the log file is full and you have to back it up. I did it almost every week to shrink my database in order to use it again because you can not perform any transaction whenever the log is full. So please help me about it. What are the procedures to do or can you send me scripts to solve this kind of problem. Thanks…

Do you back up the log? You must – but if you don’t want to, put your database in SIMPLE recovery mode. Then the log will be automatically cleared down all the time each time the CHECKPOINT process runs. Logs go on growing forever until they fill up if your database is in FULL recovery mode and you don’t take any action to prevent it filling up. Tom Pullen
DBA, Oxfam GB

You cannot turn off or avoid Transaction log setting for the database, in order to access the database Tlog file must be available. In addition to what Tom referred, I suggest you to deploy the SIMPLE recovery model and take intermittent FULL Database backups in order to maintain the consistency in data recovery. If not you can run the BACKUP LOG statements which running this DTS process. Check the size of transaction log defined for those databases and set higher limit to ensure there are no issues on disk space with this setting. Error 9002 actions include:
– Backing up the transaction log
– Freeing disk space
– Moving the log file to a disk drive with sufficient space
– Adding or enlarging a log file HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>