how to increase master device size. 
Author Message
 how to increase master device size.
Hi,

 we created master device(master db, tempdb ..) so small.
Now we want more space on master device.
If you know, Could you tell me the step to increase the master device
size?

thanks in advance,



Sun, 11 Mar 2001 03:00:00 GMT
 how to increase master device size.

This was copied from the Sybase Tech Notes pages
Document ID 2870

Using Mirroring to Move Devices to a Larger Partition

----------------------------------------------------------------------------
----
SUMMARY:  This document describes using SQL Server disk mirror to move
devices to a different or larger partition after the disk has been
repartitioned. Specific instructions are given for mirroring the master
device.
Document ID: 2870 Last Revised:  07/07/97
Topic: Database Admin Document Type: TechNote
Product: SQL Server Version: 11.0.x
Platform: Not Platform Specific Operating System: Not Operating System
Specific

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

Document:

Contents
Rather than using disk init, you can use Sybase mirroring to move a device
to another disk partition.

Using Mirroring to Move Master Device to a Larger Partition
Mirroring With Non-Master Devices
Using Mirroring to Move Master Device to a Larger Partition
There are four stages to move the master device out and back to the same
disk after the disk has been repartitioned.

Preparation for Mirroring Master
Move Master to a Temporary Device
Repartition the Disk Where Master Resided
Mirror, Unmirror, and Update sysdevices Using isql
See the SYBASE SQL Server Reference Manual: Volume 1, for syntax of the disk
mirror and disk unmirror commands.

Preparation for Mirroring Master
Dump all databases on the master device.
Turn on configuration variable allow updates to system tables using isql.
You must be logged in as a System Security Officer.
sp_configure "allow updates to system tables", 1
You may wish to restart SQL Server in single-user mode before beginning Step
3 to prevent others from using the server. If you decide to do this, keep in
mind that updates to system tables are automatically allowed in single-user
mode and you may skip Step 2 above. For details, see "How to Start SQL
Server in Single-User Mode" in the SQL Server Troubleshooting Guide.

Save the contents of sysusages, systemizes and sysdatabases in master.
1> use master
1> select * from sysusages
1> select * from sysdevices
1> select * from sysdatabases
Perform this preventative step in case the move operation fails and you need
to rebuild the master device and reload the master database.

Move Master to a Temporary Device
Mirror master to a temporary master device.
disk mirror name = "master",
mirror = "temp_phys_device"
Unmirror master from the current location and remove it.
disk unmirror name = "master",
side = "primary",
mode = remove
Run sp_helpdevice on the master device to verify that master is not
currently mirrored.
sp_helpdevice master
Repartition the Disk Where Master Resided
Shut down SQL Server.
Repartition the disk where the master device once resided.
Run the buildmaster utility against the new larger partition, making sure to
specify a new high value for the device by using the -s (size) parameter.
buildmaster -d<new_phys_device> -s <size>
Restart SQL Server.
Mirror, Unmirror, and Update sysdevices Using isql
Mirror the master device to the device just partitioned.
disk mirror name = "master",
mirror = "orig_phys_device"
Unmirror the master device.
disk unmirror name = "master",
side = "primary", mode = remove
If you are increasing the size of the master device, update sysdevices to
give SQL Server information about the enlarged master device. Otherwise, go
to Step 7 below, "Restart SQL Server".
begin tran
update sysdevices
set high = nnnn
where name = "master"
where nnnn is the size of the new database device (-s in Step 9) in 2K
blocks minus one.

----------------------------------------------------------------------------
----
Note
Calculate nnnn by rounding down the size of the device to the next megabyte
and either divide total number of bytes by 2048 or multiply the megabytes by
512.
----------------------------------------------------------------------------
----

Verify that SQL Server correctly updated the new master device and that
orig_phys_device is not currently mirrored.
select * from sysdevices
where name = "master"
Commit the transaction.
commit tran
Disallow updates to system tables.
sp_configure "allow updates to system tables", 0
Restart SQL Server in multi-user mode, if you switched to single-user mode
in "Preparation for Mirroring Master". For details, see "Returning SQL
Server in Multi-User Mode" in the SQL Server Troubleshooting Guide.

Quote:

>Hi,

> we created master device(master db, tempdb ..) so small.
>Now we want more space on master device.
>If you know, Could you tell me the step to increase the master device
>size?

>thanks in advance,



Sun, 11 Mar 2001 03:00:00 GMT
 how to increase master device size.


Quote:
> Hi,

>  we created master device(master db, tempdb ..) so small.
> Now we want more space on master device.
> If you know, Could you tell me the step to increase the master device
> size?

There are 2 ways to increase master device size:

1. take a master db dump, buildmaster on a larger device and then load master
dump 2. Use Sybase device mirroring to mirror the master device with a larger
device and then drop the  smaller  master after the mirroring is completed
(please search on techinfo.sybase.com, I remember  there is a technotes that
describes this procedure)

IMO, I prefer option 1 because it is faster..

HTH

Ken

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum



Mon, 12 Mar 2001 03:00:00 GMT
 how to increase master device size.

Quote:



> > Hi,

> >  we created master device(master db, tempdb ..) so small.
> > Now we want more space on master device.
> > If you know, Could you tell me the step to increase the master device
> > size?

> There are 2 ways to increase master device size:

> 1. take a master db dump, buildmaster on a larger device and then load master
> dump 2. Use Sybase device mirroring to mirror the master device with a larger
> device and then drop the  smaller  master after the mirroring is completed
> (please search on techinfo.sybase.com, I remember  there is a technotes that
> describes this procedure)

> IMO, I prefer option 1 because it is faster..

> HTH

> Ken

> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

--

There may be problems with option 1:

 For option 1, you are dumping a master database that has information
about the master device in the sysdevices table. When you build a larger
masterdevice and then load the old master database dump, the info.
in sysdevices is put back in, meaning your master device is only as
big as the smaller one. to work around this:

1) build a new master device using buildmaster.
2) Note sysdevices.high for the master devices from
   the sysdevices table.
3) Load the old master dump into the new server.
4) Adjust sysdevices.high (from step 1 above), so that the master
   device size is now correct.

Your server should now reflect a larger master device on to which you
can extend the master database.

Rajesh Pillai                



Fri, 16 Mar 2001 03:00:00 GMT
 how to increase master device size.


Quote:



> > > Hi,

> > >  we created master device(master db, tempdb ..) so small.
> > > Now we want more space on master device.
> > > If you know, Could you tell me the step to increase the master device
> > > size?

> > There are 2 ways to increase master device size:

> > 1. take a master db dump, buildmaster on a larger device and then load
master
> > dump 2. Use Sybase device mirroring to mirror the master device with a
larger
> > device and then drop the  smaller  master after the mirroring is completed
> > (please search on techinfo.sybase.com, I remember  there is a technotes that
> > describes this procedure)

> > IMO, I prefer option 1 because it is faster..

> > HTH

> > Ken

> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum

> --

> There may be problems with option 1:

>  For option 1, you are dumping a master database that has information
> about the master device in the sysdevices table. When you build a larger
> masterdevice and then load the old master database dump, the info.
> in sysdevices is put back in, meaning your master device is only as
> big as the smaller one. to work around this:

> 1) build a new master device using buildmaster.
> 2) Note sysdevices.high for the master devices from
>    the sysdevices table.
> 3) Load the old master dump into the new server.
> 4) Adjust sysdevices.high (from step 1 above), so that the master
>    device size is now correct.

> Your server should now reflect a larger master device on to which you
> can extend the master database.

And of course, if you are really confident in your figures, you can
just increase sysdevices.high without all the dump/buildmaster/load
steps...

--
Bret Halford
Sybase, Inc.
3665 Discovery Drive
Boulder, CO 80303

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum



Sat, 17 Mar 2001 03:00:00 GMT
 how to increase master device size.


Quote:

> --

> There may be problems with option 1:

>  For option 1, you are dumping a master database that has information
> about the master device in the sysdevices table. When you build a larger
> masterdevice and then load the old master database dump, the info.
> in sysdevices is put back in, meaning your master device is only as
> big as the smaller one. to work around this:

> 1) build a new master device using buildmaster.
> 2) Note sysdevices.high for the master devices from
>    the sysdevices table.
> 3) Load the old master dump into the new server.
> 4) Adjust sysdevices.high (from step 1 above), so that the master
>    device size is now correct.

Yeah.. pillai, you are right. I forgot to mention this.

Ken

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum



Sat, 17 Mar 2001 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Increase tempdb size/master device size?

2. Master device size in EM does not match master.dat in Explorer

3. Increasing Device sizes

4. Increasing the size of Master Table

5. Size Increase of Master Database

6. Any Method to Increase Master Size?

7. Increasing Master Database Size....How

8. Any Method to Increase Master Size?

9. Increasing SQL Server 6.5 Device Size Problems

10. Can't increase size of device?

11. Increase size of database device

12. Increase Raw Device Size


 
Powered by phpBB® Forum Software