Actual file size
-
Hi to all,
Because we run and application with a strange behaviour I was asked to monitor the filegrowth (and schrinking) and report it on an interupt of 10 minutes (stored in a report table).
Unfortionately the tables "<database>.sysfiles" and "master.altfiles" contain the fixed sizes only if a datafile has set with the NO extent option (Vendors spec). In other cases it displays the actual size though.
my last attempt was to join the sysobjects - sysindexes - sysfilesgroups - sysfiles in combination with exec sp_MStablespace '<tablename>' but is was failing because I cound not find out what makes a table and what makes an index (using the sysindexes table).
This is the last attempt I made before hitting this forum;
--888888888888888888888888888888888888888888888888888888888888888
DECLARE @var01 varchar(200)
DECLARE @var02 varchar(200)
DECLARE @string01 VARCHAR(8000)
DECLARE c_een CURSOR FOR
select groupname
from sysfilegroups
create table #temp ( filename varchar(80), Groupname varchar(80), rows integer, Data_kb integer, Index_kb integer )
OPEN c_een
FETCH NEXT FROM c_een INTO @var01
WHILE @@FETCH_STATUS = 0
BEGIN
--retrieving all the table names union the index names and insert them into the #TEMP table
set @string01 =
'insert into #temp (filename, groupname)
select distinct object_name(id),'''+@var01+''' from sysindexes
where groupid = filegroup_id('''+@var01+''')
union
select distinct ''IND_''+name,'''+@var01+''' from sysindexes
where groupid = filegroup_id('''+@var01+''') and indid > 0'
print @string01
exec ( @string01)
FETCH NEXT FROM c_een INTO @var01
END
CLOSE c_een
DEALLOCATE c_een
--888888888888888888888888888888888888888888888888888888888888888888
DECLARE @var03 varchar(200)
DECLARE @string02 VARCHAR(8000)
DECLARE @string02a VARCHAR(8000)
declare c_twee cursor for
select filename
from #temp
create table #temp2 ( rows2 integer, data_kb2 integer, index_kb2 integer)
open c_twee
fetch next from c_twee into @var03
while @@fetch_status = 0
begin
--Retieving the actual sizes using the sp_MStablespace (undocum proc)
--On non tablenames this SP will not work but this is not the real issue here. . . .
set @string02 =
'insert into #temp2 (rows2,data_kb2,index_kb2) exec sp_MStablespace '''+@var03+''''
print @string02
exec ( @string02)
if @var03 not like 'IND_%' -- THIS IS A TABLE
begin
set @string02a =
'update #temp set rows = (select isnull(rows2,0) from #temp2) where filename = '''+@var03+'''
update #temp set data_kb = (select isnull(data_kb2,0) from #temp2) where filename = '''+@var03+''''
end
if @var03 like 'IND_%'
begin
set @string02a =
'update #temp set rows = (select isnull(rows2,0) from #temp2) where filename = '''+@var03+'''
update #temp set index_kb = (select isnull(index_kb2,0) from #temp2) where filename = '''+@var03+''''
end
exec (@string02a)
delete from #temp2
fetch next from c_twee into @var03
end
close c_twee
deallocate c_twee
--888888888888888888888888888888888888888888888888888888888888888888
select * from #temp
select distinct(groupname), sum(rows) as table_rows, (sum(data_kb)/1024) as Data_MB, (sum(index_kb)/1024) as Index_MB from #temp group by groupname
drop table #temp
drop table #temp2
--888888888888888888888888888888888888888888888888888888888888888888
Can someone please tell me what I've been missing and point me into the right direction.
Thanks in advance
GKramer
The Netherlands
-
Guus,
I use the following SP which I found on a forum some time ago. It relies on the data in the msdb..backupfile table, which is updated each time a database is backed up. There is a column in this table called 'backed_up_page_count', which records the actual number of pages written to the backupfile, rather than the actual number of pages reserved for the database as a whole. This is the same as the "used" size of a database.
The formatting of the code here will be pants, unfortunately, but I'm sure you'll be able to smarten it up
Phil
CREATE procedure sp_track_db_size_used
(@dbnameParam sysname = null)
as
/***********************************************************************************************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: To calulate the file growth percentages for a given database and to show you the rate at which
your databases are growing, so that you can plan ahead for your future storage needs.
Written by: Narayana Vyas Kondreddi
Tested on: SQL Server 7.0, SQL Server 2000
Date modified: December-3-2001 01:33 AM IST
Email: vyaskn@hotmail.com
Usage: Run this script in the master database to create the stored procedure. Once it is created,
you could run it from any of your user databases. If the first parameter (database name) is
not specified, the procedure will use the current database.
Example 1:
To see the file growth information of the current database:
EXEC sp_track_db_size_used
Example 2:
To see the file growth information for pubs database:
EXEC sp_track_db_size_used 'pubs'
***********************************************************************************************************/
DECLARE @dbname sysname
/* Work with current database if a database name is not specified */
SET @dbname = COALESCE(@dbnameParam, DB_NAME())
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name],
logical_name AS [Logical Filename],
physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT bs1.backup_start_date,
bf1.backup_set_id,
(bf1.backed_up_page_count),
bf1.logical_name,
bf1.[filegroup_name],
bf1.physical_name,
(
SELECT CONVERT(numeric(6,2),
(((bf1.backed_up_page_count*8192) * 100.00)/(backupfile1.backed_up_page_count*8192))-100)
FROM msdb.dbo.backupfile backupfile1
WHERE backupfile1.backup_set_id =
(
SELECT MAX(backupfile2.backup_set_id)
FROM msdb.dbo.backupfile backupfile2 JOIN msdb.dbo.backupset backupset
ON backupfile2.backup_set_id = backupset.backup_set_id
WHERE backupfile2.backup_set_id < bf1.backup_set_id AND
backupfile2.file_type='D' AND
backupset.database_name = @dbname AND
backupfile2.logical_name = bf1.logical_name AND
backupfile2.logical_name = backupfile1.logical_name AND
backupset.type = 'D'
) AND
backupfile1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile bf1 JOIN msdb.dbo.backupset bs1
ON bf1.backup_set_id = bs1.backup_set_id
WHERE bs1.database_name = @dbname AND
bf1.file_type = 'D' AND
bs1.type = 'D'
) as Derived
WHERE (Growth 0.0) OR (Growth IS NULL)
ORDER BY [Date], logical_name
-
Philip,
Thank you for replying on my post.
Before testing the scrtip I like to comment on your phrase;
. . . There is a column in this table called 'backed_up_page_count', which records the actual number of pages written to the backupfile, rather than the actual number of pages reserved for the database as a whole. This is the same as the "used" size of a database. . . .
A backup (file) is always smaler as the actual database unless if you set the fill-factor to 100% . Setting the fill-factor to 100% will be done on OLAP environments and is NOT recommended on OLTP environments. If you set it to 100pct in a OLTP environment al justifications on a page (update alter etc) will cause unnecessary row-chaining and slow you database down (dramaticly when altering it very frequently).
You suggestion is not that acurate as I would like but it is close.
later today (or next week) I will examin your script. . .
Regards
GKramer
The Netherlands
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply