Re: Maybe the Records Are There After All
From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: Fri, 29 Oct 2004 10:51:08 -0400
Run DBCC UPDATEUSAGE to correct the row count display in Enterprise Mangler.
-- Geoff N. Hiten Microsoft SQL Server MVP Senior Database Administrator Careerbuilder.com I support the Professional Association for SQL Server www.sqlpass.org "John Bishop" <email@example.com> wrote in message news:%231Z4HYcvEHA.2016@TK2MSFTNGP15.phx.gbl... > Your explanation makes SENSE and yes, it appears that I was checking > immediately after the change. (Bear in mind that I am still feeling my way > along.) > > The indication that the records were DELETED is that Server Enterprise > Manager reports that the table has ZERO Rows. I had made the change in Data > Type in Server Enterprise Manager and then was making notes and documenting > what I had done. I went back to note the record count (which I had > previously found was conveniently reported in the Table Properties in Server > Enterprise Manager and it gave me the sensation that ALL of the data had > been deleted. > > Now, I am thinking that it is doing precisely as you describe. I was able > to use the Import and Export Data tool to COPY all of the data from that > table to another table in a different database, which reports that it has > 387,825 records (CORRECT). The original table STILL says in the Properties > that it has ZERO records, but that table has several Indices that are > probably being rebuilt. > > But, the larger database from last night STILL SAYS that it has ZERO records > nine hours after the records appeared to all be deleted. But it has nine > million records and more than a few indices. And I am running this SQL > Evaulation on an older 866 MHz single processor system with only 512 Mbs of > memory. > > Is there some way to monitor the things that SQL is doing in the BACKGROUND > to these tables? > > "Jeff Dillon" <firstname.lastname@example.org> wrote in message > news:ORaVrEcvEHA.2584@TK2MSFTNGP10.phx.gbl... > > Of course you backed up your database, prior to making major changes to > > tables, correct? > > > > Whew..thank goodness. > > > > And no, generally records are not deleted. However, behind the scenes, > it's > > probably renaming the table, creating a new structure, then inserting the > > records, then dropping and renaming the temp table. > > > > Did you check if the data was there immediately after changing the column > > datatype? > > > > Jeff > > "John Bishop" <email@example.com> wrote in message > > news:eDbrR9bvEHA.3896@TK2MSFTNGP09.phx.gbl... > > > Last night, I posted the message "HELP!: A Disasterous Delete" (Friday, > > > October 29, 2004 1:20 AM). This morning, as I pressed ahead with my > work > > > with my smaller data extract, I have had a recurrence of the problem of > > the > > > mysterious deletion of ALL RECORDS, but now with the smaller data > extract. > > > > > > But I have a better sense now of what may have CAUSED the problem (I > > think). > > > I had just used SQL Server Enterprise Manager to Change the Data Type of > > two > > > columns from char to nvarchar. I was then re-running a query. > > Thereafter, > > > in noticed that ALL ROWS of the table seemed to be missing. > > > > > > Perhaps I am SPOILED by the way MS Access handles changes to a > > datastructure > > > with data records in place. > > > > > > DOES SQL 2000 DELETE ALL RECORDS WHEN THE DATA TYPE OF A SINGLE COLUMN > IS > > > ALTERED??? This seems like a rather radical treatment to the data > within > > a > > > table if this is the case. > > > > > > Now I seem to have lost NOT ONLY the original larger 9 million record > data > > > table, but also the smaller 350,000 record data extract. > > > > > > Maybe I should just throw in the towel! > > > > > > Any suggestions are appreciated!! > > > > > > > > > > > >