Post reply

Curiosity

  • August 5, 2010 at 5:22 am

    #238621

    Hi Experts,

    I have a databae and only one table in that ..I inserted 10 million records to that table .Database size increased to 4096.81 MB(358MB for mdf and

    3,739MB for ldf)..

    Now i truncated the whole table and stillits showing the same size..Why??

    Is it possible to rollback the data??

  • August 5, 2010 at 5:41 am

    #1202648

    TRUNCATE is a logged operation, can be rolled back. Check the following article[/url] from MVP Pinal Dave.

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • August 5, 2010 at 5:59 am

    #1202660

    HI Sudeep,

    Both Truncate and delte are logged the difference in this sense is truncate is minimally logged ...u can roll back both..

    http://rknairblog.blogspot.com/2010/05/can-truncate-rolled-back.html

  • August 5, 2010 at 8:17 am

    #1202780

    Operations within tempdb are logged so that transactions on temporary objects can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it.

    Regards,
    Shivrudra W

  • August 5, 2010 at 11:50 am

    #1203010

    Ratheesh.K.Nair (8/5/2010)

    Hi Experts,

    Now i truncated the whole table and stillits showing the same size..Why??

    Just because you delete the data, it does not return the space to the operating system. The table may be empty but the space has already been allocated to the database for use. The only reason why the database will shrink in physical size if you do a DBCC SHRINKFILE or DBCC SHRINKDB.

  • August 5, 2010 at 11:12 pm

    #1203252

    Thanks Steve and Shiva..

    Will DBCC cleantable helps in this situation??

  • August 6, 2010 at 3:57 am

    #1203340

    No. Why do you think it would?

    The only things that shrink a file are DBCC ShrinkDatabase, DBCC ShrinkFile and auto_shrink. Without using one of those (which is, in general, a bad idea), deleting data makes the space reusable by other objects in the database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 6, 2010 at 6:11 am

    #1203422

    As Gails Shaw advised DBCC CLEANTABLE can not help you.

    DBCC CLEANTABLE reclaims space after a variable-length column ( varchar, nvarchar) is dropped.

    You can just try;

    1. Before truncating the table

    sp_spaceused N'tableName'

    2. Truncate the table

    3. Corrects pages and row count inaccuracies in the catalog views

    DBCC UPDATEUSAGE(dbname,"tablename")

    4. sp_spaceused N'tableName'

    Compare the results of step 1 and step 4.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

  • August 6, 2010 at 6:41 am

    #1203454

    sivaprasad (8/6/2010)

    3. Corrects pages and row count inaccuracies in the catalog views

    DBCC UPDATEUSAGE(dbname,"tablename")

    Not needed on SQL 2008. There were errors in the algorithms that maintained the page usage back in SQL 2000. Those errors were corrected in SQL 2005.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 8, 2010 at 2:53 am

    #1203921

    Thanks Gail for letting me know this feature.

    Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply