Solved

# SQL growth measurement

Posted on 2006-05-04
525 Views
Last Modified: 2013-11-15
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
Question by:TRACEYMARY
6 Comments

LVL 2

Assisted Solution

Add the following column:

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

1 = Percent based growth
0 = Absolute growth
0

LVL 20

Accepted Solution

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

LVL 7

Author Comment

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

LVL 7

Author Comment

I ran that script Sirees got errors on it.
0

LVL 7

Author Comment

I got it running.............was not on master.
I play with that i think i got it.

Cheers
0

LVL 7

Author Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The viewer will learn how to back up with the free utility from runtime software, DriveImageXML using Windows 7. Download DriveImageXML from www.runtime.org: Open folder where it was saved: Start installation by double clicking the install scrip…

#### 779 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!