Dotnetpanel Forums

Hello,

I'm wondering do you set any restrictions to your users MS SQL databases log files?

Thanks

Yes, I do. And I would recommend everyone to do it as well. I've experienced several customers who have had sql log files that grew enourmesly much by some reason even if the database wasn't near as big in size, running SQL 2005. Don't know if it's a bug in 2k5 that doesn't appear in 2k or 2k8, though.

Not exactly a bug ... Wondering what is transaction type setup for your sql install and databases. Change the transactions for the database to Simple and also as suggested above schedule something to truncate or shrink logs daily or weekly.

Does simple transactions for databases can be set globally?

use master
go

CREATE  proc rp_switch_rec_model
(@MODE varchar(20))
as
Declare
  sdb_namecurs cursor for
  select name from master..sysdatabases where name <> 'TEMPDB';
open sdb_namecurs;
  declare @db_name sysname
fetch next from sdb_namecurs into @db_name;
While @@fetch_status = 0
  Begin
    exec('ALTER DATABASE ' + @db_name + ' SET RECOVERY ' + @MODE)
    --print 'ALTER DATABASE ' + @db_name + ' SET RECOVERY ' + @MODE
    fetch next from sdb_namecurs into @db_name
   End
Deallocate sdb_namecurs
GO

-- USAGE
exec rp_switch_rec_model 'SIMPLE'
-- OR
exec rp_switch_rec_model 'FULL'
-- OR
exec rp_switch_rec_model 'BULK_LOGGED'

No, just set the recovery model for the "model" database to simple: http://technet.microsoft.com/en-us/library/ms186388.aspx
It will not however prevent users from changing the setting or restoring dbs that have the transaction mode set to full.