Pingback from  Links (6/26/2007) « Steve’s SharePoint Stuff

If running WSS 3.0, where is it that I would put in that command, considering it is not running SQL Server Embedded Edition?

Correction:  it IS running SQL Server Embedded Edition, and not SQL 2005.

You can also do "backup log <databasename> with truncate_only" then shrink the log

Many people have the problem that SQL databases grow out-of-control. Actually, it is not the database but the transaction log (the recording of all changes over time) that grow out-of-control. You don't need all those changes for the database to work perfectly, not even for recovery of old documents in sharepoint or whatever. The only reason you would like to keep the transaction logs is because you want to be able to do a point-of-time restore of your database to an earlier moment (e.g. before a database corruption took). In that case, good database backup and recovery strategy is very important and must be in place, otherwise the transaction logs are useless anyway. Therefore, if your databases grow out-of-control, change from full recovery model to simple recovery model and your problems will disappear after shrinking the transaction log. Once you understand the full recovery model and have a backup plan that includes making transaction log backups in place, you can turn full-recovery model back on and your transaction log will not grow out-of-control, because with a decent backup plan, the transaction log is stored on the backup medium and only the most recent portion is left on the server.

Many people have the problem that SQL databases grow out-of-control. Actually, it is not the database but the transaction log (the recording of all changes over time) that grow out-of-control. You don't need all those changes for the database to work perfectly, not even for recovery of old documents in sharepoint or whatever. The only reason you would like to keep the transaction logs is because you want to be able to do a point-of-time restore of your database to an earlier moment (e.g. before a database corruption took). In that case, good database backup and recovery strategy is very important and must be in place, otherwise the transaction logs are useless anyway. Therefore, if your databases grow out-of-control, change from full recovery model to simple recovery model and your problems will disappear after shrinking the transaction log. Once you understand the full recovery model and have a backup plan that includes making transaction log backups in place, you can turn full-recovery model back on and your transaction log will not grow out-of-control, because with a decent backup plan, the transaction log is stored on the backup medium and only the most recent portion is left on the server.

And once they start getting that big, a crash can be a HUGE problem! Be sure to do your backups. Mine crashed about six months ago and I had to contract an outside service (datarecoveryclinic.com) to help. I was able to get everything back, but it was an intense day or two.