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

Hi all,

I desperately need some advice from the mssql server gurus here. I'm not
the best mssql person but i do have some knowledge of database through
oracle and i'm trying to solve an urgent problem with our mssql server.

We have a table in mssql server called auditlog and this auditlog table
is taking up 7.8 GB of disk space in windows.

Environment is win2000 advanced server running mssql 2000 server.

Some wise person decided to do a bulk copy of 100 million records to
another table and this generated a lot of records. I have only 34GB hard
disk and i'm left with 10MB of space left while this operation is
running. This causes the transaction logs to fill up to 6.1 GB and a 2nd
physical datafile at 5.7GB space.

Assuming i'm happy with not rolling back and i do not have sufficient
space left to copy out the log files. Can i delete all these transaction
logs with extension .ldf and leave just the .mdb file?

When new records are added in to the database from then on, will a new
.ldf file be created for transaction log?

Else, can anyone provide ideas to solve this problem? e.g. truncate logs
command? shrink database?

In need of urgent assistance,
Alvin
LVL 1
manualvinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OtanaCommented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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


Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Harshad09Commented:
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,
Guy Hengel [angelIII / a3]Billing EngineerCommented:
As far as I know, "Shrink Database" doesn't really work good for transaction log files ...
manualvinAuthor Commented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Forget about the EM User interface for that task, use Query Analyzer to do it:
sp_attach_db should also work with several .ldf files, you simply need to put them all

EXEC sp_attach_db @dbname = N'YourDB',
   @filename1 = N'D:\data\YourDB.mdf',
   @filename2 = N'E:\logs\yourdb_log1.ldf'
   @filename3 = N'F:\logs\yourdb_log2.ldf'
etc

>>How do i put the database to simple recovery model? GUI steps or command line help will be appreciated.
EM: Database -> Properties -> Options : choose the recovery model

>>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!!
As said, the first ldf file CANNOT be removed from the database, and you might even have trouble removing the second one properly.
To reduce used file space, you will need to use the DBCC SHRINKFILE command (using Query Analyzer), eventually several times in a row with intermediate BACKUP LOG WITH TRUNCATE ONLY in between...

>>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?
If you perform regular full/transaction log backups  -- or -- have the database in simple recovery model, the transaction log space will not need to grow without end. Actually, I assume that you didn't backup the database and have added the second transaction log file to accomodate the growing file without end.
If all is setup properly, the database will NOT stop working.

>>If autogrow is removed, does this automatically deletes older entries in the transaction log file and continue adding data?
No. The regular backups or the simple recovery mode will do that.
The autogrow enabled will only ensure that the transaction log file grows if neither of these 2 options is done.
Actually Autogrow Enabled is basically a BAD setting...

Transaction Log Size:
Recovery Mode simple: 5x(maximum number of concurrent users)x(the size of the largest transaction ever run on your database)
Actually, some 50-100MB should be more than enough for medium databases...
Full Recovery Mode: 2-3x(sum of transaction size during the longest and heaviest period between 2 backups)
Actually, if you perform full backups daily and transaction log backups every 15-30 minutes, you should end up with a transaction log space of something between 200MB and 1GB, depending on the number of users that really work on your database

For both, you actually COULD use the Autogrow option to determine the size that should be needed with a MAXIMUM size specified that is more than reasonable...












Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
estylerCommented:
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.
estylerCommented:
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.  
manualvinAuthor Commented:
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!!
estylerCommented:
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
manualvinAuthor Commented:
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
estylerCommented:
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!





juliandormonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.