|
HUGE transaction logs |
Post Reply ![]() |
Author | |
BillD ![]() MicrosoftDynamicsForums.com Member ![]() Joined: February 06 2007 Location: United States Status: Offline Points: 2 |
![]() ![]() ![]() ![]() ![]() Posted: February 20 2007 at 4:47pm |
Hi All I have a question regarding Transaction Logs on the SQL database. When I run optimizations on my Solomon database, I get a huge transaction log. Actually bigger than the backup file. I have other databases that are about the same size, but I don’t get a huge transaction log from them. Any Ideas? |
|
William Hall Dillon
Sr. DBA |
|
![]() |
|
Sponsored Links | |
![]() |
|
slguru ![]() MicrosoftDynamicsForums.com Member ![]() Joined: February 19 2007 Status: Offline Points: 25 |
![]() ![]() ![]() ![]() ![]() |
Bill,
Depends what you mean when you say optimizations. But I assume you mean the default items from a maintenance plan, most importantly rebuild indexes. My guess is that you have the recovery model set to Full on these databases, and with Solomon having 5000 some odd indexes and probably alot more activity if you log the "optimization" it can make the transaction log very large.
Some suggestions I would have is to make sure a full backup runs and completes prior to your "optimization". Then I would make sure to do a shrinkdb afterwards. If you are having space issues, you can also set the recovery model to Simple after your backup but prior to running your reindexes. and when complete set the database recovery model back to full.
|
|
![]() |
|
jhunter77082 ![]() MicrosoftDynamicsForums.com Member ![]() Joined: August 15 2006 Location: United States Status: Offline Points: 237 |
![]() ![]() ![]() ![]() ![]() |
You can also schedule a job that will shrink the database on a specific schedule. I have a Stored Proc that you can schedule and run... It will read the recovery model you have set ... save it... set the recovery model to SIMPLE... shrink the database and then set the recovery model back to what it was before. Let me know if you want the script.
|
|
![]() |
|
BillD ![]() MicrosoftDynamicsForums.com Member ![]() Joined: February 06 2007 Location: United States Status: Offline Points: 2 |
![]() ![]() ![]() ![]() ![]() |
Hi
Thanks for your help and yes I'd like the script
Bill
|
|
William Hall Dillon
Sr. DBA |
|
![]() |
|
jhunter77082 ![]() MicrosoftDynamicsForums.com Member ![]() Joined: August 15 2006 Location: United States Status: Offline Points: 237 |
![]() ![]() ![]() ![]() ![]() |
Send me your email and I will email it to you... I will not post it.
|
|
![]() |
Post Reply ![]() |
|
Tweet
|
Forum Jump | Forum Permissions ![]() You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |