December 29, 2004 at 10:06 am
Got the transaction log as big as 4g while the db is only 156M.
dbcc opentran does not return any open transactions
There are no processes holding locks that I can find.
Just ran sqldiag may be missing a pertinent pointer but
did not find anything outstanding there too.
any pointers will be welcome
Mike
December 29, 2004 at 12:01 pm
Does your maintenance plan backup the logs and then truncate them? I believe that should fix the issue. If it doesn't and you really, really, really, want to get rid of the log. Change the recovery type to simple then shrink the database, then change it back to whatever recovery type you want.
If the phone doesn't ring...It's me.
January 1, 2005 at 10:35 pm
You can also reduce the transaction log by backing it up, then shrink the file. Since the transaction log is supposed to keep track of all logged transaction since it's last backup, once you backup the log, it should allow you to shrink it.
You might want to look into DBCC SHRINKDATABASE too.
January 4, 2005 at 7:29 am
January 6, 2005 at 10:02 am
What version of SQL. if you are not conducting any replication of databases you can change the db mode to simple recovery (if SQL 2000), truncate log on checkpoint if SQL 7. This will keep you log file size in check. The only way it could be growing that large for a db that size is if it is set to never truncate the inactive log entries (full recovery model)
Thanks
Mike
January 7, 2005 at 2:56 am
Hi ,
If you feel your transaction logs are filling in and need to truncate the same carry out the following commands:
Checkpoint
go
backup log <Database_Name> with no_log
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply