I can't obtain to catch the following information of the catalogue of the SQL Server:
- Used Space of each archive.
- Fragmentation of each archive.
- Archive where one is stored determined object (table or index for example)
- Flow of I/O of the bank.
- Currently invalid Objects in the bank.
- Currently invalid Indices in the bank.
- Text of querys of the processes in execution.
SOMEBODY KNOWS HOW TO MAKE THIS FOR QUERY ANALYZER?
You it showed to several procedures to me of system that do not serve for so much thing (not to say, nothing) what I need.
For example, none of them goes to show the state to me of one occupation filegroup. Much badly database shows to me of one.
They had been invalid objects and etc, sql server simply ignores exactly, is impressive.
I'm so realy sorry for my poor english(i had use a internet translator).
I've try write a better english now.
Then, my focus now, simplifing our chat, is.
I need calculate the size of each filegroup has busy.
If a use sp_spaceused for entire database, for example, your allocated space not macth with the real used space.
Then, resuming... i need calculate:
- The actual memory size used by server
- The used size of each filegroup
- The actual I/O rate of server
I agree with you that sp_spaceused show me the total size of the database, but , it don't show the real free space of the database.
Make the follow steps:
- Open the Enterprise manager
- Open the properties screen of anyone database and write in a papper the free space avaliable ("Space avaliable").
- Open the query analyzer and run the sp_spaceused for the same database analyzed on the Enterprise manager.
- Now compare the results of sp_spaceused and enterprise manager.
How can we observe, none of the results match with the real free space of the database.
Then, the problems continues, how calculate the real free space of database.
How can we calculate the real memory usage
How can we calculate the real I/O rate of database.
SQL Server is a little unreliable at determining the space used by a database. You will need to run DBCC UPDATEUSAGE(0) in your database. There is a switch on sp_spaceused which will run that for you, though.
sp_spaceused @updateusage = 'TRUE'
You can also use the fileproperty() function, and get the amount of space used per file. It appears to me that this will use the IAM/OAM pages, rather than the sysindexes table, and as such may be more reliable:
select groupid, sum(fileproperty(name, 'spaceused'))
group by groupid
This will give you the number of 8KB pages currently used.