Post reply

C Drive needs memory - Log File very large

  • August 20, 2015 at 9:48 pm

    #311005

    The SQL Server has all databases and logs installed on C drive (I know - sometimes you inherit problems). My C Drive is now virtually full. I looked at individual files and realized one SQL log file is way larger than the rest.

    I have Great Plains ERP and Microsoft CRM integrated.

    The integration uses a MSDI database.

    For some unknown reason the MSDI Log file has grown to 127GB.

    I need to either: Truncate the Log file and/or move the databases and log files.

    What would you do in my situation?

    I research and found this code for manually shrinking the log file - will this instantly free up C drive space?

    ALTER DATABASE @DBName SET RECOVERY SIMPLE WITH NO_WAIT

    DBCC SHRINKFILE(@DBName_log, 1)

    ALTER DATABASE @DBName SET RECOVERY FULL WITH NO_WAIT

    GO

    Any feed back or guidance would be appreciated.

  • August 20, 2015 at 10:05 pm

    #1821588

    Read this[/url].

  • August 21, 2015 at 2:51 am

    #1821615

    pietlinden (8/20/2015)

    Read this[/url].

    Why? He's not deleting the log.

    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 21, 2015 at 2:55 am

    #1821616

    What you probably want to read is this: http://qa.sqlservercentral.com/articles/Administration/64582/

    Before you do anything, find out what the recovery requirements are for the databases. If drives fail, is restoring to the previous night's full backup acceptable or do the databases need to be restored to near point of failure (this is called the RPO, recovery point objective, how much data can be lost in a disaster)

    If restoring to the previous night's backup is acceptable, then set the DB to simple recovery model (you don't need the NO_WAIT option) and shrink the log to a sensible size (not 1 MB)

    If point in time restores are required, then you need to implement log backups. How often depends on the allowable data loss. If 10 minutes of data loss is the maximum acceptable, then your log backups need to be running at least once every 10 minutes. Once you have log backups running, shrink the log to a sensible size.

    After the logs are under control, look into moving databases off C drive.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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