Post reply

how to fix this error?

  • January 2, 2025 at 9:02 pm

    Go to Answer

    #4509115

    hi

    I am creating a stored procedure to reduce log size when needed.  I want to explicitly state which db to use but I am getting an error.  How can I fix my sp so it knows which db to use?

    Code:

    ALTER procedure [dbo].[ToolReduceLogFileSize]

    as

    USE myDB

    CHECKPOINT

    DBCC SHRINKFILE (2,1024)

    Error:

    Msg 154, Level 15, State 1, Procedure ToolReduceLogFileSize, Line 5 [Batch Start Line 7]
    a USE database statement is not allowed in a procedure, function or trigger.

    Completion time: 2025-01-02T12:59:29.6092028-08:00

    Thank you

  • January 2, 2025 at 9:06 pm

    Answer

    #4509121 

    ALTER procedure db.ToolReduceLogFileSize
        @db_name nvarchar(128),
        @size_mb int = 1024
    AS
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);
    SET @sql = 'USE [' + @db_name + ']; CHECKPOINT; ' +
        'DBCC SHRINKFILE (2, ' + CAST(@size_mb AS varchar(10)) + ');'
    EXEC(@sql);
    /*end of proc*/GO

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 2, 2025 at 9:13 pm

    #4509128

    Thank you so much!!

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

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