| Author |
Topic |
|
Burningcoals
Starting Member
4 Posts |
 Posted - 2004-11-08 : 15:49:33
|
| Hi Everyone!I am running SQL2000 ENT and have some fairly large databases.Lately my transaction logs and other data file logs are filling up at an alarming rate.One of my DB's has a transaction log of about 3gigs! I can't for the life of me find reliable information on actually how to truncate the log.I have 3 MCSE books in front of me and NONE help me.In one case the book tells me when creating a full backup to check the truncate transaction log box in the options tab under database. But I look in every possible way there is no check box for truncating the logs.I need to create checkpoints that will truncate the logs, I am backing up the transaction logs, but why back them up and not truncate? I dont even know why MS made it so you can't configure the maintnence plans way better.Can anyone explain to me how I can create a checkpoint that will truncate the transaction logs and other logs for that matter?My recovery model is "Full" I dont want to keep upping the size limits on the logs, I would like them to be truncated on backup.Thanks for any info! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 15:51:53
|
| They are being truncated when the log is backed up. Check out BOL for more details. You probably aren't backing the log up very frequently. We backup ours every 15 minutes. How often do you do yours?3GB isn't very big, BTW. How big is the database?Tara |
 |
|
|
Burningcoals
Starting Member
4 Posts |
Posted - 2004-11-08 : 16:17:42
|
| The Database is 8gig in size.We had the limit set at 3gig. And it hit that limit. So I did a manual transaction log backup, but didnt notice any reduction in log size, still showing 3gig.So I have placed the log growth to unlimited. I just figured when I did run the transaction backup it would have truncated some of the log file.Is there anything I am doing wrong in this process to try and shrink the logfile down some?I have the transaction logs being backed up every 15 mins on our most critical DB's, and every 4 hours on other non critical DB's.I guess my main question is, after I did run the Transaction log backup I did not notice a reduction in size of the log.Thanks for the help! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 16:21:10
|
| The log being truncated does not mean that the file will shrink. It means free space in the file. To shrink it, use DBCC SHRINKFILE. But don't schedule this. Only run it when you know it will never need the space again. If you do shrink it and it later needs the space, you will encounter a performance hit while the file is expanding. So it's best to leave it alone.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 16:41:34
|
| http://www.nigelrivett.net/TransactionLogFileGrows_1.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Burningcoals
Starting Member
4 Posts |
Posted - 2004-11-08 : 17:14:09
|
| Thanks for the info!I believe I now am grasping this. I have set all my non critical databases to recovery model simple. And left the 2 critical ones at full and am doing transaction log backups every 15 minutes now.I am also doing full backups every 12 hours from 2:00am, anyone see any problems with that?Also for the Data file logs, are they truncated too when full backups are run? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-08 : 17:15:43
|
| Truncation occurs when the tlog is backed up.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2004-11-08 : 17:23:50
|
| There is no such thing as Truncating the Data File.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
Burningcoals
Starting Member
4 Posts |
Posted - 2004-11-08 : 17:26:26
|
| Thanks everyone for the help! |
|
|
|
|
HELP my logs are out of controll!