SQL Server Performance Forum – Threads Archive

regarding Log files

hi all iam a newbie and a fresh DBA rite now…
well i have this doubt nagging me as wat can be the number of virtual log files that one can have for optimum performance…I understand it all depends on the log file size but then there has to be some bench mark to judge performance…can somebody help me with
this? regards
suleman

It’s deppends.
What is you hardware (specially disks and Raid)? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I don’t think we can control virtual log files. This is something that SQL Server manages internally. But as and when space is allocated to the log file, then the newly allocated space is split into virtual log files ( I am not sure if it is split or all the newly added space becomes one single virtual log ). For performance reasons It is recommended not to keep tran log file size too low so that there are lot of vitual log files.

… One thing I forgot to mention is that atleast in OLTP env, you should separate tran log file location with data file location for performance reasons. In some DSS env, this may not be necessary like in one of my examples. I have an application which will first read flat files to load data and then it will process them so that later on user can view processed data thru the same application. During load/data process , users cannot access the application ( GUI will be disabled ). So here due to lack of space we have placed data and log files together without hurting the peformance.

I don’t have much idea about the controling virual log files.
Yes we can improve the performance through proper usage of Tempdb and allocating proper space for Transaction log file. The other way of performance optimization is using filegroups, You can improve performance by controlling the placement of data and indexes into specific filegroups on specific disk drives. I have some info with me, you can use this if you find it good.
Why Use Multiple Files? You might wonder what the reason would be for creating a database on multiple files located on one physical drive. There’s no performance benefit in doing so, but it gives you added flexibility in two important ways. First, if you need to restore a database from a backup because of a disk crash, the new database must contain the same number of files as the original. For example, if your original database consisted of one large 12-GB file, you would need to restore it to a database with one file of that size. If you don’t have another 12-GB drive immediately available, you cannot restore the database! If, however, you originally created the database on several smaller files, you have added flexibility during a restoration. You might be more likely to have several 4-GB drives available than one large 12-GB drive. Second, spreading the database onto multiple files, even on the same drive, gives you the flexibility of easily moving the database onto separate drives if you modify your hardware configuration in the future. Microsoft’s internal SAP system uses a SQL Server database created on 12 files. Microsoft has found that this provides the ultimate flexibility. They could separate the files into two groups of six, six groups of two, four groups of three, and so on, which would allow them to experiment with performance enhancements gained as files are spread over different numbers of physical drives.
—————————-

]]>