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...
> 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

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

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

LogScan in progress

Hello,
We have a mixed replication environment with SQL Server transactional
replication and Datamirror replication (with DB2). Yesterday we upgraded to
SQL Server 2005 and the transactional replication does not work.
The log reader message is "Logscan in progress. Approximately 4254920 log
records scanned in pass # 1, elapsed time 7620 (ms).". The Subscription
Watcher tells that all subscriptions are uninitialized. But actually, I have
not subscription to initialize (there are Datamirror). And, in any case, I
cannot reinitialize the subscription because the initial synchronization was
made manually.
Now, all pending changes are in the transaction log of the publisher database.
What can I do? Is to drop and recreate the publication the only way to solve
this issue?
Thank you in advanced,
qwalgrande
You need to contact DataMirror to learn how to make their product work with
SQL Server 2005.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"qwalgrande" <qwalgrande*nospam*@.yahoo.es> wrote in message
news:19C4184F-C544-43A3-B6AD-74E170047129@.microsoft.com...
> Hello,
> We have a mixed replication environment with SQL Server transactional
> replication and Datamirror replication (with DB2). Yesterday we upgraded
> to
> SQL Server 2005 and the transactional replication does not work.
> The log reader message is "Logscan in progress. Approximately 4254920 log
> records scanned in pass # 1, elapsed time 7620 (ms).". The Subscription
> Watcher tells that all subscriptions are uninitialized. But actually, I
> have
> not subscription to initialize (there are Datamirror). And, in any case, I
> cannot reinitialize the subscription because the initial synchronization
> was
> made manually.
> Now, all pending changes are in the transaction log of the publisher
> database.
> What can I do? Is to drop and recreate the publication the only way to
> solve
> this issue?
> Thank you in advanced,
> --
> qwalgrande