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 2008 Forums
 SQL Server Administration (2008)
 How to find "Primary" log file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 06/11/2014 :  19:14:57  Show Profile  Reply with Quote
How does one determine which log file is considered to be the "primary"? Is it simply the one with the lowest sys.database_files.file_id value?

Many thanks.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 06/12/2014 :  01:44:59  Show Profile  Visit jackv's Homepage  Reply with Quote
If you attempt to delete the primary log file - you should get a 5020 error.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 06/12/2014 :  12:33:14  Show Profile  Reply with Quote
I have tried to delete the primary log file and it does, indeed, yield an error. What I'm actually trying to accomplish is to proactively detertmine which log files are dropable and clean them up. I could give you the details of how I came to be in this situation but its a long, incredibly boring story. It suffices to say that the main log file is no longer considered the primary and the "temporary" additional log file (on another disk drive) has become the primary. I believe that this happened when the database was restored to a new server location. This particular entanglement I have resolved. Moving forward, I have an administrative task which looks for and removes "temporary" log files. It made the erroneous assumption that the primary log file would be on the designated Log drive. I'd like to alter the logic to explicitly exclude the "primary" log file but I can't find documentation on how to determine the "primary" attribute.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 06/12/2014 :  12:54:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes I believe file_id is the correct column. It appears file_id=2 will always be the primary log file. See here for the tests: http://beyondrelational.com/modules/2/blogs/77/Posts/18703/0231-sql-server-sysdatabasefiles-how-are-file-ids-allocated.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 06/12/2014 :  16:57:32  Show Profile  Reply with Quote
You are truly amazing... Is there anything that you don't know? Thank-you.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 06/12/2014 :  17:02:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Bustaz Kool

You are truly amazing... Is there anything that you don't know? Thank-you.



Glad to help!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
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.04 seconds. Powered By: Snitz Forums 2000