Is anybody has experience to DBA more than 100 SQl Servers at a time?
If so what are the main startegy and main challenges you had? what is
your best practice in terms of day to day admin and backup and
recovery?soalvajavab1@.yahoo.com wrote:
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
I don't think that tasks are that much different when you have 5 or 100
SQL servers, so could you be a bit more specific in what you'd like to
know? From my point of view it's quite important that the backup jobs
works. I've written my own scripts that automatically backups up all
databases on a SQL server, so once these has been scheduled they just
notify me when they fails. You'd then also like to monitor the free
space on your disks on the server, but for that the company most likely
has a monitoring tool for that.
In terms of what more you'd like to do is a matter of personal
preference and company policy. E.g. I know that many people don't like
to have their databases set to autogrow, but I normally do that for most
databases. Currently I'm looking after approx. 1000 SQL server databases
and for me it works fine to have them set to autogrow. I've then created
a stored proc that runs every night on most of the servers and collect a
number of data for each database. This tells me when there has been
created new databases, when a database has been deleted, file growth,
backup file growth, recovery mode change etc. I then get an email every
morning with the changes.
Apart from the above, there are a number of database monitoring tools
available on the market. I haven't actually used any of them yet but I
think they can do some of what I've done myself (..and of course a lot
more...). Maybe somebody else has some experience on this?
Bottomline is that what you need to look for isn't a standard answer,
but pretty much depends on the setup and your company/company policy. I
also think that the DBA role can be quite different from one company to
another.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||I think that with that number-- you'll just need to follow these guidelines:
a) use profiler on all machines
b) run index tuning wizard on your busiest databases
c) build poormon enterprise level performan aggregation tools
d) encourage people to use Reporting Services and Analysis Services in order
to leverage this mountain of data
<soalvajavab1@.yahoo.com> wrote in message
news:1177946795.389483.51580@.y80g2000hsf.googlegroups.com...
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>sql
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Thursday, March 29, 2012
DBA for multiple servers
Is anybody has experience to DBA more than 100 SQl Servers at a time?
If so what are the main startegy and main challenges you had? what is
your best practice in terms of day to day admin and backup and
recovery?soalvajavab1@.yahoo.com wrote:
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
I don't think that tasks are that much different when you have 5 or 100
SQL servers, so could you be a bit more specific in what you'd like to
know? From my point of view it's quite important that the backup jobs
works. I've written my own scripts that automatically backups up all
databases on a SQL server, so once these has been scheduled they just
notify me when they fails. You'd then also like to monitor the free
space on your disks on the server, but for that the company most likely
has a monitoring tool for that.
In terms of what more you'd like to do is a matter of personal
preference and company policy. E.g. I know that many people don't like
to have their databases set to autogrow, but I normally do that for most
databases. Currently I'm looking after approx. 1000 SQL server databases
and for me it works fine to have them set to autogrow. I've then created
a stored proc that runs every night on most of the servers and collect a
number of data for each database. This tells me when there has been
created new databases, when a database has been deleted, file growth,
backup file growth, recovery mode change etc. I then get an email every
morning with the changes.
Apart from the above, there are a number of database monitoring tools
available on the market. I haven't actually used any of them yet but I
think they can do some of what I've done myself (..and of course a lot
more...). Maybe somebody else has some experience on this?
Bottomline is that what you need to look for isn't a standard answer,
but pretty much depends on the setup and your company/company policy. I
also think that the DBA role can be quite different from one company to
another.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||I think that with that number-- you'll just need to follow these guidelines:
a) use profiler on all machines
b) run index tuning wizard on your busiest databases
c) build poormon enterprise level performan aggregation tools
d) encourage people to use Reporting Services and Analysis Services in order
to leverage this mountain of data
<soalvajavab1@.yahoo.com> wrote in message
news:1177946795.389483.51580@.y80g2000hsf.googlegroups.com...
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
If so what are the main startegy and main challenges you had? what is
your best practice in terms of day to day admin and backup and
recovery?soalvajavab1@.yahoo.com wrote:
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
I don't think that tasks are that much different when you have 5 or 100
SQL servers, so could you be a bit more specific in what you'd like to
know? From my point of view it's quite important that the backup jobs
works. I've written my own scripts that automatically backups up all
databases on a SQL server, so once these has been scheduled they just
notify me when they fails. You'd then also like to monitor the free
space on your disks on the server, but for that the company most likely
has a monitoring tool for that.
In terms of what more you'd like to do is a matter of personal
preference and company policy. E.g. I know that many people don't like
to have their databases set to autogrow, but I normally do that for most
databases. Currently I'm looking after approx. 1000 SQL server databases
and for me it works fine to have them set to autogrow. I've then created
a stored proc that runs every night on most of the servers and collect a
number of data for each database. This tells me when there has been
created new databases, when a database has been deleted, file growth,
backup file growth, recovery mode change etc. I then get an email every
morning with the changes.
Apart from the above, there are a number of database monitoring tools
available on the market. I haven't actually used any of them yet but I
think they can do some of what I've done myself (..and of course a lot
more...). Maybe somebody else has some experience on this?
Bottomline is that what you need to look for isn't a standard answer,
but pretty much depends on the setup and your company/company policy. I
also think that the DBA role can be quite different from one company to
another.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||I think that with that number-- you'll just need to follow these guidelines:
a) use profiler on all machines
b) run index tuning wizard on your busiest databases
c) build poormon enterprise level performan aggregation tools
d) encourage people to use Reporting Services and Analysis Services in order
to leverage this mountain of data
<soalvajavab1@.yahoo.com> wrote in message
news:1177946795.389483.51580@.y80g2000hsf.googlegroups.com...
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
DBA for multiple servers
Is anybody has experience to DBA more than 100 SQl Servers at a time?
If so what are the main startegy and main challenges you had? what is
your best practice in terms of day to day admin and backup and
recovery?
soalvajavab1@.yahoo.com wrote:
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
I don't think that tasks are that much different when you have 5 or 100
SQL servers, so could you be a bit more specific in what you'd like to
know? From my point of view it's quite important that the backup jobs
works. I've written my own scripts that automatically backups up all
databases on a SQL server, so once these has been scheduled they just
notify me when they fails. You'd then also like to monitor the free
space on your disks on the server, but for that the company most likely
has a monitoring tool for that.
In terms of what more you'd like to do is a matter of personal
preference and company policy. E.g. I know that many people don't like
to have their databases set to autogrow, but I normally do that for most
databases. Currently I'm looking after approx. 1000 SQL server databases
and for me it works fine to have them set to autogrow. I've then created
a stored proc that runs every night on most of the servers and collect a
number of data for each database. This tells me when there has been
created new databases, when a database has been deleted, file growth,
backup file growth, recovery mode change etc. I then get an email every
morning with the changes.
Apart from the above, there are a number of database monitoring tools
available on the market. I haven't actually used any of them yet but I
think they can do some of what I've done myself (..and of course a lot
more...). Maybe somebody else has some experience on this?
Bottomline is that what you need to look for isn't a standard answer,
but pretty much depends on the setup and your company/company policy. I
also think that the DBA role can be quite different from one company to
another.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||I think that with that number-- you'll just need to follow these guidelines:
a) use profiler on all machines
b) run index tuning wizard on your busiest databases
c) build poormon enterprise level performan aggregation tools
d) encourage people to use Reporting Services and Analysis Services in order
to leverage this mountain of data
<soalvajavab1@.yahoo.com> wrote in message
news:1177946795.389483.51580@.y80g2000hsf.googlegro ups.com...
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
If so what are the main startegy and main challenges you had? what is
your best practice in terms of day to day admin and backup and
recovery?
soalvajavab1@.yahoo.com wrote:
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
I don't think that tasks are that much different when you have 5 or 100
SQL servers, so could you be a bit more specific in what you'd like to
know? From my point of view it's quite important that the backup jobs
works. I've written my own scripts that automatically backups up all
databases on a SQL server, so once these has been scheduled they just
notify me when they fails. You'd then also like to monitor the free
space on your disks on the server, but for that the company most likely
has a monitoring tool for that.
In terms of what more you'd like to do is a matter of personal
preference and company policy. E.g. I know that many people don't like
to have their databases set to autogrow, but I normally do that for most
databases. Currently I'm looking after approx. 1000 SQL server databases
and for me it works fine to have them set to autogrow. I've then created
a stored proc that runs every night on most of the servers and collect a
number of data for each database. This tells me when there has been
created new databases, when a database has been deleted, file growth,
backup file growth, recovery mode change etc. I then get an email every
morning with the changes.
Apart from the above, there are a number of database monitoring tools
available on the market. I haven't actually used any of them yet but I
think they can do some of what I've done myself (..and of course a lot
more...). Maybe somebody else has some experience on this?
Bottomline is that what you need to look for isn't a standard answer,
but pretty much depends on the setup and your company/company policy. I
also think that the DBA role can be quite different from one company to
another.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||I think that with that number-- you'll just need to follow these guidelines:
a) use profiler on all machines
b) run index tuning wizard on your busiest databases
c) build poormon enterprise level performan aggregation tools
d) encourage people to use Reporting Services and Analysis Services in order
to leverage this mountain of data
<soalvajavab1@.yahoo.com> wrote in message
news:1177946795.389483.51580@.y80g2000hsf.googlegro ups.com...
> Is anybody has experience to DBA more than 100 SQl Servers at a time?
> If so what are the main startegy and main challenges you had? what is
> your best practice in terms of day to day admin and backup and
> recovery?
>
DB2OLEDB and Linked Servers
Has anyone had any success with linked servers and DB2. My DB2 knowledge is
very limited.
EXEC sp_addlinkedserver
@.server = 'SERVERR2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.provider = 'DB2OLEDB',
@.provstr='Network Transport Library=TCPIP;Network Address=10.73.102.115;User
ID=myuser;Password=mypassword;'
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DB2OLEDB" for linked server "SERVERR2" reported an
error. One or more arguments were reported invalid by the provider.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for
linked server "SERVERR2".
Any help would be appreciated. Thanks,
Andrew Robinson
http://blog.binaryocean.com
Hi Andrew,
Welcome to use MSDN Managed Newsgroup Support!
From your description, my understanding of this issue is: When you use the
sp_addlinkedserver
to add a DB2 Server, you got the following error message:
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
If i misunderstood your concern, please feel free to point it out.
According to the error message, you may re-configure the provider string.
Here is the sample from SQL Server 2000 Books online:
Use the Microsoft OLE DB Provider for DB2
This example creates a linked server named DB2 that uses the Microsoft OLE
DB Provider for DB2.
EXEC sp_addlinkedserver
@.server='DB2',
@.srvproduct='Microsoft OLE DB Provider for DB2',
@.catalog='DB2',
@.provider='DB2OLEDB',
@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network
Address=XYZ;Network Port=50000;Package Collection=admin;Default
Schema=admin;'
Also, you can find more details in this article:
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/default.aspx/kb/218590
You can create a Microsoft data link file (UDL) and test whether you can
connected to DB2 Server successfully.
1. Create a Text file named TestConnection.txt
2. Modify the file named to TestConnection.udl and click Yes in the
Rename dialog box.
3. Double click the TestConnection.udl file and you can follow the
article above to test the connection.
Hope this will be helpful! Thank you for your patient. If there are further
questions on the issue, please feel free to let us know. Have a great day!
Wei Lu
Microsoft Online Partner Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Andrew,
Just checking in to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
very limited.
EXEC sp_addlinkedserver
@.server = 'SERVERR2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.provider = 'DB2OLEDB',
@.provstr='Network Transport Library=TCPIP;Network Address=10.73.102.115;User
ID=myuser;Password=mypassword;'
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DB2OLEDB" for linked server "SERVERR2" reported an
error. One or more arguments were reported invalid by the provider.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for
linked server "SERVERR2".
Any help would be appreciated. Thanks,
Andrew Robinson
http://blog.binaryocean.com
Hi Andrew,
Welcome to use MSDN Managed Newsgroup Support!
From your description, my understanding of this issue is: When you use the
sp_addlinkedserver
to add a DB2 Server, you got the following error message:
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
If i misunderstood your concern, please feel free to point it out.
According to the error message, you may re-configure the provider string.
Here is the sample from SQL Server 2000 Books online:
Use the Microsoft OLE DB Provider for DB2
This example creates a linked server named DB2 that uses the Microsoft OLE
DB Provider for DB2.
EXEC sp_addlinkedserver
@.server='DB2',
@.srvproduct='Microsoft OLE DB Provider for DB2',
@.catalog='DB2',
@.provider='DB2OLEDB',
@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network
Address=XYZ;Network Port=50000;Package Collection=admin;Default
Schema=admin;'
Also, you can find more details in this article:
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/default.aspx/kb/218590
You can create a Microsoft data link file (UDL) and test whether you can
connected to DB2 Server successfully.
1. Create a Text file named TestConnection.txt
2. Modify the file named to TestConnection.udl and click Yes in the
Rename dialog box.
3. Double click the TestConnection.udl file and you can follow the
article above to test the connection.
Hope this will be helpful! Thank you for your patient. If there are further
questions on the issue, please feel free to let us know. Have a great day!
Wei Lu
Microsoft Online Partner Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Andrew,
Just checking in to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
================================================== ====
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
DB2OLEDB and Linked Servers
Has anyone had any success with linked servers and DB2. My DB2 knowledge is
very limited.
EXEC sp_addlinkedserver
@.server = 'SERVERR2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.provider = 'DB2OLEDB',
@.provstr='Network Transport Library=TCPIP;Network Address=10.73.102.115;User
ID=myuser;Password=mypassword;'
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DB2OLEDB" for linked server "SERVERR2" reported an
error. One or more arguments were reported invalid by the provider.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for
linked server "SERVERR2".
Any help would be appreciated. Thanks,
Andrew Robinson
http://blog.binaryocean.comHi Andrew,
Welcome to use MSDN Managed Newsgroup Support!
From your description, my understanding of this issue is: When you use the
sp_addlinkedserver
to add a DB2 Server, you got the following error message:
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
If i misunderstood your concern, please feel free to point it out.
According to the error message, you may re-configure the provider string.
Here is the sample from SQL Server 2000 Books online:
Use the Microsoft OLE DB Provider for DB2
This example creates a linked server named DB2 that uses the Microsoft OLE
DB Provider for DB2.
EXEC sp_addlinkedserver
@.server='DB2',
@.srvproduct='Microsoft OLE DB Provider for DB2',
@.catalog='DB2',
@.provider='DB2OLEDB',
@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network
Address=XYZ;Network Port=50000;Package Collection=admin;Default
Schema=admin;'
Also, you can find more details in this article:
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/default.aspx/kb/218590
You can create a Microsoft data link file (UDL) and test whether you can
connected to DB2 Server successfully.
1. Create a Text file named TestConnection.txt
2. Modify the file named to TestConnection.udl and click Yes in the
Rename dialog box.
3. Double click the TestConnection.udl file and you can follow the
article above to test the connection.
Hope this will be helpful! Thank you for your patient. If there are further
questions on the issue, please feel free to let us know. Have a great day!
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Andrew,
Just checking in to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
very limited.
EXEC sp_addlinkedserver
@.server = 'SERVERR2',
@.srvproduct = 'Microsoft OLE DB Provider for DB2',
@.provider = 'DB2OLEDB',
@.provstr='Network Transport Library=TCPIP;Network Address=10.73.102.115;User
ID=myuser;Password=mypassword;'
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "DB2OLEDB" for linked server "SERVERR2" reported an
error. One or more arguments were reported invalid by the provider.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for
linked server "SERVERR2".
Any help would be appreciated. Thanks,
Andrew Robinson
http://blog.binaryocean.comHi Andrew,
Welcome to use MSDN Managed Newsgroup Support!
From your description, my understanding of this issue is: When you use the
sp_addlinkedserver
to add a DB2 Server, you got the following error message:
OLE DB provider "DB2OLEDB" for linked server "SERVERR2" returned message
"The parameter is incorrect.".
If i misunderstood your concern, please feel free to point it out.
According to the error message, you may re-configure the provider string.
Here is the sample from SQL Server 2000 Books online:
Use the Microsoft OLE DB Provider for DB2
This example creates a linked server named DB2 that uses the Microsoft OLE
DB Provider for DB2.
EXEC sp_addlinkedserver
@.server='DB2',
@.srvproduct='Microsoft OLE DB Provider for DB2',
@.catalog='DB2',
@.provider='DB2OLEDB',
@.provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network
Address=XYZ;Network Port=50000;Package Collection=admin;Default
Schema=admin;'
Also, you can find more details in this article:
INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2
http://support.microsoft.com/default.aspx/kb/218590
You can create a Microsoft data link file (UDL) and test whether you can
connected to DB2 Server successfully.
1. Create a Text file named TestConnection.txt
2. Modify the file named to TestConnection.udl and click Yes in the
Rename dialog box.
3. Double click the TestConnection.udl file and you can follow the
article above to test the connection.
Hope this will be helpful! Thank you for your patient. If there are further
questions on the issue, please feel free to let us know. Have a great day!
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Andrew,
Just checking in to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Tuesday, March 27, 2012
DB2 OLEdb for linked server on SQL 2005 Express
Hi!
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/...&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysHi Marco,
You're not the first person to hit this problem. Unfortunately there is
no way that you can use an MS DB2 driver with anything other than these
editions. You're only option is to download IBM's driver.
Jonathan
Marco Dieckhoff wrote:
> Hi!
> We use a SQL Server 2005 Express to link some other SQL Servers and
> some Oracle servers together, to simplify our work.
> Now I wanted to link in a IBM DB2 running on a AS400.
> I downloaded the DB2OLEDB driver from Microsoft:
> Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
> from the "Feature Pack for Microsoft SQL Server 2005"
> http://www.microsoft.com/downloads/...&DisplayLang=en
> But that only works with the Enterprise or Developer edition.
> Is there any DB2OLEDB driver for SQL 2005 Express?
>|||On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Hi Marco,
I found a bunch of drivers but not the OLEDB one. I would suggest you
put in a post on the IBM newsgroups.
Jonathan
Marco Dieckhoff wrote:
> On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Thanks for the advice.
> I got it earlier from the german sqlserver group, and spend a good
> part of the morning searching for the IBM driver.
> Can anyone give me a link?
>
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/...&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysHi Marco,
You're not the first person to hit this problem. Unfortunately there is
no way that you can use an MS DB2 driver with anything other than these
editions. You're only option is to download IBM's driver.
Jonathan
Marco Dieckhoff wrote:
> Hi!
> We use a SQL Server 2005 Express to link some other SQL Servers and
> some Oracle servers together, to simplify our work.
> Now I wanted to link in a IBM DB2 running on a AS400.
> I downloaded the DB2OLEDB driver from Microsoft:
> Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
> from the "Feature Pack for Microsoft SQL Server 2005"
> http://www.microsoft.com/downloads/...&DisplayLang=en
> But that only works with the Enterprise or Developer edition.
> Is there any DB2OLEDB driver for SQL 2005 Express?
>|||On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Hi Marco,
I found a bunch of drivers but not the OLEDB one. I would suggest you
put in a post on the IBM newsgroups.
Jonathan
Marco Dieckhoff wrote:
> On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Thanks for the advice.
> I got it earlier from the german sqlserver group, and spend a good
> part of the morning searching for the IBM driver.
> Can anyone give me a link?
>
DB2 OLEdb for linked server on SQL 2005 Express
Hi!
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
--
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysHi Marco,
You're not the first person to hit this problem. Unfortunately there is
no way that you can use an MS DB2 driver with anything other than these
editions. You're only option is to download IBM's driver.
Jonathan
Marco Dieckhoff wrote:
> Hi!
> We use a SQL Server 2005 Express to link some other SQL Servers and
> some Oracle servers together, to simplify our work.
> Now I wanted to link in a IBM DB2 running on a AS400.
> I downloaded the DB2OLEDB driver from Microsoft:
> Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
> from the "Feature Pack for Microsoft SQL Server 2005"
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
> But that only works with the Enterprise or Developer edition.
> Is there any DB2OLEDB driver for SQL 2005 Express?
>|||On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
--
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Hi Marco,
I found a bunch of drivers but not the OLEDB one. I would suggest you
put in a post on the IBM newsgroups.
Jonathan
Marco Dieckhoff wrote:
> On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
>> Hi Marco,
>> You're not the first person to hit this problem. Unfortunately there is
>> no way that you can use an MS DB2 driver with anything other than these
>> editions. You're only option is to download IBM's driver.
> Thanks for the advice.
> I got it earlier from the german sqlserver group, and spend a good
> part of the morning searching for the IBM driver.
> Can anyone give me a link?
>
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
--
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysHi Marco,
You're not the first person to hit this problem. Unfortunately there is
no way that you can use an MS DB2 driver with anything other than these
editions. You're only option is to download IBM's driver.
Jonathan
Marco Dieckhoff wrote:
> Hi!
> We use a SQL Server 2005 Express to link some other SQL Servers and
> some Oracle servers together, to simplify our work.
> Now I wanted to link in a IBM DB2 running on a AS400.
> I downloaded the DB2OLEDB driver from Microsoft:
> Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
> from the "Feature Pack for Microsoft SQL Server 2005"
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
> But that only works with the Enterprise or Developer edition.
> Is there any DB2OLEDB driver for SQL 2005 Express?
>|||On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
--
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Hi Marco,
I found a bunch of drivers but not the OLEDB one. I would suggest you
put in a post on the IBM newsgroups.
Jonathan
Marco Dieckhoff wrote:
> On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
>> Hi Marco,
>> You're not the first person to hit this problem. Unfortunately there is
>> no way that you can use an MS DB2 driver with anything other than these
>> editions. You're only option is to download IBM's driver.
> Thanks for the advice.
> I got it earlier from the german sqlserver group, and spend a good
> part of the morning searching for the IBM driver.
> Can anyone give me a link?
>
DB2 OLEdb for linked server on SQL 2005 Express
Hi!
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys
On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys
We use a SQL Server 2005 Express to link some other SQL Servers and
some Oracle servers together, to simplify our work.
Now I wanted to link in a IBM DB2 running on a AS400.
I downloaded the DB2OLEDB driver from Microsoft:
Microsoft OLEDB Provider for DB2 (DB2OLEDB.exe)
from the "Feature Pack for Microsoft SQL Server 2005"
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en
But that only works with the Enterprise or Developer edition.
Is there any DB2OLEDB driver for SQL 2005 Express?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys
On 2007-07-27, JPD <nospam@.nospamplease.com> wrote:
> Hi Marco,
> You're not the first person to hit this problem. Unfortunately there is
> no way that you can use an MS DB2 driver with anything other than these
> editions. You're only option is to download IBM's driver.
Thanks for the advice.
I got it earlier from the german sqlserver group, and spend a good
part of the morning searching for the IBM driver.
Can anyone give me a link?
Marco Dieckhoff
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys
Sunday, March 25, 2012
db_dtsadmin role
Our DBA has given me access to MSDB on the SSIS service on one of our servers as db_dtsadmin. When I try to connect to the server using Integration Services in the connect drop down menu, I get the following generic error msg: connect to SSIS service on server 'xxxx' failed: access is denied.
I'm told this role should be sufficient to give me access. Do I need other server access roles to use in conjunction with db_dtsadmin or are we missing something really easy here.
Thanks.
This link might help: http://msdn2.microsoft.com/en-us/library/aa337083.aspx|||Thanks! This looks to be the answer. We'll try it.|||DBRICHARD wrote:
Our DBA has given me access to MSDB on the SSIS service on one of our servers as db_dtsadmin. When I try to connect to the server using Integration Services in the connect drop down menu, I get the following generic error msg: connect to SSIS service on server 'xxxx' failed: access is denied.
I'm told this role should be sufficient to give me access. Do I need other server access roles to use in conjunction with db_dtsadmin or are we missing something really easy here.
Thanks.
Thanks! This looks to be the answer. We'll try it.
If that works for you, be sure to mark this thread as answered. Thanks!|||Thanks. We're still trying to work on this. We followed the instructions on the link (connecting to a remote integration services server) but I still can't connect and still receive the same generic 'access denied' error message. There must be some other permissions needed besides what is outlined on the link. Back to the drawing board.|||You could also ask this question over on the SQL Server security forum... Might generate a few more responses.
db/server or query performance tuning?
I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisR
yes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisR
yes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
db/server or query performance tuning?
I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
db/server or query performance tuning?
I need to begin doing performance tuning on my servers. Nobody has ever done
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
any on them. Should I start with database and server tuning(AWE, filegroups,
Use NT Fibers, etc.) or start with the queries(Indexes, Hints, look to write
better code, etc.)?
TIA, ChrisRyes.
:-)
perf tuning is a hard question as your perf issues could be cause by a
number of things.
the first thing I would do is investigate bottlenecks and Inventory them.
Then, once you've listed all the potential problems, prioritize them by
Impact and Ease of fix. Use that to start knocking them off one at a time.
also spend some time on this sight.
www.SQL-Server-Performance.com
Cheers
Greg Jackson
PDX, Oregon
DB Warning when starting Full Text Population
I'm getting some very odd behavior from one of my SQL Servers.
It appears to be caused by the job which is starting the full text
population on one of my tables.
This morning when the job started the SQL Server started kicking out error
messages stating
2003-09-22 05:05:07.10 spid230 WARNING: EC 303e55e8, 0 waited 300 sec. on
latch ad8c88. Not a BUF latch.
2003-09-22 05:05:07.11 spid 230 Waiting for type 0x4, current count 0xa,
current owning EX 0xA8A55E8.
It kickes these out every 5 minutes until either the full text search starts
or as happened yesterday, the SQL Server stops responding, and we restart
the service.
Now, yesterday, when all this started at 01:59:49.11 I started to receive
the following
Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 295 ECID 0, UMS Context
0x03407150.
The server has 4 processors, and 4 Gig on RAM. 2 for SQL, 2 for the rest of
the OS. AWE is not enabled at the OS, or the SQL Server. OS is Windows
2000 Advanced Server, and SQL is 2000 Enterprise sp3 build 760.
This all started following moving the log files from a single SCSI drive, to
a SCSI RAID, then the RAID card failing. A new RAID card was added Saturday
night, and 1 hour later the 17883 started showing up. The 17883 errors
didn't appear this morning however.
I'm currently looking at KB 319892, and it's not being all that helpfull.
Both times this problem has come up, I've either been away from an internet
connection, or I've been sleeping. The first time (Sunday Morning, ending
Monday Morning ~1am) our sa rebooted the server to get the services back up
and running. This morning starting at 5am, ending at 10:15am the full text
index job finished and job the index rebuiling started. Can anyone shead
any light onto this mess? I'd like to set up an alert to page me the next
time this happens, so I can start looking at the issue as soon as it starts
to attempt to get more info about what's going on, on the server when it
happens, but I don't have an error number to setup the alert on. I will
setup one for 17883, incase that happens again.
If anyone would like to see the current, and last errorlogs please let me
know, and I'll send them. They are both very small.
A new server is on order to replace this machine. I was planning of coping
the files to the new server, and attaching them to the new database server,
but if that will cause this problem to follow, I'll keep the system offline
for longer, and copy the data into new database files via dts.
--
Denny Cherry
DBA
GameSpy IndustriesDenny,
This is very unusual (actually unique) for FT Populations to be causing
these 17883 errors, unless the FT-enable tables that it's reading is somehow
corrupt or possibly the FT Catalog is corrupt and either condition is
causing the scheduler to appear to hang... You might want to run DBCC
Checktable against your FT-enabled table to see if there is any corruption
present in the table.
While there are no real diagnostic tools for checking the MSSearch managed
FT Catalog files, you should review your Application Event log for any
"Microsoft Search" or MssCI (especially MssCI as these events will record
the corruption stack trace) source event - warnings or error or
informational, near the date/time of the 17883 errors. Also, review your
System Event log for any disk controller i/o error or warnings (especially
related to low disk space on the drives where your FT Catalog exists).
Also, in regards to the schedule job, does the FT Population (full or
incremental ?) complete successfully (you'll see a master merge
informational in the App log that indicates a successful completion) before
you fire off the job again? The SQL errorlogs are not of much help in
troubleshooting FT Population issues, as the App log is where you should
look for more answers...
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Denny" <mrdenny@.gamespy.com> wrote in message
news:#$NFjaTgDHA.2580@.tk2msftngp13.phx.gbl...
> I'm getting some very odd behavior from one of my SQL Servers.
> It appears to be caused by the job which is starting the full text
> population on one of my tables.
> This morning when the job started the SQL Server started kicking out error
> messages stating
> 2003-09-22 05:05:07.10 spid230 WARNING: EC 303e55e8, 0 waited 300 sec. on
> latch ad8c88. Not a BUF latch.
> 2003-09-22 05:05:07.11 spid 230 Waiting for type 0x4, current count 0xa,
> current owning EX 0xA8A55E8.
> It kickes these out every 5 minutes until either the full text search
starts
> or as happened yesterday, the SQL Server stops responding, and we restart
> the service.
> Now, yesterday, when all this started at 01:59:49.11 I started to receive
> the following
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 295 ECID 0, UMS Context
> 0x03407150.
> The server has 4 processors, and 4 Gig on RAM. 2 for SQL, 2 for the rest
of
> the OS. AWE is not enabled at the OS, or the SQL Server. OS is Windows
> 2000 Advanced Server, and SQL is 2000 Enterprise sp3 build 760.
> This all started following moving the log files from a single SCSI drive,
to
> a SCSI RAID, then the RAID card failing. A new RAID card was added
Saturday
> night, and 1 hour later the 17883 started showing up. The 17883 errors
> didn't appear this morning however.
> I'm currently looking at KB 319892, and it's not being all that helpfull.
> Both times this problem has come up, I've either been away from an
internet
> connection, or I've been sleeping. The first time (Sunday Morning, ending
> Monday Morning ~1am) our sa rebooted the server to get the services back
up
> and running. This morning starting at 5am, ending at 10:15am the full
text
> index job finished and job the index rebuiling started. Can anyone shead
> any light onto this mess? I'd like to set up an alert to page me the next
> time this happens, so I can start looking at the issue as soon as it
starts
> to attempt to get more info about what's going on, on the server when it
> happens, but I don't have an error number to setup the alert on. I will
> setup one for 17883, incase that happens again.
> If anyone would like to see the current, and last errorlogs please let me
> know, and I'll send them. They are both very small.
> A new server is on order to replace this machine. I was planning of
coping
> the files to the new server, and attaching them to the new database
server,
> but if that will cause this problem to follow, I'll keep the system
offline
> for longer, and copy the data into new database files via dts.
> --
> Denny Cherry
> DBA
> GameSpy Industries
>sql
It appears to be caused by the job which is starting the full text
population on one of my tables.
This morning when the job started the SQL Server started kicking out error
messages stating
2003-09-22 05:05:07.10 spid230 WARNING: EC 303e55e8, 0 waited 300 sec. on
latch ad8c88. Not a BUF latch.
2003-09-22 05:05:07.11 spid 230 Waiting for type 0x4, current count 0xa,
current owning EX 0xA8A55E8.
It kickes these out every 5 minutes until either the full text search starts
or as happened yesterday, the SQL Server stops responding, and we restart
the service.
Now, yesterday, when all this started at 01:59:49.11 I started to receive
the following
Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 295 ECID 0, UMS Context
0x03407150.
The server has 4 processors, and 4 Gig on RAM. 2 for SQL, 2 for the rest of
the OS. AWE is not enabled at the OS, or the SQL Server. OS is Windows
2000 Advanced Server, and SQL is 2000 Enterprise sp3 build 760.
This all started following moving the log files from a single SCSI drive, to
a SCSI RAID, then the RAID card failing. A new RAID card was added Saturday
night, and 1 hour later the 17883 started showing up. The 17883 errors
didn't appear this morning however.
I'm currently looking at KB 319892, and it's not being all that helpfull.
Both times this problem has come up, I've either been away from an internet
connection, or I've been sleeping. The first time (Sunday Morning, ending
Monday Morning ~1am) our sa rebooted the server to get the services back up
and running. This morning starting at 5am, ending at 10:15am the full text
index job finished and job the index rebuiling started. Can anyone shead
any light onto this mess? I'd like to set up an alert to page me the next
time this happens, so I can start looking at the issue as soon as it starts
to attempt to get more info about what's going on, on the server when it
happens, but I don't have an error number to setup the alert on. I will
setup one for 17883, incase that happens again.
If anyone would like to see the current, and last errorlogs please let me
know, and I'll send them. They are both very small.
A new server is on order to replace this machine. I was planning of coping
the files to the new server, and attaching them to the new database server,
but if that will cause this problem to follow, I'll keep the system offline
for longer, and copy the data into new database files via dts.
--
Denny Cherry
DBA
GameSpy IndustriesDenny,
This is very unusual (actually unique) for FT Populations to be causing
these 17883 errors, unless the FT-enable tables that it's reading is somehow
corrupt or possibly the FT Catalog is corrupt and either condition is
causing the scheduler to appear to hang... You might want to run DBCC
Checktable against your FT-enabled table to see if there is any corruption
present in the table.
While there are no real diagnostic tools for checking the MSSearch managed
FT Catalog files, you should review your Application Event log for any
"Microsoft Search" or MssCI (especially MssCI as these events will record
the corruption stack trace) source event - warnings or error or
informational, near the date/time of the 17883 errors. Also, review your
System Event log for any disk controller i/o error or warnings (especially
related to low disk space on the drives where your FT Catalog exists).
Also, in regards to the schedule job, does the FT Population (full or
incremental ?) complete successfully (you'll see a master merge
informational in the App log that indicates a successful completion) before
you fire off the job again? The SQL errorlogs are not of much help in
troubleshooting FT Population issues, as the App log is where you should
look for more answers...
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Denny" <mrdenny@.gamespy.com> wrote in message
news:#$NFjaTgDHA.2580@.tk2msftngp13.phx.gbl...
> I'm getting some very odd behavior from one of my SQL Servers.
> It appears to be caused by the job which is starting the full text
> population on one of my tables.
> This morning when the job started the SQL Server started kicking out error
> messages stating
> 2003-09-22 05:05:07.10 spid230 WARNING: EC 303e55e8, 0 waited 300 sec. on
> latch ad8c88. Not a BUF latch.
> 2003-09-22 05:05:07.11 spid 230 Waiting for type 0x4, current count 0xa,
> current owning EX 0xA8A55E8.
> It kickes these out every 5 minutes until either the full text search
starts
> or as happened yesterday, the SQL Server stops responding, and we restart
> the service.
> Now, yesterday, when all this started at 01:59:49.11 I started to receive
> the following
> Error: 17883, Severity: 1, State: 0
> The Scheduler 0 appears to be hung. SPID 295 ECID 0, UMS Context
> 0x03407150.
> The server has 4 processors, and 4 Gig on RAM. 2 for SQL, 2 for the rest
of
> the OS. AWE is not enabled at the OS, or the SQL Server. OS is Windows
> 2000 Advanced Server, and SQL is 2000 Enterprise sp3 build 760.
> This all started following moving the log files from a single SCSI drive,
to
> a SCSI RAID, then the RAID card failing. A new RAID card was added
Saturday
> night, and 1 hour later the 17883 started showing up. The 17883 errors
> didn't appear this morning however.
> I'm currently looking at KB 319892, and it's not being all that helpfull.
> Both times this problem has come up, I've either been away from an
internet
> connection, or I've been sleeping. The first time (Sunday Morning, ending
> Monday Morning ~1am) our sa rebooted the server to get the services back
up
> and running. This morning starting at 5am, ending at 10:15am the full
text
> index job finished and job the index rebuiling started. Can anyone shead
> any light onto this mess? I'd like to set up an alert to page me the next
> time this happens, so I can start looking at the issue as soon as it
starts
> to attempt to get more info about what's going on, on the server when it
> happens, but I don't have an error number to setup the alert on. I will
> setup one for 17883, incase that happens again.
> If anyone would like to see the current, and last errorlogs please let me
> know, and I'll send them. They are both very small.
> A new server is on order to replace this machine. I was planning of
coping
> the files to the new server, and attaching them to the new database
server,
> but if that will cause this problem to follow, I'll keep the system
offline
> for longer, and copy the data into new database files via dts.
> --
> Denny Cherry
> DBA
> GameSpy Industries
>sql
Thursday, March 22, 2012
DB taken offline by getspinlock pre-Sleep() error
We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?
This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>
|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?
This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 0057110112:30:40 AM[516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 0057110512:30:47 AM[516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>
|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
DB taken offline by getspinlock pre-Sleep() error
We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep(
):
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep(
):
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removin
g
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. W
e
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Slee
p():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Slee
p():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the databa
se
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so remov
ing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep(
):
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep(
):
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removin
g
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. W
e
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Slee
p():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Slee
p():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the databa
se
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so remov
ing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>
DB taken offline by getspinlock pre-Sleep() error
We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> > This problem was caused by Veritas Backup Exec trying to backup the database
> > files (which were obviously in use!). The scripts used to include the
> > SQLBackup folder but had been changed to include SQLData as well, so removing
> > SQLData from the script resolved the problem.
> >
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>> David Grant wrote:
>> > This problem was caused by Veritas Backup Exec trying to backup the database
>> > files (which were obviously in use!). The scripts used to include the
>> > SQLBackup folder but had been changed to include SQLData as well, so removing
>> > SQLData from the script resolved the problem.
>> >
>> Veritas shouldn't have been able to take the databases offline like
>> this, the data files should have been "in use" by SQL at the time. The
>> only way I can picture this happening is if you have "Auto-Close"
>> enabled on your databases. If SQL "closed" the database, Veritas comes
>> along and starts backing up the data file, and SQL tries to "open" the
>> database again, it's going to fail. You shouldn't use the AutoClose or
>> AutoShrink options on a production database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> > This problem was caused by Veritas Backup Exec trying to backup the database
> > files (which were obviously in use!). The scripts used to include the
> > SQLBackup folder but had been changed to include SQLData as well, so removing
> > SQLData from the script resolved the problem.
> >
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>> David Grant wrote:
>> > This problem was caused by Veritas Backup Exec trying to backup the database
>> > files (which were obviously in use!). The scripts used to include the
>> > SQLBackup folder but had been changed to include SQLData as well, so removing
>> > SQLData from the script resolved the problem.
>> >
>> Veritas shouldn't have been able to take the databases offline like
>> this, the data files should have been "in use" by SQL at the time. The
>> only way I can picture this happening is if you have "Auto-Close"
>> enabled on your databases. If SQL "closed" the database, Veritas comes
>> along and starts backing up the data file, and SQL tries to "open" the
>> database again, it's going to fail. You shouldn't use the AutoClose or
>> AutoShrink options on a production database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
Wednesday, March 7, 2012
DB mirroring setup
Prepare to setup DB mirroring. Three MS 2003 servers. Two SQL 2005 standard edition as partners and one SQL 2005 Express as witness. Question:
1. Servers are under workgroup (not domain user). Is it ok to use local service account to run DB engine service on all three machine? Is SQLServerAgent service required when setup mirroring?
2. If SQL Server is running as a local service, Do I have to manually create certificates before setup mirroring?
3. After create certificates, can management studio be used to create endpoints on all three machines? If yes, how to select certificates that have been created? If no, can management studio be used to setup mirroring after endpoints have been created?
actually i'm also would like to know about the additional settings if there is. Hope can get some guidelines here. Thx
Best Regards,
Hans
DB Mirroring Monitor and querying the "other" db?
So I successfully got the mirroring to run and now I'd like to see what's going on between the two servers ( i'm not using a witness). I launch the Database Mirroring Monitor and its showing the two servers but when I go to the history column I'm not seeing any data movement. I've done a an update on a db with 1500 rows so I was expecting to see this show up.
Also, is there anyway to query the database thats acting as the mirrored db?
thanks
daniel
Can you verify that you are connecting to the Partner server as SA or in the DBM_Monitor role?
You leave the monitor up and running, correct?
Do you have a periodic job running on the server to update the base table?
You cannot query the mirror database. You can create a snapshot on the mirror database and query that. I'm assuming you want to verify that the data has actually moved over to the other side.
Thanks,
Mark
|||I'm seeing a value of 20KB/sec in the Send Rate column now. I updated one column in a table of 1500 rows.
How do I assign someone the dbm_monitor role?
thanks for your response, seems as if your the only MS tech on this specific forum.
|||Search BOL for information on assigning a principal (a login in this case) to a role.
The role should have been created in MSDB when one of the monitoring stored procedure's was run.
Database Mirroring is my feature.
Thanks,
Mark
|||I am new to Microsoft SQL Server 2005 and i would like to make use of its database mirroring feature. My question though is: is database mirroring supported by the JDBC driver and if so how would you implement/code a connection to principal and mirror servers i.e. how would i implement a client - redirect in case of failures.Any help will be greatly appreciated.|||You may want to read the BOL http://msdn2.microsoft.com/en-US/library/ms131373(SQL.90).aspx .
If your using the new 2.0 framwork for programming e.g. vb.net, c#, you may be able to have to specify in your connection string the failover and primary.
|||This is great!! Others are posting answers here as well!!
The JDBC support for database mirroring should be in V1.1. That should ship a CTP soon and an RTM version in the middle of the year.
Now, you can always put the redirect logic into the application. This allows you to control which server you would like to connect to. More work for you the application developer, but it allows more control.
Thanks,
Mark
|||So Microsoft's JDBC driver does not currently support MS SQL 2005 's database mirroring?|||Currently Microsoft's JDBC driver does not have automatic client redirect for database mirroring. It is in development. If you would like to be a beta tester for this feature, let me know.
You can still use the JDBC driver and connect to a database that is used in mirroring, but after a failover, you will be required to somehow get the client to connect to the other server.
Thanks,
Mark
|||No support as of yet for "dual databases" in your connection string for jdbc.
Mark, we just discussed this today (jdbc probs) in our weekly db meeting. I'm glad that microsoft will be releasing this. Are we going to see the sp1 before the end of April Mark?
SQL Native Client OLE DB Provider
The SQL Native Client OLE DB provider supports database mirroring through connection and connection string attributes. Specifically, the SSPROP_INIT_FAILOVERPARTNER property has been added to the DBPROPSET_SQLSERVERDBINIT property set; and the Failover Partner keyword has been added as a new connection string attribute.
The failover cache is maintained as long as the provider is loaded, which is until CoUninitialize is called or as long as the application has a reference to some object managed by the SQL Native Client OLE DB provider such as a data source object.
For details about SQL Native Client OLE DB provider support for database mirroring, see Initialization and Authorization Properties.
SQL Native Client ODBC Driver
The SQL Native Client ODBC driver supports database mirroring through connection and connection string attributes. Specifically, the SQL_COPT_SS_FAILOVER_PARTNER attribute has been added for use with the SQLSetConnectAttr and SQLGetConnectAttr functions; and the Failover Partner keyword has been added as a new connection string attribute.
The failover cache is maintained as long as the application has at least one environment handle allocated. Conversely, it is lost when the last environment handle is deallocated.
SP1 will be released soon. I mean soon. Real soon. So, so soon. I can almost taste it.
However, the JDBC driver will RTM in "the middle of the year."
Thanks,
Mark
|||Hey Mark, thanks for your response. I will like to be a beta tester for the JDBC driver, how do I go about that?|||Contact Shelby Goerlitz. He is the Program Manager for the JDBC.
His email is shelbyg (at) microsoft (dot) com
This should start the process, but there are no guarantees, etc.
Thanks,
Mark
Saturday, February 25, 2012
DB mirroring + load balancing
Is it compatible to set up SQL DB mirroring on 2
* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?
different physical servers (High availbility + FULL transaction safety +
Automatic failover with a third server as witness) and simultaneously set up
network load balancing between these 2 servers (option with Windows Server 2003
Enterprise) ?
If yes, which type of licenses do I need and how
many (for SQL server 2005 and Windows Server 2003) with such a configuration
?
- 4 physical servers in total : A, B, C and
D
- load balancing between A and B (same application)
- load balancing between C and D (same application)
- A hosts principal DB X, B hosts mirror of DB
X
- C hosts principal DB Y, D hosts mirror of DB
Y
- D is witness for A and B
- B is witness for C and D
- A and B : only internal clients <
25
- C and D : both internal and external clients
(internal clients <25, number of external clients is unknown (>25)
)
Hoping this description will be helpful enough for
you...
(I am looking for a very high availability
system)
Thank you in advance for your support.
This licensing question is pretty much urgent (for
a bid) : a quick answer would be very appreciated...
Not going in details of your design.
There is one thing I know for sure. MS is considering a "mirror" as 1 license. Confirmed by MS "presales and licensing division". So, whatever license you will bye (Enterprise or Standard depending on hardware you are going to use) - you need two licenses for MS SQL for 4 servers listed in your design.
|||The database on mirror server is always in the "Restoring" state, and you can't connect to this database. I didn't get how you plan to achieve load balancing with this.
|||The load balancing is set up for automatic total load transfer on one server when the other one is lost (high availability, no time to activate the back-up server).
When 100% load is taken by one server, I need this server to access (read & write) to the back-up SQL database that shall be a real mirror of the one located on the lost server.
I am not familiar at all with such systems that's why I need validation of the concept I imagined.
According to your comment, mirror database is only here to be able to restore the lost database but, in any case, it could be used as a replacement of the lost one. Am I right ?
Could you recommend me a redundant system architecture maximizing availability ?
Thanks in advance for your expertise.
Yann
|||Database mirroring allows you to maintain a hot standby of your database.
The mirror database is always in restoring "state", and not available for user connections. When your principal server (the primary server serving the application) fails, you use the mirror to failover. Please note that you don't do a "restore" with a mirror as you mentioned, but failover to the mirror so that the database becomes available to your application.
I suggest reading the following:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
SQL Server Books Online for Database Mirroring.
|||Tom Rizzo has a chapter in his book "Pro SQL Server 2005" from Apress on database mirroring.
He discusses the ability to use High Availability Mirroring (i.e. automated failover) while using the second server for queries. A quick synopsis is:
A Mirror solution only runs as fast as the slowest database server. So, when you write to the primary database, and the mirror is working, then the time to execute that write is the slowest time of writing to the primary database or the mirror database. So, if you are taxing the resources of your mirror database with other kinds of activity, it can slow down your overall application because the primary server won't commit a transaction until the mirror has also committed the same transaction.
That being said, you can still use the mirror for read only access of the data being mirrored throught the use of Snapshots. As I recall, snapshots are only available in the Enterprise version of SQL Server.
Even though Tom only has one chapter on Mirroring, I found it VERY helpful, and a lot easier to wade through than the MS documentation; at lease for establishing a base understanding of the technology.
Cheers,
Ben
|||When the database mirror is only used for a standby in the case of emergencies, it does NOT need to be separately licensed. If the mirror is used for other purposes, including reporting, then it must be licensed.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||Thanks for your reply.
I have then others questions :* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?
Thanks in advance for you additional information.
Yann
DB mirroring + load balancing
Is it compatible to set up SQL DB mirroring on 2
* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?
different physical servers (High availbility + FULL transaction safety +
Automatic failover with a third server as witness) and simultaneously set up
network load balancing between these 2 servers (option with Windows Server 2003
Enterprise) ?
If yes, which type of licenses do I need and how
many (for SQL server 2005 and Windows Server 2003) with such a configuration
?
- 4 physical servers in total : A, B, C and
D
- load balancing between A and B (same application)
- load balancing between C and D (same application)
- A hosts principal DB X, B hosts mirror of DB
X
- C hosts principal DB Y, D hosts mirror of DB
Y
- D is witness for A and B
- B is witness for C and D
- A and B : only internal clients <
25
- C and D : both internal and external clients
(internal clients <25, number of external clients is unknown (>25)
)
Hoping this description will be helpful enough for
you...
(I am looking for a very high availability
system)
Thank you in advance for your support.
This licensing question is pretty much urgent (for
a bid) : a quick answer would be very appreciated...
Not going in details of your design.
There is one thing I know for sure. MS is considering a "mirror" as 1 license. Confirmed by MS "presales and licensing division". So, whatever license you will bye (Enterprise or Standard depending on hardware you are going to use) - you need two licenses for MS SQL for 4 servers listed in your design.
|||The database on mirror server is always in the "Restoring" state, and you can't connect to this database. I didn't get how you plan to achieve load balancing with this.
|||The load balancing is set up for automatic total load transfer on one server when the other one is lost (high availability, no time to activate the back-up server).
When 100% load is taken by one server, I need this server to access (read & write) to the back-up SQL database that shall be a real mirror of the one located on the lost server.
I am not familiar at all with such systems that's why I need validation of the concept I imagined.
According to your comment, mirror database is only here to be able to restore the lost database but, in any case, it could be used as a replacement of the lost one. Am I right ?
Could you recommend me a redundant system architecture maximizing availability ?
Thanks in advance for your expertise.
Yann
|||Database mirroring allows you to maintain a hot standby of your database.
The mirror database is always in restoring "state", and not available for user connections. When your principal server (the primary server serving the application) fails, you use the mirror to failover. Please note that you don't do a "restore" with a mirror as you mentioned, but failover to the mirror so that the database becomes available to your application.
I suggest reading the following:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx
SQL Server Books Online for Database Mirroring.
|||Tom Rizzo has a chapter in his book "Pro SQL Server 2005" from Apress on database mirroring.
He discusses the ability to use High Availability Mirroring (i.e. automated failover) while using the second server for queries. A quick synopsis is:
A Mirror solution only runs as fast as the slowest database server. So, when you write to the primary database, and the mirror is working, then the time to execute that write is the slowest time of writing to the primary database or the mirror database. So, if you are taxing the resources of your mirror database with other kinds of activity, it can slow down your overall application because the primary server won't commit a transaction until the mirror has also committed the same transaction.
That being said, you can still use the mirror for read only access of the data being mirrored throught the use of Snapshots. As I recall, snapshots are only available in the Enterprise version of SQL Server.
Even though Tom only has one chapter on Mirroring, I found it VERY helpful, and a lot easier to wade through than the MS documentation; at lease for establishing a base understanding of the technology.
Cheers,
Ben
|||When the database mirror is only used for a standby in the case of emergencies, it does NOT need to be separately licensed. If the mirror is used for other purposes, including reporting, then it must be licensed.
Regards,
Matt Hollingsworth
Sr. Program Manager
SQL Server High Availability
|||Thanks for your reply.
I have then others questions :* what could I really do with the mirror database after failover of the main database ?
* Could clients still enter new entries or make some modifications on the database ?
* How does the procedure to come back with the main database occur ?
Thanks in advance for you additional information.
Yann
Subscribe to:
Posts (Atom)