Hi,
I'm running a SQL Server used only for development and testing. Because of this, a lot of DELETE commands (and other "logable" operations) are issued.
Now, I DON'T WANT to use any logging at all on this server because I don't see the use for it and it's taking too much space on my hard drive.
How can I remove the log files and stop SQL Server from using them? Plus, maybe for some reason that I don't understand, this is not such a good idea. If so, can you please tell me why?
Thanks,
Skip.I'm not aware of a db that is operating normally without a log.
first of all, delete command is logged. If you look for non-logged operations, please check BOL for "bulk insert", "select into", "truncate table" etc. They will not cause the log to grow.
But a lot of times, you have to use "DELETE". you can turn the database into simple recovery mode 'cause it is testing server. The log will be truncated.
You can also run scheduled job to do "backup log xx with truncate_only" with proper frequency.
When none of the above works, you need to look into if you have open transactions by DBCC OPENTRAN. You should also modify your delete statement to do transactions at a smaller scale, say, commit transaction every 100 rows. It will allow the log to be checkpointed and truncated.|||Set the Recovery model of database to "Simple" :)|||Alright, now that's nice!
Still though, my transaction log is 602 Megs, I find it a little too big. I'd like to backup it because, as I understand, it's the only way to reduce it.
Now, when I go to the backup screen, I can't choose to backup my transaction log because the option is disabled. Why is that?
Thanks again,
Skip.|||In the database properties window navigate to Transaction Log tab and see how much is allocated. I suspect that 602 is the number you're going to see. You'll probably need to do shrinkfile against your trx log. The reason Backup Transaction Log option is disabled is because your database is in Simple Recovery mode.
No comments:
Post a Comment