Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    7

    Exclamation Unanswered: SQL Server HELP!

    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?
    HELP PLEASE

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Start by reviewing all the "sp_help..." stored procedures. Documentation is available on Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2006
    Posts
    7

    Arrow Not work!

    I already observed all procedure “sp_help” and i didn't obtain the information that i requested here in the topic.
    sp_help is very limited.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, so very limited....
    sp_help, sp_help_agent_default, sp_help_agent_parameter, sp_help_agent_profile, sp_help_datatype_mapping, sp_help_fulltext_catalogs, sp_help_fulltext_catalogs_cursor, sp_help_fulltext_columns, sp_help_fulltext_columns_cursor, sp_help_fulltext_tables, sp_help_fulltext_tables_cursor, sp_help_publication_access, sp_helpallowmerge_publication, sp_helparticle, sp_helparticlecolumns, sp_helparticledts, sp_helpconstraint, sp_helpdb, sp_helpdbfixedrole, sp_helpdevice, sp_helpdistpublisher, sp_helpdistributiondb, sp_helpdistributor, sp_helpdistributor_properties, sp_helpextendedproc, sp_helpfile, sp_helpfilegroup, sp_helpgroup, sp_helpindex, sp_helplanguage, sp_helplinkedsrvlogin, sp_helplog, sp_helplogins, sp_helpmergealternatepublisher, sp_helpmergearticle, sp_helpmergearticlecolumn, sp_helpmergearticleconflicts, sp_helpmergecleanupwait, sp_helpmergeconflictrows, sp_helpmergedeleteconflictrows, sp_helpmergefilter, sp_helpmergepublication, sp_helpmergepullsubscription, sp_helpmergesubscription, sp_helpntgroup, sp_helppublication, sp_helppublicationsync, sp_helppullsubscription, sp_helpremotelogin, sp_helpreplfailovermode, sp_helpreplicationdb, sp_helpreplicationdboption, sp_helpreplicationoption, sp_helprole, sp_helprolemember, sp_helprotect, sp_helpserver, sp_helpsort, sp_helpsql, sp_helpsrvrolesp_helpsrvrolemember, sp_helpstats, sp_helpsubscriberinfo, sp_helpsubscription, sp_helpsubscription_properties, sp_helpsubscriptionjobname, sp_helptext, sp_helptrigger, sp_helpuser
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2006
    Posts
    7

    Smile ...

    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.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, lets focus on just one of the issues you mentioned at a time. Which is your highest priority? Also, we may need to get some translation issues resolved (Archive? Bank? Occupation filegroup?).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2006
    Posts
    7
    Blindman
    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

    That's three informations will help me so much...

    Again, sorry for my english.

  8. #8
    Join Date
    Jul 2005
    Posts
    1
    Thanx for Help

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Run this, substituting your database name:
    Code:
    Use [YourDatabase]
    exec sp_helpdb '[YourDatabase]'
    exec sp_spaceused
    db_size should equal the total of the file sizes.
    data + index_size + unused should equal reserved
    Last edited by blindman; 07-12-06 at 11:45.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jul 2006
    Posts
    7

    Arrow Yes Blindman

    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.

    []'s

    Neo6

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.
    Code:
    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:
    Code:
    select groupid, sum(fileproperty(name, 'spaceused')) 
    from sysfiles
    group by groupid
    This will give you the number of 8KB pages currently used.

  12. #12
    Join Date
    Jul 2006
    Posts
    7

    Arrow MCrowley

    I already used @updateusage in my previous executions and exactly thus the result does not match.

    Using fileproperty, we have a filegroup vision, that the managemental level is a very good thing, but the results not match yet.

    Then, how we must treat such situation?

    Use the grafical tool are out of cogitation.

    And the problem persists...

    []'s

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the results and statistics you are getting for your database.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2006
    Posts
    7

    Arrow Results...

    Results of sp_spaceused using @updateusage for database MANTIQ

    database_name database_size unalocated_size
    MANTIQ 2560.00 MB 1405.20 MB

    reserved data index_size unused
    133936 KB 131808 KB 1120 KB 1008 KB

    Results of sumarize of fileproperty(,'spaceused') for MANTIQ

    select x.groupid
    , sum(fileproperty(x.name, 'spaceused')*8) as 'spaceused KB'
    from sysfiles x
    group by x.groupid

    groupid spaceused
    0 21984
    1 134016
    2 64

    Results of Enterprise Manager for MANTIQ

    Size: 2560,00 MB
    Space avaliable: 2384,02 MB

    Analysing the results, you can see that the same don't match with the "Space avaliable" of Enterprise Manager.

    Only remembering, we must calculate yet the I/O rate and memory usage.

    []'s

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •