sp_msforeachdb DBCC SHRINKFILE Query Problems

Question

sp_msforeachdb DBCC SHRINKFILE Query Problems

Pete-L

Ten Centuries

Points: 1280
More actions
August 4, 2010 at 4:18 am

#236147

I was wondering if anyone could help me with the following (im trying to automate a way of shrinking all my logfiles following a backup etc)
sp_MSforeachdb "use [?] DBCC SHRINKFILE (N'(select name from [?].dbo.sysfiles where fileid=2)' , 0, TRUNCATEONLY)"
Any help much appreciated.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

My first question is why are you shrinking at all? You should not be shrinking database files on a regular basis. Only after some unusual operation (that's grown the log file far beyond normal or cleared a large portion of the data file) should a once-off shrink be performed.

Please see:

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ (and everything it links to)

http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ (and everything it links to)

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

http://www.sqlservercentral.com/articles/64582/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pete-L Ten Centuries Points: 1280 More actions · Answer 2

I know this shouldnt be done on a regular basis. Basically we need Full recovery model but some basic admin wasnt being performed and 100s of Logs files are now huge. This is why im trying to get a dynamic script that will go through each DB and shrink the Log File as doing it on each DB is too time consuming. (I could list out the Logical Names for the Log Files and then put it into a script repeating for each DB, but wheres the fun in that :-D)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As long as it's a once-off....

You can do this with foreachDB, but it's far from the easiest way. The below script will generate all the shrink commands for you. That way you can check them before running.

select 'USE ' + DB_Name(database_id) + '

GO

DBCC SHRINKFILE(''' + name + ''')

GO

' from sys.master_files where type = 1 and database_id > 4

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pete-L Ten Centuries Points: 1280 More actions · Answer 4

Ok cheers (ill keep that as a last resort)

If I was to try and get the MSforeachdb working am I on the right track? The reason I ask is that I can reuse the code for performing other things that need to be done on all databases and I like a challenge 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Pete-L (8/4/2010)
If I was to try and get the MSforeachdb working am I on the right track?

No. Shrink file can't refer to a query, it needs a value. so you'd have to declare a variable, select the name of the log file into that variable then plug that into shrinkfile, all within the foreachDB.

btw... TRUNCATEONLY is not a valid option for log files. It only applies when shrinking data files.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pete-L Ten Centuries Points: 1280 More actions · Answer 6

Hmm strange, when I script out the GUI the command includes the TRUNCATEONLY

Just looking at the variable side of things now cheers 🙂

DECLARE @command1 varchar(100)

DECLARE @command2 varchar(100)

exec sp_MSforeachdb set @command1='select name from ?.dbo.sysfiles where fileid=2';@command2='DBCC SHRINKFILE (N'@command1', 0, TRUNCATEONLY)'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Pete-L (8/4/2010)
Hmm strange, when I script out the GUI the command includes the TRUNCATEONLY

Yes it does. Doesn't make it right. Check Books online

DECLARE @command1 varchar(100)
DECLARE @command2 varchar(100)
exec sp_MSforeachdb set @command1='select name from ?.dbo.sysfiles where fileid=2';@command2='DBCC SHRINKFILE (N'@command1', 0, TRUNCATEONLY)'

No, not like that.

The variable has to be declared within the sp_MSforeachDB. It has to be populated with the name of the log file. The variable passed to Shrinkfile much contain just the name of the file, not a query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Pete-L Ten Centuries Points: 1280 More actions · Answer 8

Finally getting somewhere now -

EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)'

Ludwig Guevara SSC Veteran Points: 225 More actions · Answer 9

Thank for script 😎

... and because we shouldn't shrink some system databases

EXEC sp_MSforeachdb '

DECLARE @ln varchar(100)

SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2 )

USE [?]

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''dbem'',''Reportserver'',''ReportserverTempDB'')

BEGIN

DBCC SHRINKFILE (@ln, 0)

PRINT ''?''

END'

Thanks

ScottPletcher SSC Guru Points: 101097 More actions · Answer 10

No need to shrink below a certain minimum size, even if you can. I've used 1GB below, but adjust it as needed for your environment.

EXEC sp_MSforeachdb '

IF ''?'' IN (''master'',''model'',''msdb'',''tempdb'')

OR ''?'' LIKE ''Reportserver%''

RETURN;

USE [?]

PRINT ''"?" database''s main log file is being shrunk.''

DBCC SHRINKFILE (2, 1024)

'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".