Relationship between tables of two database

Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-aviAvinash, why don't you create primary key and foreign key constraints to implement referential integrity. That's a better approach than triggers.

gyan.

Originally posted by avishesh
Hi All'

I'm in process of upgrading our Microsoft Access Database to SQL Server 2000. We have one front end database that links all the backend databases. But there are some databases which shares tables with other databases. Currently the refrential integrity is being done by VBA codes in the forms itself (bad na!).

Example
----
Database: Vehicle
Tables in "Vehicle" database are VechileType (v_type, v_desc) and VehicleInventory (v_RegNo, v_Type, customerID)

Database: Customer
Tables in "Customer" database are CustomerType(c_type, c_desc) and CustomerInventory (customerID, customerName, c_type).

This is just example...there are many (more than 10!) tables in each database. So, I do NOT want to place everything in a single database.

Now I'm looking solution for creating trigger that ensures the Refrential Integrity on "customerID" field in both VehicleInventory and CustomerInventory tables. eg user can not delete customerID from CustomerInventory table if its record exist in VehicleInventory table.

CAN ANY ONE HELP ME..................

Thanks

cheers'

-avi :)

Reinstalling database devices

Hello,
I am experiencing the following problem: The SAs were
upgrading apps on the server and the server literally ate
itself. Thus requiring a reinstall of SQL Server.
However, the data and log files are still in tact on the
box. The backup tapes are empty and I am trying to
recreate the devices and databases using the existing
files. Is that possible? If yes, how would I proceed?
Thanks.You can hopefully attach the database file using sp_attach_db.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Hi Albert, if the old directories are still valid, replacing the new master
with old one will be enough: it will point to your data files.
Another option (if the old master is not available) would be using
sp_attach_db to fill the new master with the information about your old db
data files.
The first option is better because the old master will be on-line with all
of your configuration options, logins, etc.
Syntax about sp_attach_db is bellow (from books on line):
sp_attach_db
Attaches a database to a server.
Syntax
sp_attach_db [ @.dbname = ] 'dbname'
, [ @.filename1 = ] 'filename_n' [ ,...16 ]
Arguments
[@.dbname =] 'dbname'
Is the name of the database to be attached to the server. The name must be
unique. dbname is sysname, with a default of NULL.
[@.filename1 =] 'filename_n'
Is the physical name, including path, of a database file. filename_n is
nvarchar(260), with a default of NULL. There can be up to 16 file names
specified. The parameter names start at @.filename1 and increment to
@.filename16. The file name list must include at least the primary file,
which contains the system tables that point to other files in the database.
The list must also include any files that were moved after the database was
detached.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_attach_db should only be executed on databases that were previously
detached from the database server using an explicit sp_detach_db operation.
If more than 16 files must be specified, use CREATE DATABASE with the FOR
ATTACH clause.
If you attach a database to a server other than the server from which the
database was detached, and the detached database was enabled for
replication, you should run sp_removedbreplication to remove replication
from the database.
Permissions
Only members of the sysadmin and dbcreator fixed server roles can execute
this procedure.
Examples
This example attaches two files from pubs to the current server.
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
hth,
Roberto de Souza Santos.
"Albert H. Offer" <ahoffer@.magellanhealth.com> wrote in message
news:0ebe01c38f34$df72f430$a101280a@.phx.gbl...
> Hello,
> I am experiencing the following problem: The SAs were
> upgrading apps on the server and the server literally ate
> itself. Thus requiring a reinstall of SQL Server.
> However, the data and log files are still in tact on the
> box. The backup tapes are empty and I am trying to
> recreate the devices and databases using the existing
> files. Is that possible? If yes, how would I proceed?
> Thanks.|||Thanks Tibor. I forgot to mention that the box is running SQL Server
6.5.. The backup tapes are no good, so I don't have a good backup of
Master.
Albert H. Offer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Then you need to use DISK REINIT and DISK REFIT. Make sure you read all you can find about these
commands. It is not even closely as easy as in 7.0 or 2000 as you need to get the database fragments
right with DISK REINIT.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Albert Offer" <ahoffer@.magellanhealth.com> wrote in message
news:eFLcGN0jDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor. I forgot to mention that the box is running SQL Server
> 6.5.. The backup tapes are no good, so I don't have a good backup of
> Master.
> Albert H. Offer
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

reindexing slow after installing SQL 2K SP4?

I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?There is an issue with AWE in SP4 - it stops using it. Get the post-SP4 hot
fix:
http://support.microsoft.com/kb/899761
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?|||Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||That's odd. How much RAM do you have on each node and let us know whether
the /3GB switch is/isn't used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>|||Just wanted to sure. What's odd is that it appears to run differently on
each node. Also, of the 8 GB, how much did you tell it to use for SQL
Server? Could you give us the sp_configure output for max server memory and
AWE?
Are there other processes running on one node and not the other?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>|||Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||6GB = 6,144MB, so you're just a little short of using the 6GB. Leaving the
remaining 2GB to the OS is plenty.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OxSToz2qGHA.3564@.TK2MSFTNGP03.phx.gbl...
Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||If you were using AWE, yes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:enizG02qGHA.2180@.TK2MSFTNGP05.phx.gbl...
If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>|||Your max server memory should be at 6 GB = 6,144 MB. Not sure why it's
below that. The only other thing I can think of is that you may have a
Windows hotfix that was applied to one box and not the other.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:ea$m452qGHA.4864@.TK2MSFTNGP03.phx.gbl...
I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>> That's odd. How much RAM do you have on each node and let us know
>> whether
>> the /3GB switch is/isn't used.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
>> Thanks. I actually forgot to mention that I did install the Post SP 4
>> Hotfix rollup. My SQL Version is at 8.00.2187.
>> However, I did check Perfmon and SQL is using up more than 50% of the
>> physical memory, which I think, according to the article, means that it
>> is
>> running normally.
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
>> hot
>> fix:
>> http://support.microsoft.com/kb/899761
>>
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "Nieves" <JuanN@.yahoo.com> wrote in message
>> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
>> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
>> SP3
>> to SP4, my re-indexing job takes 3-4 times longer than before. The
>> amount
>> of data is about the same. Nothing else within SQL has changed. I am
>> running SQL on the other node, but the hardware is identical and I
>> checked
>> the boot.ini (for enabling AWE) file and it's the same on boh boxes
>> also.
>> Has anyone experienced this after installing Service Pack 4?
>>
>>
>

reindexing slow after installing SQL 2K SP4?

I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?There is an issue with AWE in SP4 - it stops using it. Get the post-SP4 hot
fix:
http://support.microsoft.com/kb/899761
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from SP3
to SP4, my re-indexing job takes 3-4 times longer than before. The amount
of data is about the same. Nothing else within SQL has changed. I am
running SQL on the other node, but the hardware is identical and I checked
the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
Has anyone experienced this after installing Service Pack 4?|||Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||That's odd. How much RAM do you have on each node and let us know whether
the /3GB switch is/isn't used.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
Thanks. I actually forgot to mention that I did install the Post SP 4
Hotfix rollup. My SQL Version is at 8.00.2187.
However, I did check Perfmon and SQL is using up more than 50% of the
physical memory, which I think, according to the article, means that it is
running normally.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> There is an issue with AWE in SP4 - it stops using it. Get the post-SP4
> hot
> fix:
> http://support.microsoft.com/kb/899761
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OYQCGUbqGHA.928@.TK2MSFTNGP04.phx.gbl...
> I'm using SQL 2000 Enterprise Edition (Clustered). After upgrading from
> SP3
> to SP4, my re-indexing job takes 3-4 times longer than before. The amount
> of data is about the same. Nothing else within SQL has changed. I am
> running SQL on the other node, but the hardware is identical and I checked
> the boot.ini (for enabling AWE) file and it's the same on boh boxes also.
> Has anyone experienced this after installing Service Pack 4?
>|||Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>|||Just wanted to sure. What's odd is that it appears to run differently on
each node. Also, of the 8 GB, how much did you tell it to use for SQL
Server? Could you give us the sp_configure output for max server memory and
AWE?
Are there other processes running on one node and not the other?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
Thanks,
There's 8 GB of RAM on each node. The 3/GB switch is not used and this was
actually PER Microsoft.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
> That's odd. How much RAM do you have on each node and let us know whether
> the /3GB switch is/isn't used.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:OZ4SudcqGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Thanks. I actually forgot to mention that I did install the Post SP 4
> Hotfix rollup. My SQL Version is at 8.00.2187.
> However, I did check Perfmon and SQL is using up more than 50% of the
> physical memory, which I think, according to the article, means that it is
> running normally.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ux$ot8bqGHA.4032@.TK2MSFTNGP03.phx.gbl...
>|||Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||If I were to increase the Max Memory, would I need to restart SQL service
for it to take effect?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||I just ran a perfmon and measured SQLServer:Memory Manager - Target Server
Memory and Total Server Memory. It's at a steady 5 GB. My Max memory is
set to 6 GB. Is this normal?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>|||6GB = 6,144MB, so you're just a little short of using the 6GB. Leaving the
remaining 2GB to the OS is plenty.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Nieves" <JuanN@.yahoo.com> wrote in message
news:OxSToz2qGHA.3564@.TK2MSFTNGP03.phx.gbl...
Thanks so much for your help Tom.
There are no processes running on one node that's not running on the other.
SQL Server is configured to use 6 GB - again, this was suggested by
microsoft when we were troubleshooting another problem several months back.
Here's the sp_configure output
max server memory (MB) 4 2147483647 6008
6008
awe enabled 0 1 1 1
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Jp05DpqGHA.4032@.TK2MSFTNGP03.phx.gbl...
> Just wanted to sure. What's odd is that it appears to run differently on
> each node. Also, of the 8 GB, how much did you tell it to use for SQL
> Server? Could you give us the sp_configure output for max server memory
> and
> AWE?
> Are there other processes running on one node and not the other?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Nieves" <JuanN@.yahoo.com> wrote in message
> news:%23$5lw3oqGHA.2108@.TK2MSFTNGP03.phx.gbl...
> Thanks,
> There's 8 GB of RAM on each node. The 3/GB switch is not used and this
> was
> actually PER Microsoft.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:euOlQmcqGHA.4992@.TK2MSFTNGP05.phx.gbl...
>

Registered Servers are unavailable

So I've had some issues upgrading to SQL Server Mgmt Studio Dev from Express. I finally got everything upgraded / installed and I see the instances that I was using with Express in my Registered Servers window but I can't connect to any of them. After looking in my Microsoft SQL Server directory I noticed that there are no MSSQL.# folders. How can I recreate these or create a new instance on my local machine?

Hi,

Do you mean that you want to import the SQLExpress database intoSQL Server management Stuido Expresswhich provides a graphical management tool for SQL Server 2005 Express Edition (SQL Server Express).? If so, you can use "aspnet_regsql" tool and try again, the tool exists in DiskRoot:\WINDOWS\Microsoft.NET\Framework\versionEdtion\.

Thanks.