Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2

    Unanswered: Master DB size growing

    My master DB size is 200 MB.
    Its size increasing consistently (1 0r 2 MB's).40 days back the size is around 100 MB and now its around 150 MB. Want help to traceout the cause.





  2. #2
    Join Date
    Feb 2012
    Posts
    133
    do you have user database on master db ?

  3. #3
    Join Date
    Dec 2013
    Posts
    14
    Quote Originally Posted by yuvaraj.s View Post
    My master DB size is 200 MB.
    Its size increasing consistently (1 0r 2 MB's).40 days back the size is around 100 MB and now its around 150 MB. Want help to traceout the cause.
    You'll need to find out what's taking up all the space.

    Three issues that come to mind:

    1: your master database's log is growing because you're not truncating it on a regular basis; try running the following:

    use master
    go
    sp_helpsegment logsegment
    go

    If your log is filling up then this is going to be a large number under 'used_pages'. Try the following and take another look at the value under 'used_pages':

    use master
    go
    dump tran master with truncate_only
    go
    sp_helpsegment logsegment
    go

    If 'used_pages' is much smaller than before then you've just freed up some space in the log. Ideally the truncation of the master's logsegment should leave 'used_pages' < 100.

    Going forward you'll want to periodically truncate the master database's log. How often you truncate the log will depend on how quickly the log grows, which in turn is based on the type and volume of activity in the master database (eg, adding/dropping/modifying logins, keeping track of performance metrics in syslogins, CIS overhead issues with ASE 12.5.x and MDA tables, etc).

    You're dumping the master database on a regular basis, right? If so, just issue 'dump tran master with truncate_only' before you issue the 'dump database master ...' command.

    Unfortunately you cannot set the 'trunc log on chkpt' setting for the master database, but what you could do is define a few thresholds on the master logsegment and then have the threshold procedure issue the 'dump tran master with truncate_only' command (followed by an optional 'dump database master ... ' command ??) when the threshold fires.

    2: you have a long running transaction (or possibly a replication truncation point that's not moving) that's keeping you from truncating the master's log

    If in step #1 you saw a large number under 'used_pages' after issuing the 'dump tran master with truncate_only' command then you'll need to find out what's keeping you from truncating the log.

    What does the following show you:

    use master
    go
    select * from syslogshold
    where dbid = 1
    go

    If you see a record with a name = '$replication_truncation_point' then you'll need to get with your repserver DBA to figure out why the this trunc point isn't moving (eg, repagent is down, master isn't suppose to have a trunc point, the repserver is down).

    If you see some other entry in this output, and the starttime is really old then you'll need to take a look at the process owned by the spid (mentioned in the output) to find out why it's sitting there with an open transaction.

    3: you have user-defined objects (eg, tables, procs, etc) in the master database; if this is the case then this is usually a bad thing as you really want to limit what goes into the master database (eg, you don't want the master database growing too large, eh)

    What do you see when you run the following:

    use master
    go
    sp_help
    go

    Depending on your ASE version and whether you've loaded the MDA tables the number of items in this list could vary from 50 to a couple hundred ... it also depends on your environment and what should be expected in the master database. Ideally you don't want any user-defined objects in the master database ... unfortunately it's a bit tough to give a definitive list of what should (not) be in the master database without knowing your ASE version, if MDA tables have been loaded, if you're replicating the master database, etc ...

  4. #4
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2

    Master Db size growing

    Hi Mark,

    "select name from sysobjects where type='U'"

    When i fire this quey inside master DB I found,

    70 monitoring tables as prefix 'mon' and 12 tables with prefix 'spt' were present...

    can these Monitoring tables would cause this trouble?



    Thanks
    yuvaraj
    Last edited by yuvaraj.s; 12-12-13 at 05:54.

  5. #5
    Join Date
    Dec 2013
    Posts
    14
    If they are the MDA tables then 'no' they do not take up space.

    You can get a rough idea of the tables using up the most space with the following:

    select top 20 object_name(id),leafcnt+pagecnt
    from systabstats
    where indid <= 1
    order by 2 desc

    ----------------

    What did 'sp_helpsegment logsegment' show?

  6. #6
    Join Date
    Dec 2013
    Posts
    14
    Also, the dozen spt_* tables are ok, those are required by Sybase and are suppose to reside in the master database.

  7. #7
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2
    Hi Mark,

    This is what my " sp_helpsegment logsegment " shows....


    total_size total_pages free_pages used_pages reserved_pages
    ----------------- --------------- --------------- --------------- ---------------
    200.0MB 102400 21673 76148 0



    AND for this query



    select top 20 object_name(id),leafcnt+pagecnt
    from systabstats
    where indid <= 1
    order by 2 desc


    sysprocedures 1655
    sysmessages 503
    syscomments 89
    syscolumns 63
    spt_values 38
    syslogins 24
    sysattributes 23
    systabstats 19
    sysindexes 17
    sysconfigures 17
    sysobjects 10
    Last edited by yuvaraj.s; 12-13-13 at 00:24. Reason: Extra info adding

  8. #8
    Join Date
    Dec 2013
    Posts
    14
    So the logsegment is currently taking up 76,148 pages, which on a 2KB page dataserver means the log is taking up ~152MB of disk space.

    Did you perform the actions I outlined in step #1?

    As I mentioned in step #1 the log should take up less than 100 pages if it's been truncated and there are no long-running/old-open transactions in the master database.

    If you did perform the actions in step #1, and the log is still showing 76,148 pages, what were the results of performing the actions in step #2?

    ---------------

    The results from the systabstats query look ok/normal.

  9. #9
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2
    Hi Mark,

    dump tran master with truncate_only
    go



    " truncate_only " sounds like truncating the transactions.
    Will this command affects the DB anyway?
    What will happen wen we fire this.... Since it is in production I want the details please.....

  10. #10
    Join Date
    Dec 2013
    Posts
    14
    'dump tran' removes the *old* activity from the database's transaction log. 'dump tran' can dump this old activity to a file or just throw it away. Since you can't dump master's old activity to a file you have to throw it away (via the 'truncate_only' clause).

    Clearing the transaction log has no effect on the data in the system/user tables in the master database.

    As I've already mentioned in step #1, you should be dumping your master database on a regular basis (in case you have to recover the master database), so you should ... at a minimum ... truncate the log at the same time, eg:

    dump tran master with truncate_only
    go
    dump database master to "<some_path_in_the_OS>/<name_of_dump_file>"
    go

    If you have concerns about a command then you need to a) read the documentation on the command [See the ASE Reference Manual:Commands document for details on the 'dump' command - Sybase Product Documentation ] and b) practice using it on a development dataserver.

  11. #11
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2

    Thumbs up

    Thanks Mark....

    will check it...



    Thanks,
    Yuvaraj.S

  12. #12
    Join Date
    Nov 2013
    Location
    Bangalore
    Posts
    30
    Provided Answers: 2
    Hi Mark,

    Thanks , it works...


    sp_helpsegement 'logsegment'



    total_size total_pages free_pages used_pages reserved_pages
    ----------------- --------------- --------------- --------------- -----------
    200.0MB 102400 97438 422 0

Posting Permissions

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