August 20, 2008 at 8:49 am
Aside from the usual system databases (which I'm afraid to touch) I have two user database files as well:
MyDBForDEV, MyDBForPROD.
(As the names imply, for now my resources disallow me to separate the two and thus they are on the same server....However they are in separate partitions)
Here's my dilemma:
The Dev file is deemed the primary database file
The Prod file isn't deemed anything per se, its just another database.
Consequently, I have no way to perform an explicit operation on the Prod file's transaction log.
If I perform a full back up on the prod file, it seems that the log is handled, but I cannot set up a transaction log shipping schedule, since the Prod is not recognized as the primary file.
I'm not sure if I need to worry about shipping...but I'm so new at this...
I'm not very clear on how to manage the transaction logs
Any suggestions
Thanks
August 20, 2008 at 9:00 am
Each database will have one primary data file (.mdf), zero or more secondary data files (.ndf) and one or more log files (.ldf).
It is not possible to have a database without a primary data file.
The primary data file contains the system tables, among other things.
What are you trying to do and what errors are you getting?
In sysdatabases, you should see both databases listed. Query sysfiles in each database to see the files in that database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2008 at 9:03 am
Oh, and log shipping is used to create a warm standby database on a secondary server. It's a high-availability technology, so that if the main server completely fails, there's a second server that can take over.
Do you need that?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2008 at 9:14 am
Hi Gail,
I haven't fully wrapped my head around the landmines that occur with the transaction logs . I get general concepts about primary /secondary files, and that each file needs at least one transaction log. But I have two files, one that is an mdf and deemed the primary , and another file that is also an mdf and is not deemed as either primary or secondary . I don't know how to manange the log for that one...
Also, the second mdf is located in a different partition on the server (out of necessity) . Its definately attached and I can perform all the data ops i need to on it...I just can't recognize how to manage the log to ensure i'm actually doing backups correctly
:w00t:
Thanks,
Grant
August 20, 2008 at 9:22 am
Each database needs a log, not each file. You can have multiple data files within one database and only one log file.
What's the output of the following query?
select DB_NAME(dbid), fileid, name, filename from master..sysaltfiles where dbid > 4
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2008 at 9:31 am
I'm away from the server right now... So I'm unable to run the query just yet...But can you clarify something. What constitutes a database vs a file. (This may be where my confusion lies...) When I installed SQL Server, I created a new database. (The dev one) and then I did a copy database to preserve the structures (for the Prod one) I may have screwed up...But I was thinking at this point that I had two distinct databases? Was I wrong?
Thanks
Grant
August 20, 2008 at 9:56 am
If you did a database copy, then you should have two databases. What do you use to manage them? Enterprise manager? You should see two entries in there for the two databases
A database is essentially a logical collection of tables, views procedures and other objects. It will have one or more data files and one or more log files. REgardless of the number of files involved, it is managed as a single entity.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2008 at 4:22 pm
Thanks for your responses.
I am using SQL Server 2005 Developers edition. Now that you've helped me establish that I wasn't going crazy... 😉
Currently, the DEV db is the Primary File, whereas the PROD db is not. If I understand correctly, they cannot both be a primary file (or can they?)
Thanks
Grant
August 21, 2008 at 1:43 am
Databases can't be primary. Files can be primary. Each database will (must) have a primary file.
What exactly are you trying to do and what errors are you getting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply