7

SQL Server 2008: Large transaction log file in Simple Recovery Mode

view full story
linux-howto

http://serverfault.com – I am having a problem with disk space in SQL Server 2008. This is the situation: - I had a DB set with Simple Recovery and this DB used to use 2-3 GB for the transaction log file - I moved this DB to a new server and the situation was the same - I changed the recovery mode to FULL and, rightly, the log got 20 GB of disk space - I changed the recovery mode back to SIMPLE and I am not able to shrink the log file back to the 2-3 GB of disk space The log file is very big and is growing fast. I've verified and in the log file there is no room, in fact "available free space" is near 0%. Can anyon (HowTos)

dayu's picture
Submitted by dayu on 01/17/2013 – Made popular on 01/17/2013

Stories similar to SQL Server 2008: Large transaction log file in Simple Recovery Mode

I'm trying to do a large INSERT operation from a database to another. But I get an error message:

Msg 9002, Level 17, State 4, Line 1 The transaction log for database '_ARCHIVE_mydatabase' is full.

After looking into the MySQL error file, I found an error regarding disk space. Now I think its the reason for MySQL crashing:

[root@xxxx ~]# cat /var/lib/mysql/xxxx.com.err 120528 17:45:05 [Note] Crash recovery finished. /usr/sbin/mysqld: Disk is full writing './mysql-bin.~rec~' (Errcode: 28). Waiting for someone to free space...

We run an SBS 2008 server with WSS. On the drive I have set aside for WSS, I'm fast running out of space due to the ShareWebDb_log.ldf file:

I've tried doing to do what I've read online - change the recovery mode, backup and truncate - but I can't actually see how I do this via the SQL Server Management Studio tool.

Active File Recovery full version is a powerful file recovery software. Its a simple recovery software that comes with many features such as partition recovery, data recovery The full installation of Active File Recovery 8.0.1 now includes a bootable image from which you can boot your computer into. To recover a file or folder, simply [...]

An NTFS volume might sometimes misreport free disk space. This primarily occurs since NTFS supports several file-level and volume features. To solve such problems, you might need to reformat the volume and use backup to restore lost files and folders. One should use file recovery utility in case if backup fails to restore.

Our present OpenBSD server needed more disk space, so we replaced the 13 GB disk (it's pretty old too) with 150GB. An image was made and then restored on the new disk. So far, so good. The new disk has a 13GB OpenBSD partition, it boots and works as before.

Next order of business: use the other 135 GB.

Hi Experts,

I have a problem wih /var. Disk /var is full. After i investigate, i found file /var/log/syslog.0 is growing rapidly. The size is 4.3G. I tried to move syslog.0 to another disk and file was moved successfully.

My question is why size /var can't increase? used space still 100%.

When i check the size of syslog.0 in place that i moved, the size is 4.3G.

How to mount compressed disk image?

Here is my situation: I have hard drive with (fresh) OS installation. I made compressed image of that disk to another disk.

How to make compressed disk image to file (on another disk): sudo cat /dev/sdb | gzip > disk.img.gz (sdb is source)

If free space is zero-ed, compression is much better.

I'm running a Centos VM in VMWare Fusion 5.x. The VM is an Aspera file transfer server and the files being sent are stored on the mounted disk. I've allocated 50 GB for the VM disk and shared another volume from the host (/mnt/hgfs/Aspera). It looks like the file transfers are taking up space on the root volume and Centos is complaining that it's out of space.