Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
yeah those are not really tags. –  Aaron Bertrand May 20 at 20:32

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
On shrinking: Generally you're right, but there are legitimate situations. We have test databases on an application. We don't back up the test DBs (and don't want to) and the application has background transactions and never executes a CHECKPOINT. The vendor won't add it because production works flawlessly. Thus, with simple recovery the DB logs grow uncontrollably. We have a task which manually executes a CHECKPOINT and shrinks logs to 2 GB. It's rare for them to get that large (maybe once a quarter) so usually the task is just CHECKPOINTing to flush the logs, but it does happen. –  Bacon Bits May 20 at 14:25
    
Marked as answer for providing background information, example code and additionally noting that manually shrinking the transaction log file is usually not "best practise". –  user1952162 May 20 at 20:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.