Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to execute some T-sql code, when a database starts up.

the objective is to resume a session of mirroring in case the session is "suspended" when the database comes "back to life".

So I've done this code:

begin try
    Declare @state_desc nvarchar(60)
    SELECT @state_desc = mirroring_state_desc 
    FROM SYS.database_mirroring 
    WHERE database_id = DB_ID('MyDataBase')
    if @state_desc = 'SUSPENDED'
    begin
        ALTER DATABASE [MyDataBase] SET PARTNER RESUME
    end
end try
begin catch
end catch

but how can I make it when the sql server starts up?

I used this with no success:

create procedure dbm_startup_resume
                                as
                                begin
begin try
    Declare @state_desc nvarchar(60)
    WAITFOR DELAY '00:00:10'
    SELECT @state_desc = mirroring_state_desc 
    FROM SYS.database_mirroring 
    WHERE database_id = DB_ID('MinhaBaseDeDados')
    if @state_desc = 'SUSPENDED'
    begin
      ALTER DATABASE [MinhaBaseDeDados] SET PARTNER RESUME
    end
  end try
  begin catch
  end catch

exec sp_procoption @ProcName = dbm_startup_resume, 
    @OptionName = startup, @OptionValue = 'on'

end
share|improve this question
    
Is a startup stored procedure what you need? Automatic Execution of Stored Procedures –  Martin Smith Jan 23 '12 at 14:42
    
perhaps...i've done it, but is not working...maybe is executing too soon, and my database is not in 'Suspended' state, can I do some sort of sleep/wait ? –  RagnaRock Jan 23 '12 at 15:27

1 Answer 1

[Updated]

  1. Create SQL Agent job for what you need.
  2. Use Server-scoped DDL trigger for that

Approximately like this:

Create TRIGGER ddl_trig_AlterDatabes
ON ALL SERVER 
FOR ALTER_DATABASE 
AS 
    -- your code here
    -- use EventData() to check database name if needed.. or other info..
    Exec msdb..sp_start_job @job_name = 'job_name' 
share|improve this answer
    
i got "The ALTER DATABASE statement is not allowed within a trigger" error –  RagnaRock Jan 23 '12 at 15:57
    
So make SQL Agent job and call it :). –  Jānis Jan 23 '12 at 16:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.