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)
 transactions log file doesnt free up space
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 05/06/2014 :  12:32:41  Show Profile  Reply with Quote
Good morning, I need your help pls, Im not a DBA, but Ive got 2 questions:

1. There are 3 Transactions logs files located in a drive ie I: I:\sqllogs/data02.ldf, I:\sqllogs/data22.ldf,I:\sqllogs/data17.ldf

Whenever transactions are finished and there are no active transactions the engine should free up space right ? the problem is drive I: is running out of space and i have to ask our DBA to free up space and he figures it out by shrinking those logs and then it release 40 GB available, its amazing for me. So in the scenario above and my humble opinion doubts came up:

1.Why the sql server engine is not able to release space as soon as finished all transactions ?
secondly, shrinking everyday logs files by our DBA is not harmful because Database is defragmented ?

Thanks for your help in advanced


James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 05/06/2014 :  13:53:19  Show Profile  Reply with Quote
How the log files get cleared depends on the "recovery model" of your database(s). There are 3 recovery models - simple, bulk-logged, full. In simple recovery model, what you said is true - when a transaction is completed, (or more precisely, when a "checkpoint" occurs) the log file is cleared. (The space is not necessarily returned to the OS, but the data in the log file is marked as cleared).

In the other two recovery models, the log files are not cleared EVER - unless you backup the log files. This is by design. This behavior allows you to recover the database from a full backup and log backups.

So first thing, find out what recovery model your databases are using. In SQL Server Management Studio, object explorer, right click the database name, properties, options tab. Then based on that either simply shrink the file if it is too large, or change the recovery model, or backup the log file and then shrink (and schedule regular log backups).

This article may be useful: http://www.sqlservercentral.com/articles/Stairway+Series/94579/
Also this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186633
Go to Top of Page

alejo46
Posting Yak Master

Colombia
127 Posts

Posted - 05/08/2014 :  18:45:48  Show Profile  Reply with Quote
OK, thanks you very much for your support and the links u proved me, i checked it out and the database is operating in Simple REcovery Model (RM) , but i dont understand because there is 1 job running evry 2 hours that executes a backup log (and according what i read usING backup logs is in FULL RM, right ?

Is there any command to trace a histotical event if datsbase waS setup as recovery or full RM ?

I kow is very diificult to answer but which RM is adcisable takinf into account that Full Back runs one a mpnth, differential twice a month and backup logs runs ever 2 houres. Th size if Database is aprocximately 1 TB ?

Thanks for your help in advanced ?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36619 Posts

Posted - 05/08/2014 :  19:23:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by alejo46

i checked it out and the database is operating in Simple REcovery Model (RM) , but i dont understand because there is 1 job running evry 2 hours that executes a backup log



If there a log backup job, then it is excluding the database that is using SIMPLE recovery model. Otherwise, that job would be failing since it's not possible to backup the transaction log for SIMPLE RM database.

quote:

I kow is very diificult to answer but which RM is adcisable takinf into account that Full Back runs one a mpnth, differential twice a month and backup logs runs ever 2 houres. Th size if Database is aprocximately 1 TB ?



Which recovery model is selected is dependent upon the business needs. How much data loss is acceptable in the case where a restore needs to be performed? If the answer is 2 weeks to a month, then use SIMPLE recovery model as you can just use the full backup or the full+diff backups. If you say 2 hours, then use FULL. If you say 15 minutes, then you need to adjust your log backup job to account for this.

And you also need to consider how long it'll take to do a restore. I don't like the schedule of your full or diff backups. It seems much too infrequent and would mean rolling through a lot of transaction log data (dependent upon data changes).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 05/08/2014 19:26:07
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