Truncate log

  • Quick one, I have ran out of disk space on my server but my transaction log is 20gb. I want to truncate my log but the command I use is 'DUMP TRAN MWR_LIVE_DB_INCR_TEST WITH TRUNCATE_ONLY'

  • sorry pressed enter half wat through writting this post. cont.... This command is saying cannot back up the log. But i do not want to back it up just truncate it can anyone give any help thanks Paul

  • Are you running SQL 2000 or 7.0?  If 7.0, do you have "truncate log on checkpoint" on or off?  If 2000, is your recovery model full or simple (or bulk logged)?  It sounds like you have "truncate log on checkpoint" on or recovery set to simple.  The command you entered "dump tran..." is a backup command, but you have set the parameters to simply dump without backup (truncate).  Probably, if I'm correct about your settings, you just need to SHRINK the log, rather than truncate.  Truncating simply removes data from the log.  It doesn't de-allocate disk space.  SHRINK will de-allocate unused space.  Check Books Online for syntax. 

  • try "truncate xxx with no_log", then shrink the log and run a backup.

     

  • I would go with

     

    BACKUP LOG MWR_LIVE_DB_INCR_TEST  WITH NO_LOG

    GO

    USE MWR_LIVE_DB_INCR_TEST

    GO

    DECLARE @FILENAME VARCHAR(200), @sql VARCHAR(1000)

    SELECT @FILENAME = RTRIM(NAME) FROM SYSFILES WHERE GROUPID = 0

    SET @sql = 'DBCC SHRINKFILE ('''+@FILENAME+''', 128, TRUNCATEONLY)'

    EXEC (@SQL)

    This will backup the database log and then shrink the log file. You could actually encapsulate this easily into a stored procedure where you just have to pass in the parameter of the database name.



    Shamless self promotion - read my blog http://sirsql.net

  • Also if the shrinkfile doesn't work, try this:

    http://www.sqlservercentral.com/scripts/contributions/26.asp

Viewing 6 posts - 1 through 5 (of 5 total)

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