Log File Size Problem(.LDF)

• Jol

  SSC-Addicted

  Points: 429

  More actions

  September 21, 2005 at 10:10 am

  #89291

  Hi All,

  I have a database in which tables are updated every day from other database using DTS package.The log file (.LDF) for this database just grows exponentially untill I am out of Disk space.At this point the DTS package fails to execute.The error I am getting is as follows..

   

  Step Error Source: Microsoft Data Transformation Services (DTS) Package

  Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80004005): The log file for database 'Voilet' is full. Back up the transaction log for the database to free up some log space.)

  Step Error code: 8004043B

  Step Error Help File:sqldts80.hlp

  Step Error Help Context ID:1100

   

  Can any one help me how to move on from here...

  Also how can I stop this problem from recuring...

  Thanks for the help

   

  Joel

   

   

   

   

   

   

  ---

  Kindest Regards,

  Joel

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  September 21, 2005 at 10:19 am

  #591876

  Hi,

  You have to set Recovery Model in database properties Option tab to Simple or Bulk-logged for the time of package execution depending on what your package is doing. Please note that transaction logging will not be taking place and you will have to change the Recovery Model back to full and take a full database backup before returning to the normal logging and backup schedule. You can change Recovery Model by TSQL as a part of the package or a job I suppose.

  Also make sure that the package does not work as one big transaction because even with the Simple Recovery Model only transactions that are completed are removed from the transaction log

  Regards,Yelena Varsha

• Yuri E

  Valued Member

  Points: 72

  More actions

  September 22, 2005 at 9:23 am

  #592279

  Hi,

  You can set up and schedule a job to truncate log file periodically. Here is the script that I use for truncating log files:

  EXEC sp_addumpdevice 'disk', 'CFSales_Dat', 'F:\SQL2000\MSSQL\BACKUP\CFSales_dat.dat'

  GO

  EXEC sp_addumpdevice 'disk', 'CFSales_Log', 'F:\SQL2000\MSSQL\BACKUP\CFSales_log.dat'

  GO

  BACKUP DATABASE CFSales TO CFSales_Dat

  GO

  BACKUP LOG CFSales TO CFSales_Log

  GO

  BACKUP LOG CFSales WITH TRUNCATE_ONLY

  GO

  sp_dropdevice 'CFSales_dat'

  GO

  sp_dropdevice 'CFSales_Log'

  GO

  --SELECT * FROM dbo.sysfiles -- use this query to find out your log file logical name

  GO

  DBCC SHRINKFILE (CFSales_log, 1)

  GO

• Jeff Gray

  SSChampion

  Points: 10667

  More actions

  September 22, 2005 at 9:54 am

  #592308

  The log file will always grow exponentially if the growth is set to be by percentage.  To avoid exponential log growth, you would need to change the file growth to a fixed amount.

  Thyat being said:

   

  Changing the recovery model and/or truncating the log file can disrupt your ability to recover the database to a point in time.  I think you should let us know how you are backing up the database now, before we can give you a good answer.

  Either of the other answers, although perhaps fine in theory, might cause you a trip to the unemployment office.

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  September 22, 2005 at 1:12 pm

  #592370

  You also might want to sesrch the sight ... there are lots of threads concerning this topic ...

   

  Use the 'search' bar and enter 'transaction log' and select the 'search articles' and 'search discussions' checkboxes. Your answer and lots of great informatin are definitely there.

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."