Log files are very big... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log files are very big...

Author

Topic

Ali80
Starting Member

9 Posts

Posted - 2007-02-22 : 05:31:41

Hello everybody

I have a problem with the log files in my SQL Server. The log files are growing up so fast that I can't control it, and I don't know why they are growing so big and so fast.
So I tried to deattach the database, delete the log file and so attach the .mdf file and it will automaticlly make a new log file. But I didn't work, I got an error when I tried to do it. But this is not the biggest problem. I want a solution for how I can delete the log files in safe and make new log files and after make it "restrict file growth" to f.ex. 10 MB. I have some log files which are up to 30 GB. And so we got problem to work with some programs because it there is no place in the disk.
I tried many things like "skrink" the database and make it "restrict file growth" (they are already big) for the log files, but it didn't work. I have to in some way delete the log files and make new, and so set "restrict file growth" to 10MB.
Or is there another way to solve this problem?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 06:26:13

I reckon you are trying the wrong things (like SHRINK and DETACH + Delete LDF file)

Have a look at this and see if that helps:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why%20is%20my%20LDF%20Log%20File%20so%20big

Kristen

Ali80
Starting Member

9 Posts

Posted - 2007-02-22 : 07:56:53

Thanks Kristen

I red that topic, but I have to say that I don't know how I can solve the problem.
If u can explain to me how to do it please.

Ali

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 08:20:35

Do you make transaction backups?

For more info see this specific post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434#193019

Kristen

Ali80
Starting Member

9 Posts

Posted - 2007-02-23 : 03:36:37

The type of the backup "*.BAK" file, and I am sure that is taking backup of the both (mdf and ldf file).
1. Does that mean that I am taking a transaction backup?

2. Is it normal that the backup file "BAK" (for one database) is about 700MB, and the "mdf" file and the "ldf" file (for the same database) together are about 25GB (where mdf = 1GB , ldf = 24GB)?

Do u have a solution to stop the logs of being so big? Is there something that I can do with the Options of the SQL Server?
And what is the reason in ur opinion which is making the logs be very big like this?

Thank u very much

Ali

Kristen
Test

22859 Posts

Posted - 2007-02-23 : 08:19:07

1. no - could be anything. Backup doesn't "take a backup of both mdf and ldf" - its quite subtly different to just copying those files; it extracts the data necessary to reconstitute your database to exactly how it is at the time.

2. Its normal that .BAK is smaller than .MDF, ignoring .LDF for a moment! However, if your system is NOT in SIMPLE Recovery Model, AND you have not been making TLog backups, then your .LDF file will be (relatively) huge - given your figures that looks to be the case/

3. Well, that's why I pointed you to the other post, so I don't have to retype it all here!

The issue raised there is:

"Do you make transaction backups?"

which has three possible answers:

Yes
No and I don't want to
Don't know

You are "don't know" presumably?

So you need to decide whether you want TLog backups, or not (as per the reasons I gave in that post).

If you do NOT then change your database to SIMPLE Recovery Model.

If you DO then make sure you are taking TLog backups regularly (I suggest every 10 minutes) and make a ONE TIME ONLY Shrink of your database.

Kristen

Ali80
Starting Member

9 Posts

Posted - 2007-02-23 : 14:11:55

Thank u very much Kristen

I got it... Actually I am taking backup of the ldf files, but by another program where it works with SQL Server.
So I don't want/need to take backup of transaction logs. Simple mode is the solution.
But u suggest that I take backup every 10 min. So it will not be a problem that SQL Server takes backup while I am working with the databases?

I want to ask u about something else. Here in our company we take full backup of important data (including SQL Server) in the night. We have a program which is doing this job, and it saves all the backup in an external HD. Is this enough if I changed the mode of the databases to "simple", or do I have to find another backup system?

Really thank u again for ur help and ur time.

Have a nice weekend

Ali

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-02-23 : 15:38:17

Ali,

Just keep in mind that if your db is following the simple recovery model, you will not be able to do point in time recovery. That is, if you back up at night, in case the database becomes corrupt, you'll be able to recover only the information you had in the db up to the time you backed up.

Also, you cannot backup the transaction log of the db following simple recovery model.

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-02-23 : 16:43:57

Remember, the backup of a database MUST be through the SQL backup command, or some package that backs up SQL specifically. Backing up the datafiles with something like xcopy while the databases are open will result in GARBAGE for anything but the smallest least used databases.

Kristen
Test

22859 Posts

Posted - 2007-02-24 : 04:41:10

"I am taking backup of the ldf files"

You worry me describing it like that! You cannot backup the MDF and LDF files directly (unless SQL Server is stopped at the time). Even if you do, I very much doubt that a backup of an LDF file is NO USE unless it is used with the MDF file from the exact same time - and I am just staying into guessing what might work - which is why SQL Server has built-in Backup commands that avoid any such uncertainty!

We get lots of questions here from people who are using 3rd Party tools to backup SQL Server database, and have had a problem. That's not to say that they don't work, but there is obviously the possibility that a Service Pack to the database means that the 3rd party tools need an update too, and so on, and folk discover that they have a problem when they try to restore ... which is a bit late of course!

I think you would be better setting up a maintenance plan that makes a backup of your database, and then you copy that Backup using your existing process that "backs up your important data"

"But u suggest that I take backup every 10 min"

That is ONLY important if you use a FULL Recovery Model. If you change to using SIMPLE Recovery Model that you should backup at a frequency that you are comfortable to restore from, as sql_er has described.

If you can re-create one day's worth of data, then a FULL backup each night is enough.

If you can really only re-create, say, 4 hours data then you could look at getting SQL Server to generate a Full backup each night and a Differential backup every 4 hours (the differential backup will be smaller, and quicker, because it only backs up the changed parts of the database)

"So it will not be a problem that SQL Server takes backup while I am working with the databases?"

That is correct. SQL Server's Backup is designed to be used whilst the database is still being used. Obviously it puts some extra effort on the server whilst it is running, but in practice its not a problem. Ideally you would backup the database at night (or during some "quiet" period); for databases using FULL Recovery Model you then do Transaction backups every, say, 10 minutes. They have very little to physically copy, so they run very quickly. In SIMPLE Recovery Model doing a Differential backup is also likely to have very little to copy, and is equally quick.

But lets not make it complicated. If you can re-create a day's data then a once-a-day Full backup is all you need (and so set the database to SIMPLE Recovery Model and do a one-time shrink of your database, and then let it grow back to the size it needs to be for normal working)

Whatever route you go down make sure you test it - before you have a real need to recover.

Create a new temporary database.

Restore your backup(s) to that new database.

Do a

DBCC CHECKDB('TempDatabaseName') WITH NO_INFOMSGS

and check that you don't get any errors.

Then you will know that the backup files where able to be restored safely.

(Its a good idea to do this once-in-a-while so that you are confident that the backups continue to work OK. People here with mission-critical databases use this restore-and-test approach for EVERY backup they make!)

Kristen

Ali80
Starting Member

9 Posts

Posted - 2007-02-26 : 14:32:37

Hello

First I want to thank u all for ur help.

About the backup, I take backup of the databases through SQL Backup Command and through a program which we use it to work with the SQL Server.
I understood the different between full model and simple model. Today I made a test, I took a backup of a database and after that i changed the recovery model from full to simple. So after that I took backup of the database, and the LDF file was the same size (26GB), nothing changed. The size of LDF file should return to the normal size, isn't that right?
Do I have to do something before or after the change?
I have a friend of mine who told me to delete the database and so restore it, and when restore it I put the database in simple model? Would that solve the prolem?

Thanks

Ali

Ali80
Starting Member

9 Posts

Posted - 2007-02-27 : 03:06:30

Hi

When I checked the database today in the morning, I saw that the LDF have been rezied to the normal size (1MB).
How can I make the SQL Server take backup many times at the one day (every 15 min or 1 hour)?

Ali

Kristen
Test

22859 Posts

Posted - 2007-02-27 : 05:08:54

"the LDF file was the same size (26GB), nothing changed."

When you take a Transaction backup in FULL Recovery Model the "committed" transactions in the TLog are backed up. The space they used is then marked as "free". The size of the LDF file is NOT changed.

When you use SIMPLE Recovery Model committed transactions are marked as free, and thus available for reuse.

The size of the LDF file is not shrunk by any of these activities - the reason being that shrinking & growing the file just fragments it, and it takes a lot of "resources" to extend the file.

If your file is big then perform a one-time-shrink, and then leave it alone to grow back to the size it needs to be to handle your normal tasks.

You may need to shrink it again if you do some massive one-off delete, or something like that, but otherwise best to leave it alone

Kristen

Ali80
Starting Member

9 Posts

Posted - 2007-02-28 : 03:02:41

I want to thank u for ur help Kirsten

After that test with one database I got now a free space in the HD.
I have a question and I hope that u can help me please. It is about taking backup several times in the day, how can I do that?

Ali

Kristen
Test

22859 Posts

Posted - 2007-02-28 : 08:47:57

"backup several times in the day, how can I do that?"

Either:

Database Recovery Model = FULL

Then FULL backup once a day (e.g. during the night, quiet time), and then TRANSACTION backup every 10 minutes. The transaction backups will allow you to recover to any time within the day (NOT just the 10 minute intervals)

alternatively

Database Recovery Model = SIMPLE

Then FULL backup once a day (e.g. during the night, quiet time), and then DIFFERENTIAL backup as often as necessary. You can recover to the last Full + Differential backup.

Note that the Differential backups will get bigger each time, until the next Full backup is taken. They may become the same size as the Full backups and take just as long ...

If you want backups during the day then the FULL Recovery Model backup method, above, is normally the most desirable way of achieving this.

Kristen

achall60
Starting Member

1 Post

Posted - 2009-09-16 : 16:21:25

If you find your database is growing excessively (more than you would expect), your stored procedure code may need tweaking.
Sometimes use of temporary tables can cause more transactions to be logged than is neccessary.
Have a look at this article....

http://www.weblitmus.com/BlogEngin/post/2009/09/16/Reducing-Transaction-log-growth-in-MIcrosoft-SQL-server-2005-2008.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-09-16 : 23:35:16

I would say this article doesn't make sense. The person who wrote this has to carefully read in booksonline.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources