We have a scenario that will require us to maintain a central system that
will be taking in data from 15 other locations around the state. I'm
interested in creating a situation that will allow the individual locations
the ability to have a local copy of their data, but only their data. We want
changes made to the central repository to be pushed back down the the
appropriate location. It there a way or doing this through some kind of SQL
Server transaction synchronization? Could we have each locations data use
SQL Server log/transaction shipping synchronizing into individual databases
at the central site and then have some process that would just merge each of
these databases into a single large database? Is this more of a headache to
try to create and maintain? Any ideas or suggestions would be great. The
main idea here is that each location is a separate entity collecting their
own data - we are acting as a repository for all the locations and want to
be able to do research analysis on the data as a whole. As I said, any ideas
or suggestions would be welcome! Thanks!
>
> We have a scenario that will require us to maintain a central system
that
> will be taking in data from 15 other locations around the state. I'm
> interested in creating a situation that will allow the individual
locations
> the ability to have a local copy of their data, but only their data. We
want
> changes made to the central repository to be pushed back down the the
> appropriate location. It there a way or doing this through some kind of
SQL
> Server transaction synchronization? Could we have each locations data use
> SQL Server log/transaction shipping synchronizing into individual
databases
> at the central site and then have some process that would just merge each
of
> these databases into a single large database? Is this more of a headache
to
> try to create and maintain? Any ideas or suggestions would be great. The
> main idea here is that each location is a separate entity collecting
their
> own data - we are acting as a repository for all the locations and want
to
> be able to do research analysis on the data as a whole. As I said, any
ideas
> or suggestions would be welcome! Thanks!
It looks your requirement conforms to the multiple publishers and central
subscriber physical model. For more info, please consult SQL Server Books
Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Multi location data syncronization
We have a scenario that will require us to maintain a central system that
will be taking in data from 15 other locations around the state. I'm
interested in creating a situation that will allow the individual locations
the ability to have a local copy of their data, but only their data. We want
changes made to the central repository to be pushed back down the the
appropriate location. It there a way or doing this through some kind of SQL
Server transaction synchronization? Could we have each locations data use
SQL Server log/transaction shipping synchronizing into individual databases
at the central site and then have some process that would just merge each of
these databases into a single large database? Is this more of a headache to
try to create and maintain? Any ideas or suggestions would be great. The
main idea here is that each location is a separate entity collecting their
own data - we are acting as a repository for all the locations and want to
be able to do research analysis on the data as a whole. As I said, any ideas
or suggestions would be welcome! Thanks!>
> We have a scenario that will require us to maintain a central system
that
> will be taking in data from 15 other locations around the state. I'm
> interested in creating a situation that will allow the individual
locations
> the ability to have a local copy of their data, but only their data. We
want
> changes made to the central repository to be pushed back down the the
> appropriate location. It there a way or doing this through some kind of
SQL
> Server transaction synchronization? Could we have each locations data use
> SQL Server log/transaction shipping synchronizing into individual
databases
> at the central site and then have some process that would just merge each
of
> these databases into a single large database? Is this more of a headache
to
> try to create and maintain? Any ideas or suggestions would be great. The
> main idea here is that each location is a separate entity collecting
their
> own data - we are acting as a repository for all the locations and want
to
> be able to do research analysis on the data as a whole. As I said, any
ideas
> or suggestions would be welcome! Thanks!
--
It looks your requirement conforms to the multiple publishers and central
subscriber physical model. For more info, please consult SQL Server Books
Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
will be taking in data from 15 other locations around the state. I'm
interested in creating a situation that will allow the individual locations
the ability to have a local copy of their data, but only their data. We want
changes made to the central repository to be pushed back down the the
appropriate location. It there a way or doing this through some kind of SQL
Server transaction synchronization? Could we have each locations data use
SQL Server log/transaction shipping synchronizing into individual databases
at the central site and then have some process that would just merge each of
these databases into a single large database? Is this more of a headache to
try to create and maintain? Any ideas or suggestions would be great. The
main idea here is that each location is a separate entity collecting their
own data - we are acting as a repository for all the locations and want to
be able to do research analysis on the data as a whole. As I said, any ideas
or suggestions would be welcome! Thanks!>
> We have a scenario that will require us to maintain a central system
that
> will be taking in data from 15 other locations around the state. I'm
> interested in creating a situation that will allow the individual
locations
> the ability to have a local copy of their data, but only their data. We
want
> changes made to the central repository to be pushed back down the the
> appropriate location. It there a way or doing this through some kind of
SQL
> Server transaction synchronization? Could we have each locations data use
> SQL Server log/transaction shipping synchronizing into individual
databases
> at the central site and then have some process that would just merge each
of
> these databases into a single large database? Is this more of a headache
to
> try to create and maintain? Any ideas or suggestions would be great. The
> main idea here is that each location is a separate entity collecting
their
> own data - we are acting as a repository for all the locations and want
to
> be able to do research analysis on the data as a whole. As I said, any
ideas
> or suggestions would be welcome! Thanks!
--
It looks your requirement conforms to the multiple publishers and central
subscriber physical model. For more info, please consult SQL Server Books
Online.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Multi location data syncronization
We have a scenario that will require us to maintain a central system that
will be taking in data from 15 other locations around the state. I'm
interested in creating a situation that will allow the individual locations
the ability to have a local copy of their data, but only their data. We want
changes made to the central repository to be pushed back down the the
appropriate location. It there a way or doing this through some kind of SQL
Server transaction synchronization? Could we have each locations data use
SQL Server log/transaction shipping synchronizing into individual databases
at the central site and then have some process that would just merge each of
these databases into a single large database? Is this more of a headache to
try to create and maintain? Any ideas or suggestions would be great. The
main idea here is that each location is a separate entity collecting their
own data - we are acting as a repository for all the locations and want to
be able to do research analysis on the data as a whole. As I said, any ideas
or suggestions would be welcome! Thanks!>
> We have a scenario that will require us to maintain a central system
that
> will be taking in data from 15 other locations around the state. I'm
> interested in creating a situation that will allow the individual
locations
> the ability to have a local copy of their data, but only their data. We
want
> changes made to the central repository to be pushed back down the the
> appropriate location. It there a way or doing this through some kind of
SQL
> Server transaction synchronization? Could we have each locations data use
> SQL Server log/transaction shipping synchronizing into individual
databases
> at the central site and then have some process that would just merge each
of
> these databases into a single large database? Is this more of a headache
to
> try to create and maintain? Any ideas or suggestions would be great. The
> main idea here is that each location is a separate entity collecting
their
> own data - we are acting as a repository for all the locations and want
to
> be able to do research analysis on the data as a whole. As I said, any
ideas
> or suggestions would be welcome! Thanks!
--
It looks your requirement conforms to the multiple publishers and central
subscriber physical model. For more info, please consult SQL Server Books
Online.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
will be taking in data from 15 other locations around the state. I'm
interested in creating a situation that will allow the individual locations
the ability to have a local copy of their data, but only their data. We want
changes made to the central repository to be pushed back down the the
appropriate location. It there a way or doing this through some kind of SQL
Server transaction synchronization? Could we have each locations data use
SQL Server log/transaction shipping synchronizing into individual databases
at the central site and then have some process that would just merge each of
these databases into a single large database? Is this more of a headache to
try to create and maintain? Any ideas or suggestions would be great. The
main idea here is that each location is a separate entity collecting their
own data - we are acting as a repository for all the locations and want to
be able to do research analysis on the data as a whole. As I said, any ideas
or suggestions would be welcome! Thanks!>
> We have a scenario that will require us to maintain a central system
that
> will be taking in data from 15 other locations around the state. I'm
> interested in creating a situation that will allow the individual
locations
> the ability to have a local copy of their data, but only their data. We
want
> changes made to the central repository to be pushed back down the the
> appropriate location. It there a way or doing this through some kind of
SQL
> Server transaction synchronization? Could we have each locations data use
> SQL Server log/transaction shipping synchronizing into individual
databases
> at the central site and then have some process that would just merge each
of
> these databases into a single large database? Is this more of a headache
to
> try to create and maintain? Any ideas or suggestions would be great. The
> main idea here is that each location is a separate entity collecting
their
> own data - we are acting as a repository for all the locations and want
to
> be able to do research analysis on the data as a whole. As I said, any
ideas
> or suggestions would be welcome! Thanks!
--
It looks your requirement conforms to the multiple publishers and central
subscriber physical model. For more info, please consult SQL Server Books
Online.
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, March 12, 2012
MSSQLSERVER error(s) - severity and state
Hi
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
Bryan
If I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.
|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>
|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times in
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>
|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
[vbcol=seagreen]
> All of the errors point to disk problems, but given you are on a SAN(NAS?) I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the error
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
Bryan
If I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.
|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>
|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times in
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>
|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
[vbcol=seagreen]
> All of the errors point to disk problems, but given you are on a SAN(NAS?) I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the error
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
MSSQLSERVER error(s) - severity and state
Hi
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
BryanIf I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times in
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
> All of the errors point to disk problems, but given you are on a SAN(NAS?) I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the error
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
> > Hi
> >
> > W2K3 std edition (no SP) - SQL Server Std SP3
> >
> > I have a lot of errors showing up in app log on different days and times in
> > the form of:
> >
> > ***********************************
> > MSSQLSERVER, Category (2), Event ID: 17055
> > Description:
> > 18052
> > Error: 3314, Severoty:21, State: 4
> > ***********************************
> > The 'error' number varies between, 3314, 9001 and 823.
> >
> > The account is always the same one.
> >
> > Trawling through the SQL log - I can see some entries with the following
> > detail:
> >
> > ***********************************
> > database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> > the event log for more information.
> > ***********************************
> >
> > DBCC CHECKDB shows no errors on this database.
> > Any pointers on this very much appreciated.
> >
> > All SQL services are currently running.
> >
> > Thanks
> > Bryan
> >
> >
> >
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
BryanIf I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times in
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
> All of the errors point to disk problems, but given you are on a SAN(NAS?) I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the error
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
> > Hi
> >
> > W2K3 std edition (no SP) - SQL Server Std SP3
> >
> > I have a lot of errors showing up in app log on different days and times in
> > the form of:
> >
> > ***********************************
> > MSSQLSERVER, Category (2), Event ID: 17055
> > Description:
> > 18052
> > Error: 3314, Severoty:21, State: 4
> > ***********************************
> > The 'error' number varies between, 3314, 9001 and 823.
> >
> > The account is always the same one.
> >
> > Trawling through the SQL log - I can see some entries with the following
> > detail:
> >
> > ***********************************
> > database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
> > the event log for more information.
> > ***********************************
> >
> > DBCC CHECKDB shows no errors on this database.
> > Any pointers on this very much appreciated.
> >
> > All SQL services are currently running.
> >
> > Thanks
> > Bryan
> >
> >
> >
MSSQLSERVER error(s) - severity and state
Hi
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
BryanIf I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times i
n
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. S
ee
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
[vbcol=seagreen]
> All of the errors point to disk problems, but given you are on a SAN(NAS?)
I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the erro
r
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
>
W2K3 std edition (no SP) - SQL Server Std SP3
I have a lot of errors showing up in app log on different days and times in
the form of:
***********************************
MSSQLSERVER, Category (2), Event ID: 17055
Description:
18052
Error: 3314, Severoty:21, State: 4
***********************************
The 'error' number varies between, 3314, 9001 and 823.
The account is always the same one.
Trawling through the SQL log - I can see some entries with the following
detail:
***********************************
database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. See
the event log for more information.
***********************************
DBCC CHECKDB shows no errors on this database.
Any pointers on this very much appreciated.
All SQL services are currently running.
Thanks
BryanIf I were you, I would suck and backup as much of the data as you can
from the database files / disks. Seems to me that this is a hard error
inidcating that your underlying disks are about to die (or the
controller has a defect). Backup up and then checking / replacing the
databases would be the best to do. (for me)
HTH, Jens Suessmeyer.|||Hi Jens
Thanks for the prompt reply.
Would this still be the case, as I have a whole bunch of DBs stting on the
same disk location.
I should have also noted that this is a SAN attached server.
Are there any particular tools (that are non intrusive as this is a
production server) that can check the integrity of a particular database
(other than DBCC CHECKDB/CHECKALLOC/CHECKCATALOG)?
Thanks
Bry
"Jens" wrote:
> If I were you, I would suck and backup as much of the data as you can
> from the database files / disks. Seems to me that this is a hard error
> inidcating that your underlying disks are about to die (or the
> controller has a defect). Backup up and then checking / replacing the
> databases would be the best to do. (for me)
> HTH, Jens Suessmeyer.
>|||All of the errors point to disk problems, but given you are on a SAN(NAS?) I
would expect the problem is not hardware, but either overloading or
networking.
If you NAS/SAN configuration is not tuned properly, you could get the error
codes you listed.
Another possibility (which is why I added NAS rather than SAN) is that if
you have a bad or misconfigured switch, you could be seeing packe drops if
your server is 100MB and the switch port is 10MB.
Just ideas.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"bryan" wrote:
> Hi
> W2K3 std edition (no SP) - SQL Server Std SP3
> I have a lot of errors showing up in app log on different days and times i
n
> the form of:
> ***********************************
> MSSQLSERVER, Category (2), Event ID: 17055
> Description:
> 18052
> Error: 3314, Severoty:21, State: 4
> ***********************************
> The 'error' number varies between, 3314, 9001 and 823.
> The account is always the same one.
> Trawling through the SQL log - I can see some entries with the following
> detail:
> ***********************************
> database 'XYZ' cannot be opened. it has been marked SUSPECT by recovery. S
ee
> the event log for more information.
> ***********************************
> DBCC CHECKDB shows no errors on this database.
> Any pointers on this very much appreciated.
> All SQL services are currently running.
> Thanks
> Bryan
>
>|||Hi Joseph
Thanks for your comments, always appreciated. I shall certainly take a look
at all your suggestions.
I will update any progress I make.
Best rgds
Bryan
"jrpm" wrote:
[vbcol=seagreen]
> All of the errors point to disk problems, but given you are on a SAN(NAS?)
I
> would expect the problem is not hardware, but either overloading or
> networking.
> If you NAS/SAN configuration is not tuned properly, you could get the erro
r
> codes you listed.
> Another possibility (which is why I added NAS rather than SAN) is that if
> you have a bad or misconfigured switch, you could be seeing packe drops if
> your server is 100MB and the switch port is 10MB.
> Just ideas.
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "bryan" wrote:
>
Friday, March 9, 2012
MSSQL6.5 'Golden'/Rollback state problem
Hi, anyone knows if the 'golden' / rollback state in sql
6.5 causes the entire server to hang is a known problem?
Are there any advices as to what can be done to avoid
this problem from occurring in the future again? ThanksHi Kimkia.
Golden state applies to spids, not the server - so there is no direct
relationship I can see between a spid in golden and the server hanging. A
spid in golden state could cause an individual database to be inaccessible
but as far as I know, this shouldn't affect the whole server. Then again,
6.5 seems like a distant memory these days for me so perhaps someone else
could chip in with some more information for you.
As far as avoiding golden state is concerned - you should apply normal
deadlock / rollback avoidance techniques - keeping transactions short,
indexes up to date (to help keep transactions short & avoid deadlocks),
inspect regular deadlocks for coding mistakes such as accessing tables in
opposite orders. Also avoid doing major batch work in busy periods so that
you won't have to roll back large transactions during busy periods (during
which spids go golden)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Kimkia" <ahkimkia@.hotmail.com> wrote in message
news:051d01c39dc3$ba897520$a601280a@.phx.gbl...
> Hi, anyone knows if the 'golden' / rollback state in sql
> 6.5 causes the entire server to hang is a known problem?
> Are there any advices as to what can be done to avoid
> this problem from occurring in the future again? Thanks
6.5 causes the entire server to hang is a known problem?
Are there any advices as to what can be done to avoid
this problem from occurring in the future again? ThanksHi Kimkia.
Golden state applies to spids, not the server - so there is no direct
relationship I can see between a spid in golden and the server hanging. A
spid in golden state could cause an individual database to be inaccessible
but as far as I know, this shouldn't affect the whole server. Then again,
6.5 seems like a distant memory these days for me so perhaps someone else
could chip in with some more information for you.
As far as avoiding golden state is concerned - you should apply normal
deadlock / rollback avoidance techniques - keeping transactions short,
indexes up to date (to help keep transactions short & avoid deadlocks),
inspect regular deadlocks for coding mistakes such as accessing tables in
opposite orders. Also avoid doing major batch work in busy periods so that
you won't have to roll back large transactions during busy periods (during
which spids go golden)
HTH
Regards,
Greg Linwood
SQL Server MVP
"Kimkia" <ahkimkia@.hotmail.com> wrote in message
news:051d01c39dc3$ba897520$a601280a@.phx.gbl...
> Hi, anyone knows if the 'golden' / rollback state in sql
> 6.5 causes the entire server to hang is a known problem?
> Are there any advices as to what can be done to avoid
> this problem from occurring in the future again? Thanks
mssql2k sqloledb.1 (re)connection failure
Hey all, 2 questions.
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen
*-- The connection is already open.
ELSE
create connection
ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @. on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
Any ideas?
If you look for DBPROP_CONNECTIONSTATUS you will see that the status can be
uninitialized, initialized, or communicationfailure.
You cannot get communicationfailure unless you execute something and it fails.|||I suspected that wouldn't be able to do anything with .State once the connection has been made.
Any tips re: the other problem? Is there anything else I need to recreate/reinitialise besides command.activeconnection when i open a new connection?
|||On a second thought the provider can actually check the connection when the property is requested by GetProperties. I think that SQLOLEDB might be doing this. I will have to check the code to see if this is the case, and also how ADO is refreshing the properties, unless of course somebody else does not come up with the answer sooner.|||Could you try checking "Connection Status" property of the ADO Connection?
I beleive that this is how DBPROP_CONNECTIONSTATUS is being exposed from a corresponding provider.
mssql2k sqloledb.1 (re)connection failure
Hey all, 2 questions.
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen
*-- The connection is already open.
ELSE
create connection
ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @. on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
Any ideas?
If you look for DBPROP_CONNECTIONSTATUS you will see that the status can be
uninitialized, initialized, or communicationfailure.
You cannot get communicationfailure unless you execute something and it fails.|||I suspected that wouldn't be able to do anything with .State once the connection has been made.
Any tips re: the other problem? Is there anything else I need to recreate/reinitialise besides command.activeconnection when i open a new connection?
|||On a second thought the provider can actually check the connection when the property is requested by GetProperties. I think that SQLOLEDB might be doing this. I will have to check the code to see if this is the case, and also how ADO is refreshing the properties, unless of course somebody else does not come up with the answer sooner.|||Could you try checking "Connection Status" property of the ADO Connection?
I beleive that this is how DBPROP_CONNECTIONSTATUS is being exposed from a corresponding provider.
Subscribe to:
Posts (Atom)