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.
