[dba-SQLServer] When are log files used

jwcolby jwcolby at colbyconsulting.com
Sat Jul 2 12:48:36 CDT 2011


Francisco,

Of course I need to do that.  Thanks for the suggestion.

John W. Colby
www.ColbyConsulting.com

On 7/2/2011 12:23 PM, Francisco Tapia wrote:
> Wow I like your speedy setup, just remember to backup often, also
> something that might help during large operations is to switch the
> recovery model to simple mode to help maintain the log file size,
> operations are automatically flushed and the space is reused when
> write operations commit to the db files.  Full recovery models are
> only really needed if you have to be able to restore back up to the
> minute before failure.
>
> Sent from my mobile
>
> On Jul 2, 2011, at 7:29 AM, jwcolby<jwcolby at colbyconsulting.com>  wrote:
>
>> Mark,
>>
>>> Just curious, what prompted your question?
>>
>> When I got into this business I bought a 16 port Areca RAID controller and a bunch of 1 TB drives. I built big arrays and RAID06 volumes for maximum reliability and as much speed as I could muster. I created 2 tb partitions and placed my data files on one and my log files on another.  Awhile back I bought a pair of SSDs
>>
>> http://www.newegg.com/Product/Product.aspx?Item=N82E16820227590
>>
>> And made a 220 GB RAID 0 array and placed a set of three databases (my "central" databases) on there for speed.
>>
>> This last week I was doing some Update / Append operations on some of these databases and ended up with "disk full" - stopped me cold!!!  Luckily I was able to move the logs off to rotating media and let them complete their operations and then finish up what I was doing.  Anyway...
>>
>>
>> I upgraded the server last night.  I added a very reasonably priced (and reasonably powerful) RAID expansion card called the ASUS PIKE 1068E raid controller.  It only supports Raid 0 and 1 but that is perfect for this application since I am using Raid 0 for these volumes.  It also has no write cache so it is not appropriate for high write applications.
>>
>> http://www.newegg.com/Product/Product.aspx?Item=N82E16816110042 ASUS PIKE
>>
>> and four new SSDs to hold the central database files I work with:
>>
>> Mushkin Enhanced Callisto Deluxe MKNSSDCL120GB-DX
>>
>> http://www.newegg.com/Product/Product.aspx?Item=N82E16820226152
>>
>>> Just curious, what prompted your question?
>>
>> What I was trying to discover was when log files are used in order to discover how much room I needed to give them.  I had all of the databases and their log files on a single RAID0.  I was doing some appends / updates and the log files filled up the disk, which is what prompted the expansion.
>>
>> In the end I decided to put the data files on a new RAID0 created from the 4 new SSDs (~440 GB) and leave the log files on the old RAID0 using the old two SSDs (~220 GB).
>>
>> I really only write to these files roughly once per month, but I ended up doing some processing unrelated to the monthly thing.
>>
>> ATM the data disk has 160 GB used (280 GB free) and the log file disk has 18 GB used (204 GB free).  That should hold me for awhile, but I still have 4 more SATA ports on the Pike controller if I need them.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>> On 7/2/2011 6:25 AM, Mark Breen wrote:
>>> Hello John,
>>>
>>> Just curious, what prompted your question?
>>>
>>> Mark
>>>
>>>
>>>
>>>
>>> On 2 July 2011 00:16, Francisco Tapia<fhtapia at gmail.com>   wrote:
>>>
>>>> It's for updates and inserts only, read operations may use the tempdb
>>>> depending on how you constructed the select...
>>>>
>>>> Sent from my mobile
>>>>
>>>> On Jul 1, 2011, at 4:04 PM, jwcolby<jwcolby at colbyconsulting.com>   wrote:
>>>>
>>>>> Are log files used for read operations or only data modifications?
>>>>>
>>>>> --
>>>>> John W. Colby
>>>>> www.ColbyConsulting.com
>>>>> _______________________________________________
>>>>> dba-SQLServer mailing list
>>>>> dba-SQLServer at databaseadvisors.com
>>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>>> http://www.databaseadvisors.com
>>>>>
>>>> _______________________________________________
>>>> dba-SQLServer mailing list
>>>> dba-SQLServer at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>>> http://www.databaseadvisors.com
>>>>
>>>>
>>> _______________________________________________
>>> dba-SQLServer mailing list
>>> dba-SQLServer at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>> http://www.databaseadvisors.com
>>>
>>>
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list