how to know the log size of a table after removing some records from a table

• punna

  Newbie

  Points: 1

  More actions

  February 28, 2012 at 3:16 am

  #256725

  hi,

  1.how to know the log size of a table after removing some records from a table??

  2.My table have 1,00,000 rows,if i removing some records using delete command the log file size is reduced or not??

  3.truncate is used to remove all rows from a table and free space is available in this.My question is the log file size also decreased when I am using truncate??

  4.how to decrease the size of a table when removing some records from a table??

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  February 28, 2012 at 4:24 am

  #1452489

  punna (2/28/2012)

  ---

  hi,

  1.how to know the log size of a table after removing some records from a table??

  2.My table have 1,00,000 rows,if i removing some records using delete command the log file size is reduced or not??

  3.truncate is used to remove all rows from a table and free space is available in this.My question is the log file size also decreased when I am using truncate??

  4.how to decrease the size of a table when removing some records from a table??

  Interview questions?

  1. Tables don't have a log size. Databases do.

  SELECT cntr_value AS [log kb used]

  FROM sys.dm_os_performance_counters

  WHERE counter_name = 'Log File(s) Used Size (KB)'

  AND instance_name = 'YourDatabaseNameGoesHere'

  2. Some transaction log records are written. Log file size may increase or not, depending on the free space inside it.

  3. Same as above.

  4. Rebuild or reorganize the clustered index, if any. The space used in the data file will drop, but the data file won't release space to the operating system unless you issue a shrink command.

  -- Gianluca Sartori