SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log files are very big...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ali80
Starting Member

Norway
9 Posts

Posted - 02/22/2007 :  05:31:41  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 02/22/2007 :  06:26:13  Show Profile  Reply with Quote
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
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/22/2007 :  07:56:53  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/22/2007 :  08:20:35  Show Profile  Reply with Quote
Do you make transaction backups?

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

Kristen
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/23/2007 :  03:36:37  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/23/2007 :  08:19:07  Show Profile  Reply with Quote
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
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/23/2007 :  14:11:55  Show Profile  Reply with Quote
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
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/23/2007 :  15:38:17  Show Profile  Reply with Quote
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.
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 02/23/2007 :  16:43:57  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/24/2007 :  04:41:10  Show Profile  Reply with Quote
"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
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/26/2007 :  14:32:37  Show Profile  Reply with Quote
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
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/27/2007 :  03:06:30  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/27/2007 :  05:08:54  Show Profile  Reply with Quote
"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
Go to Top of Page

Ali80
Starting Member

Norway
9 Posts

Posted - 02/28/2007 :  03:02:41  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/28/2007 :  08:47:57  Show Profile  Reply with Quote
"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
Go to Top of Page

achall60
Starting Member

United Kingdom
1 Posts

Posted - 09/16/2009 :  16:21:25  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 09/16/2009 :  23:35:16  Show Profile  Reply with Quote
I would say this article doesn't make sense. The person who wrote this has to carefully read in booksonline.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000