SQL Server USE ignores IF block

Question

I want to shrink the transaction log file of several databases using a maintenance plan. Those databases are mirrored, therefore the shrink task cannot be executed on the MIRROR databases, but the maintenance plan has to be configured on both servers. I'm using SQL Server 2012 Standard (SP1) on our development system but the code needs to work on 2008 R2 too.

I tried to use the following script, which handles one database called DR, within an Execute T-SQL Statement Task:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    PRINT 'test';
    USE DR;
    DBCC SHRINKFILE (DR_log, 2048);
END
GO

This works fine on the PRINCIPLE, however it results in the following error message on the MIRROR:

Msg 954, Level 14, State 1, Line 4
The database "DR" cannot be opened. It is acting as a mirror database.

It seems the IF block is ignored by the USE command. The Print is not executed if USE is commented out. The Shrink Database maintenance task which is available in SSMS provides an option to ignore databases where the state is not online. However, the log file does not reveal how this is accomplished. This Task cannot be used because it also shrinks data files, not only log files.

How does one make sure the USE is only executed if the database is in Online state? Another option I can think of would be the use of dynamic SQL, which I'd like to avoid.

Note: This task will not be executed frequently but rather subsequent to an exceptionally big transaction which caused the log file to grow beyond it's usual size.

Solution: we used dynamic sql to workaround this issue, see also the answer provided by @Aaron Bertrand

score 5 · Accepted Answer · edited Apr 13 '17 at 12:42

This is happening at parse time, and there is little you can do to change the actual behavior. SQL Server does some checking of the statements within the batch, and for all intents and purposes, pretends that your conditional logic does not exist.

For the same reasons, you can't use conditional logic to determine which of two possible #temp tables to create (the most common symptom for this problem). Even though looking at this as a human, you see that only one version of #t could ever be created, SQL Server doesn't see it that way. It will report the error message whether you choose Parse (Ctrl+F5) or Execute (just F5):

IF (1=1)
BEGIN
  CREATE TABLE #t(a INT);
END
ELSE
BEGIN
  CREATE TABLE #t(b INT);
END

Result in either case:

Msg 2714, Level 16, State 1, Line 7
There is already an object named '#t' in the database.

The error message makes very little sense when all you've done is parse the batch; obviously #t can't exist yet, but it demonstrates that SQL Server is evaluating the statements in your batch, but ignoring those statements that deal with conditional logic.

To get around this problem, you can use dynamic SQL, which will not be parsed prior to execution:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    EXEC DR.sys.sp_executesql N'DBCC SHRINKFILE (DR_log, 2048);';
END

However, this seems really, really, really wrong to me. You are constantly shrinking your log file? Why? This is such a self-defeating process. Please read this post in its entirety:

Why Does the Transaction Log Keep Growing or Run Out of Space?

Then, size your log file appropriately - ONCE - and make sure you are using the right recovery model so that you're not constantly playing this grow-shrink-grow-shrink-grow-shrink cycle. If the log is too big for the mirror, get a bigger disk. Doesn't seem right to cripple the primary to compensate for the mirror's inadequacies.

SQL Server USE ignores IF block

1 Answers1