Microsoft SQL Server forum

Author

Is this normal

2005-11-17, 9:23 am

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.

2005-11-18, 9:23 am

Almost 2 days already . . . still the same . . .

"CK" <cf276@hotmail.com> wrote in message
news:437c7530$0$7533
1$c30e37c6@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.
>

Hugo Kornelis

2005-11-18, 8:24 pm

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)

2005-11-19, 7:23 am

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:e3hsn15bpfht5a1
i452ros2i19dhsh2hjc@
4ax.com...
> On Thu, 17 Nov 2005 23:19:08 +1100, CK wrote:
>
>
> 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)

Hugo Kornelis

2005-11-19, 8:23 pm

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)

2005-11-20, 3:23 am

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:gc2vn1tfeb652jn
j32vhl2li4qjq0l05fm@
4ax.com...
> On Sun, 20 Nov 2005 00:17:11 +1100, CK wrote:
>
>
> Hi CK,
>
> But is it also blocked?
>
> (Check the column BlkBy)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Erland Sommarskog

2005-11-20, 8:23 pm

CK (cf276@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2005-11-21, 9:23 am

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" <esquel@sommarskog.se> wrote in message
news:Xns9714D735F65A
Yazorman@127.0.0.1...
> CK (cf276@hotmail.com) writes:
>
> 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, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links