how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>|||very good point Andrew, sorry for being a little dim.
i read something about a READ ONLY option 2 which is supposed to speed up
queries.
Thanks for your time, great help
Scott|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Monday, March 26, 2012
killer query - limit usage ?
how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>sql
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>sql
Friday, March 9, 2012
Kerberos and merge replication
our server administartors want to shutdown the kerberos
service on our servers due to some problems in active
directory. When they do this, I can no longer connect to
the sql servers through enterprise manager from my
machine (login fails) nor does my merge replications
work. I dont know very much about active directory, and I
am not getting answers that I understand from our admins
why we should shut off the kerberos service. However what
I have read, that service handles security! Our admins
also tell me that microsoft says sql server doesent need
kerberos security? (we are using mixed mode,sql server
2000 sp2) Any information on this would be very much
appreciated.
Thanks!
You should be able to use the sa account and its password to logon and do your work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Larry,
I can understand issues if there is no authentication at all possible on the
network, but there shouldn't be a dependency on Kerberos per se - is NTLM
not running in the absence of Kerberos? Can you use a domain login for
anything else? Kerberos is useful in SQL Server for a double-hop with linked
servers but is not required for replication - SQL Server is supported on NT4
which doesn't have Kerberos and replication works fine there.
HTH,
Paul Ibison
service on our servers due to some problems in active
directory. When they do this, I can no longer connect to
the sql servers through enterprise manager from my
machine (login fails) nor does my merge replications
work. I dont know very much about active directory, and I
am not getting answers that I understand from our admins
why we should shut off the kerberos service. However what
I have read, that service handles security! Our admins
also tell me that microsoft says sql server doesent need
kerberos security? (we are using mixed mode,sql server
2000 sp2) Any information on this would be very much
appreciated.
Thanks!
You should be able to use the sa account and its password to logon and do your work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Larry,
I can understand issues if there is no authentication at all possible on the
network, but there shouldn't be a dependency on Kerberos per se - is NTLM
not running in the absence of Kerberos? Can you use a domain login for
anything else? Kerberos is useful in SQL Server for a double-hop with linked
servers but is not required for replication - SQL Server is supported on NT4
which doesn't have Kerberos and replication works fine there.
HTH,
Paul Ibison
Wednesday, March 7, 2012
Keeping disconnected servers in sync
Hello all.
Because I work at home quite often, I keep updated copies of the databases I
normally work on in the office on my servers at home. This is accomplished
by backing up databases to DAT and restoring when I get home.
These databases have gotten quite large, as databases are wont to do and
while I can still get them on a tape, the backup and restore time is too
much to live with.
Does anyone know of a method to keep these two disconnected servers and/or
databases in sync without a lengthy backup and restore?
Thanks.
David
--
ROT13 my email address to reply directly: qnircra@.gpd.argDavid
Perhaps you want to compress your backup with WINZIP or WINRAR.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> Hello all.
> Because I work at home quite often, I keep updated copies of the databases
I
> normally work on in the office on my servers at home. This is accomplished
> by backing up databases to DAT and restoring when I get home.
> These databases have gotten quite large, as databases are wont to do and
> while I can still get them on a tape, the backup and restore time is too
> much to live with.
> Does anyone know of a method to keep these two disconnected servers and/or
> databases in sync without a lengthy backup and restore?
> Thanks.
> David
> --
> ROT13 my email address to reply directly: qnircra@.gpd.arg
>|||I'm using SQL Backup, so compression is not an option. Backing up 18GB to a
disk device, compressing, and backing up to tape takes longer still.
Thanks anyway.
--
ROT13 my email address to reply: qnircra@.gpd.arg
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> David
> Perhaps you want to compress your backup with WINZIP or WINRAR.
>
>
> "David Pendleton" <qnircra@.gpd.arg> wrote in message
> news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > Hello all.
> >
> > Because I work at home quite often, I keep updated copies of the
databases
> I
> > normally work on in the office on my servers at home. This is
accomplished
> > by backing up databases to DAT and restoring when I get home.
> >
> > These databases have gotten quite large, as databases are wont to do and
> > while I can still get them on a tape, the backup and restore time is too
> > much to live with.
> >
> > Does anyone know of a method to keep these two disconnected servers
and/or
> > databases in sync without a lengthy backup and restore?
> >
> > Thanks.
> >
> > David
> > --
> > ROT13 my email address to reply directly: qnircra@.gpd.arg
> >
> >
>|||David
I don't agree.
> I'm using SQL Backup, so compression is not an option.
What did you mean?
I backup my 15 GB database with compression. You need to write batch file
and place it in system directory.
If you want I can show the example,let me know.
> Backing up 18GB to a
> disk device, compressing, and backing up to tape takes longer still.
After compression I delete .bak file ,so I keep on a device .RAR file of 2
MB.
Regarding to the time belive me it is almost the same.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:e9DLmJhjDHA.688@.TK2MSFTNGP10.phx.gbl...
> I'm using SQL Backup, so compression is not an option. Backing up 18GB to
a
> disk device, compressing, and backing up to tape takes longer still.
> Thanks anyway.
> --
> ROT13 my email address to reply: qnircra@.gpd.arg
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > David
> > Perhaps you want to compress your backup with WINZIP or WINRAR.
> >
> >
> >
> >
> > "David Pendleton" <qnircra@.gpd.arg> wrote in message
> > news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > > Hello all.
> > >
> > > Because I work at home quite often, I keep updated copies of the
> databases
> > I
> > > normally work on in the office on my servers at home. This is
> accomplished
> > > by backing up databases to DAT and restoring when I get home.
> > >
> > > These databases have gotten quite large, as databases are wont to do
and
> > > while I can still get them on a tape, the backup and restore time is
too
> > > much to live with.
> > >
> > > Does anyone know of a method to keep these two disconnected servers
> and/or
> > > databases in sync without a lengthy backup and restore?
> > >
> > > Thanks.
> > >
> > > David
> > > --
> > > ROT13 my email address to reply directly: qnircra@.gpd.arg
> > >
> > >
> >
> >
>
Because I work at home quite often, I keep updated copies of the databases I
normally work on in the office on my servers at home. This is accomplished
by backing up databases to DAT and restoring when I get home.
These databases have gotten quite large, as databases are wont to do and
while I can still get them on a tape, the backup and restore time is too
much to live with.
Does anyone know of a method to keep these two disconnected servers and/or
databases in sync without a lengthy backup and restore?
Thanks.
David
--
ROT13 my email address to reply directly: qnircra@.gpd.argDavid
Perhaps you want to compress your backup with WINZIP or WINRAR.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> Hello all.
> Because I work at home quite often, I keep updated copies of the databases
I
> normally work on in the office on my servers at home. This is accomplished
> by backing up databases to DAT and restoring when I get home.
> These databases have gotten quite large, as databases are wont to do and
> while I can still get them on a tape, the backup and restore time is too
> much to live with.
> Does anyone know of a method to keep these two disconnected servers and/or
> databases in sync without a lengthy backup and restore?
> Thanks.
> David
> --
> ROT13 my email address to reply directly: qnircra@.gpd.arg
>|||I'm using SQL Backup, so compression is not an option. Backing up 18GB to a
disk device, compressing, and backing up to tape takes longer still.
Thanks anyway.
--
ROT13 my email address to reply: qnircra@.gpd.arg
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> David
> Perhaps you want to compress your backup with WINZIP or WINRAR.
>
>
> "David Pendleton" <qnircra@.gpd.arg> wrote in message
> news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > Hello all.
> >
> > Because I work at home quite often, I keep updated copies of the
databases
> I
> > normally work on in the office on my servers at home. This is
accomplished
> > by backing up databases to DAT and restoring when I get home.
> >
> > These databases have gotten quite large, as databases are wont to do and
> > while I can still get them on a tape, the backup and restore time is too
> > much to live with.
> >
> > Does anyone know of a method to keep these two disconnected servers
and/or
> > databases in sync without a lengthy backup and restore?
> >
> > Thanks.
> >
> > David
> > --
> > ROT13 my email address to reply directly: qnircra@.gpd.arg
> >
> >
>|||David
I don't agree.
> I'm using SQL Backup, so compression is not an option.
What did you mean?
I backup my 15 GB database with compression. You need to write batch file
and place it in system directory.
If you want I can show the example,let me know.
> Backing up 18GB to a
> disk device, compressing, and backing up to tape takes longer still.
After compression I delete .bak file ,so I keep on a device .RAR file of 2
MB.
Regarding to the time belive me it is almost the same.
"David Pendleton" <qnircra@.gpd.arg> wrote in message
news:e9DLmJhjDHA.688@.TK2MSFTNGP10.phx.gbl...
> I'm using SQL Backup, so compression is not an option. Backing up 18GB to
a
> disk device, compressing, and backing up to tape takes longer still.
> Thanks anyway.
> --
> ROT13 my email address to reply: qnircra@.gpd.arg
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#iAGMSWjDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > David
> > Perhaps you want to compress your backup with WINZIP or WINRAR.
> >
> >
> >
> >
> > "David Pendleton" <qnircra@.gpd.arg> wrote in message
> > news:#G4Cb6TjDHA.1696@.TK2MSFTNGP12.phx.gbl...
> > > Hello all.
> > >
> > > Because I work at home quite often, I keep updated copies of the
> databases
> > I
> > > normally work on in the office on my servers at home. This is
> accomplished
> > > by backing up databases to DAT and restoring when I get home.
> > >
> > > These databases have gotten quite large, as databases are wont to do
and
> > > while I can still get them on a tape, the backup and restore time is
too
> > > much to live with.
> > >
> > > Does anyone know of a method to keep these two disconnected servers
> and/or
> > > databases in sync without a lengthy backup and restore?
> > >
> > > Thanks.
> > >
> > > David
> > > --
> > > ROT13 my email address to reply directly: qnircra@.gpd.arg
> > >
> > >
> >
> >
>
Keep the same data on two servers
Hi,
I need to maintain the same data across two servers, and I wanted to know what is the best method that I can use to do that.
There is Server A (SQL server 2000) on which the changes are made. And there are few tables which have "text" column, which needs to be replicated to server B (Server server 2000). The changes made on server A should be more or less immedietely replicated (5-10 mins lag is acceptable) to server B.
There are several options I could think of;
(a) Create trigger on server A, which then implements the same changes on server B
(b) Transactional replication (But does this allow text field to be replicated?)
(b) Logshipping
I wanted to know, if there are any other options available.
And If you are suggesting one of the options mentioned above, could you please give me the reason as to why that is the best option.
Thanks very much
GYKTransactional replication would appear the best method for what you are
trying to do. It is easy to set up and will allow text data to be
replicated from Server A to Server B.
Rand
This posting is provided "as is" with no warranties and confers no rights.
I need to maintain the same data across two servers, and I wanted to know what is the best method that I can use to do that.
There is Server A (SQL server 2000) on which the changes are made. And there are few tables which have "text" column, which needs to be replicated to server B (Server server 2000). The changes made on server A should be more or less immedietely replicated (5-10 mins lag is acceptable) to server B.
There are several options I could think of;
(a) Create trigger on server A, which then implements the same changes on server B
(b) Transactional replication (But does this allow text field to be replicated?)
(b) Logshipping
I wanted to know, if there are any other options available.
And If you are suggesting one of the options mentioned above, could you please give me the reason as to why that is the best option.
Thanks very much
GYKTransactional replication would appear the best method for what you are
trying to do. It is easy to set up and will allow text data to be
replicated from Server A to Server B.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Friday, February 24, 2012
Keep registered servers in tact
Every time I register servers in Management Studio, the next time I reopen management studio after closing it, they all disappear. Is there a setting somewhere to tell SSMS to keep those settings?Hmmm... That's strange. The data for registered servers is stored in C:\Documents and Settings\%User%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml take a look at that file between sessions.
Monday, February 20, 2012
Keep all servers registered and connected
I have 5 servers in my Server Mgmt Studio divided into 4 groups,
Local
Production
Test
Development
In Sql 2000 Enterprise Manager would put the little green arrow next to your db if it was connected, I noticed that Sql 2005 does do that but it wont allow me to keep all 5 servers connected and I need them all to stay connected.
If I look under Registered Servers it shows all 5 servers there but again only 2 show as connected even if I connect the other ones they dont stay connected. I have deleted the registerd ones and re added them and still no difference.
How can I keep all servers registered and connected and then have them show up on start-up?
Thanks!
Stokh
Launch Mgmt Studio
In the Registered Servers tool window right-click on the server and select Properties
On the Properties dialog select the Connection Properties tab
Set Connection time-out to 0 (the default is 15 seconds; 0 indicates no time-out).
|||I already did that and it still does the same thing, only shows 2 servers registered and doesnt keep the other 3 connected|||I suggest you file a bug using the Product Feedback Center: http://lab.msdn.microsoft.com/productfeedback/Default.aspx.|||I would also like to complain about this new 2005 behavior. As a DBA managing dozens of servers, it is ridiculous to keep jumping through the connection hoops in 2005 whenever I switch server context. It's hard to believe MS could screw up something so basic... and something that worked perfectly well in 2000.|||The best thing to do is search the Product Feedback Center for the issue and vote on it. In SP2 we are targeting many of the annoyances of the Mgmt Studio but we need your votes. The voting system let's us know what issues are causing pain for people and those tend to float to the top part of the list.KB913580 related problem
I hope this is posted in the correct forum.
I have Linked Servers that are used to query Access databases that reside on a file server.This has been working for a long time, until I applied KB913580 which references the Distributed Transaction Controller. I am running SQL Server 2000 on the same desktop (development environment) from which I am accessing the Linked Server.The file server is on the same LAN segment – there is no firewall involved.
I uninstalled the update and rebooted but still getting the error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\tacir2k3\Infrastructure\Databases\July 2003 Databases\General\GenDb2004_TR_Db.mdb'.It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:].
The SQL Server log contains the following (see bolded item):
2006-05-12 07:58:29.79 serverMicrosoft SQL Server2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
2006-05-12 07:58:29.79 serverCopyright (C) 1988-2002 Microsoft Corporation.
2006-05-12 07:58:29.79 serverAll rights reserved.
2006-05-12 07:58:29.79 serverServer Process ID is 1720.
2006-05-12 07:58:29.79 serverLogging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2006-05-12 07:58:29.98 serverSQL Server is starting at priority class 'normal'(1 CPU detected).
2006-05-12 07:58:31.85 serverSQL Server configured for thread mode processing.
2006-05-12 07:58:31.95 serverUsing dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-05-12 07:58:32.18 serverAttempting to initialize Distributed Transaction Coordinator.
2006-05-12 07:58:32.37 serverFailed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-12 07:58:32.46 spid3Starting up database 'master'.
The Distributed Transaction Coordinator service is started. The settings are all set to OFF (0).
A query to a table in the linked server returns:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\tacir2k3\Infrastructure\Databases\July 2003 Databases\General\GenDb2004_TR_Db.mdb'.It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:].
(It is not in use by any other user).
exec xp_cmdshell 'type \\TACIR2K3\infrastructure\Databases\July 2004 Databases\General\GenDb2005_TR_Db.mdb'
This returns” “access is denied”
Is there any way to resolve this problem?
Hi,
I am not sure if the issue is related to the patch or not.
Most probably uninstalling the patch won't help. I have experienced few issues after installing this patch on Windows 2000 server.
Even uninstalling didn't help.
Solution which worked:
Uninstall the patch, reboot.
Install the patch manually from Microsoft website and not from windows update site, reboot and the issue should be fixed if it's related to the patch.
The patch if installed from windows update is not updating few core files in Windows 2000 server. Manual install is able to update the files.
|||I installed KB913580 on a Windows 2000 server hosting our Exchange Server (5.5). This sits on an internal IP and receives traffic from a TrimMail (mail filtering device). The TrimMail gets an internal IP NATted from a Cisco Pix firewall. The firewall is connected directly to a cable modem (the router is a Cisco "router on a stick" handled through a trunked vlan on a Cisco Catalyst 6600 switch).
The way it is designed, the TrimMail device receives mail, filters, forwards to the Exchange server. If the Exchange server is unavailable, TrimMail holds the mail for up to five days.
After the installation of KB913580, incoming mail from outside our network stopped. Some of the incoming mail bounced, some was acquired by TrimMail and held but not delivered to Exchange. Every "ping" from inside the network to any outside address failed. Browser traffic worked fine without any delays, so the connection was all right.
When KB913580 was uninstalled, "ping" worked, mail came through fine.
I hate to think we are exposed to the security vulnerability this patch is designed to fix, but until I figure out what happened and why, I cannot re-install it.
Any ideas?
|||Yes, there's no doubt in my mind that this patch caused these problems and the problems I have encountered.
I applied the Microsoft patch MS06-018 (KB913580) Vulnerability in Microsoft Distributed Transaction Coordinator Could Allow Denial of Service on the 13th (4 days after it was issued) to my dedicated server (at a hosting site) running SQL2K SP4. Since then, on two occasions, I needed to reboot my server (Win 2K Server SP4) and the server took exceptionally long to boot and then when I finally gained access to the server SQL Server EM could not find my server (my databases were gone), I couldn't access Internet sites with IE (although I could ping remote sites), and I couldn't even open the Control Panel to look at network services. In the Services menu, it just said mssql.exe "Starting...". When that service was stopped, the network stuff all happened so I could access sites, and I could restart SQL Server.
Excerpts from the SQL Server Log:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Attempting to initialize Distributed Transaction Coordinator.
(12 -30 minutes later)
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
Could not set up Net-Library 'SSNETLIB'..
Unable to load any netlibs.
SQL Server could not spawn FRunCM thread.
Does anyone know SPECIFICALLY the issue here? This is all on a production server where I cannot experiment with uninstalling/rebooting/installing/rebooting etc.
Thanks for any help.
|||I did as you suggested and rebooted.
The SQL Server log errors related to the Distributed Transaction Coordinator are gone and I can reference the linked database.
Thank you many times!!! I would never have come to this solution.
|||Well I spoke too soon about this solving my problem. Got one good SQL Server start and now I am back to the same problem.
I also noticed a couple of other problems that started occurring after KB900485 was applied on 4/28/2006. I tried the suggestion regarding reinstalling this patch from the MS site and keep getting the automatic updates notice for it when I reboot. It displays on the Add/Remove programs list.
I get about 21 messages " Error 15457. Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install." These started on 5/1/2006 after KB900485 was applied. On the last test these occurred at the time I executed SQL to access the linked database.
I looked closer at the Events log and notice that I also started getting the following error related to MSSQLServer after KB900485 was applied. "SuperSocket into: (SpnRegister): Error 1355.
GRRRRR. Anyone with any other suggestions.
|||
I had issues with KB913580 and developed a bunch of strange problems on my server and it turned out to be a tape driver conflict.
Everything else that happened was a symptom of the problem and not the actual problem. I could not log out or shutdown the server, I could not browse the internet and some of the core services were not started or timed out etc which caused a series of problems overall.
So, if you have backup exec v10D installed on this server and have the veritas drivers installed try removing the veritas tape drivers and go to your native tape drivers from MS or the Manufacture and see if your other problems go away. It may take two reboots for the tape drives and veritas to work properly. Once to initially identify the tape drive and install the drivers and then a second reboot to actually load the drivers.
Let me know if you have this situation and if it corrects it.
|||After a MONTH of PSS from MS, dozens of hours of emails and phone calls, and several PSS techs, it turns out that this was a SQL Server bug, that occasionally appears on some Windows 2000 SP4 machines when the KB913580 patch changes something about the order of startup services.
The fix can be found here:
http://support.microsoft.com/?kbid=917405
Note: Much of this time was wasted because the KB913580 patch could not be completely uninstalled (as expected) once it was manually installed (not from the update process).
KB892923....questions.
We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/defaul...kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/defaul...kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
KB892923....questions.
We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default.aspx?scid=kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default.aspx?scid=kb;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
KB892923....questions.
We do weekly fulls, daily differentials, and hourly trans logs on all user
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default...b;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?
> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
db's and on all SQL servers (~30). So I am concerned and what to understand
completely.
http://support.microsoft.com/default...b;en-us;892923
FIX: Differential database backups may not contain database changes in the
Page Free Space (PFS) pages in SQL Server 2000
snip>
In SQL Server 2000, differential backups may not contain Page Free Space
(PFS) pages if the following conditions are true: . The PFS page was
allocated between the last full backup and the differential backup.
. The PFS page allocation was part of a file growth that is larger
than 64 MB
Concerning first bullet.
1) How would the PFS page be allocated between Full and Diff if there was
not some sort of db growth ether auto or manual?
Concerning second bullet.
2) Is this implying if one of the data files grows ether auto or manual in
one leap more than 64mb?
> 2) Is this implying if one of the data files grows ether auto or manual in
> one leap more than 64mb?
Sure, if your database file is 40 GB and you have autogrow set to 10%,
that's a lot more than 64 MB.
Subscribe to:
Posts (Atom)