cancel
Showing results for 
Search instead for 
Did you mean: 

Tempdb with wrongly assigned device fragements

Former Member
0 Kudos

Dear ASE DBA World,

An ASE instance has its tempdb fragments as follows.

master                               24.0 MB data only            Jan 30 2013  9:18AM                  24480

tempdbdev                          2048.0 MB data only            Jan 30 2013  9:19AM                2088336

tempdbdev                          1024.0 MB data only            Aug 24 2014  5:14PM                1044368

tempdbdev                          1024.0 MB data only            Jul 12 2015 11:17AM                1044368

tempdbdev                          2048.0 MB log only             Jul 25 2015  3:11PM       not applicable

tempdbdev                          2048.0 MB log only             Jan 12 2016  3:08PM       not applicable

tempdbdev                          2048.0 MB data only            Jan 24 2016  4:13PM                2088960

tempdblog                          3072.0 MB log only             Mar 15 2016  2:14PM       not applicable

tempdblog                          1024.0 MB log only             Mar 15 2016  2:40PM       not applicable

For some mistakes, by the DBA the log only shows in the device meant for the data. Log is now over configured.

How can I set the fragments back to normal? What issues (if-so-ever) should i expect, in such scenario?

1> select * from sysusages where dbid =2

2> go

dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno

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

      2           0          0       1536       65540        0          1530             Jan 30 2013  9:18AM           0

      2           3       1536     131072           2        0        130553             Jan 30 2013  9:19AM           3

      2           3     132608      65536     1048578        0         65280             Aug 24 2014  5:14PM           3

      2           3     198144      65536     1572866        0         65280             Jul 12 2015 11:17AM           3

      2           4     263680     131072     2097154        0        130560             Jul 25 2015  3:11PM           3

      2           4     394752     131072     3145730        0        130560             Jan 12 2016  3:08PM           3

      2           3     525824     131072     4194306        0        130560             Jan 24 2016  4:13PM           3

      2           4     656896     196608           2        0        195840             Mar 15 2016  2:14PM          45

      2           4     853504      65536     1572866        0         65280             Mar 15 2016  2:40PM          45

Following steps should be good?

sp_configure "allow updates to system tables",1

go

update master..sysusages

  set segmap = 3

  where dbid = 2

  and lstart = 263680

update master..sysusages

  set segmap = 3

  where dbid = 2

  and lstart = 394752

go

sp_configure "allow updates to system tables",0

go

shutdown and reboot

go

Former Member
0 Kudos

Dear Mark,

I think we have cracked it.

The sp_helpdb output is as follows.

I feel things are good, 

Except for the message , which i got after firing drop segment.

There are no longer any segments referencing devices 'master, tempdbdev'.  These devices will no longer be used for space allocation.

device_fragments               size          usage                created                   free kbytes

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

master                               24.0 MB data only            Jan 30 2013  9:18AM                  24480

tempdbdev                          2048.0 MB data only            Jan 30 2013  9:19AM                2088464

tempdbdev                          1024.0 MB data only            Aug 24 2014  5:14PM                1044368

tempdbdev                          1024.0 MB data only            Jul 12 2015 11:17AM                1044368

tempdbdev                          2048.0 MB data only            Jul 25 2015  3:11PM                2088832

tempdbdev                          2048.0 MB data only            Jan 12 2016  3:08PM                2088960

tempdbdev                          2048.0 MB data only            Jan 24 2016  4:13PM                2088960

tempdblog                          3072.0 MB log only             Mar 15 2016  2:14PM       not applicable

tempdblog                          1024.0 MB log only             Mar 15 2016  2:40PM       not applicable

                                                                                                                                                                                                                                                                                                                                               

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

log only free kbytes = 4177920                                                                                                                                                                                                                                                                                                                

device                               segment

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

master                                -- unused by any segments --

tempdbdev                          -- unused by any segments --

tempdbdev                            default

tempdbdev                            system

tempdblog                            logsegment

(return status = 0)

Mark_A_Parsons
Active Participant
0 Kudos

What does the following query return:

=================

select * from master..sysusages where dbid = db_id('tempdb')

go

=================


I'm guessing it'll show one of those tempdbdev fragments with a segmap=0.


You could try extending the system/default segments onto this fragment (see sp_extendsegment), but I wouldn't be surprised if sp_extendsegment complains and says the segment already exists on that fragment ... in which case you'd likely need to update that sysusages fragment and set segmap=3 (and bounce the dataserver at your convenience).

Former Member
0 Kudos

You are absolutely right.

So this literally mean, no future space allocation would happen onto those two fragments.

1> select * from master..sysusages where dbid = db_id('tempdb')

2> go

dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno

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

      2           0          0       1536       65540        0          1530             Jan 30 2013  9:18AM           0

      2           3       1536     131072           2        0        130553             Jan 30 2013  9:19AM           3

      2           3     132608      65536     1048578        0         65280             Aug 24 2014  5:14PM           3

      2           3     198144      65536     1572866        0         65280             Jul 12 2015 11:17AM           3

     2           0     263680     131072     2097154        0        130560             Jul 25 2015  3:11PM           3

      2           0     394752     131072     3145730        0        130560             Jan 12 2016  3:08PM           3

      2           3     525824     131072     4194306        0        130560             Jan 24 2016  4:13PM           3

      2           4     656896     196608           2        0        195840             Mar 15 2016  2:14PM          45

      2           4     853504      65536     1572866        0         65280             Mar 15 2016  2:40PM          45

(9 rows affected)

Mark_A_Parsons
Active Participant
0 Kudos

correct

Former Member
0 Kudos

Should that necessarily be a problem, excluding the said fact?

Honestly how severe this can be, i am a little hesitant here to update the system tables, as this is being done on PROD environment.

Backing up the master database. Anything else i need to take care of?

tables such as sysusages as well?

Regards,

Ajith Prabhakaran

kevin_sherlock
Contributor
0 Kudos

if you are hesitant to update system tables, just use the system procedures in a way that shuffles things around on whole devices:

Extend segments "system" and "default" on to master device.

Drop "system" and "default" segments from tempdbdev.

Add "system" and "default" segments back to tempdbdev.

Drop "system" and "default" segments from master device.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Hans, Kevin and Mark.

I have done the changes now. The fragment allocation looks perfect now.

1> select * from master..sysusages where dbid =2

2> go

dbid   segmap      lstart     size       vstart      location unreservedpgs crdate                          vdevno

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

      2           0          0       1536       65540        0           686             Jan 30 2013  9:18AM           0

      2           3       1536     131072           2        0        130560             Jan 30 2013  9:19AM           3

      2           3     132608      65536     1048578        0         65280             Aug 24 2014  5:14PM           3

      2           3     198144      65536     1572866        0         65280             Jul 12 2015 11:17AM           3

      2           3     263680     131072     2097154        0        130560             Jul 25 2015  3:11PM           3

      2           3     394752     131072     3145730        0        130560             Jan 12 2016  3:08PM           3

      2           3     525824     131072     4194306        0        130560             Jan 24 2016  4:13PM           3

      2           4     656896     196608           2        0        195840             Mar 15 2016  2:14PM          45

      2           4     853504      65536     1572866        0         65280             Mar 15 2016  2:40PM          45

Kind Regards,

Ajith Prabhakaran

hans-juergen_schwindke
Active Participant
0 Kudos

Hi Ajith,

I think your approach will work.

There is also the possibility of shrinking a database using "alter database ... log off ...". And afterwards you can extend the database again.

In your case it is "only" tempdb which is recreated when rebooting ASE. Maybe the update commands will change the segmap of your currently used part of the transaction log. For other databases than tempdb this could be a risk. So for these databases I would compare with firstpage and rootpage in the following select:

select * from syspartitions where  name like '%syslogs%'

with sysusages

and wait until the transaction log uses fragments that will remain to the transaction log after my rebuild operation. Or in other words: wait until firstpage and rootpage are lying in the new transaction log.

Best regards,

Juergen

Former Member
0 Kudos

Dear Hans,

alter database off, could not work in my case, as the tempdb db is made with durability as no_recovery.

Regards,

Ajith Prabhakaran