SP_MSforeachdb different results

Question

Post reply

SP_MSforeachdb different results

PHXHoward

SSCrazy Eights

Points: 8389
More actions
June 8, 2010 at 3:10 pm

#233472

Hi everyone, I've taken the SQL found here to query autogrow status: http://beyondrelational.com/blogs/jacob/archive/2009/06/04/how-to-query-the-auto-growth-properties-of-a-sql-server-database-using-tsql.aspx
and combined it with SP_MSforeachdb like this:
DECLARE @command varchar(1000)
SELECT @command = 'USE [?] IF ''[?]'' NOT IN (''[master]'', ''[model]'', ''[msdb]'', ''[tempdb]'')
SELECT name as [?],
CASE
WHEN is_percent_growth = 0
THEN ''By '' + LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB, ''
ELSE
''By '' + CAST(growth AS VARCHAR) + '' percent, ''
END +
CASE
WHEN max_size = -1 THEN ''unrestricted growth''
ELSE ''restricted growth to '' +
LTRIM(STR(max_size * 8.0 / 1024,10,1)) + '' MB''
END AS Autogrow
FROM sys.database_files'
EXEC sp_MSforeachdb @command
Each time I run the query on the same instance, I get a different number of output rows. Certain seemingly random databases are excluded from the results. Any ideas why that might be?

Viewing 6 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

What happens if you try this?

DECLARE @command varchar(1000)

SELECT @command = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'', ''[msdb]'', ''[tempdb]'')

SELECT name as [?],

CASE

WHEN is_percent_growth = 0

THEN ''By '' + LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB, ''

ELSE

''By '' + CAST(growth AS VARCHAR) + '' percent, ''

END +

CASE

WHEN max_size = -1 THEN ''unrestricted growth''

ELSE ''restricted growth to '' +

LTRIM(STR(max_size * 8.0 / 1024,10,1)) + '' MB''

END AS Autogrow

FROM [?].sys.database_files'

EXEC sp_MSforeachdb @command

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

no need for a parameter......

just run:

EXEC sp_MSforeachdb 'USE [?] IF ''[?]'' NOT IN (''[master]'', ''[model]'', ''[msdb]'', ''[tempdb]'')

SELECT name as [?],

CASE

WHEN is_percent_growth = 0

THEN ''By '' + LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB, ''

ELSE

''By '' + CAST(growth AS VARCHAR) + '' percent, ''

END +

CASE

WHEN max_size = -1 THEN ''unrestricted growth''

ELSE ''restricted growth to '' +

LTRIM(STR(max_size * 8.0 / 1024,10,1)) + '' MB''

END AS Autogrow

FROM sys.database_files'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 3

Hi WayneS,

Thanks for responding. The changes that you suggested also worked but as before, I get different results each time I run this query. The first time, 68 rows were returned then running it again, 38 rows were returned.

WayneS (6/8/2010)
What happens if you try this?

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

Hi Henrico, thanks for responding.

It does work without the variable but I still get the same issue. 68 rows returned sometimes and 38 rows returned other times.

Henrico Bekker (6/8/2010)
no need for a parameter......
just run:

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 5

I've compared the output of the querys and for some reason, sometimes the query just gives up part way through. If I keep executing the query, it will eventually give me a full output.