SQL Server Performance

Data and log size used

Discussion in 'General DBA Questions' started by jonathanB, May 9, 2007.

  1. jonathanB New Member

    Hi,

    Is there a script to capture the data size used and its log? Thanks in advance.
  2. xiebo2010cx Member

    If you go to taskpad, you can see the information you want,
    Also you can use sp_spaceused, sp_helpdb and DBCC SQLPERF(Logspace)

    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  3. jonathanB New Member


    How about the transactions/sec without using the table sysperfinfo, how can I get those value per database?... Thanks in advance.
  4. xiebo2010cx Member

    For transactions, you can use the SQL profiler to see how busy your server is.


    ------------------
    Bug explorer/finder/seeker/locator
    ------------------
  5. MohammedU New Member

    Profiler is expensive to use for this purpose...
    It is better to use sysperfinfo or perfmon (sysmon) or Logman utility...or wmi....

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. jonathanB New Member


    Is there a script to produce transactions/sec other than using sysperfinfo?... Thanks in advance
  7. MohammedU New Member

    As I mentioned before you need write wmi script to get the data....
    or you can use logman utility....

    http://www.microsoft.com/resources/...roddocs/en-us/nt_command_logman.mspx?mfr=true


    Why you don't want to use sysperfinfo table?
    where as sysperfinfo table gets its data from the same sources as perfmon... Sysperfinfo is used as the source for SQL Agent Performance Alerts..

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. jonathanB New Member


    Some servers I monitored has empty records on the sysperfinfo table...
  9. MohammedU New Member

    If sysperfinfo table is empty then you don't the data from perfmon too...
    I don't think you can get the data from any where else other than memory sturctures...but I don't think any one has any query for that...

    Check the following for solution...

    FIX: "Performance monitor shared memory setup failed: -1" error message when you start SQL Server
    http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&Product=sql2k

    PRB: SQL Performance Monitor Counters Missing
    http://support.microsoft.com/kb/227662


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. jonathanB New Member


    How about getting the value for total memory used?
  11. satya Moderator

    What is the problem in using the PERFMON rather than depending upon the scripts?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  12. jonathanB New Member


    I am not allowed to use perfmon because we are running a schedule tasks to run the script every 2 hours and the output will depend on their requirements...
  13. satya Moderator

    Fyi, you can schedule the PERFMON trace as similar to SQLagent tasks.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  14. jonathanB New Member


    The problem with perfmon is when there's a new database on the server, will have to update the perfmon regarding the new database based on the statistics required...
  15. satya Moderator

    How often you will have new database installed there?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  16. jonathanB New Member


    probably once every two months...
  17. satya Moderator

    By chance are you using SQL 2005?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  18. MohammedU New Member

    Try to use the logman utility or wmi script...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page