MSSQL database size growing, can .ldf file be deleted to reduce space or any other suggestions?

You could use:

BACKUP DATABASE db1 WITH TRUNCATE_ONLY

Or, you could detach your database, delete the ldf file, and attach your database again. This will create a new ldf file. However, if you do not have enough disk space left, this could cause your detach to fail, leaving you with a corrupt mdf file, so be careful when using this.

You cannot delete the (primary) .ldf file, you MUST have at least 1.

You CAN shrink the file(s) of a database, including the transaction log files
-> refer to DBCC SHRINKFILE
-> search EE for this topic

You SHOULD remove the Autogrow option from the transaction log file, or put at least a reasonable Maximum size for the transaction log file(s)
--> of course, AFTER you shrinked the files

To MOVE a file, you have to backup & restore the database, during the restore you can profile alternate file locations.
To MOVE a file, you can also DETACH the database, move the files where you want them, and attach the database using sp_attach_db stored procedure providing all the database file path

If you put your database into "simple" recovery model, it will still require the transaction log file, but for sure a much smaller one, as all the transaction space used can be reused as soon as the transaction commits (simplified view)

Follow these instructions:

Open Enterprise Manager --> double click on "Microsoft SQL Server --> double click on "SQL Server Group" --> Locate your server --> double click on your server --> double click on "Databases" --> Locate your database --> right click on your database --> click on "All Tasks..." --> Click on "Shrink Databases..." --> Choose appropriately and click OK

Thanks,

As far as I know, "Shrink Database" doesn't really work good for transaction log files ...

Hi,

Thanks for all your comments.

Fortunately for my case, it's a HP DL series server, i have a spare hard disk available 36.4 which i'm gonna slot in to the existing slot. Existing are 2 36.4 hardware raid disks.

I do have 2 ldf files and apparently i can't detach and reattach with 2 ldf files. Can someone please give me more specific commands to perform a detach reattach to the new hard disk, tentatively named e:

Shrink database doesn't seem to be doing the job as the total disk size was not reduced. The process is done through shrink database using enterprise manager. Is this method wrong or the query way should be the correct answer? I would appreciate specific commands as i'm totally not good in mssql server at all...

How do i put the database to simple recovery model? GUI steps or command line help will be appreciated.

Is there anyway to set the transaction logs to continue to work but delete all transactions later than a certain date?

Angel, if i need at least 1 ldf file, which one should i delete the first ldf file at 6.1GB or the 2nd at 5.7 GB? I really need the space!!

What will "You SHOULD remove the Autogrow option from the transaction log file, or put at least a reasonable Maximum size for the transaction log file(s)"  this do for the transaction log assuming it doesn't grow? the database will grind to a halt?  If autogrow is removed, does this automatically deletes older entries in the transaction log file and continue adding data?

What is a reasonable maximum size for transaction log file? normal practice?

Sorry but again, please provide detailed answers, i'm really a newbie to mssql server! Thanks but i've been sitting in the datacentre for the past 15 hours alone searching for solutions.

I had a similar problem.  

I fixed by doing the following:

SHORT TERM
1.  Take steps shown in other posts either through EM or QA to backup the database LOG files.  You must backup the log files before they can be shrunk(?).  You can map to another machine and backup log files since you are low on space.  You do not need to move the log files.
2.  Take steps shown in other posts either through EM or QA to shrink database.

LONG TERM
1.  Setup a server agent job to backup the log files at a frequency that makes sense.  I created a daily job that overwrites the log backup on a shared device with cheap (IDE/ATA) disk space.  You need to backup the log file in order to shrink.
2.  Choose autoshrink option on database if processing power is concern or create another job to shrink the log files after the log backup is complete.

Forgot to mention that you may need to perform the SHORT TERM steps more than once and to make sure you choose the log file in the shrink database screen by clicking the FILES button above HELP button in EM.  Also, remember to look at current size and space used fields in Shrink File screen.  I just did this to a 5G log file and it took about 10 minutes to complete.  

Hi all,

Thanks for taking the time to answer my questions. I finally managed to get the other hard disk online and transferred a lot of junk over...

Now i have about 13GB free after transferring over and will be looking to backup the log files to the other hard disk E:

Sorry estyler but can you elaborate on the command to backup the log file in order to shrink in? In fact, i still don't get why you have to backup the log files before it can be shrunk.....Care to elaborate? My guess it's due to the truncate which minimises the logical space?

What's the difference if the commands are done by query analyser compared to EM?

Thanks for all your help, will update your points accordingly the next day...or rather shortly today...for my timezone that is....i've been up 20 hours!!

Difference between EM and QA is personal preference.  Beginners will choose to use EM much more for point and click, I know I did.  I now use every tool for different purposes.

I believe backing up the log file sets a checkpoint for truncating.  You can witness this by choosing the LDF file and shrinking in EM (Make sure you choose LDF file in shrink file screen).   Notice the Current Size and Space Used values before and after shrinking.  If you try to shrink before the logs are backed up they will stay the same size.  Once an LDF backup is completed (Explained below) the Space Used value will decrease to the checkpoint.  The Current Size, which is the log file size, will stay the same until you shrink the log file again.  If you check the Space Used and Current Size values both will have then decreased.  The amount each decreaes depends upon other configuration settings.

My way to Backup LDF files to reduce size:
Right click on database in EM, choose AllTasks, choose Backup.  Select transaction log, for destination I like to map a drive letter to another device with cheap space and backup the LDF to a file usually named DBNAME_LOG_BACKUP, choose overwrite existing media.  You will need to click the ADD button to create a backup file and place it in the appropriate area for your environment.  You also have the option to schedule this same log backup job to recur at an appropriate time.

As I said in may last post, this is how I resolved an issue we found problematic because we import approximately 4GB of data a night and could not let the logs grow unchecked.  This has worked well for 1.5 years now.

I hope I gave you all information needed to be successful.  Please let me know.

Thanks

Hi Estyler,

I have backuped the transaction logs and issued a dbcc shrinkfile (logical_filename, TruncateOnly)

I managed to shrink the 6.9 GB transaction logs to 600MB! Amazing!

I have a current maintenance plan that runs every nite ar 2 am to perform a full backup of the database and backup of transaction logs. This is from the previous engineer who left.

I am thinking if it's feasible to implement another job that will include this script to run on a nitely basis to shrink the transaction logs? Will this shrink the transaction logs even further?

I understand that setting autogrow enabled is a bad setting, but let's say if i deselect autogrow. What is the max size of the transaction log and what will happen if 1 day the transaction log grow past the max size? Will this cause the database to stop working? or i will just lose all the additional transactions that could not be updated into the log since the trans logs were full?

My main MDF file is still 8.9 GB though which is really big...for a 36.4GB hard disk. My question is if the dbcc shrinkfile will work on it? Does it work in the same principle, need to backup the database first before running it?  I'm worried about database corruption, then i'm really screwed.

The measure put in by my previous engineer was to backup to a directory and run zip on it, archive and move off site to a linux machine via samba. However the backup that run did not include the main database for some time since it outgrew 4GB, it's now 8.9GB. I think 4GB being the max windows size for winzip to zip up. Any other zip programs that are free or good alternatives to suggest whereby the main idea is to shrink the backups and move it offsite?

Regards,
Alvin

I am thinking if it's feasible to implement another job that will include this script to run on a nitely basis to shrink the transaction logs? Will this shrink the transaction logs even further? >>

You can definitely create another job for the shrink database script.  The size of the log file will depend upon what transactions are posted into the mdf file.  The backup creates a checkpoint to determine what transactions have been moved to the mdf file and can now be truncated.  If you have activity after the backup those records will not be removed until another backup, so it will depend upon your system activity.  Our 4GB data import results in a 25mb log file after shrinking because not much data is actually entered into this database from other sources after our large update.

I understand that setting autogrow enabled is a bad setting, but let's say if i deselect autogrow. What is the max size of the transaction log and what will happen if 1 day the transaction log grow past the max size? Will this cause the database to stop working? or i will just lose all the additional transactions that could not be updated into the log since the trans logs were full?
My main MDF file is still 8.9 GB though which is really big...for a 36.4GB hard disk. My question is if the dbcc shrinkfile will work on it? Does it work in the same principle, need to backup the database first before running it?  I'm worried about database corruption, then i'm really screwed.>>>

I dont' necessarily agree the autogrow is a bad setting.  I like to use it with a limit on the growth.  I am using autogrow without a limit and using the scheduled maintenance to keep the file sizes in check.  Your application will get 'log file full' messages when the HD space runs out or the log file hits a set limit not allowing additional transactions.  I have seen this happen and the database becomes in accessible from an application, but not from EM or QA, which would allow you to correct if needed.

The most important thing you can do right now is to monitor the mdf and ldf files over a long period (create a log to check weekly or monthly) to determine the rate of long term growth.  How long did it take to amass a 9GB mdf?  If it took 2 years and the company will continue to grow this way, you could say you have enough space for another 2 years without creating an additional mdf on another drive or adding more HD space.  If you expect a boom in business, plan to add additional HD space and limit the current mdf to a percentage of your 36GB HD and create a 2nd mdf on an additional HD.  As far as mdf shrinking, you can safely run on the mdf, however, you will not see such a drastic change because mdf files tend collect data without deleting it.  Data would need to be deleted from within the mdf file before it reduces in size.

The measure put in by my previous engineer was to backup to a directory and run zip on it, archive and move off site to a linux machine via samba. However the backup that run did not include the main database for some time since it outgrew 4GB, it's now 8.9GB. I think 4GB being the max windows size for winzip to zip up. Any other zip programs that are free or good alternatives to suggest whereby the main idea is to shrink the backups and move it offsite?>>
Can't help here.  I use Veritas for online backups while the database is still running.  

Good Luck!

I tried the above method of backing up the Log File, before Shrinking it but this was not successful because I didn't even have enough disk space to run the initial backup.

Here's the easiest solution I found.
Run the query BACKUP LOG DB_Name WITH TRUNCATE_ONLY

Then run a shrink on the log file.

Then reset your file to not auto grow to a crazy size in the database properties > Files section