March 28, 2002 at 9:15 am
I was working on a test database when I received the message that the transaction log was full. At that point I performed a transaction log backup thinking that that would remove the inactive entries from the log and free up some space. After having done that, I looked at the used and free space on the log, there was no change. i then tried to backup the database and got the same message saying 'backup the log to free up some log space'. After several hours passed, and not having touched the log or database, the FREE space was acquired again.. I'm very confused on what's happening. The size of the file is fine so I don't want to do the DBCC shrinkfile, I just wanted to free up some log space.
March 28, 2002 at 9:56 am
For log files, SQL Server uses target_size to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. Unlike data files, the shrinking of log files is not immediate. Each log file is marked with the target size of the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file and bring its size as close to the target size as possible. Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB. For more information on when truncation occurs, see Truncating the Transaction Log. For more information about determining virtual log file sizes, see Virtual Log Files.
<Above taken from post where Tom replied.>
Angela Beckwith
March 28, 2002 at 1:49 pm
If you are just trying to free the space without shrinking the log then try
BACKUP LOG [dbname]
WITH TRUNCATE_ONLY
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 29, 2002 at 7:46 am
This is also assuming that these are commited transactions in your log. If you are running a very large transaction, a database and/or transaction log backup will not effect the free space in the log.
You have a few options open to you then:
Change the size of your transaction. ie. add some commits
Increase the size of your transaction log.
Hope this helps.
Tom Goltl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply