locked
Log Files RRS feed

  • Question

  • Hi All,

     

    We are moving our data and log files to a new machine. What would be the issue if I create new log files instead of using the same ones? The data is not critical and so am not worried about breaking the continuity of the logs. The reason I want to create new files is because the current log files are very large. Any suggestions?

    Wednesday, August 11, 2010 12:51 AM

Answers

  • make sure that database is in single user mode and there is only you in that database>>Take the database offline >> delete the log file >> bring the database online .

    It will recreate the log file with the default size.

    HTH


    ... if you're lucky. Workt case you have a corrupt database. I've been on forums many many years now, and I've seen many posts over the years regarding "lost log files" - it isn't fun to have a corrupted database, even if the risk is small it happens. I prefer to eliminate the risk in the first place and not delete log files. I've never deleted a ldf file, I havenät had the need for it. Either shrink the file ( if it is way too large), or shrink it using EMPTYFILE and then use ALTER DATABASE to get rid of it. My $0.02.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, August 11, 2010 6:57 PM

All replies

  • Can you add some numbers to the term "large"? This is very relative until we see what you are referring to? What you don't want to have is auto growth of log files while the server is running. Log files can't use the instant file initialization and the autogrowth of log files (depends on your settings) can take long time & can be detrimental to performance and lead to excess disk fragmentation.

    Add details on the current file size, your planned file size, type of activity OLTP vs DW for good responses.


    http://SankarReddy.com/
    • Edited by Sankar Reddy Wednesday, August 11, 2010 1:35 AM complete the sentence
    Wednesday, August 11, 2010 1:35 AM
  • In General if a database was *cleanly shutdown* then you might be able to rebuild their log using the following TSQL when you are attaching the data file.

     

    CREATE DATABASE TRACE

          ON (FILENAME = 'E:\MSSQL\MSSQL.1\MSSQL\Data\trace.mdf')

          FOR ATTACH_rebuild_log;

    GO

     

    But the reason for a log rebuild should never be an existing large size log file! Answer Sankar’s question so we will get a clear picture what you are trying to do and suggest based on that.


    Thanks, Leks
    Wednesday, August 11, 2010 3:29 AM
    Answerer
  • Hi Sam1980

    Is database in full recovery mode? if so change it to simple recovery mode and shrink database using DBCC SHRINKFILE command or right click the database & use shrink database option.

    Once shrink is completed change recovery model to previous one. Now you can detach database. Move files to desired location and attach them again.

    ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    USE [YourDatabase]
    GO
    DBCC SHRINKFILE (YourDatabase_log, 10)  --- you can get your database log logical name if you use sp_helpdb yourdatabase
    GO

    Detach and attach

    http://www.mssqlcity.com/Articles/Adm/attach_database.htm


    Ramesh S
    www.sequalserver.com
    Wednesday, August 11, 2010 8:30 AM
  • Chnaging from Full to simple works normal but again back to full wont complete if you didnt take a full backup
    http://rknairblog.blogspot.com/ http://ratheeshknair.wordpress.com/
    Wednesday, August 11, 2010 9:50 AM
  • make sure that database is in single user mode and there is only you in that database>>Take the database offline >> delete the log file >> bring the database online .

    It will recreate the log file with the default size.

    HTH

    Wednesday, August 11, 2010 10:08 AM
  • make sure that database is in single user mode and there is only you in that database>>Take the database offline >> delete the log file >> bring the database online .

    It will recreate the log file with the default size.

    HTH


    ... if you're lucky. Workt case you have a corrupt database. I've been on forums many many years now, and I've seen many posts over the years regarding "lost log files" - it isn't fun to have a corrupted database, even if the risk is small it happens. I prefer to eliminate the risk in the first place and not delete log files. I've never deleted a ldf file, I havenät had the need for it. Either shrink the file ( if it is way too large), or shrink it using EMPTYFILE and then use ALTER DATABASE to get rid of it. My $0.02.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, August 11, 2010 6:57 PM