none
MDF & LDF Files - large size

    Question

  • Dear Sir,

    I would like to know that in order to take database to another machine, whether i need to take both .mdf & .ldf files.  I have noted that the .ldf file is have much higher size than the mdf files.  Many times it is in Giga Bytes.  So it is very difficult to copy the same in CDs.  Can I do away with .ldf file & manage with only the .mdf file.  Can u also suggest me any method by which i can reduce the size of these files.

    with regards,
    wilfi

    Saturday, June 21, 2008 12:51 PM

Answers

  • You can attach an MDF file without the LDF and it will create a new one.  In terms of reducing the size of you logs you can do regular log backups or set the database to Simple mode.

     

    Backing up the Log will only cleare the inactive portion. You have to run DBCC Shrinkfile to reclaim the space.

    Here is the information on how to shrink the transaction log.

     

    http://support.microsoft.com/kb/272318

    Saturday, June 21, 2008 12:54 PM
  • Another way to move your files around would be to backup the transaction log, then perform a full database backup, copy the backup file to CD, then restore the database to your second server.

     

    BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY

     

    <you might want to consider shrinking the log file at this point>

     

    BACKUP DATABASE [MyDatabase] TO DISK = 'C:\MyDatabase.bak'

     

    As the log will consist of mostly 'free space', the corresponding backup file should be small in comparison, and could actually be smaller than the mdf file.

     

    As an aside, I think you need to consider exactly why the log file is so large. Is your database in FULL recovery mode, and do you take regular log backups? If you have no need for regular log backups then you might be best switching the database to SIMPLE recovery mode to help reduce log growth - you should then use Ken's method (posted above) to shrink the log file.

     

    Chris

    Saturday, June 21, 2008 10:59 PM

All replies

  • You can attach an MDF file without the LDF and it will create a new one.  In terms of reducing the size of you logs you can do regular log backups or set the database to Simple mode.

     

    Backing up the Log will only cleare the inactive portion. You have to run DBCC Shrinkfile to reclaim the space.

    Here is the information on how to shrink the transaction log.

     

    http://support.microsoft.com/kb/272318

    Saturday, June 21, 2008 12:54 PM
  • Another way to move your files around would be to backup the transaction log, then perform a full database backup, copy the backup file to CD, then restore the database to your second server.

     

    BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY

     

    <you might want to consider shrinking the log file at this point>

     

    BACKUP DATABASE [MyDatabase] TO DISK = 'C:\MyDatabase.bak'

     

    As the log will consist of mostly 'free space', the corresponding backup file should be small in comparison, and could actually be smaller than the mdf file.

     

    As an aside, I think you need to consider exactly why the log file is so large. Is your database in FULL recovery mode, and do you take regular log backups? If you have no need for regular log backups then you might be best switching the database to SIMPLE recovery mode to help reduce log growth - you should then use Ken's method (posted above) to shrink the log file.

     

    Chris

    Saturday, June 21, 2008 10:59 PM
  • Dear Ken,

    Thanx a lot for ur immediate reply.  Yes i could reduce the size by shrinking. I could also  do the backup & restore of the database.

    But, I am not able to attach a database just by having only mdf file in the specified folder.  Can u suggest me the reason or way out for this prob.

    with regards,
    wilfi

    Monday, June 23, 2008 6:57 AM
  • Attaching MDF without LDF is not recommended practice and often will have problem because , the database is not properly detached before copying. If you want to attach a db without LDF , the database has to be properly detached and also the database should not have morethan one LDF file. So its always better to go for Backup/Restore or shrink the database before detach and then copy LDF and MDF file. sp_attach_single_file_db system sp will attach a database without LDF but conditions applies.

     

    Conditions

    Use sp_attach_single_file_db only on databases that were previously detached from the server by using an explicit sp_detach_db operation or on copied databases.

    sp_attach_single_file_db works only on databases that have a single log file. When sp_attach_single_file_db attaches the database to the server, it builds a new log file. If the database is read-only, the log file is built in its previous location.

     

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

    Monday, June 23, 2008 7:22 AM