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.
| Author |
Topic |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2013-07-05 : 07:50:57
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 07:59:19
|
| 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 database2. 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 FULL5. 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. |
 |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-07-05 : 08:18:32
|
| 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? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 08:47:07
|
| 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 |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-07-06 : 04:02:38
|
| Thanks James KThere is no DBA here. Can you please tell me how I can take regular log backups? What does it mean by log backups? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-06 : 08:11:40
|
| 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 |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-07-07 : 16:19:28
|
| Many Thanks JamesK for your detailed explanation |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-08 : 10:52:37
|
| You are very welcome - glad to help. |
|
|
|
|
|
|
|
|
.ldf files size gone to 200 GB