SQL Server - Need query for dbfile size - Asked By ananda murugesan on 09-Sep-09 07:55 AM

Hi friends,

I want collect the database file growth details,  format like as below

Filename,Filesize,FileGrowth,FileGrowthPercent

I tried the follwing query but it is not hope.

SELECT name as Filedbname,size,
FileGrowth = CASE WHEN status >= 0x100000
THEN convert(decimal(9,2),growth)*8
ELSE convert(decimal(9,2),growth)*size*8/100 END,
FileGrowthPercent = CASE WHEN status >= 0x100000
THEN convert(decimal(9,2),growth)/size*100
ELSE convert(decimal(9,2),growth) END
FROM master..sysaltfiles

Could anyone give me correct sloutions.

Thanks

re - Web Star replied to ananda murugesan on 09-Sep-09 08:00 AM

u can get every thing usein sys.database_files in sql server

select *from sys.database_files

Each database on the SQL Server contains information regarding the size of the database files, along with some other related information. In order for me to get to this information, I need a method to retrieve the data from the individual databases one at a time. I have two available options:

  • sp_spaceused: This system stored procedure will return the size statistics for the current database context in which it is running. It is very useful for returning ad hoc information regarding database or table sizes within the database; however, it is not very friendly for reporting purposes. It is possible to capture the information for each database through a script, but it would require the use of a user-defined cursor.
  • sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to for in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself. This is the method I will use for my code to capture database file size information.

The information I want to gather and store is available in the sys.database_files system view. This gives me the size of the database files, along with some other handy information such as the state of the database, the manner in which the files grow (size or percentage), and if it is read-only. I will need to capture this information for each database.

The script below creates a table named DatabaseFiles (if it does not already exist) based upon the structure of the system view sys.database_files; it also adds a new column to capture when the record was added to the table.

IF OBJECT_ID('DatabaseFiles') IS NULL
 BEGIN
     SELECT TOP 0 * INTO DatabaseFiles
     FROM sys.database_files    

     ALTER TABLE DatabaseFiles
     ADD CreationDate DATETIME DEFAULT(GETDATE())
 END

Now it is time to populate the DatabaseFiles table. This script uses the sp_msforeachdb stored procedure and passes a SQL script that inserts data from the sys.database_files view into the DatabaseFiles table that I created above. If you examine the script, you will notice that I am building in the database name for each database. This is subtle, and it’s accomplished by the [?] prefix to the sys.database_files view. This code is actually executed in each database on the instance, and the name of the database is used in place of the [?] marker. Information for each database is inserted into the DatabaseFiles table with one line of code, and it is a lot easier than writing a cursor to do the same. I also added a GETDATE() call to indicate when the records were inserted into the table.

Note: This example somewhat goes against two coding standards that I am typically strict about: using SELECT * and inserting into a table without a column list. I omitted them because the SQL string that I am building would have been a lot less desirable to view. If this was code that I put into a production environment, I would have made the necessary changes accordingly.

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'

To make sure that all of my data was captured correctly, I’ll look at what is in the table.

SELECT * FROM DatabaseFiles

Filegrowth - ananda murugesan replied to Web Star on 09-Sep-09 08:15 AM

hi AK,

As per your reply, it was working fine sql server 2005, so we are using sql server 2000.

i could tried system SP for sp_databases and sp_spaceused, but both are given only dbsize,

I want query like, dbanme,dbsize,filegrowth and filegrowth% in sql server 2000

Thanks

re - Web Star replied to ananda murugesan on 09-Sep-09 08:23 AM

if u wnat to do in sql server 2000

then look here all things given for sql server 2000 as well as 2005 in details, so u can get every thing what ever u wnat.

The database information mapping between SQL Server 2000 and 2005 is critical to ensure scripts are working properly when upgrading to SQL Server 2005.  Below outlines the common database related objects.

ID Information SQL Server 2000 SQL Server 2005
1 Database system table\view - Source for all databases on the SQL Server to include the name, owner, creation date, etc.
 
SELECT *
FROM master.dbo.sysdatabases
GO
SELECT *
FROM sys.databases;
GO
 
2 Database files system table\view - Source for the currently connected database's file names, size, location, type (database or log), etc.
 
SELECT *
FROM dbo.sysfiles
GO
SELECT *
FROM sys.database_files;
GO
3 Database files system table\view - Source for all database's file related information
 
SELECT *
FROM master.dbo.sysaltfiles
GO
 
SELECT *
FROM sys.master_files;
GO
4 IO statistics on database files - Returns the usage statistics on a per file basis -- Single database file
SELECT *
FROM :: fn_virtualfilestats(1, 1)
GO
-- All database files
SELECT *
FROM sys.dm_io_virtual_file_stats(NULL, NULL);
GO
 
5 Database meta data - Returns the pertinent database name, size and remarks
 
EXEC master.dbo.sp_databases
GO
EXEC master.dbo.sp_databases;
GO
6 Database meta data - Fairly complete set of pertinent database information that can return data for all databases or 1 database
 
-- All databases
EXEC master.dbo.sp_helpdb
GO

-- Single database
EXEC master.dbo.sp_helpdb 'Northwind'
GO
 

-- All databases
EXEC master.dbo.sp_helpdb;
GO

-- Single database
EXEC master.dbo.sp_helpdb 'AdventureWorks';
GO

 
7 Change database ownership - System stored procedure to change the database owner
 
EXEC sp_changedbowner sa
GO
 
EXEC sp_changedbowner sa;
GO
8 Database ID to name translation - System function that will provide the database name when passed the database ID from the database system table
 
-- Returns the master database
SELECT DB_NAME(1)
GO
-- Returns the master database
SELECT DB_NAME(1);
GO
9 Database name to ID translation - System function that will provide the database ID when passed the database name from the database system table
 
-- Returns 1
SELECT DB_ID('master')
GO
-- Returns 1
SELECT DB_ID('master');
GO
10 Database status - System function that will return the value for 1 of ~25 database specific values SELECT DATABASEPROPERTYEX('master', 'Status')
GO
 
SELECT DATABASEPROPERTYEX('master', 'Status');
GO

http://www.mssqltips.com/tip.asp?tip=1037


Filegrowth - mv ark replied to ananda murugesan on 09-Sep-09 01:32 PM
To get database name,db size,file growth and file growth % in Sql Server 2000, try the custom stored procedure provided here - http://vyaskn.tripod.com/track_sql_database_file_growth.htm