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 |
|
alejo46
Posting Yak Master
157 Posts |
 Posted - 2014-05-06 : 12:32:41
|
| 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.ldfWhenever 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-05-06 : 13:53:19
|
| 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 |
 |
|
|
alejo46
Posting Yak Master
157 Posts |
Posted - 2014-05-08 : 18:45:48
|
| 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 ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-08 : 19:23:27
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
transactions log file doesnt free up space