Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
--
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table> SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
--
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
>> How about below?
>> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Friday, March 30, 2012
long time process - what's the progress?
Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PLHow about below?
SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piotr Lipski" <pl@.mibi.pl> wrote in message news:fimij9$56h$1@.news.onet.pl...n">
> Hi.
> SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql table>
SELECT * FROM
> [I].[SERIES].[TA].[BLE]
> There are about 23mln records to copy. I's been running for the last half
an hour and I would like
> to check what the progress is. Is there any way of doing this?
> --
> PL|||Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
long time process - what's the progress?
Hi.
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
SQL 2005 Std. Linked server to iSeries. I have run INSERT INTO <sql
table> SELECT * FROM [I].[SERIES].[TA].[BLE]
There are about 23mln records to copy. I's been running for the last
half an hour and I would like to check what the progress is. Is there
any way of doing this?
PL
Tibor Karaszi wrote:
> How about below?
> SELECT COUNT(*) FROM <sql table> WITH(NOLOCK)
The result is zero. I guess that the whole import is being done through
some kind of temporary storage and enclosed in a transaction so it can
be tricky (impossible?) to see the progress.
Any other ideas?
PL
|||Piotr,
I suppose that if you know the size of your data and any other update load
being placed on the database, you could track the size of your transaction
log to see how much it is growing as the data is imported.
DBCC SQLPERF(LogSpace)
So, assume:
23,000,000 rows at 50 bytes average size.
Indexes add about 20% in size. (Depends on your index definitions)
Add another 20% overhead for the log entries.
1,656,000,000 bytes of log space
These are definitely just made up numbers, but do your own calculation
(taking into account all the factors that I do not know) and maybe you can
guess. (Truth in Advertising: I have never tried to use this approach for
figuring out the 'progress bar'.)
RLF
"Piotr Lipski" <pl@.mibi.pl> wrote in message
news:fimklr$bj5$1@.news.onet.pl...
> Tibor Karaszi wrote:
> The result is zero. I guess that the whole import is being done through
> some kind of temporary storage and enclosed in a transaction so it can be
> tricky (impossible?) to see the progress.
> Any other ideas?
> --
> PL
Wednesday, March 28, 2012
Long running queries with low priority
When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I donâ't want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server â' â'run this, but if anything else
happens, give that the first priority!â'
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don?t want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server ? ?run this, but if anything
else
> happens, give that the first priority!?
> Any suggestions?
>
>|||Sorry, but i don´t think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||I know that DB2 has something called the â'Query Patrollerâ' that can be useed
to proactively and dynamically control the flow of queries against your DB2
database in the following key ways:
a. Define separate query classes for queries of different sizes to better
share system resources among queries and to prevent smaller queries from
getting stuck behind larger ones
b. Give queries submitted by certain users high priority so that these
queries run sooner
c. Automatically put large queries on hold so that they can be cancelled or
scheduled to run during off-peak hours
d. Track and cancel runaway queries
The purpose of this is to allow you to regulate your database's query
workload so that small queries and high-priority queries can run promptly and
your system resources are used efficiently.
I donâ't thing these thing are unreasonable requirements for a database
server in the enterprise. Iâ'm surprised the this seems so hard to do on Sql
server.
Have anyone looked into the features on the Sql server 2005? Any
improvements there?
Are there any of you guys that has been struggling with the same problems â'
and found a solution or a workaround?
"pehuan" wrote:
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I donâ't want them to take any resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server â' â'run this, but if anything else
> happens, give that the first priority!â'
> Any suggestions?
>
>|||Read up this article
http://www.databasejournal.com/features/mssql/article.php/3427581
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> I know that DB2 has something called the ?Query Patroller? that can be
useed
> to proactively and dynamically control the flow of queries against your
DB2
> database in the following key ways:
> a. Define separate query classes for queries of different sizes to better
> share system resources among queries and to prevent smaller queries from
> getting stuck behind larger ones
> b. Give queries submitted by certain users high priority so that these
> queries run sooner
> c. Automatically put large queries on hold so that they can be cancelled
or
> scheduled to run during off-peak hours
> d. Track and cancel runaway queries
> The purpose of this is to allow you to regulate your database's query
> workload so that small queries and high-priority queries can run promptly
and
> your system resources are used efficiently.
> I don?t thing these thing are unreasonable requirements for a database
> server in the enterprise. I?m surprised the this seems so hard to do on
Sql
> server.
> Have anyone looked into the features on the Sql server 2005? Any
> improvements there?
> Are there any of you guys that has been struggling with the same
problems ?
> and found a solution or a workaround?
>
> "pehuan" wrote:
> > When you run a process on you Windows machine, you can set the process
> > priority to 'low', if you don't want the process to take resources away
form
> > other processes running in the same timeframe.
> >
> > Does anyone know of a similar technique for long running queries on Sql
> > server 2000 or 2005?
> >
> > I have a series on long running analytical queries, where the query
> > execution time is not important, and I don?t want them to take any
resources
> > away form other queries running in the same timeframe.
> >
> > I would like a way to tell the Sql server ? ?run this, but if anything
else
> > happens, give that the first priority!?
> >
> > Any suggestions?
> >
> >
> >|||Hi Uri,
Thanks for the article â' however there question here is not whether spâ's or
queries can be executed asynchronously from the application, but rather how
the machine resource consumption of particular queries can be controlled so
the wonâ't affect the execution time of other queries or spâ's running in
parallel.
Cheers
pehuan
"Uri Dimant" wrote:
> Read up this article
> http://www.databasejournal.com/features/mssql/article.php/3427581
> "pehuan" <pehuan@.discussions.microsoft.com> wrote in message
> news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> > I know that DB2 has something called the â'Query Patrollerâ' that can be
> useed
> > to proactively and dynamically control the flow of queries against your
> DB2
> > database in the following key ways:
> >
> > a. Define separate query classes for queries of different sizes to better
> > share system resources among queries and to prevent smaller queries from
> > getting stuck behind larger ones
> >
> > b. Give queries submitted by certain users high priority so that these
> > queries run sooner
> >
> > c. Automatically put large queries on hold so that they can be cancelled
> or
> > scheduled to run during off-peak hours
> >
> > d. Track and cancel runaway queries
> >
> > The purpose of this is to allow you to regulate your database's query
> > workload so that small queries and high-priority queries can run promptly
> and
> > your system resources are used efficiently.
> >
> > I donâ't thing these thing are unreasonable requirements for a database
> > server in the enterprise. Iâ'm surprised the this seems so hard to do on
> Sql
> > server.
> >
> > Have anyone looked into the features on the Sql server 2005? Any
> > improvements there?
> >
> > Are there any of you guys that has been struggling with the same
> problems â'
> > and found a solution or a workaround?
> >
> >
> > "pehuan" wrote:
> >
> > > When you run a process on you Windows machine, you can set the process
> > > priority to 'low', if you don't want the process to take resources away
> form
> > > other processes running in the same timeframe.
> > >
> > > Does anyone know of a similar technique for long running queries on Sql
> > > server 2000 or 2005?
> > >
> > > I have a series on long running analytical queries, where the query
> > > execution time is not important, and I donâ't want them to take any
> resources
> > > away form other queries running in the same timeframe.
> > >
> > > I would like a way to tell the Sql server â' â'run this, but if anything
> else
> > > happens, give that the first priority!â'
> > >
> > > Any suggestions?
> > >
> > >
> > >
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I donâ't want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server â' â'run this, but if anything else
happens, give that the first priority!â'
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don?t want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server ? ?run this, but if anything
else
> happens, give that the first priority!?
> Any suggestions?
>
>|||Sorry, but i don´t think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||I know that DB2 has something called the â'Query Patrollerâ' that can be useed
to proactively and dynamically control the flow of queries against your DB2
database in the following key ways:
a. Define separate query classes for queries of different sizes to better
share system resources among queries and to prevent smaller queries from
getting stuck behind larger ones
b. Give queries submitted by certain users high priority so that these
queries run sooner
c. Automatically put large queries on hold so that they can be cancelled or
scheduled to run during off-peak hours
d. Track and cancel runaway queries
The purpose of this is to allow you to regulate your database's query
workload so that small queries and high-priority queries can run promptly and
your system resources are used efficiently.
I donâ't thing these thing are unreasonable requirements for a database
server in the enterprise. Iâ'm surprised the this seems so hard to do on Sql
server.
Have anyone looked into the features on the Sql server 2005? Any
improvements there?
Are there any of you guys that has been struggling with the same problems â'
and found a solution or a workaround?
"pehuan" wrote:
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I donâ't want them to take any resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server â' â'run this, but if anything else
> happens, give that the first priority!â'
> Any suggestions?
>
>|||Read up this article
http://www.databasejournal.com/features/mssql/article.php/3427581
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> I know that DB2 has something called the ?Query Patroller? that can be
useed
> to proactively and dynamically control the flow of queries against your
DB2
> database in the following key ways:
> a. Define separate query classes for queries of different sizes to better
> share system resources among queries and to prevent smaller queries from
> getting stuck behind larger ones
> b. Give queries submitted by certain users high priority so that these
> queries run sooner
> c. Automatically put large queries on hold so that they can be cancelled
or
> scheduled to run during off-peak hours
> d. Track and cancel runaway queries
> The purpose of this is to allow you to regulate your database's query
> workload so that small queries and high-priority queries can run promptly
and
> your system resources are used efficiently.
> I don?t thing these thing are unreasonable requirements for a database
> server in the enterprise. I?m surprised the this seems so hard to do on
Sql
> server.
> Have anyone looked into the features on the Sql server 2005? Any
> improvements there?
> Are there any of you guys that has been struggling with the same
problems ?
> and found a solution or a workaround?
>
> "pehuan" wrote:
> > When you run a process on you Windows machine, you can set the process
> > priority to 'low', if you don't want the process to take resources away
form
> > other processes running in the same timeframe.
> >
> > Does anyone know of a similar technique for long running queries on Sql
> > server 2000 or 2005?
> >
> > I have a series on long running analytical queries, where the query
> > execution time is not important, and I don?t want them to take any
resources
> > away form other queries running in the same timeframe.
> >
> > I would like a way to tell the Sql server ? ?run this, but if anything
else
> > happens, give that the first priority!?
> >
> > Any suggestions?
> >
> >
> >|||Hi Uri,
Thanks for the article â' however there question here is not whether spâ's or
queries can be executed asynchronously from the application, but rather how
the machine resource consumption of particular queries can be controlled so
the wonâ't affect the execution time of other queries or spâ's running in
parallel.
Cheers
pehuan
"Uri Dimant" wrote:
> Read up this article
> http://www.databasejournal.com/features/mssql/article.php/3427581
> "pehuan" <pehuan@.discussions.microsoft.com> wrote in message
> news:66FFBDA0-98CA-44F3-A4ED-A3B43958C546@.microsoft.com...
> > I know that DB2 has something called the â'Query Patrollerâ' that can be
> useed
> > to proactively and dynamically control the flow of queries against your
> DB2
> > database in the following key ways:
> >
> > a. Define separate query classes for queries of different sizes to better
> > share system resources among queries and to prevent smaller queries from
> > getting stuck behind larger ones
> >
> > b. Give queries submitted by certain users high priority so that these
> > queries run sooner
> >
> > c. Automatically put large queries on hold so that they can be cancelled
> or
> > scheduled to run during off-peak hours
> >
> > d. Track and cancel runaway queries
> >
> > The purpose of this is to allow you to regulate your database's query
> > workload so that small queries and high-priority queries can run promptly
> and
> > your system resources are used efficiently.
> >
> > I donâ't thing these thing are unreasonable requirements for a database
> > server in the enterprise. Iâ'm surprised the this seems so hard to do on
> Sql
> > server.
> >
> > Have anyone looked into the features on the Sql server 2005? Any
> > improvements there?
> >
> > Are there any of you guys that has been struggling with the same
> problems â'
> > and found a solution or a workaround?
> >
> >
> > "pehuan" wrote:
> >
> > > When you run a process on you Windows machine, you can set the process
> > > priority to 'low', if you don't want the process to take resources away
> form
> > > other processes running in the same timeframe.
> > >
> > > Does anyone know of a similar technique for long running queries on Sql
> > > server 2000 or 2005?
> > >
> > > I have a series on long running analytical queries, where the query
> > > execution time is not important, and I donâ't want them to take any
> resources
> > > away form other queries running in the same timeframe.
> > >
> > > I would like a way to tell the Sql server â' â'run this, but if anything
> else
> > > happens, give that the first priority!â'
> > >
> > > Any suggestions?
> > >
> > >
> > >
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
--
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
Long running queries with low priority
When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resourc
es
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything e
lse
happens, give that the first priority!”
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resourc
es
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything e
lse
happens, give that the first priority!”
Any suggestions?pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
Long running queries with low priority
When you run a process on you Windows machine, you can set the process
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything else
happens, give that the first priority!”
Any suggestions?
pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>
|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
priority to 'low', if you don't want the process to take resources away form
other processes running in the same timeframe.
Does anyone know of a similar technique for long running queries on Sql
server 2000 or 2005?
I have a series on long running analytical queries, where the query
execution time is not important, and I don’t want them to take any resources
away form other queries running in the same timeframe.
I would like a way to tell the Sql server – “run this, but if anything else
happens, give that the first priority!”
Any suggestions?
pehuan
I'm afraid you cannot manage it. Perhaps it's time to start an optimization
of the long running queries.
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I dont want them to take any
resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server run this, but if anything
else
> happens, give that the first priority!
> Any suggestions?
>
>
|||Sorry, but i dont think there is a way to do this.
Jens SUessmeyer.
"pehuan" <pehuan@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
|||On a multi-processor box, you can use OPTION(MAXDOP 1) with the query, so
that it only uses 1 processor, but that is as far as you can get.
Jacco Schalkwijk
SQL Server MVP
"pehuan" <pehuan@.discussions.microsoft.com> wrote in message
news:F35C0287-0412-444F-9A96-38BEB106D0F1@.microsoft.com...
> When you run a process on you Windows machine, you can set the process
> priority to 'low', if you don't want the process to take resources away
> form
> other processes running in the same timeframe.
> Does anyone know of a similar technique for long running queries on Sql
> server 2000 or 2005?
> I have a series on long running analytical queries, where the query
> execution time is not important, and I don't want them to take any
> resources
> away form other queries running in the same timeframe.
> I would like a way to tell the Sql server - "run this, but if anything
> else
> happens, give that the first priority!"
> Any suggestions?
>
>
Long running open connections badly leak memory
Hi,
I wrote a batch import process that uses connection pooling, i.e. the
connections are never closed until the process is finished. I am writing
large chunks of data (byte[]) into the database, and I made absolutely
sure that my application does not hold any references to the byte[]
objects. However, the process increasingly consumes memory until an
OutOfMemoryError, and I tracked it down to the Microsoft JDBC driver
still holding references to the byte[] objects. The workaround is to
clear the pool "from time to time", closing its connections. When I do
that, the memory is properly reclaimed by the GC.
This "from time to time" involves some unsatisfying heuristics, though
(apart from the inconvenience of having to find the pool and access it).
If the driver is doing any caching on purpose, it should be documented
somewhere, and it should be configurable. My guess is that it's not on
purpose, though. I can't see much sense in holding on to written data
after a transaction is finished.
In case any driver developer should be interested, using a profiling
tool I found references from the following driver classes to a byte[]
object that should normally be reclaimed by the GC:
com.microsoft.jdbc.sqlserver.SQLServerPacketizingD ataConsumer and
com.microsoft.jdbc.sqlserver.tds.TDSConnection
Regards,
Jrg.
Joerg von Frantzius wrote:
> Hi,
> I wrote a batch import process that uses connection pooling, i.e. the
> connections are never closed until the process is finished. I am
writing
> large chunks of data (byte[]) into the database, and I made
absolutely
> sure that my application does not hold any references to the byte[]
> objects. However, the process increasingly consumes memory until an
> OutOfMemoryError, and I tracked it down to the Microsoft JDBC driver
> still holding references to the byte[] objects. The workaround is to
> clear the pool "from time to time", closing its connections. When I
do
> that, the memory is properly reclaimed by the GC.
> This "from time to time" involves some unsatisfying heuristics,
though
> (apart from the inconvenience of having to find the pool and access
it).
> If the driver is doing any caching on purpose, it should be
documented
> somewhere, and it should be configurable. My guess is that it's not
on
> purpose, though. I can't see much sense in holding on to written data
> after a transaction is finished.
> In case any driver developer should be interested, using a profiling
> tool I found references from the following driver classes to a byte[]
> object that should normally be reclaimed by the GC:
> com.microsoft.jdbc.sqlserver.SQLServerPacketizingD ataConsumer and
> com.microsoft.jdbc.sqlserver.tds.TDSConnection
The MS driver does indeed do a lot of unnecessary caching (such as
caching the whole ResultSet in direct mode), but I don't think this is
your problem here.
You are closing all JDBC resources (i.e. connections, statements and
result sets), aren't you? Even if SQLExceptions are thrown...
Alin,
The jTDS Project.
|||Alin Sinpalean schrieb:
>The MS driver does indeed do a lot of unnecessary caching (such as
>caching the whole ResultSet in direct mode), but I don't think this is
>your problem here.
>You are closing all JDBC resources (i.e. connections, statements and
>result sets), aren't you? Even if SQLExceptions are thrown...
>Alin,
>The jTDS Project.
>
When I close the connections, memory is reclaimed properly. What I
wanted to say is that memory is eaten up indefinitely as long as the
connection *remains open* and is used to repeatedly write large chunks
of data into the DB.
|||Joerg von Frantzius wrote:
> When I close the connections, memory is reclaimed properly. What I
> wanted to say is that memory is eaten up indefinitely as long as the
> connection *remains open* and is used to repeatedly write large
chunks
> of data into the DB.
I understand that. What I meant to say is "are you sure you are
explicitly closing ALL Statements and ResultSets when you're done with
them?". Leaving them open could and will cause the effects you now see.
Alin.
|||<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Alin Sinpalean schrieb:
<blockquote
cite="mid1109058703.898619.117040@.f14g2000cwb.goog legroups.com"
type="cite">
<pre wrap="">Joerg von Frantzius wrote:
</pre>
<blockquote type="cite">
<pre wrap="">When I close the connections, memory is reclaimed properly. What I
wanted to say is that memory is eaten up indefinitely as long as the
connection *remains open* and is used to repeatedly write large
</pre>
</blockquote>
<pre wrap=""><!-->chunks
</pre>
<blockquote type="cite">
<pre wrap="">of data into the DB.
</pre>
</blockquote>
<pre wrap=""><!-->
I understand that. What I meant to say is "are you sure you are
explicitly closing ALL Statements and ResultSets when you're done with
them?". Leaving them open could and will cause the effects you now see.
Alin.
</pre>
</blockquote>
Alright, now I got the point about the Statements and ResultSets,
sorry. To be honest, I can't be absolutely sure about that, because I'm
relying on a JDO implementation to take care of it.<br>
</body>
</html>
|||Joerg von Frantzius wrote:
> Alright, now I got the point about the Statements and ResultSets,
sorry.
> To be honest, I can't be absolutely sure about that, because I'm
relying
> on a JDO implementation to take care of it.
You could try jTDS (disclaimer: I'm a jTDS developer). I'm not saying
it will definitely make the problem disappear, but at least I will be
able to help more.
Alin.
I wrote a batch import process that uses connection pooling, i.e. the
connections are never closed until the process is finished. I am writing
large chunks of data (byte[]) into the database, and I made absolutely
sure that my application does not hold any references to the byte[]
objects. However, the process increasingly consumes memory until an
OutOfMemoryError, and I tracked it down to the Microsoft JDBC driver
still holding references to the byte[] objects. The workaround is to
clear the pool "from time to time", closing its connections. When I do
that, the memory is properly reclaimed by the GC.
This "from time to time" involves some unsatisfying heuristics, though
(apart from the inconvenience of having to find the pool and access it).
If the driver is doing any caching on purpose, it should be documented
somewhere, and it should be configurable. My guess is that it's not on
purpose, though. I can't see much sense in holding on to written data
after a transaction is finished.
In case any driver developer should be interested, using a profiling
tool I found references from the following driver classes to a byte[]
object that should normally be reclaimed by the GC:
com.microsoft.jdbc.sqlserver.SQLServerPacketizingD ataConsumer and
com.microsoft.jdbc.sqlserver.tds.TDSConnection
Regards,
Jrg.
Joerg von Frantzius wrote:
> Hi,
> I wrote a batch import process that uses connection pooling, i.e. the
> connections are never closed until the process is finished. I am
writing
> large chunks of data (byte[]) into the database, and I made
absolutely
> sure that my application does not hold any references to the byte[]
> objects. However, the process increasingly consumes memory until an
> OutOfMemoryError, and I tracked it down to the Microsoft JDBC driver
> still holding references to the byte[] objects. The workaround is to
> clear the pool "from time to time", closing its connections. When I
do
> that, the memory is properly reclaimed by the GC.
> This "from time to time" involves some unsatisfying heuristics,
though
> (apart from the inconvenience of having to find the pool and access
it).
> If the driver is doing any caching on purpose, it should be
documented
> somewhere, and it should be configurable. My guess is that it's not
on
> purpose, though. I can't see much sense in holding on to written data
> after a transaction is finished.
> In case any driver developer should be interested, using a profiling
> tool I found references from the following driver classes to a byte[]
> object that should normally be reclaimed by the GC:
> com.microsoft.jdbc.sqlserver.SQLServerPacketizingD ataConsumer and
> com.microsoft.jdbc.sqlserver.tds.TDSConnection
The MS driver does indeed do a lot of unnecessary caching (such as
caching the whole ResultSet in direct mode), but I don't think this is
your problem here.
You are closing all JDBC resources (i.e. connections, statements and
result sets), aren't you? Even if SQLExceptions are thrown...
Alin,
The jTDS Project.
|||Alin Sinpalean schrieb:
>The MS driver does indeed do a lot of unnecessary caching (such as
>caching the whole ResultSet in direct mode), but I don't think this is
>your problem here.
>You are closing all JDBC resources (i.e. connections, statements and
>result sets), aren't you? Even if SQLExceptions are thrown...
>Alin,
>The jTDS Project.
>
When I close the connections, memory is reclaimed properly. What I
wanted to say is that memory is eaten up indefinitely as long as the
connection *remains open* and is used to repeatedly write large chunks
of data into the DB.
|||Joerg von Frantzius wrote:
> When I close the connections, memory is reclaimed properly. What I
> wanted to say is that memory is eaten up indefinitely as long as the
> connection *remains open* and is used to repeatedly write large
chunks
> of data into the DB.
I understand that. What I meant to say is "are you sure you are
explicitly closing ALL Statements and ResultSets when you're done with
them?". Leaving them open could and will cause the effects you now see.
Alin.
|||<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Alin Sinpalean schrieb:
<blockquote
cite="mid1109058703.898619.117040@.f14g2000cwb.goog legroups.com"
type="cite">
<pre wrap="">Joerg von Frantzius wrote:
</pre>
<blockquote type="cite">
<pre wrap="">When I close the connections, memory is reclaimed properly. What I
wanted to say is that memory is eaten up indefinitely as long as the
connection *remains open* and is used to repeatedly write large
</pre>
</blockquote>
<pre wrap=""><!-->chunks
</pre>
<blockquote type="cite">
<pre wrap="">of data into the DB.
</pre>
</blockquote>
<pre wrap=""><!-->
I understand that. What I meant to say is "are you sure you are
explicitly closing ALL Statements and ResultSets when you're done with
them?". Leaving them open could and will cause the effects you now see.
Alin.
</pre>
</blockquote>
Alright, now I got the point about the Statements and ResultSets,
sorry. To be honest, I can't be absolutely sure about that, because I'm
relying on a JDO implementation to take care of it.<br>
</body>
</html>
|||Joerg von Frantzius wrote:
> Alright, now I got the point about the Statements and ResultSets,
sorry.
> To be honest, I can't be absolutely sure about that, because I'm
relying
> on a JDO implementation to take care of it.
You could try jTDS (disclaimer: I'm a jTDS developer). I'm not saying
it will definitely make the problem disappear, but at least I will be
able to help more.
Alin.
Wednesday, March 21, 2012
logs getting full
Hi all. If we have a long running process for a database and the log
is threatening to hit 100%, whats the best way to resolve it?"sumGirl" <emebohw@.netscape.net> wrote in message
news:a5e13cff.0410160524.3a7087c8@.posting.google.c om...
> Hi all. If we have a long running process for a database and the log
> is threatening to hit 100%, whats the best way to resolve it?
is threatening to hit 100%, whats the best way to resolve it?"sumGirl" <emebohw@.netscape.net> wrote in message
news:a5e13cff.0410160524.3a7087c8@.posting.google.c om...
> Hi all. If we have a long running process for a database and the log
> is threatening to hit 100%, whats the best way to resolve it?
Expand the log or turn the long running processes into smaller chunks.
Logreader failed to construct replicated command from LSN
I have this error coming up 'The process could not execute 'sp_replcmds' on
'VDB1' which always seems to accompany 'Logreader failed to construct
replicated command from LSN {0008e253:00009ddb:0003}.'
I turned on verbose logging and it gave this:
2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
500000)}
2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
Looking up that sequence number with:
exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
, @.xact_seqno_end = '0x0008e25300009ddb0005'
Returned nothing.
I also tried:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
It returned nothing also.
This same error is preventing 2 publications, but other 8 are working fine.
I have stopped and started log reader job a couple of times.
Any ideas?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
I've bumped into this from time to time. What you need to do is to run
profiler with the xactSequence number column.
Next time you get this error, look up the offending statement, for example
it could be a proc which is writing something unexpected to the database.
Last time I got this, it was an update to a text column with a ''.
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
>I have this error coming up 'The process could not execute 'sp_replcmds' on
> 'VDB1' which always seems to accompany 'Logreader failed to construct
> replicated command from LSN {0008e253:00009ddb:0003}.'
> I turned on verbose logging and it gave this:
> 2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
> 500000)}
> 2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
> Looking up that sequence number with:
> exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
> , @.xact_seqno_end = '0x0008e25300009ddb0005'
> Returned nothing.
> I also tried:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> It returned nothing also.
> This same error is preventing 2 publications, but other 8 are working
> fine.
> I have stopped and started log reader job a couple of times.
> Any ideas?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||Update:
One of the two publications started to work, not sure why or when.
The batchsize to 1 still brought back the same LSN error - which is still
returning nothing when I query it with:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
We are going to kill the publications and recreate them - luckily they are
not super critical like the others.
Still having Replication Monitor issues (Still running SP1 on server; SP2
on my local box).
-Chuck
On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||While recreating the publications, the snapshot errored because of an
obsolete XSD for an XML table. We have the option set to false to publish
the XSD's, so not sure how this came about.
Now that these are working, the Replication Monitor server nodes can be
expanded now.
-Chuck
On Fri, 4 May 2007 10:15:26 -0700, Chuck Lathrope wrote:
[vbcol=seagreen]
> Update:
> One of the two publications started to work, not sure why or when.
> The batchsize to 1 still brought back the same LSN error - which is still
> returning nothing when I query it with:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> We are going to kill the publications and recreate them - luckily they are
> not super critical like the others.
> Still having Replication Monitor issues (Still running SP1 on server; SP2
> on my local box).
> -Chuck
>
> On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
> Thanks, it only seems to be getting worse for me. The replication monitor
> can't expand the server nodes now. I restarted sql service on the
> distributor and that didn't help. Has anyone seen that?
> I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
> can find to correct issue as this command in error reports looks to be
> pointed to something already completed.
> -Chuck
'VDB1' which always seems to accompany 'Logreader failed to construct
replicated command from LSN {0008e253:00009ddb:0003}.'
I turned on verbose logging and it gave this:
2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
500000)}
2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
Looking up that sequence number with:
exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
, @.xact_seqno_end = '0x0008e25300009ddb0005'
Returned nothing.
I also tried:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
It returned nothing also.
This same error is preventing 2 publications, but other 8 are working fine.
I have stopped and started log reader job a couple of times.
Any ideas?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
I've bumped into this from time to time. What you need to do is to run
profiler with the xactSequence number column.
Next time you get this error, look up the offending statement, for example
it could be a proc which is writing something unexpected to the database.
Last time I got this, it was an update to a text column with a ''.
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
>I have this error coming up 'The process could not execute 'sp_replcmds' on
> 'VDB1' which always seems to accompany 'Logreader failed to construct
> replicated command from LSN {0008e253:00009ddb:0003}.'
> I turned on verbose logging and it gave this:
> 2007-05-04 01:31:16.018 Publisher: {call sp_replcmds (500, 0, 0, , 15,
> 500000)}
> 2007-05-04 01:31:16.065 I~0x0008e25300009ddb0005~1:
> Looking up that sequence number with:
> exec sp_browsereplcmds @.xact_seqno_start = '0x0008e25300009ddb0005'
> , @.xact_seqno_end = '0x0008e25300009ddb0005'
> Returned nothing.
> I also tried:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> It returned nothing also.
> This same error is preventing 2 publications, but other 8 are working
> fine.
> I have stopped and started log reader job a couple of times.
> Any ideas?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||Update:
One of the two publications started to work, not sure why or when.
The batchsize to 1 still brought back the same LSN error - which is still
returning nothing when I query it with:
select * from MSrepl_transactions
where xact_seqno = '0x0008e25300009ddb0005'
We are going to kill the publications and recreate them - luckily they are
not super critical like the others.
Still having Replication Monitor issues (Still running SP1 on server; SP2
on my local box).
-Chuck
On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
Thanks, it only seems to be getting worse for me. The replication monitor
can't expand the server nodes now. I restarted sql service on the
distributor and that didn't help. Has anyone seen that?
I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
can find to correct issue as this command in error reports looks to be
pointed to something already completed.
-Chuck
[vbcol=seagreen]
> I've bumped into this from time to time. What you need to do is to run
> profiler with the xactSequence number column.
> Next time you get this error, look up the offending statement, for example
> it could be a proc which is writing something unexpected to the database.
> Last time I got this, it was an update to a text column with a ''.
>
> "Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
> news:5likshd2rlr9.1cei74wrqkxlw$.dlg@.40tude.net...
|||While recreating the publications, the snapshot errored because of an
obsolete XSD for an XML table. We have the option set to false to publish
the XSD's, so not sure how this came about.
Now that these are working, the Replication Monitor server nodes can be
expanded now.
-Chuck
On Fri, 4 May 2007 10:15:26 -0700, Chuck Lathrope wrote:
[vbcol=seagreen]
> Update:
> One of the two publications started to work, not sure why or when.
> The batchsize to 1 still brought back the same LSN error - which is still
> returning nothing when I query it with:
> select * from MSrepl_transactions
> where xact_seqno = '0x0008e25300009ddb0005'
> We are going to kill the publications and recreate them - luckily they are
> not super critical like the others.
> Still having Replication Monitor issues (Still running SP1 on server; SP2
> on my local box).
> -Chuck
>
> On Fri, 4 May 2007 08:29:28 -0400, Hilary Cotter wrote:
> Thanks, it only seems to be getting worse for me. The replication monitor
> can't expand the server nodes now. I restarted sql service on the
> distributor and that didn't help. Has anyone seen that?
> I am setting the -ReadBatchsize to 1 as Jonathan suggested, hopefully it
> can find to correct issue as this command in error reports looks to be
> pointed to something already completed.
> -Chuck
Monday, March 12, 2012
Logins from SQL7 to SQL2000
We are upgraded our vendor software (Jenzabar EX) from SQL7 to SQL2000. In the process, we are also moving the databases to a new server. Everything seemed to go fine until we tried to log on to one of Jenzabar EX modules. We have tried several differe
nt things, including the sp_change_users (which failed because of the @.action clause). We've tried running that huge script included in the Knowledge Base article. We also have tried using the transfer login wizard. Which, report success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we can try? I sure would greatly appreciate any advice or assistance.
Thanks!
Karma
What is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.
nt things, including the sp_change_users (which failed because of the @.action clause). We've tried running that huge script included in the Knowledge Base article. We also have tried using the transfer login wizard. Which, report success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we can try? I sure would greatly appreciate any advice or assistance.
Thanks!
Karma
What is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.
Logins from SQL7 to SQL2000
We are upgraded our vendor software (Jenzabar EX) from SQL7 to SQL2000. In
the process, we are also moving the databases to a new server. Everything s
eemed to go fine until we tried to log on to one of Jenzabar EX modules. We
have tried several differe
nt things, including the sp_change_users (which failed because of the @.actio
n clause). We've tried running that huge script included in the Knowledge B
ase article. We also have tried using the transfer login wizard. Which, re
port success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we
can try? I sure would greatly appreciate any advice or assistance.
Thanks!
KarmaWhat is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.
the process, we are also moving the databases to a new server. Everything s
eemed to go fine until we tried to log on to one of Jenzabar EX modules. We
have tried several differe
nt things, including the sp_change_users (which failed because of the @.actio
n clause). We've tried running that huge script included in the Knowledge B
ase article. We also have tried using the transfer login wizard. Which, re
port success, but the login
s still do not work.
Could we have possibly missed a step somewhere? Is there something else we
can try? I sure would greatly appreciate any advice or assistance.
Thanks!
KarmaWhat is the exact problem you are having when you login from this
application?
Rand
This posting is provided "as is" with no warranties and confers no rights.
Wednesday, March 7, 2012
Login Time and Last Batch not displayed correctly
I recently installed SQL Server 2005 CTP. Now in 2000 Enterprise Manager,
under Current Activity, Process Info, the Login Time and Last Batch columns
are not displayed correctly.
The time is displayed but the date is shown as a square to the left of the
time. This is the same for all SQL Servers registered.
Is there any way to display the date correctly?
Many Thanks
I think that's just one of the bugs, still in the CTP. It's not the
finished product yet, and it's not the only thing not working
correctly.
M
|||The CTP looks to be working ok but its affected my SQL 2000 Enterprise
Manager. I thought it was possible to run them both together.
Thanks
|||I reinstalled SQL Server 2000 and everything works correctly.
Many Thanks
|||Hi
It is, but the warning applies that since it is in beta, it is not
guaranteed not to affect other products.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"si" <si@.discussions.microsoft.com> wrote in message
news:EF2F2DA2-1BEA-4E62-A512-F244C9E25FBC@.microsoft.com...
> The CTP looks to be working ok but its affected my SQL 2000 Enterprise
> Manager. I thought it was possible to run them both together.
> Thanks
|||Hi
Re-registering the SQL DMO component would have done the trick too, but
since you did not post the question in the SQL Server 2005 Beta/CTP
newsgroup, you would have not had the benefit of an answer that would have
saved you a lot of work. This question came up a lot over there and has been
answered as many times.
http://communities.microsoft.com/new...r2005&slcid=us
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"si" <si@.discussions.microsoft.com> wrote in message
news:EE75CCCD-8384-49C3-8455-406B39B85813@.microsoft.com...
>I reinstalled SQL Server 2000 and everything works correctly.
> Many Thanks
under Current Activity, Process Info, the Login Time and Last Batch columns
are not displayed correctly.
The time is displayed but the date is shown as a square to the left of the
time. This is the same for all SQL Servers registered.
Is there any way to display the date correctly?
Many Thanks
I think that's just one of the bugs, still in the CTP. It's not the
finished product yet, and it's not the only thing not working
correctly.
M
|||The CTP looks to be working ok but its affected my SQL 2000 Enterprise
Manager. I thought it was possible to run them both together.
Thanks
|||I reinstalled SQL Server 2000 and everything works correctly.
Many Thanks
|||Hi
It is, but the warning applies that since it is in beta, it is not
guaranteed not to affect other products.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"si" <si@.discussions.microsoft.com> wrote in message
news:EF2F2DA2-1BEA-4E62-A512-F244C9E25FBC@.microsoft.com...
> The CTP looks to be working ok but its affected my SQL 2000 Enterprise
> Manager. I thought it was possible to run them both together.
> Thanks
|||Hi
Re-registering the SQL DMO component would have done the trick too, but
since you did not post the question in the SQL Server 2005 Beta/CTP
newsgroup, you would have not had the benefit of an answer that would have
saved you a lot of work. This question came up a lot over there and has been
answered as many times.
http://communities.microsoft.com/new...r2005&slcid=us
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"si" <si@.discussions.microsoft.com> wrote in message
news:EE75CCCD-8384-49C3-8455-406B39B85813@.microsoft.com...
>I reinstalled SQL Server 2000 and everything works correctly.
> Many Thanks
Friday, February 24, 2012
Login process error
Hi guys
Just setup a new system running Win2k3, SQL Server 2005, etc (all msdn).
I've got a web app (.net 2) which is running perfectly well, most of the
time, and then there's one page where I'm getting the following error...
Server Error in '/' Application.
A connection was successfully established with the server, but then an error
occured during the login process. (provider: Shared Memory Provider, error:
0
- No process is on the other end of the pipe.)
I guess the question is what does this message mean and how can I fix it?!
It's a website that's working fine apart from that page, and as far as I can
tell the code should be fine. Help!!
Cheers
Danmusosdev wrote:
> Hi guys
> Just setup a new system running Win2k3, SQL Server 2005, etc (all
> msdn). I've got a web app (.net 2) which is running perfectly well,
> most of the time, and then there's one page where I'm getting the
> following error...
> Server Error in '/' Application.
> A connection was successfully established with the server, but then
> an error occured during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> I guess the question is what does this message mean and how can I fix
> it?! It's a website that's working fine apart from that page, and as
> far as I can tell the code should be fine. Help!!
> Cheers
>
> Dan
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
http://blogs.msdn.com/sql_protocols.../22/483684.aspx (see
message 4)
David Gugick - SQL Server MVP
Quest Software
Just setup a new system running Win2k3, SQL Server 2005, etc (all msdn).
I've got a web app (.net 2) which is running perfectly well, most of the
time, and then there's one page where I'm getting the following error...
Server Error in '/' Application.
A connection was successfully established with the server, but then an error
occured during the login process. (provider: Shared Memory Provider, error:
0
- No process is on the other end of the pipe.)
I guess the question is what does this message mean and how can I fix it?!
It's a website that's working fine apart from that page, and as far as I can
tell the code should be fine. Help!!
Cheers
Danmusosdev wrote:
> Hi guys
> Just setup a new system running Win2k3, SQL Server 2005, etc (all
> msdn). I've got a web app (.net 2) which is running perfectly well,
> most of the time, and then there's one page where I'm getting the
> following error...
> Server Error in '/' Application.
> A connection was successfully established with the server, but then
> an error occured during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> I guess the question is what does this message mean and how can I fix
> it?! It's a website that's working fine apart from that page, and as
> far as I can tell the code should be fine. Help!!
> Cheers
>
> Dan
http://msdn2.microsoft.com/en-us/library/ms175496.aspx
http://blogs.msdn.com/sql_protocols.../22/483684.aspx (see
message 4)
David Gugick - SQL Server MVP
Quest Software
Subscribe to:
Posts (Atom)