Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: SQL script to set a DB to Simple then run a reorg and reindex then set DB back 2 full

    Does anyone know what the commands would be? I am trying to create a job that puts a DB in simple mode then launches a reorg and re-index, then sets it back to full when it is complete. This way I can eliminate large transaction logs being created.

    Any help would be great!
    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    --- change all db simple recovery-------
    use master
    go
     
    CREATE PROC Alldb_reindexAllTables_sp
    as
     
    declare @sql  nvarchar(4000)
    set @sql=''
     select @sql = @sql+'exec  sp_MSforeachdb @command1=''alter database '+name+'  set RECOVERY SIMPLE''' +char(13)
    from master..sysdatabases where name<>'tempdb'
    --print @sql
    exec (@sql)
     
    --- reindexing all tables except tempdb---------
     
    set @sql = ''
     select @sql = @sql + 'exec ' + name + '..sp_MSforeachtable @command1=''dbcc dbreindex (''''*'''') WITH NO_INFOMSGS '', @replacechar=''*''' + char(13)
    from master..sysdatabases where name <> 'tempdb'
    --print @sql
    exec (@sql)
     
    ----change to full recovery  model--------
    set @sql=''
     select @sql = @sql+'exec  sp_MSforeachdb @command1=''alter database '+name+'  set RECOVERY FULL''' +char(13)
    from master..sysdatabases where name<>'tempdb'
    --print @sql
    exec (@sql)
     
     
    --exec master..Alldb_reindexAllTables_sp
    Last edited by mallier; 01-18-06 at 12:04.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •