By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,652 Members | 1,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,652 IT Pros & Developers. It's quick & easy.

Is this normal

P: n/a
CK
Hi. Sorry if I am asking a stupid question since I am an absolutely beginner
in SQL Server. Here is the question . . .

About 13 hours ago, I got my SQL Server 2000 to index a table which has 104
million records. At first the CPU usage was high. But after an hour or two,
the process has seemed dead and the Enterprise Manager has had no response.
The CPU usuage dropped to zero and has been jumping between 0 to 5%. The
harddisk indicator has been blinking at a rate of roughly three times per
two seconds.

Is this normal? Has anyone got any idea how long the process would take? I
have assigned 1.8GB of RAM to the SQL service and is currently taking up
about 1GB.
Nov 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
CK
Almost 2 days already . . . still the same . . .

"CK" <cf***@hotmail.com> wrote in message
news:43***********************@ken-reader.news.telstra.net...
Hi. Sorry if I am asking a stupid question since I am an absolutely
beginner in SQL Server. Here is the question . . .

About 13 hours ago, I got my SQL Server 2000 to index a table which has
104 million records. At first the CPU usage was high. But after an hour or
two, the process has seemed dead and the Enterprise Manager has had no
response. The CPU usuage dropped to zero and has been jumping between 0 to
5%. The harddisk indicator has been blinking at a rate of roughly three
times per two seconds.

Is this normal? Has anyone got any idea how long the process would take? I
have assigned 1.8GB of RAM to the SQL service and is currently taking up
about 1GB.

Nov 23 '05 #2

P: n/a
On Thu, 17 Nov 2005 23:19:08 +1100, CK wrote:
Is this normal?


Hi CK,

Two days for indexing? No, that's not normal.

Open a new window in Query Analyzer and execute sp_who2. I think that
something is blocking your indexing process.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Nov 23 '05 #3

P: n/a
CK
Thanks for letting me know about this command. Otherwise I would have waited
forever without realising the process is in the state of "sleeping".
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:e3********************************@4ax.com...
On Thu, 17 Nov 2005 23:19:08 +1100, CK wrote:
Is this normal?


Hi CK,

Two days for indexing? No, that's not normal.

Open a new window in Query Analyzer and execute sp_who2. I think that
something is blocking your indexing process.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Nov 23 '05 #4

P: n/a
On Sun, 20 Nov 2005 00:17:11 +1100, CK wrote:
Thanks for letting me know about this command. Otherwise I would have waited
forever without realising the process is in the state of "sleeping".


Hi CK,

But is it also blocked?

(Check the column BlkBy)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Nov 23 '05 #5

P: n/a
CK
It's not blocked. After some testings, I realised the problem was caused by
insufficient free harddisk space for the transaction log file. It worked
fine after I created another log file in a drive with plenty of space.
Nevertheless, I was surprised that SQL Server didn't give me any warning
message about this.
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:gc********************************@4ax.com...
On Sun, 20 Nov 2005 00:17:11 +1100, CK wrote:
Thanks for letting me know about this command. Otherwise I would have
waited
forever without realising the process is in the state of "sleeping".


Hi CK,

But is it also blocked?

(Check the column BlkBy)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Nov 23 '05 #6

P: n/a
CK (cf***@hotmail.com) writes:
It's not blocked. After some testings, I realised the problem was caused
by insufficient free harddisk space for the transaction log file. It
worked fine after I created another log file in a drive with plenty of
space. Nevertheless, I was surprised that SQL Server didn't give me any
warning message about this.


I bet it did. But it sounds like you ran this through Enterprise Manager,
and there could be some hickup in this tool, hiding the error message
from you. Had you run the CREATE INDEX from Query Analyzer directly,
you should have seen an error message. (Or at least so I like believe. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #7

P: n/a
CK
You absolutely are right about me using Enterprise Manager to index the
table. I guess it's now the time for me to brush up on my SQL skill!!!
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
CK (cf***@hotmail.com) writes:
It's not blocked. After some testings, I realised the problem was caused
by insufficient free harddisk space for the transaction log file. It
worked fine after I created another log file in a drive with plenty of
space. Nevertheless, I was surprised that SQL Server didn't give me any
warning message about this.


I bet it did. But it sounds like you ran this through Enterprise Manager,
and there could be some hickup in this tool, hiding the error message
from you. Had you run the CREATE INDEX from Query Analyzer directly,
you should have seen an error message. (Or at least so I like believe. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.