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 |
|
jjc
Starting Member
8 Posts |
 Posted - 2004-10-04 : 10:17:18
|
| Hi all,I'm 'looking after' a SQL Server whilst the DBA is on leave. Day 1 and I've run into problems already... can anybody help?The Transaction Log is approaching 5GB in size and the filesystem holding the log is now 99% full. What can/should I do about this?The transaction log is backed up daily using:EXECUTE master.dbo.xp_sqlmaint N'-PlanID E682F567-3F14-49B0-89BA-79D8C696B76D -VrfyBackup -BkUpMedia DISK -BkUpLog "R:\SQL Backups\Logs" -DelBkUps 1DAYS -CrBkSubDir -BkExt "TRN"'My limited knowledge of SQL Server led me to believe that the transaction log was truncated after every backup, but this doesn't seem to be the case.Is it possible that the data in the log file is being truncated but the filesize is staying the same? If so, can I simply reduce the size in Enterprise Manager (viewing the database properties and the Transaction Log tab)? Could you please help?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-04 : 13:46:24
|
| Yes the transaction log gets truncated after a tlog backup. Look up BACKUP LOG for more information on this.So the backup log option is only being run once per day? That doesn't seem like a lot. We backup ours every 15 minutes. I wouldn't suggest changing your schedule without the DBA's consent on it, but it's possible that once a day is no longer enough for the amount of data going through your system.Are you sure the backup log job isn't failing?Tara |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-04 : 13:56:43
|
| seehttp://www.nigelrivett.net/TransactionLogFileGrows_1.htmlCould be a long running transaction that is stopping the log from being cleared.==========================================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. |
|
|
|
converge
Starting Member
7 Posts |
Posted - 2004-10-04 : 21:27:18
|
| I shrink mine and it does thejob. Maybe you can try it. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-04 : 23:26:25
|
quote:
Originally posted by jjc
Is it possible that the data in the log file is being truncated but the filesize is staying the same? If so, can I simply reduce the size in Enterprise Manager (viewing the database properties and the Transaction Log tab)?
when you truncate the log file, it frees up space but does not reduce the size, you need to issue a dbcc shrinkfile. then set a reasonable size for your log file (not unrestricted growth), so that you'll have certain allowance when worse comes to worst and hard disk shows 0 disk space. |
|
|
|
|
|
|
Transaction Log Size