August 13, 2003 at 7:15 pm
Hi
I have a backup scheme as follows:
I backup up the transaction log (append) hourly in the morning.
I then backup diff append at noon and then again hourly transaction logs (append)
I then perform a full overwrite backup.
The log file size of my databse has grown to about 40 % of my database size. I ran the shrinkdatabase on the database but it came back with the message that it could not shrink log since the virtual log file 2 was in use. I then tried to backup log to a brand new backup device and then tried to shrink again but the same thing happened (hope its okay to backup the log to a new device!). Please advise how I can shrink the log. I don't want to truncate it since I want point in time recovery. The database is using only 50 MB but the log file is 1.5 GB. Please advise on how I can shrink the log ?
August 14, 2003 at 4:33 pm
You can use DBCC LOGINFO(dbname) to see which virtual log segment is active. The trans log is actually divided into multiple "virtual logs." If you notice that there are entries toward the bottom of the list where the "Status" column is "2", then these are active, and the trans log can only be shrunk back to that point. After some activity in the system, it will eventually "roll" back to the beginning. Also I'd check to make sure you don't have any open transactions in the system (use DBCC OPENTRAN to see which spid is constantly in a transaction).
-Dan
-Dan
August 25, 2003 at 6:16 am
Look at this script:
http://qa.sqlservercentral.com/scripts/contributions/26.asp
which will create transacations and free space for you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply