April 28, 2008 at 5:28 am
Hi there,
I am a bit flabbergasted. I found a script in the SCRIPTS section on monitoring the growth of databases. I wanted to know the USED amount of Mb in a databasefile. I came up with a script which used SYSINDEXES for that. That part of the script is like this:
INSERT INTO DBSpaceCheck
EXEC
('SELECT (SELECT SUM(SIZE) FROM [' + @srvname + '].[' + @DBNAME + '].[dbo].[SYSFILES]) / 128.0,'
+ '(SELECT SUM(RESERVED) FROM [' + @srvname + '].[' + @DBNAME + '].[dbo].[SYSINDEXES] WHERE INDID IN (0,1,255))/128.0,'
+ '((SELECT SUM(SIZE) FROM [' + @srvname + '].[' + @DBNAME + '].[dbo].[SYSFILES])
- (SELECT SUM(RESERVED) FROM [' + @srvname + '].[' + @DBNAME + '].[dbo].[SYSINDEXES] WHERE INDID IN (0,1,255)))/128.0, '
+ '''' + @SRVNAME + '''' + ', ' + '''' + @DBNAME + '''' + ', getdate()'
)
I would like to understand how this table is used to count the reserved pages in a file. As I understand it the Sysindexes holds info on indexes and tables. I can imagine you can use this to calculate the real used size of the file, but not like it is used here. The IN(0,1,255) clause limits the tables used, yet it does show the right amount of used space in the file.
Can some1 explain this to me like I am a 6-year old?
TIA,
Hans Brouwer
Greetz,
Hans Brouwer
April 28, 2008 at 8:59 am
Hans,
If I remember right, the sysindexes can get out of date in 2000 and only gets fixed if you reindex things. I'm not sure I'd depend on this to accurately determine the file size.
It's probably good enough for growth tracking, though perhaps rows is good enough as well?
April 28, 2008 at 11:24 pm
Rows tracking makes me check ALL tables, this piece of code does the job.
I am finding out how it works, except for the counting of TXT, ntxt and such.
Tnx for answering, I am still curious on the mechanics
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply