Conditionally execute SHRINKFILE "only" if the file / DB exists

Question

Conditionally execute SHRINKFILE "only" if the file / DB exists

Express12

SSCoach

Points: 18514
More actions
February 10, 2017 at 4:38 am

#318602

QUES: Would anyone happen to have a solution for this check for existence & SHRINKFILE requirement? thanks in advance!
I need to check for existence of a DB before I attempt to exec SHRINKFILE. (we have over 100 SQL Servers and hundreds of DB's -- and I run SHRINKFILES on various servers which I realize is not a best practice)
The statement I'm using is illegal:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName')
BEGIN TRAN MySQLServer_DBName
use [MySQLServer_DBName];
dbcc shrinkfile(2,25);
COMMIT TRAN MySQLServer_DBName
GO
I receive ERROR:
Msg 8920, Level 16, State 2, Line 4
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.
Now, If I use this t-sql:
use [MySQLServer_DBName];
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName')
dbcc shrinkfile(2,25);
GO
I receive this error whenever the DB does NOT exist:
Msg 911, Level 16, State 1, Line 11
Database 'OrderVerificationAudit' does not exist. Make sure that the name is entered correctly.
BT

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

That's because the parser parses the whole script, not taking into account any control flow logic. You'd need to make it dynamic, something like this:

SELECT 'USE ' + name + '; DBCC SHRINKFILE (2,25); ' + char(10)
FROM sys.databases
FOR XML PATH ('');

and then execute the result. But you're right - this certainly isn't a best practice. Why are you doing it - perhaps we can suggest an alternative?

John

Express12 SSCoach Points: 18514 More actions · Answer 2

John - thanks for this solution. I'm having trouble EXECUTING the output from the SELECT USE statement you've provided. I tried declaring an nvarchar(4000) variable and populating w/ the SELECT output but receiving an error.

DECLARE @SHRINK_DBS NVARCHAR(4000)
SELECT @SHRINK_DBS = 'SELECT USE ' + name + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH ('');
select @SHRINK_DBS as MyCommand -- <<<<<<< to view the contents of my variable

Msg 6819, Level 16, State 3, Line 2
The FOR XML clause is not allowed in a ASSIGNMENT statement.

any assistance is greatly appreciated.. thanks again..

BT

Lowell SSC Guru Points: 323518 More actions · Answer 3

I'm assuming you know that shrinking is a bad thing, etc etc.

your command is almost right, just need to tweak it if you want to assign it to a variable.

DECLARE @SHRINK_DBS NVARCHAR(4000) SELECT @SHRINK_DBS = CONVERT(nvarchar(max),( SELECT 'USE ' + quotename(name) + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH (''), TYPE )) select @SHRINK_DBS as MyCommand -- <<<<<<< to view the contents of my variable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Express12 - Friday, February 10, 2017 4:38 AM
The statement I'm using is illegal:
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName')
BEGIN TRAN MySQLServer_DBName
use [MySQLServer_DBName];
dbcc shrinkfile(2,25);
COMMIT TRAN MySQLServer_DBName
GO
I receive ERROR:
Msg 8920, Level 16, State 2, Line 4
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.

Yes, that's because shrinkfile cannot be used inside a user transaction

Either of these would work

use [MySQLServer_DBName]; IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName') dbcc shrinkfile(2,25); GO

use [MySQLServer_DBName]; IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName') BEGIN dbcc shrinkfile(2,25); END GO

To check if the db exists or not you could use

use [MySQLServer_DBName];

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MySQLServer_DBName')

begin

dbcc shrinkfile(2,5);

end

ELSE print 'db doesn''t exist'

GO

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Express12 SSCoach Points: 18514 More actions · Answer 5

John and Lowell - thx a million.. Your combined solution worked perfectly:

DECLARE @SHRINK_DBS NVARCHAR(4000)
SELECT @SHRINK_DBS = CONVERT(nvarchar(max),(SELECT 'USE ' + quotename(name) + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH (''), TYPE))
-- select @SHRINK_DBS as MyCommand -- <<<<<<< to view the contents of my variable
EXEC (@SHRINK_DBS)

I just need to be careful when running this on any SQL Server where the ordinal position of the LOG file is NOT # 2 !! as per the DBCC SHRINKFILE (2,25)

BT

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

You're welcome. Let me know if you find a database where file_id 2 isn't a log file - I couldn't!

You never answered my question about why you're doing this - it really isn't a good idea. Maybe we can either talk you out of it or find a better way for you?

John

Express12 SSCoach Points: 18514 More actions · Answer 7

slight tweak to the previous script which had a typo causing an error.. this one works:

DECLARE @SHRINK_DBS NVARCHAR(4000)
SELECT @SHRINK_DBS = CONVERT(nvarchar(max),(SELECT 'USE ' + quotename(name) + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH (''), TYPE))
EXEC (@SHRINK_DBS)

BT

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Express12 - Friday, February 10, 2017 8:26 AM
John and Lowell - thx a million.. Your combined solution worked perfectly:
DECLARE @SHRINK_DBS NVARCHAR(4000)
SELECT @SHRINK_DBS = CONVERT(nvarchar(max),(SELECT 'USE ' + quotename(name) + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH (''), TYPE))
-- select @SHRINK_DBS as MyCommand -- <<<<<<< to view the contents of my variable
EXEC (@SHRINK_DBS)
I just need to be careful when running this on any SQL Server where the ordinal position of the LOG file is NOT # 2 !! as per the DBCC SHRINKFILE (2,25)

in every database there will be a primary file with id of 1 and a primary log file with id of 2.
neither of these can be removed.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

Express12 - Friday, February 10, 2017 8:26 AM
John and Lowell - thx a million.. Your combined solution worked perfectly:
DECLARE @SHRINK_DBS NVARCHAR(4000)
SELECT @SHRINK_DBS = CONVERT(nvarchar(max),(SELECT 'USE ' + quotename(name) + '; DBCC SHRINKFILE (2,25); ' + char(10) FROM sys.databases FOR XML PATH (''), TYPE))
-- select @SHRINK_DBS as MyCommand -- <<<<<<< to view the contents of my variable
EXEC (@SHRINK_DBS)
I just need to be careful when running this on any SQL Server where the ordinal position of the LOG file is NOT # 2 !! as per the DBCC SHRINKFILE (2,25)

I'll stress that this is a horrible idea on many fronts, not the least of which is the massive logical and physical fragmentation that this causes. It also hides and perpetuates problems instead of fixing them so that shrinking isn't actually ever necessary. Yes, I agree that there are certain circumstances where shrinking a file makes sense but they're generally rare and should be handled as a one-off problem rather than a generically and frequently applied "solution".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

Express12 - Friday, February 10, 2017 4:38 AM
I receive this error whenever the DB does NOT exist:
Msg 911, Level 16, State 1, Line 11
Database 'OrderVerificationAudit' does not exist. Make sure that the name is entered correctly.

Yes but that error is coming from the USE [databasename] not the code that follows it.
How can you set a database focus when it doesnt actually exist. 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉