Skip to Content
0
Former Member
Mar 15, 2016 at 10:58 AM

Tempdb with wrongly assigned device fragements

103 Views

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