Re: Maybe the Records Are There After All

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 10/29/04 

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" <ugradfrnd@aol.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" <jeff@removeemergencyreporting.com> 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" <ugradfrnd@aol.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!!
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Viewing default db role permissions
    ... > whether that user or role has access to various objects in the database ... > the database in Enterprise Manager, going to the "Role" tree item, ... > checkmarks representing what the role/user has access to and red X's ... > permissions of each of these roles from the SQL Server documentation, ...
    (microsoft.public.sqlserver.security)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • SQL Server does not exist or access denied
    ... I get the error both in application code and from Enterprise Manager. ... The database is LOCAL. ... Microsoft SQL Server 2000 Developer Edition SP4 ... Once it fails, it will fail ...
    (comp.databases.ms-sqlserver)
  • Re: Downgrade - 2005 Ent. Cluster -> 2005 Stand. Cluster
    ... SQL Server 2005 Standard Edition supports 2-node clustering... ... Senior Database Administrator ... Enterprise version of the Application to cluster. ...
    (microsoft.public.sqlserver.clustering)