June 26, 2004 at 1:19 pm
I have a db that is 2 gb and I do full backups on it every night. Latey the transaction log seem like it is not shrinking. I went into the maintance schedule and setup a backs for full and also a full on the transaction.
The transaction log is now 79 gb. ( the reason it got huge was during testing of the accounting app we duped the company almost daily to test new methods.) I am not really sure why it is not being droped. I am now at a point that I can not do a transaction back automated at all.
So the question. Can I detach the DB and then reattach it without the log file. I am sure that it is completed all of the transactions in the log.
Or should I run the "BACKUP LOG database_name WITH TRUNCATE_ONLY" I can do a backup of the transaction log but it will take a long time, 40 minutes.
And how can I make sue this does not happen again?
Open to any and all suggetions...
It does not get hit at night and I am sure we only put a couple hundred megs of trans actions a day and the server CPU seem very bored even under load.
(FYI I use winrar and compact the SQL backups from 2.5 gb to 200 mb. I keep them on a disk and regularly burn old months to DVD's - works great)
Thanks for the Help
Matt
June 26, 2004 at 2:22 pm
Update info -
Okay I have been diggin and I see that the DB is in Full mode and in the DBCC loginfo has all of the logs as active.
On a smaller DB I did another full backup of the transaction log but it did not chage them to inactive. So the size stayed the same.
I changed it from Full to Simple and did a full backup.
Then uses DBCC SHRINKFILE(Art_log,2)
Then DBCC loginfo - it worked great and now it is 2 mb. I am curiuos still how to get the log file to stay small and do Full backups while in Full mode?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply