February 23, 2010 at 12:47 am
Hi,
First off, great forum, have learnt heaps from here.
Ok in to business...
SQL 2005
I have a database with a 6Gb data file and a 30Gb log file (I believe the log file is to big)
There is 2.6Gb space free in the DB
Full recovery mode
Full backup everynight
Transaction log backup every hour between 7 and 7
From what i have read i have a few options to get this log file down to a reasonable size.
1. change the recovery mode to SIMPLE, (does this commit the tansactions?) then change back to full afterwards.
2. Do a manual transaction log backup
Can anyone tell me the best way to go here, problems risks etc...maybe there is another option.
One other thing, i ran the query as below and the result for the db in question is LOG_Backup
SELECT name, log_reuse_wait_desc
FROM sys.databases
Thankyou in advance 🙂
Dave
February 23, 2010 at 8:28 am
I have a database with a 6Gb data file and a 30Gb log file (I believe the log file is to big)
Why do you think this? It may be perfectly okay to have a database with a 6GB data file and 30 GB transaction log file. There just may be a lot of inserting, updating and deleting that is happening in the database or anything else that gets written to the transaction log. It's not always abnormal to have a transaction log that is bigger than the data file. It is dependent on what is happening within the database.
How much free space is in the log file? If there is no free space in the 30GB log file you won't be able to shrink it.
1. change the recovery mode to SIMPLE, (does this commit the tansactions?) then change back to full afterwards.
This will work, but you will have to take a full backup of the database once you change it back to full recovery model to start your transaction log backups again.
2. Do a manual transaction log backup
This will work too. Take a manual transaction log backup, then run the dbcc shrinkfile command on the transaction log in question to take it down to whatever size you want.
The risk involved is that it may just go back up to 30GB sometime soon. It may have grown because of some large inserts, updates, deleted, reindexing, and etc.
February 23, 2010 at 10:07 am
Take a look through this article - http://qa.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2010 at 11:34 am
Are you sure your transaction log backups are running? If so, then most likely you have a nightly/weekly operation to rebuild indexes that is expanding the log file. If that is the case, there are a couple of options available to reduce the size of the transaction log - but you will still see a very large backup when you start it up again at 7am.
BTW - no reason to stop/start your transaction log backups. You can run them 24/7 every hour with no problems.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply