[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

SQL growth measurement

If i do the following:
SELECT
A.NAME, B.*, C.SIZE, C.GROWTH, C.NAME, C.FILENAME, C.MAXSIZE FROM MASTER.DBO.SYSDATABASES  A
INNER JOIN SYSFILES B
ON B.FILEID = 1
INNER JOIN MASTER.dbo.sysaltfiles C
ON C.DBID = A.DBID AND C.FILEID = 1
WHERE A.NAME = DB_NAME()

In the growth how would i determine if it is a % or a fixed MG amount
I get 10 and 6400 coming out where i know that 10% and a 6400 mg value
0
TRACEYMARY
Asked:
TRACEYMARY
  • 4
2 Solutions
 
cpunate9Commented:
Add the following column:

(c.status & power(2,20))/power(2,20) as GrowthInPercent

1 = Percent based growth
0 = Absolute growth
0
 
SireesCommented:
Check this link

http://www.sqlservercentral.com/columnists/sMaganti/arrivingatdatabasegrowthfactorinsqlserver2000_printversion.asp


SQL Server Books Online specifies that if the status entry for a particular database file is 0x100000, the growth column entry in SYSALTFILES for that particular database file should be perceived as a percentage. If not, then the corresponding entry in the growth column should be read as number of pages. The same logic holds true for the system table, SYSFILES.

0
 
TRACEYMARYAuthor Commented:
i read that in the on line book but did not know what to do with it with regards to the 0x000000

I have the following:   (They have same Logical File Names) but Location different.

DEVUFCP  (M40BARE_Data)
    Growth   12800      Status 0         GrowthInPercent 0     (but in EM actually  100mg for data)

Then i have
CONSULCP (M40BARE_Data)
   Growth 10   Status 32770             GrowthInPercent 0     (but in EM is  10%)

GrowthInPercent both coming out as 0

some in sysaltfiles status = 3

confused how to add the growth % mg to data size.





0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TRACEYMARYAuthor Commented:
I ran that script Sirees got errors on it.
0
 
TRACEYMARYAuthor Commented:
I got it running.............was not on master.
I play with that i think i got it.

Cheers
0
 
TRACEYMARYAuthor Commented:
Can you confirm something...If i used that calculation.

If i have
data sizemg   calculation         growth (%)
28500           222.65625       10

If i use that calculation
 (10 * 8192)/(1024 * 1024) MB = 222 MG

I was under the impression that i have
      28500 * 10 % = 2850 MG    

That a lot of difference.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now