SQL Server Performance Forum – Threads Archive

can have more than 1 .ldf

Is there is any chance by which we can have more than 1 .ldf for a database like we have for datafile .mdf,.ndf and so on.

Yes it is possible, you can shrink the log by using sp_detach_db and sp_attach_db system stored procedures. For more information about detaching and attaching the databases refer BOL. When you detach the database using sp_detach_db, SQL Server will know that the database was cleanly shutdown and the log file need not have to be available to attach the database again using SP_ATTACH_SINGLE_FILE_DB. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

As mentioned you can have more than one .ldf, but SQL will always only use one file at a time, it will use the first file in the list until it is full and then move onto the next file. So there isn’t really a huge amount of benefit Cheers
Twan

Do a search here on the fora. This has been discussed here recently. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

BOL says "The transaction log in a database maps over one or more physical files. Conceptually, the log file is a serial string of log records. Physically, the sequence of log records must be stored efficiently in the set of physical files that implement the transaction log." But in my production environment, I have only one log with enough space ,for each databases .Some time when bulk opetaions takes place , it may grow large and fill diskspace .Even though 2 hours log backups are there, it will just only shrink the file, but physical logfile size will be still large, so i have a weekly trncation job which does the job of truncation of logfile and frees extra space to OS . rajiv
NewBie-23

]]>