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