April 24, 2002 at 9:02 am
SQL Server 2000 Standard
I have a Transaction Log file with allocated space of 113MB. It is set to automatically grow by 10MB. Max size is 300MB.
Current status:
Used --> 104.09MB
Free --> 8.4MB.
What should happen to the size of this Log when I run Transaction Log Backups in a maintenance plan at 30 minute intervals ?
Should the size go down ? Or does it just remove all inactive transactions from the Log and keep the log the same size ?
(I want to make the Log a smaller size.)
April 24, 2002 at 9:20 am
Bryan,
When the Transaction Log is backed up, all of the commited transactions in the Log are removed. So the size of the TLog will remain the same, but the Used percentage of the TLog will decrease.
To shrink the TLog itself, you should use DBCC SHRINKFILE.
Clive
Clive Strong
April 24, 2002 at 9:37 am
the committed transactions are removed and put where ?
what is contained in the T-Log backup exactly ?
April 24, 2002 at 9:44 am
Hey,
They are not moved anywhere. They are physically deleted from the TLog. As the Log is backup, it removes all of the commited transactions (inserts, updates & deletes).
The Log file contains all inserts, updates & deletes performed on the Database it is associated with. So, if you get a server failure, you can restore the last full backup, and then all of the Log files upto the point where the server failed. This writes all of the transactions back to the database, so in theory, you are back (as close as possible) to the time of the server failure.
Think thats pretty much right!
Clive
Clive Strong
April 24, 2002 at 9:49 am
Can you run the dbcc shrinkfile while users are accessing the database?
I ran the transaction log backup and that didn't decrease the size of the log file.
Tracy
April 24, 2002 at 10:01 am
Tracy,
Yes, you can run DBCC SHRINKFILE while users are accessing the Database.
With regards to the file not shrinking, when you issue the command, SQL tries to shrink the file straight away. At times it cannot free all of the space in one hit (I think thats due to the active part of the log being greater than the target physical size you requested...I could be wrong on that though).
Check out BOL and "Shrinking the Transaction Log" for a better description of how it all works!
Clive
Clive Strong
April 24, 2002 at 1:13 pm
From BOL
quote:
The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run SQL Server in single-user mode to shrink the system databases
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply