| Author |
Topic |
|
fredong
Yak Posting Veteran
80 Posts |
 Posted - 2004-02-13 : 10:13:54
|
| I have transaction log is growing rapidly overnight and I tried DBCC shrinkfile and DBCC shrinkdatabase and it won't work. Is there a way to shrink the transaction log without bringing the SQL 2000 server down? Also how can find out which query is causing the transaction log to grow rapidly? DO I use DBCC log or sp_who and what should I look for if I use these command? Thanks.k |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-13 : 12:17:04
|
| Every query will use the transaction log. What is your database recovery model set to? If FULL, are you performing regular backups of the transaction log. This is very important to keep the size down. If the transaction log doesn't have any free space, it will keep growing until it isn't able to anymore either due to lack of free disk space or it has restricted growth set.Tara |
 |
|
|
fredong
Yak Posting Veteran
80 Posts |
Posted - 2004-02-13 : 14:11:36
|
| Lucky this is not my database. it is someone else. they are doing only full nightly backup and and no transaction backup hourly.Now their transaction ldf file has grow up to 89 gig , I did a hot backup and then DBCC shrinkfile and the log file grows even larger.. wow is crazy.I guess the DBCC shrinkfile increase the log file too. and is there a way to shrink it without stopping the SQL server? Please advise. Thanks.k |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-13 : 14:28:15
|
| What is the database recovery model set to?Tara |
|
|
|
fredong
Yak Posting Veteran
80 Posts |
Posted - 2004-02-13 : 14:33:17
|
| I beleive is bulk-logged or whatever the default isk |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-13 : 15:04:58
|
| You'll need to check it. The default is FULL. With FULL, you must backup the transaction log regularly. But your environment might not need the ability to restore to a point in time. If that is the case, change the recovery model to SIMPLE. With SIMPLE, you do not need to backup the transaction log. But you also lose the ability to restore to a point in time.After you backup the transaction log (schedule a job to do it about once an hour) or change the recovery model to SIMPLE, then run DBCC SHRINKFILE on the LDF file.Tara |
|
|
|
fredong
Yak Posting Veteran
80 Posts |
Posted - 2004-02-13 : 15:11:26
|
| I don't think I can backup the transaction log because is 86 gig in size and they only have 11 gig left on that drive.k |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-13 : 15:53:45
|
| Do they have room to do a full database backup? If so, do that and then truncate the transaction log (of course, make sure no users are online when you do this) and then set the recovery mode to whatever it needs to be, and make sure they do transaction log backups.If they generate 86GB of transactions per day (that's a lot) then they certainly need to re-evaluate their hardware configuration.And hire a DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-13 : 16:27:04
|
| Run:BACKUP LOG DBName WITH NO_LOGto clear out the transaction log. Then run DBCC SHRINKFILE.If you don't care about point in time recovery, change the recovery model to SIMPLE. If you do, schedule a transaction log backup job.I'm sure that the database has been set to FULL and not a single backup log has occurred, thus the 86GB.Tara |
|
|
|
fredong
Yak Posting Veteran
80 Posts |
Posted - 2004-02-17 : 10:19:49
|
| Thanks, it worksk |
|
|
|
|
DBCC shrinkfile don't work on transaction log