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 |
|
CanadaDBA
583 Posts |
 Posted - 2004-09-08 : 14:31:18
|
| [code]Win2000 + SQL 2000DB's property - General tab: Database size: 4892MB Space Available: 3029 MB DB's property - Data file tab: 2268 MB Space allocatedDB's property - Transaction log tab: 2625 MB Space allocatedRecovery Model: FULL[/code]I have set the Alert to notify me if the T-Log size over 2.5GB. I am receiving alert emails from the server indicating the T-Log size is over 2.5GB. Should I increase the T-Log size in alert setting? Or decrease the T-Log size? I get a full backup every night and T-Log every hours but why the transaction log file is growing up? What's the best thing to do and what is your recommendation?Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-08 : 14:38:17
|
| It just depends. We have no way of knowing how big your tlog should be. Are you getting the alert when an optimizations job is running or has run? Optimizations would be from a maintenance plan or DBCC DBREINDEX. If so, then that file size is required for that operation to run.Tara |
 |
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-09-09 : 01:00:18
|
| Your transaction log is bigger that your database itself. That may not be a good sign.Are you truncating the transaction log after taking a backup every hour?Are you getting the alert messages at any particular time of the day? If so investigate and analyse what is running on the database in that time period.Hemanth GorijalaBI Architect / DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-09 : 08:01:25
|
Shouldn't it be? What it means?quote:
Your transaction log is bigger that your database itself. That may not be a good sign.
I don't truncate the log file after backup. Should I do that? How?quote:
Are you truncating the transaction log after taking a backup every hour?
What about if I use DBCC SHRINKFILE command to shrink the TLog?Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2004-09-09 : 08:05:48
|
What's your idea about shrink the TLog using DBCC SHRINKFILE? Does it have any disadvantage? quote:
Originally posted by tduggan
It just depends...Tara
Canada DBA |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-09 : 08:11:17
|
| >> I don't truncate the log file after backup. Should I do that? How?You are taking log backups so don't need to truncate.>> What's your idea about shrink the TLog using DBCC SHRINKFILE? Does it have any disadvantage? If the tr log needs to be that size then shrinking it will just use (a lot of) resources when it has to grow again.Decide whether the size of the log is causing a problem. If it is then change the way you run things so that they don't cause so many log entries or free them more frequently.One long running transaction will prevent any log entries being reused until it completes.Needing a large tr log isn't a problem in itself but may be a symptom of some other problem.seehttp://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. |
|
|
|
|
|
|
T-Log size