Thread: Log file auto shrink question
11-18-04, 12:09 #1Registered User
- 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
11-18-04, 14:33 #2Registered User
- 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."