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