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 |
|
hismightiness
Posting Yak Master
164 Posts |
 Posted - 2008-12-04 : 20:50:09
|
I have some log files that are quite large, and I wanted to lower their size. I found this bit of T-SQL and it works on my test machine. But I want to know if this is safe to execute on a production environment, and if there are any other pros or cons to be aware of.backup log [databasename] with truncate_only;DBCC SHRINKDATABASE ([databasename], 10, TRUNCATEONLY); - - - -- Will -- - - -http://www.willstrohl.com |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-12-04 : 21:04:38
|
| It depends whether or not you want to be able to backup the transaction logs and restore from the backups. They won't harm your database but they will affect your backup and restore strategy.You need to read up on backup and restore stratgies, understand the implications of the choices you have and then use whichever strategy is best for your environment. Start herehttp://msdn.microsoft.com/en-us/library/ms175477(SQL.90).aspxFor example, if you are just testing then you could use a Simple Recovery Model and the logs will mostly stay small and you won't need to run those commands you found. But, in a production environment, you want very reliable up to the minute recovery offered by the Full recovery Model and you must not run those commands you found, you must make regular log backups - which again will keep your logs from growing.Once you understand the different backup and restore strategies you'll find that those commands you found are not necessary. |
 |
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2008-12-04 : 21:06:45
|
| Interesting. Thanks!- - - -- Will -- - - -http://www.willstrohl.com |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-12-04 : 21:21:36
|
quote:
Originally posted by snSQL
... you must make regular log backups ...
Just wanted to point out that for a Full Recovery Model, you must make regular log backups AND also full backups (less frequently, but also regularly), but that's all very clear in the docs I referred you to. |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2008-12-04 : 21:27:14
|
| I already do. I had no idea that those two lines of code could have so much impact on an existent back-up and recovery plan. We backup regularly the database (full/diff) and transaction log. The frequency depends on the database.- - - -- Will -- - - -http://www.willstrohl.com |
|
|
|
|
|
|
|
|
Is This T-SQL Safe to Use