[dba-SQLServer] Whats going on and how to fix it

Michael Maddison michael at ddisolutions.com.au
Mon Jan 24 19:20:31 CST 2005


John,

EM most likely will create the new table, copy + convert the data, drop
the original table, rename the new table.
This is all logged and within a transaction.
If you stop the process, SQL will roll back the changes...  Re booting
doesn't stop it, this is a feature...
I've also ended up with suspect DB's if you reboot in the middle of an
operation.
This is 1 reason why scripting changes yourself in QA is safer,
particulary with large db's.

cheers

Michael M


It was a Boolean.  I typed in var and hit return when entering the data
type.  VarChar is not the default.  8-(

I did indeed just delete the column and added it back in as the correct
type.  Took all of 30 seconds.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Steve
Erbach
Sent: Monday, January 24, 2005 2:16 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Whats going on and how to fix it


John,

What were you converting from and what's the new field type?

I'd say that you'd be better served (since there was no data in the
field) by deleting the field and then re-inserting it.

64 million rows. Yikes! You are definitely going to be a guru of large,
unwieldy tables after this project's over, eh?

Steve Erbach
Neenah, WI


On Mon, 24 Jan 2005 13:34:00 -0500, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> I created a new field in one of my "little tables" - ~10 fields, 64 
> million records, wrong data type by mistake.  I went in to design view

> and changed the datatype.  When I closed the table EM "went away" with

> the hourglass.  4 hours later still an hour glass.  Task manager shows

> Em spending 99% of time on the folding task, with occasional jumps in 
> SQL Server activity (CPU Time) to 10, 7,9, etc %.
> 
> I used Task Manager to shut it down.  Shut windows down (just in
> caseA) and started it back up.  Opened EM and as soon as I click on 
> the databases "folder" the hourglass comes up.  More hours later...
> 
> Shut EM down again, rebooted and started it back up, same thing.
> 
> Now I am copying the log and pasting it back in so I can look at it 
> (can't open the log itself) and it says
> 
> Spid11 Recovery of database 'Conduit' (7) is 28% complete (approx 2216

> more
> seconds) (Phase 3 of 3)
> 
> Soo... 37 minutes to go.
> 
> It appears that the db is being recovered.  The question is, WTF?  4 
> hours (and not finished) to convert a field to another datatype?  Can 
> I just delete the field and recreate it (assuming the repair works)?
> It had no data in it.
> 
> John W. Colby
> www.ColbyConsulting.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list