Shrinking a Transaction Log 1

[bessebo]

My transaction log is currently 600MB and I want it shrunk to roughly 100MB. I read in BOL that shrinking a transaction log file does not shrink the file immediately but instead causes the file to be marked for later shrinking. Also, if the active portion of the transaction log is at the end of the transaction log file, the file cannot be shrunk. However, as soon as the active portion of the transaction log moved to the beginning of the file, the transaction log file can be shrunk.

Does anyone know of a way to facilitate the shrinking of a transaction log? I have backed up and restored our production database and want as small a database as I can use for a test database. Our transaction log in our production database is set at 600MB and I want this smaller. Any help?

Thanks

 

[foxdev]

See the FAQ faq183-345 for one method. Another way is to set up a series (probably thousands) of UPDATE statements that force log write activity. Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[bessebo]

Thanks so much! It worked like a charm. The code you referred me to is a keeper...

Bessebo...

 

[larrywsamuel]

I have the same problem, I have a 70 GB transaction log file that only uses ~300MB. I could not get to FAQ referenced, recieved a jave error. Do you have a more specifice url to get to the FAQ.
Thanks

 

[foxdev]

I just tried the link above to the FAQ, and it worked fine. You can also access it by going back to the main SQL Server thread list and clicking on the FAQ tab. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org