Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data?
Any Help Will Be Appreciated
Hi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David
|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>

Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data'
Any Help Will Be AppreciatedHi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>

Database startup

in sql server (re)start where does it find the location of Master.mdf file .

In Oracle, there is control file where the location of *.dbf file is stored and Instance find the location of dbf file from that location

In sql server where it finds the location of Databases as master, msdb etc..

The location is set duing Server installation.

That information is stored in the registry. It can also be supplied as a command line start up parameter [ -dMasterFilePath ].

The registry location 'should' be:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters

Database size....

Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
ChrisWhen you are about to start the next process, why not drop the database and
re-create it?
On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl, "Chris
Marsh" <cmarsh@.synergy-intl.com> wrote:
> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the table
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that solve
> the size issue?
> A half a GB database that only contains a small number of records is a waste
> of disk space.
> Thanks,
> Chris
>|||Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
are using temporary tables to stage and then dropping them when you are done
with the import.
Sincerely,
Anthony Thomas
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris|||This is a multi-part message in MIME format.
--000809080808010904020109
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Have you confirmed if it's the data file or the log file that accounts
for the majority of the DB size? You can find out with a simple
sp_helpdb '<MyDB>'. Perhaps for this process (I'm guessing it's the
transaction log) you should set the recovery model to SIMPLE so that the
log doesn't grow large while preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data into
the master db? That's almost certainly a bad thing to do.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chris Marsh wrote:
>Hi,
>I have a master database that I am using to import data into while we
>migrate and upgrade our customers. Once complete I detach and rename the
>database for my client, attach to their server, etc. However, when I move
>to import the next customers set of data I delete all records from the table
>and begin the reimport process one after the other. This approach is
>working fine however we are finding that the database is 500+/- MB's when it
>should be tiny. Is there a step or process I am missing for the "shrink"
>procedures. Should I "shrink" when the database is empty? Will that solve
>the size issue?
>A half a GB database that only contains a small number of records is a waste
>of disk space.
>Thanks,
>Chris
>
>
--000809080808010904020109
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Have you confirmed if it's the data file or the log file that
accounts for the majority of the DB size? You can find out with a
simple sp_helpdb '<MyDB>'. Perhaps for this process (I'm
guessing it's the transaction log) you should set the recovery model to
SIMPLE so that the log doesn't grow large while preparing your
customers' data.<br>
<br>
Just out of curiosity, is there a reason you're throwing this data into
the master db? That's almost certainly a bad thing to do.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Chris Marsh wrote:
<blockquote cite="miduXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl" type="cite">
<pre wrap="">Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris
</pre>
</blockquote>
</body>
</html>
--000809080808010904020109--|||This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C51E3C.900058A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Mike,
Thank you for the reply, I poorly worded my question by stating that I =was using the "master database". I should have been clear in saying our =master database, I obviously need to come up with a new term for our =default, install database. What I did double-check was that I am using =the "simple" method and withoiut almost any records in the database it =appears as if I can get it down to 87MB's, I will assume that some of =the required data that we include is what's taking up the space. That's =a lot better than when I first sent my email out.
I came from the VFP (foxpro) data world and the SQL database is easily 4 =- 5 times larger than VFP's.
Thanks again.
Chris
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23i%23KDlgHFHA.3196@.TK2MSFTNGP15.phx.gbl...
Have you confirmed if it's the data file or the log file that accounts =for the majority of the DB size? You can find out with a simple =sp_helpdb '<MyDB>'. Perhaps for this process (I'm guessing it's the =transaction log) you should set the recovery model to SIMPLE so that the =log doesn't grow large while preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data =into the master db? That's almost certainly a bad thing to do.
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Chris Marsh wrote: Hi,
I have a master database that I am using to import data into while we migrate and upgrade our customers. Once complete I detach and rename =the database for my client, attach to their server, etc. However, when I =move to import the next customers set of data I delete all records from the =table and begin the reimport process one after the other. This approach is working fine however we are finding that the database is 500+/- MB's =when it should be tiny. Is there a step or process I am missing for the ="shrink" procedures. Should I "shrink" when the database is empty? Will that =solve the size issue?
A half a GB database that only contains a small number of records is a =waste of disk space.
Thanks,
Chris
--=_NextPart_000_000B_01C51E3C.900058A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Mike,
Thank you for the reply, I poorly =worded my question by stating that I was using the "master database". I should =have been clear in saying our master database, I obviously need to come up with a =new term for our default, install database. What I did double-check was =that I am using the "simple" method and withoiut almost any records in the =database it appears as if I can get it down to 87MB's, I will assume that some of the required data that we include is what's taking up the =space. That's a lot better than when I first sent my email out.
I came from the VFP (foxpro) data world =and the SQL database is easily 4 - 5 times larger than VFP's.
Thanks again.
Chris
"Mike Hodgson" wrote in message news:%23i%23KDlgH=FHA.3196@.TK2MSFTNGP15.phx.gbl...Have you confirmed if it's the data file or the log file that accounts for =the majority of the DB size? You can find out with a simple =sp_helpdb ''. Perhaps for this process (I'm guessing it's the transaction log) you should set the recovery model to SIMPLE so that =the log doesn't grow large while preparing your customers' data.Just =out of curiosity, is there a reason you're throwing this data into the master = db? That's almost certainly a bad thing to do.
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Chris Marsh wrote: Hi,
I have a master database that I am using to import data into while we migrate and upgrade our customers. Once complete I detach and rename =the database for my client, attach to their server, etc. However, when I =move to import the next customers set of data I delete all records from the =table and begin the reimport process one after the other. This approach is working fine however we are finding that the database is 500+/- MB's =when it should be tiny. Is there a step or process I am missing for the ="shrink" procedures. Should I "shrink" when the database is empty? Will that =solve the size issue?
A half a GB database that only contains a small number of records is a =waste of disk space.
Thanks,
Chris

---

--=_NextPart_000_000B_01C51E3C.900058A0--|||Aaron,
Very interesting thought! Are you suggesting doing that by a script? If
so, then how to I deal with some of the default data that we do not import
but is required to make the application function? In fact, if I may how do
you suggest that one deals with general updates to an application. We are
updating stored procs, tables, views, etc. Currently we have been doing
this by hand but there must be a better method. Any suggestions?
Thanks,
Chris
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE492D4F.1826%ten.xoc@.dnartreb.noraa...
> When you are about to start the next process, why not drop the database
> and
> re-create it?
>
>
> On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl,
> "Chris
> Marsh" <cmarsh@.synergy-intl.com> wrote:
>> Hi,
>> I have a master database that I am using to import data into while we
>> migrate and upgrade our customers. Once complete I detach and rename the
>> database for my client, attach to their server, etc. However, when I
>> move
>> to import the next customers set of data I delete all records from the
>> table
>> and begin the reimport process one after the other. This approach is
>> working fine however we are finding that the database is 500+/- MB's when
>> it
>> should be tiny. Is there a step or process I am missing for the "shrink"
>> procedures. Should I "shrink" when the database is empty? Will that
>> solve
>> the size issue?
>> A half a GB database that only contains a small number of records is a
>> waste
>> of disk space.
>> Thanks,
>> Chris
>>
>|||Anthony,
Thank you for the reply. That also might be a good option to try.
Chris
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uY3NORgHFHA.2620@.tk2msftngp13.phx.gbl...
> Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
> are using temporary tables to stage and then dropping them when you are
> done
> with the import.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the
> table
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when
> it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that
> solve
> the size issue?
> A half a GB database that only contains a small number of records is a
> waste
> of disk space.
> Thanks,
> Chris
>

Database size....

Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
ChrisWhen you are about to start the next process, why not drop the database and
re-create it?
On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl, "Chris
Marsh" <cmarsh@.synergy-intl.com> wrote:

> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the tab
le
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when
it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that solv
e
> the size issue?
> A half a GB database that only contains a small number of records is a was
te
> of disk space.
> Thanks,
> Chris
>|||Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
are using temporary tables to stage and then dropping them when you are done
with the import.
Sincerely,
Anthony Thomas
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris|||Have you confirmed if it's the data file or the log file that accounts
for the majority of the DB size? You can find out with a simple
sp_helpdb '<MyDB>'. Perhaps for this process (I'm guessing it's the
transaction log) you should set the recovery model to SIMPLE so that the
log doesn't grow large while preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data into
the master db? That's almost certainly a bad thing to do.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chris Marsh wrote:

>Hi,
>I have a master database that I am using to import data into while we
>migrate and upgrade our customers. Once complete I detach and rename the
>database for my client, attach to their server, etc. However, when I move
>to import the next customers set of data I delete all records from the tabl
e
>and begin the reimport process one after the other. This approach is
>working fine however we are finding that the database is 500+/- MB's when i
t
>should be tiny. Is there a step or process I am missing for the "shrink"
>procedures. Should I "shrink" when the database is empty? Will that solve
>the size issue?
>A half a GB database that only contains a small number of records is a wast
e
>of disk space.
>Thanks,
>Chris
>
>|||Mike,
Thank you for the reply, I poorly worded my question by stating that I was u
sing the "master database". I should have been clear in saying our master da
tabase, I obviously need to come up with a new term for our default, install
database. What I did double-check was that I am using the "simple" method
and withoiut almost any records in the database it appears as if I can get i
t down to 87MB's, I will assume that some of the required data that we inclu
de is what's taking up the space. That's a lot better than when I first sen
t my email out.
I came from the VFP (foxpro) data world and the SQL database is easily 4 - 5
times larger than VFP's.
Thanks again.
Chris
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
i%23KDlgHFHA.3196@.TK2MSFTNGP15.phx.gbl...
Have you confirmed if it's the data file or the log file that accounts for t
he majority of the DB size? You can find out with a simple sp_helpdb '<MyDB
>'. Perhaps for this process (I'm guessing it's the transaction log) you sh
ould set the recovery model to SIMPLE so that the log doesn't grow large whi
le preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data into the
master db? That's almost certainly a bad thing to do.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Chris Marsh wrote:
Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris|||Aaron,
Very interesting thought! Are you suggesting doing that by a script? If
so, then how to I deal with some of the default data that we do not import
but is required to make the application function? In fact, if I may how do
you suggest that one deals with general updates to an application. We are
updating stored procs, tables, views, etc. Currently we have been doing
this by hand but there must be a better method. Any suggestions?
Thanks,
Chris
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE492D4F.1826%ten.xoc@.dnartreb.noraa...
> When you are about to start the next process, why not drop the database
> and
> re-create it?
>
>
> On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl,
> "Chris
> Marsh" <cmarsh@.synergy-intl.com> wrote:
>
>|||Anthony,
Thank you for the reply. That also might be a good option to try.
Chris
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uY3NORgHFHA.2620@.tk2msftngp13.phx.gbl...
> Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
> are using temporary tables to stage and then dropping them when you are
> done
> with the import.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the
> table
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when
> it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that
> solve
> the size issue?
> A half a GB database that only contains a small number of records is a
> waste
> of disk space.
> Thanks,
> Chris
>sql

Database size....

Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris
When you are about to start the next process, why not drop the database and
re-create it?
On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl, "Chris
Marsh" <cmarsh@.synergy-intl.com> wrote:

> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the table
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that solve
> the size issue?
> A half a GB database that only contains a small number of records is a waste
> of disk space.
> Thanks,
> Chris
>
|||Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
are using temporary tables to stage and then dropping them when you are done
with the import.
Sincerely,
Anthony Thomas

"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris
|||Have you confirmed if it's the data file or the log file that accounts
for the majority of the DB size? You can find out with a simple
sp_helpdb '<MyDB>'. Perhaps for this process (I'm guessing it's the
transaction log) you should set the recovery model to SIMPLE so that the
log doesn't grow large while preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data into
the master db? That's almost certainly a bad thing to do.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Chris Marsh wrote:

>Hi,
>I have a master database that I am using to import data into while we
>migrate and upgrade our customers. Once complete I detach and rename the
>database for my client, attach to their server, etc. However, when I move
>to import the next customers set of data I delete all records from the table
>and begin the reimport process one after the other. This approach is
>working fine however we are finding that the database is 500+/- MB's when it
>should be tiny. Is there a step or process I am missing for the "shrink"
>procedures. Should I "shrink" when the database is empty? Will that solve
>the size issue?
>A half a GB database that only contains a small number of records is a waste
>of disk space.
>Thanks,
>Chris
>
>
|||Mike,
Thank you for the reply, I poorly worded my question by stating that I was using the "master database". I should have been clear in saying our master database, I obviously need to come up with a new term for our default, install database. What I did double-check was that I am using the "simple" method and withoiut almost any records in the database it appears as if I can get it down to 87MB's, I will assume that some of the required data that we include is what's taking up the space. That's a lot better than when I first sent my email out.
I came from the VFP (foxpro) data world and the SQL database is easily 4 - 5 times larger than VFP's.
Thanks again.
Chris
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23i%23KDlgHFHA.3196@.TK2MSFTNGP15.phx.gbl...
Have you confirmed if it's the data file or the log file that accounts for the majority of the DB size? You can find out with a simple sp_helpdb '<MyDB>'. Perhaps for this process (I'm guessing it's the transaction log) you should set the recovery model to SIMPLE so that the log doesn't grow large while preparing your customers' data.
Just out of curiosity, is there a reason you're throwing this data into the master db? That's almost certainly a bad thing to do.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Chris Marsh wrote:
Hi,
I have a master database that I am using to import data into while we
migrate and upgrade our customers. Once complete I detach and rename the
database for my client, attach to their server, etc. However, when I move
to import the next customers set of data I delete all records from the table
and begin the reimport process one after the other. This approach is
working fine however we are finding that the database is 500+/- MB's when it
should be tiny. Is there a step or process I am missing for the "shrink"
procedures. Should I "shrink" when the database is empty? Will that solve
the size issue?
A half a GB database that only contains a small number of records is a waste
of disk space.
Thanks,
Chris
|||Aaron,
Very interesting thought! Are you suggesting doing that by a script? If
so, then how to I deal with some of the default data that we do not import
but is required to make the application function? In fact, if I may how do
you suggest that one deals with general updates to an application. We are
updating stored procs, tables, views, etc. Currently we have been doing
this by hand but there must be a better method. Any suggestions?
Thanks,
Chris
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:BE492D4F.1826%ten.xoc@.dnartreb.noraa...
> When you are about to start the next process, why not drop the database
> and
> re-create it?
>
>
> On 2/28/05 6:21 PM, in article uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl,
> "Chris
> Marsh" <cmarsh@.synergy-intl.com> wrote:
>
|||Anthony,
Thank you for the reply. That also might be a good option to try.
Chris
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:uY3NORgHFHA.2620@.tk2msftngp13.phx.gbl...
> Shrink each file individually, not SHRINKDATABASE. In all likelihood, you
> are using temporary tables to stage and then dropping them when you are
> done
> with the import.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:uXH1EweHFHA.896@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a master database that I am using to import data into while we
> migrate and upgrade our customers. Once complete I detach and rename the
> database for my client, attach to their server, etc. However, when I move
> to import the next customers set of data I delete all records from the
> table
> and begin the reimport process one after the other. This approach is
> working fine however we are finding that the database is 500+/- MB's when
> it
> should be tiny. Is there a step or process I am missing for the "shrink"
> procedures. Should I "shrink" when the database is empty? Will that
> solve
> the size issue?
> A half a GB database that only contains a small number of records is a
> waste
> of disk space.
> Thanks,
> Chris
>

Database size entry?

Which system table is the currently defined size (hopefully the total size)
of the data and log devices found?
I'm assuming in master somewhere? sysobjects? I just can't find it...
thanksDave,
Check out:
sysfiles
HTH
Jerry
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
> Which system table is the currently defined size (hopefully the total
> size)
> of the data and log devices found?
> I'm assuming in master somewhere? sysobjects? I just can't find it...
> thanks
>|||That's what I'm doing now.. is that how 'properties' figures the size?
lol: I totally looked past size there, and was just getting the file
names...
Thanks...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Dave,
> Check out:
> sysfiles
> HTH
> Jerry
> "Dave H" <DaveH@.noemail.nospam> wrote in message
> news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
>|||Hi,
Its been taken from sysfiles table. You could just run a prfiler and get the
query. See the query I get for Master database property.
SELECT o.fileid, o.name, o.filename, o.groupid, o.size, o.maxsize, o.growth,
o.status FROM dbo.sysfiles o WHERE o.groupid = (SELECT u.groupid FROM
dbo.sysfilegroups u WHERE u.groupname = N'PRIMARY') and (o.status & 0x40) =
0
go
SELECT fileid, name, filename, size, growth, status, maxsize FROM
dbo.sysfiles WHERE (status & 0x40) <> 0
Thanks
Hari
SQL Server MVP
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:ec-dnRXjTNF0iKHeRVn-rw@.comcast.com...
> That's what I'm doing now.. is that how 'properties' figures the size?
> lol: I totally looked past size there, and was just getting the file
> names...
> Thanks...
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
>

database script error

I just upgraded from sql7.0 to sql2000.
everything seemed to be fine.
but when I click on master database it shows the following
error;
Internet Explorer error
line: 307
char: 2
error: unspecified error
code: 0
url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
and asks me if I wish to conntinue running scripts.
if I create a new database and try to view it, it works
fine, so is this a problem because of the upgrade.
I'm using Internet Explorer 5.5
thanks in advance,
DarrinDarrin,
Had that problem sometime back.Dont know what fixed it - IE version or some
SQL Server service pack.Anyways, here is a workaround - try changing the
view to something other than 'taskpad' temporarily and then return back.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Darrin" <darrin.adams@.eei.ericsson.se> wrote in message
news:0ba001c35062$d936cc10$a501280a@.phx.gbl...
> I just upgraded from sql7.0 to sql2000.
> everything seemed to be fine.
> but when I click on master database it shows the following
> error;
> Internet Explorer error
> line: 307
> char: 2
> error: unspecified error
> code: 0
> url: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
> \Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
> and asks me if I wish to conntinue running scripts.
>
> if I create a new database and try to view it, it works
> fine, so is this a problem because of the upgrade.
> I'm using Internet Explorer 5.5
> thanks in advance,
> Darrin

Database recovery

Had a server crash totaly. Rebuilt a new server with
different drive partitions. Reinstalled SQL 7 and then
restored master from backup. SQL will not start because
it is looking for model,msdb, temp in the original
location which no longer exists. Had this problem on 2000
as well. Any suggestions?You can try to start SQL Server with two undocumented trace flags:
Trace Flag 3608 does not recover any database except the master
Trace Flag 3609 start without TempDB.
Start the service with -f (minimal configuration) option as well.
If this works, attach the databases, except tempdb. Try to move tempdb to a
new location as described in the "Expanding a Database" topic in BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\cr
eatedb.chm::/cm_8_des_03_71d1.htm). If this works, restart SQL Server.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Franco" <franco.carusi@.sbs.siemens.co.za> wrote in message
news:018401c37cdc$b94d0df0$a001280a@.phx.gbl...
> Had a server crash totaly. Rebuilt a new server with
> different drive partitions. Reinstalled SQL 7 and then
> restored master from backup. SQL will not start because
> it is looking for model,msdb, temp in the original
> location which no longer exists. Had this problem on 2000
> as well. Any suggestions?|||Unfortunately a list of undocumented trace flags does not exist in public,
otherwise they would not be undocumented. What you can do is not to restore
the master db after reinstallation, you can just use the "new" one and
attach other databases. But you would have to recreate all logins, map them
to db users etc. The other solution is to call MS support. I think they have
the list:-)
Have you tried with all three, the -f option and both trace flags mentioned
at the same time? I remember few years ago when I had some problems with
tempdb these options forced SQL Sever to skip recovery for all databases,
not just model.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Franco" <franco.carusi@.sbs.siemens.co.za> wrote in message
news:08d101c37cf0$7f7ed680$a401280a@.phx.gbl...
> Tried it and does not work - from the error log it seems
> as if it skipped the model database but is still looking
> for msdb and temp. (previously looking for msdb and
> model). Definately looks as if a step closer. Do you know
> where I can get a list of trace flags?
> >--Original Message--
> >You can try to start SQL Server with two undocumented
> trace flags:
> >Trace Flag 3608 does not recover any database except the
> master
> >Trace Flag 3609 start without TempDB.
> >Start the service with -f (minimal configuration) option
> as well.
> >If this works, attach the databases, except tempdb. Try
> to move tempdb to a
> >new location as described in the "Expanding a Database"
> topic in BOL
> >(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
> 20Server\80\Tools\Books\cr
> >eatedb.chm::/cm_8_des_03_71d1.htm). If this works,
> restart SQL Server.
> >
> >--
> >Dejan Sarka, SQL Server MVP
> >FAQ from Neil & others at: http://www.sqlserverfaq.com
> >Please reply only to the newsgroups.
> >PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >"Franco" <franco.carusi@.sbs.siemens.co.za> wrote in
> message
> >news:018401c37cdc$b94d0df0$a001280a@.phx.gbl...
> >> Had a server crash totaly. Rebuilt a new server with
> >> different drive partitions. Reinstalled SQL 7 and then
> >> restored master from backup. SQL will not start because
> >> it is looking for model,msdb, temp in the original
> >> location which no longer exists. Had this problem on
> 2000
> >> as well. Any suggestions?
> >
> >
> >.
> >

database owner chaining

A question of database owner chaining:
I have a SQL 200 server.
In master the database owner is sa.
In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
The dbo is linked to login sa.
I create a stored procedure in SPOA:
create proc test_OA as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.src varchar(255), @.desc varchar(255)
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
I create a user in SPOA
sp_addlogin sune
sp_adduser sune
grant execute on test_OA to sune
I connect to SPOA as sune and do EXECUTE test_OA
As expected I get: EXECUTE permission denied on object 'sp_OACreate',
database 'master'
I then give user sune role db_owner.
I connect again to SPOA as sune and do EXECUTE test_OA
And I get: EXECUTE permission denied on object 'sp_OACreate', database
'master'
I expected it to work.
What have I missed?
/kCross database ownership chaining turned is off by default in SQL 2000 SP3+.
You can selectively turn it on in your user database using sp_dboption:
EXEC sp_dboption 'SPOA', 'db chaining', true
Note that you should enable cross-database chaining in an sa-owned database
when only symin role members can create dbo-owned objects in that
database.
Hope this helps.
Dan Guzman
SQL Server MVP
"kurt sune" <apa@.apa.com> wrote in message
news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>A question of database owner chaining:
> I have a SQL 200 server.
> In master the database owner is sa.
> In a db called SPOA the database owner is sa. (sp_changedbowner 'sa')
> The dbo is linked to login sa.
> I create a stored procedure in SPOA:
> create proc test_OA as
> DECLARE @.object int
> DECLARE @.hr int
> DECLARE @.src varchar(255), @.desc varchar(255)
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
> IF @.hr <> 0
> BEGIN
> EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
> SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
> RETURN
> END
> I create a user in SPOA
> sp_addlogin sune
> sp_adduser sune
> grant execute on test_OA to sune
> I connect to SPOA as sune and do EXECUTE test_OA
> As expected I get: EXECUTE permission denied on object 'sp_OACreate',
> database 'master'
> I then give user sune role db_owner.
> I connect again to SPOA as sune and do EXECUTE test_OA
> And I get: EXECUTE permission denied on object 'sp_OACreate', database
> 'master'
> I expected it to work.
> What have I missed?
> /k
>|||yes it helped.
THANKS!!!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uG1JAunGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Cross database ownership chaining turned is off by default in SQL 2000
SP3+.
> You can selectively turn it on in your user database using sp_dboption:
> EXEC sp_dboption 'SPOA', 'db chaining', true
> Note that you should enable cross-database chaining in an sa-owned
database
> when only symin role members can create dbo-owned objects in that
> database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "kurt sune" <apa@.apa.com> wrote in message
> news:elLPghnGFHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||Does anyone know what table I need to look into for SQL 2000 which stores db
o's name and in VSQL, IP address & port number.
Many Thanks