Moving tempdb

Hi,
We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
Tempdb is around 5GB or 7GB in size and is killing performance.
We want to move tempdb to a volume on the machine (e.g. T which has 40GB
free) so that we do not cause any problems on C:\.
Can you please tell me how we do this. Maybe links on the web with step by
step instructions would be useful.
SkcYou will find it in BOL, look for "Moving tempdb".
AMB
"Skc" wrote:
> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step by
> step instructions would be useful.
> Skc|||there is an example in BOL, under "ALTER DATABASE", look for "Move the tempdb
database".
AMB
"Alejandro Mesa" wrote:
> You will find it in BOL, look for "Moving tempdb".
>
> AMB
>
> "Skc" wrote:
> > Hi,
> >
> > We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> > Tempdb is around 5GB or 7GB in size and is killing performance.
> >
> > We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> > free) so that we do not cause any problems on C:\.
> >
> > Can you please tell me how we do this. Maybe links on the web with step by
> > step instructions would be useful.
> >
> > Skc|||As I replied within .setup:
The steps are described within this KB article:
http://support.microsoft.com/?id=224071
--
Keith
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:3DA1E004-3A30-4524-BCE9-539F3EBF1123@.microsoft.com...
> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step
by
> step instructions would be useful.
> Skcsql

Moving tempdb

Hi,
We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
Tempdb is around 5GB or 7GB in size and is killing performance.
We want to move tempdb to a volume on the machine (e.g. T which has 40GB
free) so that we do not cause any problems on C:\.
Can you please tell me how we do this. Maybe links on the web with step by
step instructions would be useful.
Skc
You will find it in BOL, look for "Moving tempdb".
AMB
"Skc" wrote:

> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step by
> step instructions would be useful.
> Skc
|||there is an example in BOL, under "ALTER DATABASE", look for "Move the tempdb
database".
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You will find it in BOL, look for "Moving tempdb".
>
> AMB
>
> "Skc" wrote:
|||As I replied within .setup:
The steps are described within this KB article:
http://support.microsoft.com/?id=224071
Keith
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:3DA1E004-3A30-4524-BCE9-539F3EBF1123@.microsoft.com...
> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step
by
> step instructions would be useful.
> Skc

Moving tempdb

Hi,
We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
Tempdb is around 5GB or 7GB in size and is killing performance.
We want to move tempdb to a volume on the machine (e.g. T which has 40GB
free) so that we do not cause any problems on C:\.
Can you please tell me how we do this. Maybe links on the web with step by
step instructions would be useful.
SkcYou will find it in BOL, look for "Moving tempdb".
AMB
"Skc" wrote:

> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step b
y
> step instructions would be useful.
> Skc|||there is an example in BOL, under "ALTER DATABASE", look for "Move the tempd
b
database".
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> You will find it in BOL, look for "Moving tempdb".
>
> AMB
>
> "Skc" wrote:
>|||As I replied within .setup:
The steps are described within this KB article:
http://support.microsoft.com/?id=224071
Keith
"Skc" <Skc@.discussions.microsoft.com> wrote in message
news:3DA1E004-3A30-4524-BCE9-539F3EBF1123@.microsoft.com...
> Hi,
> We have a Dell PowerEdge with 12GB assigned to C. We have noticed that
> Tempdb is around 5GB or 7GB in size and is killing performance.
> We want to move tempdb to a volume on the machine (e.g. T which has 40GB
> free) so that we do not cause any problems on C:\.
> Can you please tell me how we do this. Maybe links on the web with step
by
> step instructions would be useful.
> Skc

Moving SQL Server 2005 x86 from Dell 1950 to Dell 2950 running SQL2005 x64

Hi,
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?
On Apr 9, 4:57Xam, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
>
>
>
> Hi
> If you are using windows security and the access is granted by membership of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.

Moving SQL Server 2005 x86 from Dell 1950 to Dell 2950 running SQL

Hi,
I'm trying to figure out the best way to do this with the less
headache. I have a SQL 2005 server, let's call it SQL01. It is on a
Dell 1950. I want to move the SQL services to a Dell 2950 running 64
bit OS and SQL 2005 x64 but I want to keep the server name the same
and also retain login permissions on the databases that get moved.
I'm trying to see if I can leverage Backup Exec for this. So I would:
1. Perform a Full SQL Database backup on server SQL01 with Backup
Exec.
2. Remove Dell 1950 SQL01 server from Domain and reset computer
account in AD.
3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
including any patches.
4. Perform SQL restore on new SQL01 server with Backup Exec
5. Bring up applications and test applications.
Is this reasonable or not?
Is there a better way?"Jose A. Hernandez" <jhboricua@.gmail.com> wrote in message
news:b48aa821-eb8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
> Hi,
> I'm trying to figure out the best way to do this with the less
> headache. I have a SQL 2005 server, let's call it SQL01. It is on a
> Dell 1950. I want to move the SQL services to a Dell 2950 running 64
> bit OS and SQL 2005 x64 but I want to keep the server name the same
> and also retain login permissions on the databases that get moved.
> I'm trying to see if I can leverage Backup Exec for this. So I would:
> 1. Perform a Full SQL Database backup on server SQL01 with Backup
> Exec.
> 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> account in AD.
> 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> including any patches.
> 4. Perform SQL restore on new SQL01 server with Backup Exec
> 5. Bring up applications and test applications.
> Is this reasonable or not?
> Is there a better way?
Hi
If you are using windows security and the access is granted by membership of
domain groups your transfer of logins will be the simplest.
http://support.microsoft.com/kb/314546 is a good place to start. Don't
forget jobs and SSIS packages if you have them.
John|||On Apr 9, 4:57=A0am, "John Bell" <jbellnewspo...@.hotmail.com> wrote:
> "Jose A. Hernandez" <jhbori...@.gmail.com> wrote in messagenews:b48aa821-eb=8d-4b78-be58-cc20b7283745@.x41g2000hsb.googlegroups.com...
>
>
> > Hi,
> > I'm trying to figure out the best way to do this with the less
> > headache. =A0I have a SQL 2005 server, let's call it SQL01. =A0It is on =a
> > Dell 1950. =A0I want to move the SQL services to a Dell 2950 running 64
> > bit OS and SQL 2005 x64 but I want to keep the server name the same
> > and also retain login permissions on the databases that get moved.
> > I'm trying to see if I can leverage Backup Exec for this. =A0So I would:=
> > 1. Perform a Full SQL Database backup on server SQL01 with Backup
> > Exec.
> > 2. Remove Dell 1950 SQL01 server from Domain and reset computer
> > account in AD.
> > 3. Join Dell 2950 to domain as SQL01 and Install SQL 2005 x64,
> > including any patches.
> > 4. Perform SQL restore on new SQL01 server with Backup Exec
> > 5. Bring up applications and test applications.
> > Is this reasonable or not?
> > Is there a better way?
> Hi
> If you are using windows security and the access is granted by membership =of
> domain groups your transfer of logins will be the simplest.
> http://support.microsoft.com/kb/314546is a good place to start. Don't
> forget jobs and SSIS packages if you have them.
> John- Hide quoted text -
> - Show quoted text -
Thanks for the KBs John, I'll check them up.

Moving Publication Databases

I have a silly problem that could have been solved months ago if it was
caught, but it wasn't.
I purchased a Dell Poweredge as my server to host publication databases that
a string of retail stores synchronize (pull merge subscriptions) with to
share customer information. The developer of the software that utilizes the
databases set up the initial databases and publications, and the first
subscriptions. We don't have an IT department, and I am the person who knows
the most about computers out of a very computer illiterate employee base, so
I have become the SQL goto guy.
Since then (it's been about 4 months) I have learned enough about SQL to be
dangerous and have found a problem. Dell ships it's Servers with a small hard
drive (10GB) as the operating system drive (C drive), and the larger mirrored
drive (60GB) is separate from this (D Drive). The software developer created
the publication databases on the small drive and I am getting dangerously
close to the size limit, while leaving the mirrored "safe" drive virtually
empty.
Here is the question, is there a way to move the publications to the larger
drive without dropping the subscriptions and having to recreate the
publications? At the moment I have 16 retail stores subscribed, and they are
not close together, and they are open 18 hours a day (some are 24 hours). If
I need to drop the subscriptions, download the snapshot to each remote site,
and then recreate the subscription, it will be a disaster; the zipped
snapshot file is 215MB at the moment. Can I move the databases to the D Drive
while maintaining the current subscriptions?
Hopefully someone can help me with my problem.
no, but you can add filegroups to the data file and then migrate tables and
indexes to this filegroup. You can also add a filegroup to the tlog and then
shrink the existing one, and cap how large it can grow.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:1DB4433F-833E-4B6B-9D6F-22621EE880FD@.microsoft.com...
> I have a silly problem that could have been solved months ago if it was
> caught, but it wasn't.
> I purchased a Dell Poweredge as my server to host publication databases
that
> a string of retail stores synchronize (pull merge subscriptions) with to
> share customer information. The developer of the software that utilizes
the
> databases set up the initial databases and publications, and the first
> subscriptions. We don't have an IT department, and I am the person who
knows
> the most about computers out of a very computer illiterate employee base,
so
> I have become the SQL goto guy.
> Since then (it's been about 4 months) I have learned enough about SQL to
be
> dangerous and have found a problem. Dell ships it's Servers with a small
hard
> drive (10GB) as the operating system drive (C drive), and the larger
mirrored
> drive (60GB) is separate from this (D Drive). The software developer
created
> the publication databases on the small drive and I am getting dangerously
> close to the size limit, while leaving the mirrored "safe" drive virtually
> empty.
> Here is the question, is there a way to move the publications to the
larger
> drive without dropping the subscriptions and having to recreate the
> publications? At the moment I have 16 retail stores subscribed, and they
are
> not close together, and they are open 18 hours a day (some are 24 hours).
If
> I need to drop the subscriptions, download the snapshot to each remote
site,
> and then recreate the subscription, it will be a disaster; the zipped
> snapshot file is 215MB at the moment. Can I move the databases to the D
Drive
> while maintaining the current subscriptions?
> Hopefully someone can help me with my problem.
|||Hmmm, I think that would help to restrict growth, but I need to move all of
the databases to the larger hard drive. The large hard drive is mirrored
(RAID) and is therefore "safer" than the unprotected smaller drive. Plus, I
may need the space on the smaller drive for program upgrades and installs in
the future.
I read something in one of the threads about moving the file and, if it
hasn't been changed since the last synch, then it doesn't need to synch on
initialization...how is this done? My biggest problem is the synch on
reinitialization when I recreate the subscription, it would take too long to
download the snapshot to all the remote sites.
So, if I detach and then attach the publication database in another
location, but on the same server, can the subscribers reinitialize without
synch'ing if there haven't been any changes?
"Hilary Cotter" wrote:

> no, but you can add filegroups to the data file and then migrate tables and
> indexes to this filegroup. You can also add a filegroup to the tlog and then
> shrink the existing one, and cap how large it can grow.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Pete" <Pete@.discussions.microsoft.com> wrote in message
> news:1DB4433F-833E-4B6B-9D6F-22621EE880FD@.microsoft.com...
> that
> the
> knows
> so
> be
> hard
> mirrored
> created
> larger
> are
> If
> site,
> Drive
>
>
|||When I said adding a file group to tempdb or any other database, I intended
for this filegroup to be on the larger drive.
You can't detach databases which are published for replication. You could
1) Kick all users off the system(s)
2) schedule the merge agents or remove the -Continuous switch, stop and
start the agents and then run then until they stop
3) script out the publication
4) remove the publications
5) unpublish the database
6) detach the databases
7) retach them on the larger drive
8) recreate the publications
9) do a no-sync subscription (the subscriber already has the schema and
data)
10) when the metadata snapshot has been applied, allow your users back on
The no-sync subscription method seems to be the "moving the file and, if it
hasn't been changed since the last synch, then it doesn't need to synch on
initialization..." method you are talking about.
The problem with this is that if there are updates going on on your
subscriber you are asking for trouble.
The good news is that the snapshot with the no-sync subscription is very
small and can be created and distributed very quickly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:58A5855C-EB3B-4AB0-A4FC-0C85934087C3@.microsoft.com...
> Hmmm, I think that would help to restrict growth, but I need to move all
of
> the databases to the larger hard drive. The large hard drive is mirrored
> (RAID) and is therefore "safer" than the unprotected smaller drive. Plus,
I
> may need the space on the smaller drive for program upgrades and installs
in
> the future.
> I read something in one of the threads about moving the file and, if it
> hasn't been changed since the last synch, then it doesn't need to synch on
> initialization...how is this done? My biggest problem is the synch on
> reinitialization when I recreate the subscription, it would take too long
to[vbcol=seagreen]
> download the snapshot to all the remote sites.
> So, if I detach and then attach the publication database in another
> location, but on the same server, can the subscribers reinitialize without
> synch'ing if there haven't been any changes?
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
then[vbcol=seagreen]
was[vbcol=seagreen]
databases[vbcol=seagreen]
to[vbcol=seagreen]
utilizes[vbcol=seagreen]
base,[vbcol=seagreen]
to[vbcol=seagreen]
small[vbcol=seagreen]
dangerously[vbcol=seagreen]
virtually[vbcol=seagreen]
they[vbcol=seagreen]
hours).[vbcol=seagreen]
D[vbcol=seagreen]
|||I think that's what I need to do, a no-synch subscription. So, as long as no
changes are made between the time I detach the publication and then recreate
it and reinitialize it on the larger drive then I shouldn't have any problems?
"Hilary Cotter" wrote:

> When I said adding a file group to tempdb or any other database, I intended
> for this filegroup to be on the larger drive.
> You can't detach databases which are published for replication. You could
> 1) Kick all users off the system(s)
> 2) schedule the merge agents or remove the -Continuous switch, stop and
> start the agents and then run then until they stop
> 3) script out the publication
> 4) remove the publications
> 5) unpublish the database
> 6) detach the databases
> 7) retach them on the larger drive
> 8) recreate the publications
> 9) do a no-sync subscription (the subscriber already has the schema and
> data)
> 10) when the metadata snapshot has been applied, allow your users back on
> The no-sync subscription method seems to be the "moving the file and, if it
> hasn't been changed since the last synch, then it doesn't need to synch on
> initialization..." method you are talking about.
> The problem with this is that if there are updates going on on your
> subscriber you are asking for trouble.
> The good news is that the snapshot with the no-sync subscription is very
> small and can be created and distributed very quickly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Pete" <Pete@.discussions.microsoft.com> wrote in message
> news:58A5855C-EB3B-4AB0-A4FC-0C85934087C3@.microsoft.com...
> of
> I
> in
> to
> and
> then
> was
> databases
> to
> utilizes
> base,
> to
> small
> dangerously
> virtually
> they
> hours).
> D
>
>
|||Pete,
one caveat about nosync initializations -
sp_repladdcolumn or sp_repldropcolumn can't be used
afterwards for the published articles.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||yes, if you can guarantee that all users are off your subscribers while you
are making these changes you should be fine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:FCBD98A4-2984-4298-9CAA-9B8026195D2E@.microsoft.com...
> I think that's what I need to do, a no-synch subscription. So, as long as
no
> changes are made between the time I detach the publication and then
recreate
> it and reinitialize it on the larger drive then I shouldn't have any
problems?[vbcol=seagreen]
> "Hilary Cotter" wrote:
intended[vbcol=seagreen]
could[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
all[vbcol=seagreen]
mirrored[vbcol=seagreen]
Plus,[vbcol=seagreen]
installs[vbcol=seagreen]
it[vbcol=seagreen]
synch on[vbcol=seagreen]
long[vbcol=seagreen]
without[vbcol=seagreen]
tables[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
with[vbcol=seagreen]
first[vbcol=seagreen]
who[vbcol=seagreen]
employee[vbcol=seagreen]
SQL[vbcol=seagreen]
larger[vbcol=seagreen]
developer[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
remote[vbcol=seagreen]
zipped[vbcol=seagreen]
the[vbcol=seagreen]
|||Thanks for the help Hillary, much appreciated :-)
Paul,
What if I reinitialize them with synch (and a full snapshot download)
gradually over the weeks after the initial no-synch reinitialization? Will
that correct the caveat?
"Paul Ibison" wrote:

> Pete,
> one caveat about nosync initializations -
> sp_repladdcolumn or sp_repldropcolumn can't be used
> afterwards for the published articles.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Using sp_dropsubscription for a separate article and
sp_addsubscription with @.sync_type = automatic (followed
by running the snapshot agent and the distribution agent)
should work.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)