September 20, 2003 at 12:41 pm
Hi,
I have a 600Mb transaction log that filled up during a mass update. The mass update also managed to fill up the database on MSSQL 6.5 (which will be upgraded to MSSQL2K next year). I have since exteneded the database size.
I tried truncating the log file which had no effect and then I tried dumping it (with init) to free up some log space but haven't had any luck with that either. The fail message I got from the dump said something about using the NO_LOG option. Not sure what that does so will look that up tonight.
Any helpful suggestions as to how I can free up the log would be apreciated.
Thanks,
Sal.
September 20, 2003 at 8:22 pm
I've sent all my SQL 6.5 servers to the recycle bin, but I think the syntax is:
DUMP TRANSACTION YourDBName WITH NO_LOG
or
DUMP TRANSACTION YourDBName WITH TRUNCATE_ONLY, NO_LOG
If these fail, you might want to try the following to ensure SQL isn't confused about the database's log usage:
USE YourDBName
DBCC CHECKTABLE('syslogs')
Cheers,
- Mark
Cheers,
- Mark
September 21, 2003 at 9:34 am
Thanks McCork, it's sorted now.
I ran:
DBCC OPENTRAN (images) --check for open transactions>none were found
DUMP TRANSACTION images WITH TRUNCATE_ONLY, NO_LOG --flush log file
USE images DBCC CHECKTABLE (syslogs) --check log size>614Mb free but still
reporting its full
DBCC UPDATEUSAGE (images) --to update the sysindexes table as it might not
have been able to update if no room on log file
this fixed the log file but it still reported it was full and didn't corrected itself until I did some updates on the database, so when I ran the updates there would have been enough room for the sysindexes to update themselves and report the correct logfile sizes.
Sal.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply