Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: How to check log space from a script

    Hi All,
    I was looking to set up a script to check the log space for a particular database.
    I know there is the sp_helpdb stored procedure but it produces too much output - is there a table or view that would be better for this information.
    Thanks for any help,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    Germany, near Aachen
    Posts
    120

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Bernd,
    Thanks for your post but I was hoping for a sql script.
    I found the below at your link:
    1> select db_name(dbid), segmap, size, curunreservedpgs(dbid, lstart, unreserved
    pgs) from master..sysusages where db_name(dbid)='MSB'
    2> go
    segmap size
    ------------------------------ ----------- ----------- -----------
    MSB 3 51200 34568
    MSB 0 25600 25448
    MSB 3 1496064 1496064
    MSB 4 524288 516944
    (4 rows affected)
    But I'm not sure how to link this to log space.
    Could you help?
    Cheers,
    Breen.

  4. #4
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Wink

    I think this is the easiest way

    USE your_databases
    go

    SELECT data_pgs(8, doampg)
    FROM sysindexes where id=8
    go

    bye

    Sebastian

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi svelasco,
    Could you talk me through your script.
    What is id=8 actually?
    Thanks for your reply and any help,
    Breen.

  6. #6
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78
    If you see into sysobjects, you will find that the syslogs allways is the id=8

    select * from sysobjects where type = "S"

    The "data_pgs" is an internal function of sybase to find out how many pages use an objects (read the manual for more information).

    Bye

    Sebastian

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Sebastian,
    Thank you for your help and I have just one further question to confirm:

    1> select data_pgs(8,doampg)
    2> from sysindexes where id=8
    3> go

    -----------
    32

    (1 row affected)

    From this I would take it that there are 32 pages used for the log segment => 32 x 2k = 64k used.

    Is this correct?
    And again thanks for your help and patience,
    Breen.

  8. #8
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Thumbs up

    That correct,
    also you can check with "DBCC CHECKTABLE(syslogs)"
    You can try this in master Databases (in a test server)

    USE master
    go

    SELECT data_pgs(8, doampg)
    FROM sysindexes where id=8
    go

    -- if you dont backup the transaction log !!!!!!
    DUMP TRANSACTION master WITH TRUNCATE_ONLY
    go

    SELECT data_pgs(8, doampg)
    FROM sysindexes where id=8
    go

    8 pages

    DBCC CHECKTABLE(syslogs)
    go

    will say 1 pages

    In fact, the syslogs have take an extends that have 8 pages, but only use 1 page from this.
    If are you checking the spaces, then is more usefull the pages take by extends, that it's the select, because the server take by extends(8pages) from the databases.

    bye

    Sebastian

  9. #9
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks for all your help Sebastian.
    Breen.

Posting Permissions

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