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
 General SQL Server Forums
 New to SQL Server Programming
 .ldf files size gone to 200 GB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

218 Posts

Posted - 07/05/2013 :  07:50:57  Show Profile  Reply with Quote
HI,

My database .mdf file size is around 6 GB however .ldf file size is 200 GB.

Though I don't know exactly what .ldf file is, I believe maybe that is the reason why some of the stored procedures taking long time to run which were running good before.

Can I delete the .ldf file? If not, can I do something to reduce the file size?

And please note that the database (tables) are also replicated to another sql server in another computer.

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  07:59:19  Show Profile  Reply with Quote
DO NOT DELETE THE LOG FILE. That will make your database unusable.

Do the following - I am assuming here that you are not doing regular log backups. If you are, strike everything I am saying below. If you have dev environment, try it out there first before you do it in a production environment:

1. Take a full backup of the database
2. Change the recovery model to SIMPLE. Normally I don't recommend that you do this, but it appears that you are not regularly taking log backups, so changing to SIMPLE is ok.
3. Shrink the log file.
4. Change the recovery model to FULL
5. If the log file size does not reduce, restart the server.

Going forward, schedule a maitainenace job so you take regular backups of the log file. In full and bulk-logged recovery models, the file will keep on growing until you take log backups.
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 07/05/2013 :  08:18:32  Show Profile  Reply with Quote
Thanks James K,

Take a full backup of the database means I have to right click on the database and click on task >> backup, right?
And how to shrink the log file after changing to Simple?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  08:47:07  Show Profile  Reply with Quote
Do you have a DBA who does regular backups? If you have, do this in consultation with him/her. If you don't have a DBA, then right click on the database name, Tasks->Backup.

Changing the recovery model is in the Options tab of the dialog you will see when you right-click and select properties.

To shrink, Tasks -> Shrink -> Files and then select Filetype = log

Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 07/06/2013 :  04:02:38  Show Profile  Reply with Quote
Thanks James K
There is no DBA here. Can you please tell me how I can take regular log backups? What does it mean by log backups?

Edited by - learning_grsql on 07/06/2013 06:19:50
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/06/2013 :  08:11:40  Show Profile  Reply with Quote
This is sort of a long-winded explanation, but bear with me here :)

SQL Server has three recovery modes: Full, Bulk-logged, Simple. You can see the recovery model you are using if you right-click on the database name in SSMS object explorer and select Properties, then Options tab.

In simple recovery model, if you have a disaster, the only way to recover is to restore from a full or full+differential backup. So you will lose everything since that last backup.

In Full and Bulk-logged recovery models, you can take not only full/differential backups, but you can/should also take log backups. These allow you to recover data upto the last log backup (and even more if your disaster scenario allows you to take tail log backup). It also has features that allow you to recover data as of certain point in time (e.g. restore for me the database as it existed at 8:17:37 AM today etc.)

The downside of full and bulk-logged recovery models is that it REQUIRES that you take regular log backups. The log file will keep on growing unless you take log backups. That is the reason your log file grew to 200 gigs.

In your case, depending on what your SLA is, you might decided that you don't want to deal with log backups and set the recovery model as Simple. But, I would suggest you don't do that. Even when the pointy haired guy tells you that it is ok, when a disaster strikes, you will be asked to recover the data beyond what they had specified.

What most people do is to set up the backup plans to take a full back up at a certain interval, differential backps in between, and log backups very frequently. So you might decided that you are going to do full backup once a day at midnight, a differential backup at 600, 1200 and 1800 hours, and log backups every 15 minutes. Each time you do the log backup, SQL Server will release any unused portions of the log file back to the SQL OS so it can be reused. That prevents the every-increasing log file problem.

Setting up the backup plans is pretty simple: In SSMS, under the server node, open the Management -> Maintenanice plans node, and select Maintenance plan wizard. It will walk you through what to do. I am sure there are tutorials and guides with pictures and screen shots that you will find if you google for it.

After you have set all of this up and have full/differential/log backups, do a restore on to a development server and see if it works as you expected. Document the procedure and keep it safe. When you have a disaster, it will come in very handy.

There is a lot of information on MSDN here: http://msdn.microsoft.com/en-us/library/ms187048(v=sql.105).aspx
Go to Top of Page

learning_grsql
Posting Yak Master

218 Posts

Posted - 07/07/2013 :  16:19:28  Show Profile  Reply with Quote
Many Thanks JamesK for your detailed explanation
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/08/2013 :  10:52:37  Show Profile  Reply with Quote
You are very welcome - glad to help.
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.06 seconds. Powered By: Snitz Forums 2000