Results 1 to 4 of 4

Thread: Tempdb Movement

  1. #1
    Join Date
    May 2003
    Posts
    12

    Unanswered: Dropping Master Device Segments ...

    Hi

    I have just created 2 new devices for tempdb and expanded tempdb onto the new data and log devices.

    I then followed the procedure for dropping the master device from the tempdb segments:

    sp_dropsegment "default", tempdb, master
    sp_dropdegment system, tempdb, master
    sp_dropdegment logsegment, tempdb, master

    ran the following -

    select dbid, name, segmapfrom sysusages, sysdeviceswhere sysdevices.low <= sysusages.size + vstart and sysdevices.high >= sysusages.size + vstart -1 and dbid = 2 and (status = 2 or status = 3

    and get :

    2 master 0
    2 tempdb_data1 3
    2 tempdb_log1 4

    In the manual the Master device should read '1' here ??? Where did I go wrong here ?

    1> sp_helpdb master
    2> go
    name db_size owner dbid
    created
    status
    ------------------------ ------------- ------------------------ ------
    --------------
    ------------------------------------------------------------------------------------------------------
    master 92.5 MB sa 1
    Aug 27, 2003
    no options set

    (1 row affected)
    device_fragments size usage
    created free kbytes

    master 6.0 MB data and log
    Aug 27 2003 1:11PM 1324
    master 86.5 MB data and log
    Aug 27 2003 1:13PM 87928
    (return status = 0)

    1> sp_helpdb tempdb
    2> go
    name db_size owner dbid
    created
    status
    tempdb 2003.0 MB sa 2
    Jan 20, 2004
    select into/bulkcopy/pllsort, trunc log on chkpt

    (1 row affected)
    device_fragments size usage
    created free kbytes
    ------------------------------ ------------- --------------------
    ------------------- ------------------------------
    master 3.0 MB data only
    Aug 27 2003 1:11PM 1864
    tempdb_data1 1000.0 MB data only
    Aug 28 2003 12:45PM 1019940
    tempdb_log1 1000.0 MB log only
    Aug 28 2003 12:45PM not applicable

    --------------------------------------------------------------
    log only free kbytes = 1019964
    (return status = 0)
    1>

    Mick
    Last edited by mickge; 01-20-04 at 12:22.

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: Dropping Master Device Segments ...

    Dropping the segments to master only avoid any new allocation in the master device. The existing allocated pages never move.

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    I never recommend my clients dropping default tempdb segment off master. Rather I prefer changing the segmap for that fragment to 0 then shutdown and restart the server.

    Yeah.. sure, this means you will be wasting someting like 3MB on master device. But when you consider disaster recovery for master failure having the tempdb fragment on master will be much easier and quicker than not having it at all.

  4. #4
    Join Date
    Mar 2004
    Posts
    1

    Re: Dropping Master Device Segments ...

    I had the same thing happen to me. I think it is an issue with the editing of the Performance Tuning Guide. In an earlier version of the Performance Tuning Guide (up to 11.5) it only had you run sp_dropsegment on the default and logsegment, leaving the system segment (the number 1 in the "verify" query result). Later versions (12.0.0 on) of the Performance Tuning Guide have you drop all three segments, but their is no change to the "verify" query or the text that surrounds it.

    Because of the persistent typo in the PT Guide (sp_dropdegment), it's easy to check this for yourself. Enter "sp_dropdegment" into Google and you will see what i mean.

    Having said this, i don't know if I should follow the guidelines of the newer or older Performance Tuning guide, but i am reassured that i didn't do anything wrong.

Posting Permissions

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