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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2012-01-23 : 12:24:30
|
| I have a 21 gb log file the db is big because there is a table with many archived recordswhat can I do to reduce the size of the log fileI tried backing up and truncating but it didn't helpwhat can I do to help this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-23 : 12:41:11
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-01-23 : 12:44:08
|
| mdf is 15 mbI just set the log file to backup transaction log every 15 minutes (but only set that an hour ago - should that help)recovery is set to full |
|
|
|
biswajitdas
Starting Member
44 Posts |
Posted - 2012-01-23 : 13:44:00
|
| we can do in other steps that add another log file to database , and remove the 21gb one.But make sure there is no activity going on the database.Sr Sql server DBA/Artitech |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-01-23 : 14:12:00
|
quote:
Originally posted by biswajitdas
we can do in other steps that add another log file to database , and remove the 21gb one.But make sure there is no activity going on the database.Sr Sql server DBA/Artitech
Bad idea...the proper approach is to make sure you are taking regular transaction log backups. If your database does not need point in time recovery, you can switch to simple recovery model and avoid the frequent transaction log backups.Once you have that done - then you can perform a one time shrink of the log back down to normal operating size.Jeff |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-23 : 14:22:51
|
quote:
Originally posted by biswajitdas
we can do in other steps that add another log file to database , and remove the 21gb one.But make sure there is no activity going on the database.
Totally unnecessary, to get it down to a reasonable size a simple shrink would suffice, once we've identified the size to shrink it to and have fixed the maintenance problems that caused the huge growth in the first place. Also, iirc you can't remove the first log file that a DB has. You can add and then remove other log files, but not the first.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
21 gb log file