SQL Server, Shrink Database Maintenance Task Failing

LevAstra 1 Reputation point
2021-08-19T18:15:35.03+00:00

Hello.

I am experiencing problems with the script below. The Error message reads:

"Executing the query "Declare @SQL3 VarChar(500)
SELECT @SQL3 = 'USE ' ..." failed with the following error: "Must declare the scalar variable "@db1 ".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

I would very much appreciate help and assistance to get this running. Thank you so much.

DECLARE @db1 varchar(20)

Declare @SQL3 VarChar(500)
SELECT @SQL3 = 'USE ' + @db1
Exec (@SQL3)
Go
Exec sys.sp_cdc_disable_db;
Go
Declare @SQL4 VarChar(500)
SELECT @SQL4 = 'DBCC SHRINKDATABASE(' + @db1 + ', 5, TRUNCATEONLY)'
Exec (@SQL4)
GO

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points
    2021-08-19T21:31:12.613+00:00

    There are two fundamental errors in your post:

    1) You don't include the error message that you get.
    2) You do not have a PRINT of the generated SQL.

    There are also a few more flaws in your script:

    1) The variable declared @db1 is not declared. (You declare it on top, but you have multiple batches, and a variable only lives for a batch.)
    2) Your @SQL4 variable is way too short, and could be truncated if @db1 would have a longer value. Always use nvarchar(MAX) for variables you use for dynamic SQL.
    3) Your @SQL3 is fairly meaningless. The effect of USE is reverted when you exit the scope the USE was executed in. And you dynamic SQL is a scope of its own. (Think of it as a anonymous stored procedure.)


  2. Seeya Xi-MSFT 16,451 Reputation points
    2021-08-20T02:37:12.283+00:00

    Hi @AdamHudak-2964,

    Is there any error message?
    Agree with Erland's suggestions for your script. After you modify it, execute it again. If there is any error message, please post it.

    Best regards,
    Seeya


  3. Jeffrey Williams 1,891 Reputation points
    2021-08-22T16:11:15.423+00:00

    Regardless of the great advice you have been given - it is actually a good thing this process is failing. There are very few good reasons to be shrinking a database and all files in that database after it has been restored. The TRUNCATEONLY option just releases available space at the end of the file to the OS - and shrinks the transaction log.

    Because this is a restore - the transaction log will be empty and will then be shrunk to its minimum size or the target size specified - whichever is larger. As soon as anything happens in that database the transaction log needs to grow - and depending on the defined growth size could be a performance issue. As soon as you run an index rebuild process or any process that adds data on that newly restored database - the data file will have to grow to accommodate the space requirements needed for that operation.

    In other words - any usage of the restored database is going to regrow the files, undoing the shrink database operation.


  4. Seeya Xi-MSFT 16,451 Reputation points
    2021-08-23T07:21:26.533+00:00

    Hi @AdamHudak-2964 ,

    Must declare the scalar variable "@db1

    you are using a variable inside the dynamic SQL that is declared outside the dynamic SQL. For this you will need to use sp_executesql.
    Change it as:
    Exec(@db1 )
    exec sp_executesql @db1
    If this still reports an error, change its varchar(20) to a larger size.

    possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    See this thread: https://stackoverflow.com/questions/8059980/ssis-execute-sql-task-sql-command-issue

    Finally, I want to say that this is just my answer based on your error message. As Erland said, we don't know what Adam aim to use this script for. It may not be same purpose as the script was originally written for.

    Best regards,
    Seeya


    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments