how to fix this error?

• water490

  Ten Centuries

  Points: 1026

  More actions

  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

• ScottPletcher

  SSC Guru

  Points: 101180

  More actions

  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".

• water490

  Ten Centuries

  Points: 1026

  More actions

  January 2, 2025 at 9:13 pm

  #4509128

  Thank you so much!!