February 11, 2009 at 11:37 am
I have a pair of servers set up in a mirrored arrangement. The problem is that the size of the transaction log file for a key production database is approximately 23GB. However, the total amount of the data in the file is only 3% (Leaving 97% unused).
Can someone suggest the best way to recover this space?
February 11, 2009 at 12:10 pm
The question that you should first ask your self is if the file will get to its current size again. For example if you checked the amount of free space just after the log backup, then you should see that only a small part of the file is being used. Also if you have a job that reindexes the server once a week at night, you might end up with the file getting bigger and reach its current size again after that job runs. If you know that the file will not get to its current size because you modified something (for example you’ve added more log backups or you modified the maintenance jobs) then you should shrink the log file. You can do it with DBCC shrinkfile command. There are lots of details about it in BOL.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 11, 2009 at 12:44 pm
pjlewis (2/11/2009)
I have a pair of servers set up in a mirrored arrangement. The problem is that the size of the transaction log file for a key production database is approximately 23GB. However, the total amount of the data in the file is only 3% (Leaving 97% unused).Can someone suggest the best way to recover this space?
Why is the size so large? Are you taking regular backups? What is your recovery model on? Are you running any large transactions? How busy is the database?
You need to consider the above questions before shrinking it so that this problem would not arise in the future.
and Before shrinking the log file backup your transaction log and then shrink it to as little as possible but before you need to analyze how much your transaction log is going to grow to.
Is autogrowth/autoshrink enabled for the file?
The best thing is to set the transaction log file to a lareg value.
February 11, 2009 at 1:56 pm
Thank you for your prompt reply. I cannot answer the question as of yet since I am new to the company.
I do know that the actual data in the file is small (less than 2 GB), but the file size remains 23GB.
I know that I can break mirroring, backup the log file, truncate it and then reestablish mirroring.
Can I use DBCC Shrinkfile while mirroring is running?
Thanks.
February 11, 2009 at 2:00 pm
I hope to shrink the log file for the key database, then start observing what happens to this database in a systematic fashion. The person whose duties I am assuming is the founder (great person, but not a DB heavyweight, and desperate to step away from the technical side so he can do "President" stuff to move our company forward.
I do not think Indexing has been done for at least 1/2 year. However I will be putting maintenance plans in place so this will not be a recurring problem.
Thanks.
February 11, 2009 at 2:07 pm
You don't need to kill your mirroring session to truncate your transaction log file.
The Redneck DBA
February 11, 2009 at 6:54 pm
Do I use the shrinkfile command? I have used it on non-mirrored databases with great success.
February 12, 2009 at 12:56 am
Also I doubt that mirroring is not in sync hence unable to truncate the data from log on production.
Make sure your mirroring is working find before taking any steps.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 12, 2009 at 8:23 pm
You shrink a mirrored databases's log file just like any other. Mirroring has no effect on your log file backups/truncating.
The Redneck DBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply