Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Afroblanca
Starting Member
9 Posts |
 Posted - 2005-08-23 : 10:08:46
|
| I've noticed that the transaction logs for my SQL Server databases just keep growing and growing. I know that there is an option to constrain the log file size, but this sounds like a terrible idea - what happens when all the allocated space is used up? Here is what I want to do - 1) Set up a system whereby the transaction logs are backed up nightly.2) Once the logs have been backed up, shrink the actual transaction logs down to a reasonable size (like a couple of gigs or something)Is this possible? If so, how do I do it? Keep in mind that I am not a DBA.Thanks. |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2005-08-23 : 10:22:53
|
| Yes it is possible, but it is not recommended. Shrinking transaction logs causes external fragmentation (log will grow again anyway). Also, log file(s) will expand at the time of highest activity. The solution is to backup transaction log regurarly and frequently enough (I assume db uses full recovery model), to avoid long running transactions and to rebuild indexes selectively based on fragmentation level, not all at once. Transaction log file size should be set to maximum expected in advance to avoid expensive growth and fragmentation on filesystem level. |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-08-23 : 10:27:07
|
quote:
Originally posted by Afroblanca
I've noticed that the transaction logs for my SQL Server databases just keep growing and growing. I know that there is an option to constrain the log file size, but this sounds like a terrible idea - what happens when all the allocated space is used up?
Then the database would stop working.quote:
Here is what I want to do - 1) Set up a system whereby the transaction logs are backed up nightly.2) Once the logs have been backed up, shrink the actual transaction logs down to a reasonable size (like a couple of gigs or something)Is this possible? If so, how do I do it? Keep in mind that I am not a DBA.Thanks.
It is possible, but as has been said it is a bit pointless to shrink the file. Do you need point in time recovery? If not, turn your database to simple recovery mode. This will ensure that your logs are automatically truncated and should keep the size down.-------Moo. :) |
|
|
|
|
|
|
|
|
How to keep transaction logs to a reasonable size