Post reply

Clear Message in Stored Procedure

  • July 6, 2008 at 5:32 am

    #129811

    I use this code to shrink a log. When I execute this (R2008 being the database) in my stored procedure the text message it creates must interfere with the rest of the procedure.

    After I execute it, is there a way I can clear the message it generates? Thanks.

    Backup LOG R2008 WITH NO_LOG

    DBCC SHRINKFILE ('R2008_log',1,TRUNCATEONLY)

  • July 6, 2008 at 6:27 am

    #838586

    Ok, I have to ask. Why are you truncating and shrinking your log file?

    If you don't care about being able to restore to a point-in-time, set the database into simple recovery mode and you won't have to worry about the log at all. If you are concerned with been able to restore to a point-in-time, set up regular log backup to keep the size of the log under control.

    Either way, the log file should not be shrunk as it can lead to file-level fragmentation and lots and lots of little virtual log files. Set the size of the log to however big it needs to be depending on the frequency of changes in your DB (and log backups if you stay in full recovery) and leave it alone.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 6, 2008 at 9:20 pm

    #838655

    I figured it out, I need to add WITH NO_INFOMSGS.

    Backup LOG R2008 WITH NO_LOG

    DBCC SHRINKFILE ('R2008_log',1,TRUNCATEONLY) WITH NO_INFOMSGS

    BUT your point is a good one. I had an issue running SQL Server 2000 on XP ... I was on service pack 3 xp and still could not get the properties of the database without enterprise manager crashing.

    I reloaded service pack 4 for sql server ... and was able to get to the properties window. I set the database to the simple recovery model.

    Thanks

  • July 7, 2008 at 12:08 am

    #838686

    swtrans (7/6/2008)

    I reloaded service pack 4 for sql server ... and was able to get to the properties window. I set the database to the simple recovery model.

    Thanks

    ALTER DATABASE <DB Name> SET RECOVERY SIMPLE

    🙂 For next time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply