autogrowth setting

Question

autogrowth setting

giri10488

Right there with Babe

Points: 729
More actions
July 1, 2015 at 8:49 am

#299711

plsss give me query or script to find autogrowth setting of all databases in instance

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

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

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

SELECT * FROM sys.master_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

giri10488 Right there with Babe Points: 729 More actions · Answer 2

In growth column it is showing different values like 128 for 1 mb,0 for none.can u pls give me script through which i will get exact information like by 1 mb,unrestricted growth

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

Look in Books Online (the SQL help documentation) on sys.master_files or sys.database_files for what the values mean.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here: https://msdn.microsoft.com/en-us/library/ms186782(v=sql.110).aspx

Almighty SSCrazy Points: 2001 More actions · Answer 5

select DB_NAME(mf.database_id) database_name

, mf.name logical_name

, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]

, CASE mf.is_percent_growth

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS [is_percent_growth]

, CASE mf.is_percent_growth

WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'

WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'

END AS [growth_in_increment_of]

, CASE mf.is_percent_growth

WHEN 1 THEN

CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)

WHEN 0 THEN

CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))

END AS [next_auto_growth_size_MB]

, CASE mf.max_size

WHEN 0 THEN 'No growth is allowed'

WHEN -1 THEN 'File will grow until the disk is full'

ELSE CONVERT(VARCHAR, mf.max_size)

END AS [max_size]

, physical_name

from sys.master_files mf

complete solution