Tracking Size of the Data File 1

[JohnBates]

hi all,

This is 2005.

I want to log the size of the .mdf data file into a table and update it daily. I dont want the entire db size because it would include log space which can vary day to day. I'm loading millions of records and I want to see how it impacts the actual data file size.

When I run

use AVNAPPDB
Exec sp_spaceused

it gives me 2 sets of data. The second one has column 'Data'. This seems to refect the actual amount/size of data in the database so I think this value is what I want to track every day.

How can I output that into a table. Is there a view perhaps that I can select from?

Thanks, John

 

[gmmastros]

sp_spaceused is a system stored procedure. However, you can easily get the source of this stored procedure, like this...

sp_helptext 'sp_spaceused'

Looking at the procedure, it seems like the relevant parts (to you) is....

	select 
		8 * sum(
				CASE
					-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
					When it.internal_type IN (202,204) Then 0
					When a.type <> 1 Then a.used_pages
					When p.index_id < 2 Then a.data_pages
					Else 0
				END
			)
	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
		left join sys.internal_tables it on p.object_id = it.object_id

The result of this query is an int value, but it's reported in KB (the same as the sp_spaceused procedure).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom