SQL Server, Shrink Database Maintenance Task Failing

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.