SQL Server Transaction Log Full



  • Hi,

    I'm sure someone here can help me with this... I'm sort of new to SQL
    Server myself. I'm currently doing some work on a virtally-hosted site
    that uses an
    SQL server backend. When trying to add data to the server I'm getting
    "Transaction Log Full", with advice to backup the transaction log.
    Transaction log backup fails, however, with a no permission to write
    error. Am I doing something silly here? (Not using Simple Recovery
    model, btw)

    Rob



  • Why not SIMPLE? Seems what you'd want for a hosted site.

    Anyway, just use DBCC SHRINKFILE (db_name,target_size_in_mb)



  • It was set to FULL when I got here. And changing to SIMPLE, seemingly,
    requires putting something in the transaction log. In fact, nearly
    everything seems to require putting something in the transaction log,
    and it's having none of it...



  • Ah, never mind, fixed; the host had let their backup drive fill, which
    was preventing backups. Grr. Think I may shift it to SIMPLE...



  • @rsynnott said:

    It was set to FULL when I got here. And changing to SIMPLE, seemingly,
    requires putting something in the transaction log. In fact, nearly
    everything seems to require putting something in the transaction log,
    and it's having none of it...




    Pretty much, yeah. Usually, the SQLServers I've dealt with have trunc log on checkpoint, which alleviates this problem.



  • For future reference, BACKUP LOG WITH TRUNCATE_ONLY ought to allow you to get rid of a trans log you don't need.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.