What is for Unused space for MDF and LDF?

• GivThx

  SSCommitted

  Points: 1758

  More actions

  April 12, 2004 at 12:04 pm

  #84796

  Hi, All

  I have a q for u.

  I have a DB(around 200GB for Data after shrinking DB). Last weekend something happened.. I inserted 500,000 records into one table by schedule. Ths size of this records is around 150MB. Before inserting data, size of DB was around 180GB.. That is quite unusual to grow 20GB in Data size.. I have been inserting data using the same script by schedule over the 4 months but nothing like this. Therefore I shrinked but still 200GB. Finally I checked file size of data itself using shrink DB in the one of options. Based on this, I can reduce file size up to 130GB by shrinking file.

  My q to u is that how come this big size around 70GB is existed in the data file and how come DB does not release this size during backup and shrinking DB..

  Any Idea.

  Thx in advance..

  Jay

   

   

• dtipser

  SSC-Addicted

  Points: 416

  More actions

  April 13, 2004 at 7:49 am

  #502554

  Hi,

  First check your log file with the command in Query Analizer in your database:

  DBCC LOGFILE

  If the last record in column "STATUS" have the value 0 than pass the command:

  DBCC SHRINKFILE('DATABASELOGNAME',100) where

  'DATABASELOGNAME' is the logical name of your log file

  100 - is the space that you want to have your LOG file in MB.

  If the value is 2 than you can not shrink physicly the LOG file (complicated to explain, because of Virtual Log....)

  You can shrink in the same way your Data File if you want.

  Hope that this can help you.