Out of Control Log Files

MS Sql Server 2000

[Argblat]

So I suppose that I'm learning this lesson the hard way at this point, but forgive me, I'm no database expert.

What do I do with an out of control log file for a MS Sql Server 2000 Database? In setting up the database I didn't realize the importance of capping the file size for the log. Now the log is 1 GB in size, and the minimum I can shrink it to is only a little less than that. Whatsmore, I can only cap the log file size (which I guess I should have done at the get go) to something larger than the current size.

I don't care about the logs...they have no use for me. If they have a use for the system itself, I can understand. But my question is..how do I regain my disk space from this log file, and what is a good practice for setting up log limits and growth for future databases I create?

Thank you
-Mike

[syber]

BACKUP LOG databasename
WITH TRUNCATE_ONLY

This will trim the log without backing it up.

The log will continue to grow unless you perodically back it up or truncate it.

[Argblat]

syber, thanks for that..it did the trick

As a follow up, what use are these log files? I would be interested to have someone point out something that I'm not thinking of that might make me rethink looking at them as such a pest.

Also, as far as capping the size of the log file (now that I finally can)...does anyone have any suggestions / best practices / what they usually do?

Thanks for the help
-Mike

[fischermx]

You should not cap the log file growth. If you do, what you'll get is a big error when it hits the max size, and all of your database activity, except some small queries would stop to run.
The only thing you can do against log file growth is review the queries on your database, searching for long consuming queries, specially that returns tons of sites, or large transactions.
Also, if you're not doing regular hourly log backups, there's no point in having "full recovery mode" on in your database, switch it to "simple mode" and it will reduce the growth of the log file.

[emulti]

The log file keeps a store of the insert and update transactions performed on your database. They are used for recovering the database but this is top heavy for most web sites, particularly if you have a daily backup.

As said probably best to set the logging option to simple mode and then use SHRINKFILE to shrink your existing log.

[aspdaddy]

>You should not cap the log file growth.

Id rather get an error than run out of disk space :)

Good practice is to define the database and log file sizes based on your systems capacity.

If you really have no use for the log file then just delete it and start again, the system has no need for the data it contains if you dont. The mdf file has all your data. SQL will just recreate an empty log and start filling it up again to the limit you define

[ixyst]

The log file is vital for your database recovery options. It contains information about all the transactions that happened sine the last database backup. And this is your problem - you have not made a database backup in a long time, which has cause the log file to grow to gigabytes.

Why is log important - because it helps restore your database to latest possible point. If you server crashed, powers down, hard drive goes bad and so on, the log file is your saviour!

Should you cap the size of the log - hell NO! You should only do this if the database is not as important for your server as another space-consuming application. The moment your database reaches the ap, it will stop functioning.

What is the best approach? - do backups on a regular basis. A DB Backup will truncate the log naturally back to close to 0. If you do BACKUP with TRUNCATE_ONLY, you lose any recovery options, should the dabatase file (mdf) become corrupt for one reason or another.

If you do not want/need such backups (if you information is really not sensitive and you can afford to lose it in case of hardware failure), simple switch your datbase recovery model to Simple.

[aspdaddy]

Should you cap the size of the log - hell NO! You should only do this if the database is not as important for your server as another space-consuming application.

You mean like the operating system itself or the systems event logs that may be requested in a court of law? I think they are more important than your database

What are you basing the above statement on? It certainly isnt industry best practice.

[ixyst]

How much space does the system and system log need? If you are fighting for the last few gigabytes of space left on the server - you may very well shut SQL Server down, instead of letting it reach the log file limit and just start giving errors for 95% or the queries.

I have not heard of any Best Practice advise, telling you to put a limit on the Log file. Instead, the most common advice is to set the site of the Log file to a good number (depending on your database size and number of transactions). In such a case, the Log file will stay the same size (although a good part of it will be empty most of the time) and not grow if you backup on a regular basis. This will help you plan your space on the server.

If you currently have a limit on your log file and you are not getting errors, this is only because you are never reaching that limit. SQL Server does not consider this limit when it writes data to the log. Setting a limit, does not make SQL Server conserve space.

I strongly advise against setting a limit/cap on the SQL Server log file. Instead, watch it for a while and see what is the maximum size it reaches before a backup. Multiply that size by 3 and set the size ofthe log to that number (not the limit of it).

[aspdaddy]

Best Practices
• For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.

AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.

Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.

[support.microsoft.com...]