question

LevAstra avatar image
0 Votes"
LevAstra asked LevAstra edited

SQL Server, Shrink Database Maintenance Task Failing

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AdamHudak-2964 ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your response, @ErlandSommarskog.

Please forgive me, this form of SQL is relatively new to me, and the script is from a prior colleague that is no longer accessible. I have updated the above to include the full script (with the exception of the noted "Sample" and "location"). If there are aspects missing, I would greatly appreciate your help in filling in the gaps.

Thank you again, and best regards.

0 Votes 0 ·

Despite the update, I think all points in my previous post still applies.

If you have not worked with dynamic SQL before, I have an article about dynamic SQL on my web site: https://www.sommarskog.se/dynamic_sql.html.

Sorry, I don't have the time to clean up script now - I'm off to bed right now!

0 Votes 0 ·

Thanks again for the response, @ErlandSommarskog. I appreciate your insight and resources. To your prior comment regarding the error message. This is the error received:

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

Again, your help and feedback is enormously appreciated. Thank you.

0 Votes 0 ·

So if you read my original post, you will find out that I pointed out this particular error.

And now is the question: what do you expect us to do? You have a non-functioning script you have found in the drawer of someone who has left the company. Do you expect us to correct that script? But we don't know what the script is supposed to do. Well, we can guess, but since it very apparently never have been used for real, there are fair chances that the script still will do what it should. And we can't know what is should do, because we don't know anything about your system.

So the first thing you need to do is to figure out what the requirements actually are. Then you can start from the beginning. There is little point to from something that is both dusty and half-baked.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered LevAstra commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, @SeeyaXi-msft. Thank you so much for your reply.
Yes, I was just able to obtain the error:

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.

Thank you again.

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

I made that observation too, but I also noticed that the recovery is set to simple. So I assume that this is intended to be a restore to a dev/test environment. I have scripts on the same theme. (But I use DBCC SHRINKFILE on the log flle; little point in SHRINKDATABASE.)

Then again, 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.

0 Votes 0 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.