Re: What did I do wrong?

• Previous message: graham: "What did I do wrong?"
• In reply to: graham: "What did I do wrong?"
• Next in thread: graham: "Re: What did I do wrong?"
• Messages sorted by: [ date ] [ thread ]

Date: Sun, 9 May 2004 16:01:49 -0400

Maybe when you decided to reboot it was still rebuilding the table for your
change?

FWIW, I *ALWAYS* enter DML statements in Query Analyzer when trying to
change a table, rather than allow the GUI. I *ALWAYS* test the changes on a
test environment (with just as much data, where practical) before
interrupting the live environment based on the advice of a "guru". And I
*ALWAYS* dig a little deeper before trying to 'resolve' the issue by
rebooting.

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"graham" <grahamblandford@rogers.com> wrote in message 
news:qZunc.2645$n7P1.2236@twister01.bloor.is.net.cable.rogers.com...
> Hi All,
>
> Wonder if anyone can shed some light on a problem that arose on Friday, 
> that
> I am still in shell-shock from..
>
> SQL2000 on W2Kserver.
>
> On Thursday evening, I performed what I thought was a much-needed
> modification on our database - specifically, I have a 7.5million record
> 'prospect' table, that had been deisgned using BIGINT columns where 
> various
> keys resided... including the primary key. E.g. ID, PROVINCE_STATE_ID,
> SALESPERSON_ID.. etc.. were all BIGINT. I was given some advice from a
> 'guru' that the BIGINT was overkill and that I should see quite an
> improvement in performance if I changed them to INT.
>
> Anyway, on Thursday evening, using the SQL EM, I changed the columns to 
> int.
> clicked save, I got the warning messaged as expected indicating that I may
> lose data...  and off it went..
>
> On Friday morning I returned to the EM and everythign appeared to have 
> been
> completed.
>
> However, the system ran to a standstill - I had blocking left, right and
> centre, timeouts, the lot. We rebooted the server physically twice - whioh
> was no mean feat in asking over 100 users to holdfire... all with little
> success.. finally making it to 5pm.. with little or no answer as to why 
> this
> happened, or whether it will continue.
>
> I received some advice to 'UPDATE STATISTICS' on the offending table, and
> also 'sp_recompile' .
>
> So here I sit on a Sunday afternoon, blindly deleteing and recreating
> indexes for the offending table and (just to be safe) tables 'linked' to 
> the
> offending table, without really knowing whether I am making the situation
> better, worse or having no impact.
>
> Does anyone have any ideas as to what caused this issue and/or what I can 
> do
> to rectify the problem.
>
> Any advice would be greatly appreciated.
>
> Thanks In Advance,
> Graham Blandford
>
>
>
>
>
>
>
>
>
>
>
>
>
> 

• Previous message: graham: "What did I do wrong?"
• In reply to: graham: "What did I do wrong?"
• Next in thread: graham: "Re: What did I do wrong?"
• Messages sorted by: [ date ] [ thread ]