tsql to get data files growth type in SQL 2000

Question

Hi,

I want to know the growth type (%age or MB) for database files on SQL server 2000. I Used sys.database_files files on SQl 2005 to get this information. I tried using sysfiles on SQl 2000 for this, but it wasn't good enough.

Can anyone help please?

Regards

Manjot

Answer 1

You can query the sysfiles system view:

SELECT * FROM sysfiles

It will give you quite a few bits for each file including its current size, its maximum size, and the growth (plus a flag whether that growth is a fixed number of pages, or a percentage).

See the MSDN documentation for the details on what the columns are and what they mean.

Answer 2

This query should help:

SELECT
  name,
  size,
  growth,
  status,
  size * 8 AS size_in_kb,
  size * 8 / 1024. AS size_in_mb,
  CASE WHEN status & 0x100000 > 0
       THEN growth 
       ELSE NULL END AS growth_in_percent,
  CASE WHEN status & 0x100000 = 0 
       THEN growth * 8 / 1024. END AS growth_in_mb  
FROM sysfiles