February 20, 2012 at 5:53 pm
Hi,
I had tried to create the following statements to capture the autogrowth of all the databases on the server.
CREATE TABLE #HoldforEachDB
(
[Server] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LogicalName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Filename] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AutoGrowth] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #HoldforEachDB
EXEC sp_MSforeachdb 'SELECT CONVERT(char(100),
SERVERPROPERTY(''Servername'')) AS Server,
''?'' as DatabaseName,
[?]..sysfiles.name,
[?]..sysfiles.filename,
Autogrowth = ''Autogrowth: ''
+
CASE
WHEN ([?]..status & 0x100000 = 0 AND CEILING(([?]..growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR [?]..growth = 0 THEN ''None''
WHEN [?]..status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING(([?]..growth * 8192.0) / (1024.0 * 1024.0))) + '' MB''
ELSE ''By '' + CONVERT(VARCHAR,[?]..growth) + '' percent''
END
+
CASE
WHEN ([?]..status & 0x100000 = 0 AND CEILING(([?]..growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR [?]..growth = 0 THEN ''''
WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth''
ELSE '', restricted growth to '' + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + '' MB''
END
FROM [?]..sysfiles s'
select * from #HoldforEachDB
DROP TABLE #HoldforEachDB ;
It is throwing the below error message
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "master..sysfiles.name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "master..sysfiles.filename" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "master..status" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "master..status" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "master..status" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "master..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "tempdb..sysfiles.name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "tempdb..sysfiles.filename" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "tempdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "tempdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "tempdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "tempdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "model..sysfiles.name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "model..sysfiles.filename" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "model..status" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "model..status" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "model..status" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "model..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "msdb..sysfiles.name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "msdb..sysfiles.filename" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "msdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "msdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 9
The multi-part identifier "msdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "msdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "msdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 11
The multi-part identifier "msdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "msdb..status" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "msdb..growth" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "msdb..growth" could not be bound.
I am running sql server 2008 r2 enterprise edition(RTM) 64 bit on Windows server 2008 r2 enterprise edition sp1 64 bit edition.
Can anyone help me in fixing the query please.
Thank You,
February 20, 2012 at 7:46 pm
Well... using your query, you just had a couple of syntax errors.
IF OBJECT_ID('tempdb..#HoldforEachDB') is not null BEGIN DROP TABLE #HoldforEachDB END
CREATE TABLE #HoldforEachDB
(
[Server] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DatabaseName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LogicalName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Filename] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AutoGrowth] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
INSERT INTO #HoldforEachDB
EXEC sp_MSforeachdb 'SELECT CONVERT(char(100),
SERVERPROPERTY(''Servername'')) AS Server,
''?'' as DatabaseName,
s.name,
s.filename,
Autogrowth = ''Autogrowth: ''
+
CASE
WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN ''None''
WHEN s.status & 0x100000 = 0 THEN ''By '' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0) / (1024.0 * 1024.0))) + '' MB''
ELSE ''By '' + CONVERT(VARCHAR,s.growth) + '' percent''
END
+
CASE
WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR s.growth = 0 THEN ''''
WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth''
ELSE '', restricted growth to '' + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + '' MB''
END
FROM [?]..sysfiles s'
select * from #HoldforEachDB;
Alternatively, I believe you can pull all of the information from sys.master_files like the following:
I'm running this on 2K8 R2. Give it a shot and let me know what you think.
select @@servername as 'Server',
DB_NAME(m.database_id) as 'DatabaseName',
name as 'LogicalName',
physical_name as 'Filename',
'Autogrowth: By '+ case when is_percent_growth = 1 then cast(growth as varchar(25))+' percent, '
else cast(((growth*8)/1024) as varchar(25))+' MB, ' end
+ case when max_size <> -1 then 'restricted growth to '+cast(cast(((max_size *8.0)/1024) as bigint) as varchar(25))+' MB' else 'unrestricted growth'
end as 'AutoGrowth'
from sys.master_files m
Best of luck.
~Tim
February 22, 2012 at 3:44 pm
Hi,
Thank you for correcting my query.
My modified query is working.
I will check your other query also.
Thank You
February 22, 2012 at 4:07 pm
Hi,
Your second query is working for me.
Thank You very much for the help.
Thank You
February 22, 2012 at 8:54 pm
Glad to hear they work for you. Nice! Also, you may wish to be careful with your autogrowth settings to avoid unnecessary VLFs being created. You'll generally want to grow your tranlogs ahead of time, off hours if possible, to avoid the autogrowth altogether.
Kimberly Tripp has a great blog post on these:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
hope this helps
Best of Luck!
~Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply