Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Unanswered: Log file auto shrink question

    Hi, I am trying to automate shrinking the transaction log file on SQL server.
    Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.

    I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
    How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...Thanks

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    The error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this post.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts