March 10, 2010 at 9:44 am
Due to some unusual transactional migration efforts, we have a log file that has completely taken over all available space on the disk. I have tried to shrink the log using the following command:
use <database name>
go
DBCC SHRINKFILE ('LogFileName', 100)
go
There is no disk space left for performing a log file backup. Using the above script, I am seeing no reduction in the size of the log file. Any suggestions from the experts? Assistance would be greatly appreciated!
March 10, 2010 at 9:50 am
You can't shrink it if there are open transactions.
You might want to try:
DBCC OpenTran (LogFileName)
to find out what SPID is the offending one and take proper action on it.
March 10, 2010 at 9:53 am
I know what is causing the file to grow; and there are still open transactions as the process is still running. If I ask the user to terminate the migration, I'm not sure of the implications of whether they could restart it at a certain point. Is there any option to clearing out inactive transactions while open transactions are still being written?
March 10, 2010 at 10:00 am
kwilt (3/10/2010)
I know what is causing the file to grow; and there are still open transactions as the process is still running. If I ask the user to terminate the migration, I'm not sure of the implications of whether they could restart it at a certain point. Is there any option to clearing out inactive transactions while open transactions are still being written?
What are they doing, updates? You can ask them to stop the process it will just rollback it they started this in single batch. Log is growing due to running transaction, i am not sure clearing inactive will do any better.
March 10, 2010 at 10:12 am
More info: The process was running as a job, which has failed due to insufficient disk space. I again tried the SHRINKFILE commands with no results. The team doing the migration (which is inserting rows into the target database) has indicated they can verify where the process failed and reset their parameters to resume where the process failed.
In an instance such as this, is truncating the log file an option?
March 10, 2010 at 10:15 am
kwilt (3/10/2010)
Due to some unusual transactional migration efforts, we have a log file that has completely taken over all available space on the disk. I have tried to shrink the log using the following command:use <database name>
go
DBCC SHRINKFILE ('LogFileName', 100)
go
There is no disk space left for performing a log file backup. Using the above script, I am seeing no reduction in the size of the log file. Any suggestions from the experts? Assistance would be greatly appreciated!
You should be doing backups to another drive. Do you have a network share you can use for backups ?
March 10, 2010 at 10:23 am
You are a genius! (Or I wasn't thinking--too much pressure?)
I just ran a transaction log backup onto a different drive that DID have disk space...once the backup completed i used the SHRINKFILE command, and our crisis has been resolved. I can't thank you enough!
March 10, 2010 at 10:24 am
I am assuming the database is NOT using the SIMPLE recovery model. How often are you running transaction log backups? These will clear out inactive transaction log records.
At this point, if you haven't run any, you may need to run two before you will be able to shrink the log file. Is the job doing the updates in batches or as one large batch? If it is uploading or updating a large number of records, you may want to look at modifying the process to work in batches.
March 10, 2010 at 10:28 am
We definitely need to rework the process that is causing this problem. It is being run in batches, but I think even then the batches are too large.
The bigger issue here is my lack of knowledge of how to control these log files. I'll be spending some time reading Gail Shaw's article "Managing Transaction Logs."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply