Multiples Databases

I need to choose the database on the fly...; how can I do that?RS2000 doesn't support to change the datasoure dynamically.
Hope this feature will be available in RS 2005
Raj
"e" <ezenteno@.prodigy.net.mx> wrote in message
news:uV6Jw4V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I need to choose the database on the fly...; how can I do that?
>|||If you search through old postings in this newsgroup you will find many
related threads about the available options in RS 2000.
RS 2005 supports expression-based connection strings - e.g. as described
here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSRS" <Mahashares@.yahoo.com> wrote in message
news:Ot3b1pX4FHA.1188@.TK2MSFTNGP12.phx.gbl...
> RS2000 doesn't support to change the datasoure dynamically.
> Hope this feature will be available in RS 2005
> Raj
> "e" <ezenteno@.prodigy.net.mx> wrote in message
> news:uV6Jw4V4FHA.2432@.TK2MSFTNGP10.phx.gbl...
>> I need to choose the database on the fly...; how can I do that?
>

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
Simon
Simon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/de..._adda_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>
|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/de..._adda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/de..._adda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegro ups.com...
>
|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
SimonSimon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
>> Hi,
>> I'm currrently running two computers with sql server on and in each
>> server there are a number of databases. If I'm running query analyzer
>> in on server say server 1 how do I access a table in server 2? For
>> example where you would normally say:
>> select * from dababase.owner.table_name how do I specify the
>> actual server?
>> Thanks
>> Simon
>|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple versions of sql server

Hi,
I'm currrently running two computers with sql server on and in each
server there are a number of databases. If I'm running query analyzer
in on server say server 1 how do I access a table in server 2? For
example where you would normally say:
select * from dababase.owner.table_name how do I specify the
actual server?
Thanks
SimonSimon,
The easiest way is to create a linked server to the second SQL Server using
sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the 4-part
naming convention or OPENQUERY to query the remote table. The OPENROWSET
function can be used as well with the creation of a linked server
definition.
For more information see:
sp_addlinkedserver
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
and
sp_addlinkedsrvlogin
http://msdn.microsoft.com/library/d... />
a_6e26.asp
HTH
Jerry
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm currrently running two computers with sql server on and in each
> server there are a number of databases. If I'm running query analyzer
> in on server say server 1 how do I access a table in server 2? For
> example where you would normally say:
> select * from dababase.owner.table_name how do I specify the
> actual server?
> Thanks
> Simon
>|||>The OPENROWSET function can be used as well with the creation of a linked
>server definition.
CORRECTION: should state 'without' the creation of a linked server
definition.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e9z$QGozFHA.3756@.tk2msftngp13.phx.gbl...
> Simon,
> The easiest way is to create a linked server to the second SQL Server
> using sp_addlinkedserver and sp_addlinkedsrvlogin. You can then use the
> 4-part naming convention or OPENQUERY to query the remote table. The
> OPENROWSET function can be used as well with the creation of a linked
> server definition.
> For more information see:
> sp_addlinkedserver
> http://msdn.microsoft.com/library/d...>
dda_8gqa.asp
> and
> sp_addlinkedsrvlogin
> http://msdn.microsoft.com/library/d...>
dda_6e26.asp
> HTH
> Jerry
> "accyboy1981" <accyboy1981@.gmail.com> wrote in message
> news:1129048125.264786.92290@.g44g2000cwa.googlegroups.com...
>|||Hi,
The above two links were really helpful but I'm still having a bit of
difficulty. I've run the following command on one server:
EXEC sp_addlinkedserver
'server_name',
N'SQL Server'
GO
and this will allow me to run the queries, i.e. select * from
server_name.database_name.owner_name.table_name
I've tried to run the same command on the other server just
substituting the server name. However when I try and run any queries I
get the following error message:
SQL Server does not exist or access denied.
As far as I'm aware both servers are setup the same. The only different
is that the names are different on is just a singal name e.g. 'server'
while to other has a slash in e.g. 'server\server'. I've tried every
combination of name but it still doesnt seem to be working.
I now have 2 questions on this.
1: How can I get this server to be recognised?
2: I've now got that many linked servers that dont work in sysservers
how do I delete them?
Thanks
Simon

Multiple Values for single row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int --> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

if it helps..iam using sql server express edition..and iam accessing using asp.net/C#Hi,

Create a contact list table which includes columns,

UserId,
ContactId

Eralper

Multiple TB SQL 2000 Databases

I'm a SQL DBA but have only dealt with databases less than 1 TB in
size. Some of my programmers are asking how well SQL 2000 will handle
a 5 TB database. We're wondering if SQL is sufficient of if we need
to go to Oracle. I know the SQL 2000 specs say it can handle it but
I'm looking for real world experiences.SQL Server can handle much more data than that and does every day if done
properly. I can bring a server to it's knees with a 10MB database with a
poor schema and a poor app etc. With a proper design and planning size
really shouldn't matter.
Andrew J. Kelly SQL MVP
"Charles Crosby" <crosbyct@.yahoo.com> wrote in message
news:8433ccee.0403101124.280f58a8@.posting.google.com...
> I'm a SQL DBA but have only dealt with databases less than 1 TB in
> size. Some of my programmers are asking how well SQL 2000 will handle
> a 5 TB database. We're wondering if SQL is sufficient of if we need
> to go to Oracle. I know the SQL 2000 specs say it can handle it but
> I'm looking for real world experiences.

Multiple Suspect Databases Please help

I administer a server with 300 small databases. It appears to be some type of file corruption and now all the databases are suspect.
I am getting the following errors:
Cannot associate files with differnet databases.
Also: Log file does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Device activation error...
Now I read several online articles and determined the only way I can recover these databases, is using "sp_attach_single_file_db"
Now, with 300 databases, that would take several hours. That is the last thing I want to do.
Can someone please help. I dunno what to do. The backups I have are outdated.What events have occured to get you to this stage? Form the errors you =describe then it sounbds as though the pathnames for one db have become =scrambled with another. I can think of no waythis could occur other than =manually updating system tables incorrectly.
Anyway, if you can describe how you got to this point - as much detail =as possible then someone may well be able to help. Beig able to rescue =the data vis sp-attach is somewhat dodgy as the databases have not been =detached via sp_detach_db. To try it i would copy ALL files (data and =logs) somewhere safe, then generate as script to try the attaches. =(backup master first in case you mess it up)
Alternatively open a call with PSS to get someone helping.
Mike John
"Richard M." <anonymous@.discussions.microsoft.com> wrote in message =news:4E2AD297-67F2-40FC-AE04-2B601E5E5BFD@.microsoft.com...
> I administer a server with 300 small databases. It appears to be some =type of file corruption and now all the databases are suspect. > > I am getting the following errors:
> > Cannot associate files with differnet databases. > > Also: Log file does not match the primary file. It may be from a =different database or the log may have been rebuilt previously.
> > Device activation error...
> > > Now I read several online articles and determined the only way I can =recover these databases, is using "sp_attach_single_file_db"
> > Now, with 300 databases, that would take several hours. That is the =last thing I want to do. > > Can someone please help. I dunno what to do. The backups I have are =outdated.|||Hi ,
THank you for your reply. You are right! The "sp_attach_single_file_db " did work, but with 300 DBs it would take for ever. However, as luck may have it the hard drive on the SQL server containing the OS completely crashed! It went dead. So, the cause seems to be a combination of file curruption due to hardware failure. So, what I did was create a new server and migrated the data and log files just before the server completely died. On the new server, when I installed SQL and attempted to recover by installing the exact version and replacing the existing ystem databases witht he original so the server can see all the databases, the server completely all the databases were suspect. Hence, this means that allt eh files I transferred were corrupted some how. Now, I have two options:
A: Manually use "sp_attach_single_file_db " on all databases to recover it since it seems to work or
B: Bring the old server back online... and attempt to rectify a hand full of suspect databases.
I choose B, however the OS drive was completely dead. Luckily the drivers containing the data files and the otehr drive containg the log files were OK. The server was then rebuilt, with a new OS drive. Win2K server ws installed and surely it saw the other two drives with the data and log files. I then installed SQL server and the updates. As luck may have it, all the databses were ther with an exception of a dozen datasbes still suspect. S, I then applied "sp_attach_single_file_db : to repair those dozen databases and performed an immediate backup for all the databases. Right now, everything seems to be working but I just don't trust those drives. What do you think?
Regards,
Richard|||I would scrsap the old drives and rebuild something like:
Raid 1 System drives.
Raid 0 + 1 for data
Raid 1 for logs (different set of drives to data)
regular log and data backups to differnet discs
NT backup of 'backup discs' to tape.
Sleep at night as you need several discs to go pop at the same time to =get a real problem!
Good luck.
Mike John
"Richard M." <richard@.richardtheman.com> wrote in message =news:297A2516-6BC0-4B23-9F13-ADA4515EF88D@.microsoft.com...
> Hi ,
> > THank you for your reply. You are right! The "sp_attach_single_file_db =" did work, but with 300 DBs it would take for ever. However, as luck =may have it the hard drive on the SQL server containing the OS =completely crashed! It went dead. So, the cause seems to be a =combination of file curruption due to hardware failure. So, what I did =was create a new server and migrated the data and log files just before =the server completely died. On the new server, when I installed SQL and =attempted to recover by installing the exact version and replacing the =existing ystem databases witht he original so the server can see all the =databases, the server completely all the databases were suspect. Hence, =this means that allt eh files I transferred were corrupted some how. =Now, I have two options: > > A: Manually use "sp_attach_single_file_db " on all databases to =recover it since it seems to work or
> > B: Bring the old server back online... and attempt to rectify a hand =full of suspect databases.
> > > I choose B, however the OS drive was completely dead. Luckily the =drivers containing the data files and the otehr drive containg the log =files were OK. The server was then rebuilt, with a new OS drive. Win2K =server ws installed and surely it saw the other two drives with the data =and log files. I then installed SQL server and the updates. As luck may =have it, all the databses were ther with an exception of a dozen =datasbes still suspect. S, I then applied "sp_attach_single_file_db : to =repair those dozen databases and performed an immediate backup for all =the databases. Right now, everything seems to be working but I just =don't trust those drives. What do you think?
> > Regards,
> > Richard > > > > >

Multiple Stored Procedure Execute Together - How?

Hi,
I have multiple stored procedures (SP) running in multiple databases. The
output of all the databases has same column names, same number of columns an
d
column types.
I want to run all these different SP's as one SP and combine the output as
one result.
I tried creating one new SP as SPAll and calling all the SP's in SPAll.
eg.
Create SPAll @.Parameter int
AS
Exec SP1 @.Parameter
Exec SP2 @.Parameter
GO
But when I execute SPAll from 'sql server reporting services' (vs.net), it
executes and displays results from SP1 also. I want to display results from
both SP1 and SP2.
Thanks in advance.Hello GJ.
execute the stored procedures saving the result sets to a temporary table
and then select the results from the temporary table to give you a single
result set
IE:
use Northwind
GO
set nocount on
if object_id('tempdb..#results') is not null drop table #results
create table #results(
index_name sysname,/* Index name. */
index_description varchar(210),/* Index description. */
index_keys nvarchar(2078)/* Table or view column(s) upon which the index is
built. */
)
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Categories'
insert #results(index_name,index_description,in
dex_keys)
exec sp_helpindex @.objname = 'dbo.Customers'
select * from #results
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"GJ" wrote:

> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results fro
m
> both SP1 and SP2.
> Thanks in advance.|||Best way, based on what you are telling us:
Create SPAll @.Parameter int
AS
create table #spAllReturn
(
<columns that match procs>
)
insert into #spAllReturn
Exec SP1 @.Parameter
insert into #spAllReturn
Exec SP2 @.Parameter
select * from #spAllReturn
GO
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
> and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
> from
> both SP1 and SP2.
> Thanks in advance.|||GJ
create table #test
(
col ...
....
....
)
insert into #test exec sp1
insert into #test exec sp2
select * from #test
"GJ" <GJ@.discussions.microsoft.com> wrote in message
news:AA68D163-4681-4794-B4F0-748E72C18760@.microsoft.com...
> Hi,
> I have multiple stored procedures (SP) running in multiple databases. The
> output of all the databases has same column names, same number of columns
and
> column types.
> I want to run all these different SP's as one SP and combine the output as
> one result.
> I tried creating one new SP as SPAll and calling all the SP's in SPAll.
> eg.
> Create SPAll @.Parameter int
> AS
> Exec SP1 @.Parameter
> Exec SP2 @.Parameter
> GO
> But when I execute SPAll from 'sql server reporting services' (vs.net), it
> executes and displays results from SP1 also. I want to display results
from
> both SP1 and SP2.
> Thanks in advance.

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this ?
Rgds
MS-USER
Hi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another area
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:

> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this ?
> Rgds
> MS-USER
>
>

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this '
Rgds
MS-USERHi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another area
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:
> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this '
> Rgds
> MS-USER
>
>

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this '
Rgds
MS-USERHi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another are
a
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:

> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-1
5
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this '
> Rgds
> MS-USER
>
>