SQL Server Performance
  1. Jackson New Member

    HI,
    I have a stored procedure which was working untill day before yesterday but all of sudden it stopped working today. It doesn't give any error. Can somebody please, help me with this problem? What can be the reason?
    Thanks,
    Jackson
  2. preethi Member

    Can you use sp:Stmt Starting and sp:Stmt Completed events on Trace and filter for the said stored procedure?Also, check on SQL Server's error log for any possible errors.
    Is the stored procedure called from an applciation? Did you try running from Management Studio?

    I have seen this behaviour in early versions of SQL Server When there is a high resource utilization, it happend... Not heard of the same thing in SQL Server 2005.
  3. Jackson New Member

    Hi preethi,
    Thanks for quick reply. I will try that way.
    Jackson

  4. Madhivanan Moderator

    When you say all of sudden it stopped working today, what do you mean?
    Did you get any error?
  5. Madhivanan Moderator

    [quote user="Jackson"]
    HI,
    I have a stored procedure which was working untill day before yesterday but all of sudden it stopped working today. It doesn't give any error. Can somebody please, help me with this problem? What can be the reason?
    Thanks,
    Jackson
    [/quote]
    Can you post the procedure code?
  6. Jackson New Member

    Hi Madhivanan,
    Here is the procedure code.
    create procedure DB_DatabaseBackup
    @Path varchar(500) ,
    @DBName varchar(128),
    @BackupType Varchar(10)
    as
    declare @FileName varchar(4000)

    If @BackupType = 'FULL'
    BEGIN
    select @FileName = @Path + @DBName + '_Full_'
    + convert(varchar(8),getdate(),112) + '_'
    + replace(convert(varchar(8),getdate(),108),':','')
    + '.bak'
    backup database @DBName
    to disk = @FileName WITH init
    END

    If @BackupType = 'Diff'
    BEGIN
    select @FileName = @Path + @DBName + '_Diff_'
    + convert(varchar(8),getdate(),112) + '_'
    + replace(convert(varchar(8),getdate(),108),':','')
    + '.bak'

    backup database @DBName
    to disk = @FileName
    with differential, INIT
    END

    If @BackupType = 'Log'
    BEGIN
    select @FileName = @Path + @DBName + '_Log_'
    + convert(varchar(8),getdate(),112) + '_'
    + replace(convert(varchar(8),getdate(),108),':','')
    + '.bak'

    backup log @DBName
    to disk = @FileName WITH INIT
    END
    Thanks,
    JACKSON
  7. satya Moderator

    IF not any error are you getting any adverse performance issues with this SP?

Share This Page