Solved

Database Log on Failed - Primavera P6 Version 6.2

Posted on 2014-02-25
19
6,738 Views
Last Modified: 2014-03-21
Hi,

The error I am getting in during P6 login is:
"Bad public user name or password. Database Server Error: Login failed for user 'pubuser'. Cannot open database "pmdb$primavera" requested by the login. The login failed."

Now this problem usually occurs on the first few attempts but I do always manage to get into P6 eventually. Someday I fear my luck will run out and I need to be prepared with a solution.

I have looked at the solution to this already discussed topic under the title:
"Database Log on Failed - Primavera P6 Version 6"

In the event that it is the same error, can the "Yellowbus Team" or someone with the SQL server Express 2005 knowledge please let me know how I can find the log file within SQL Express and if shrinking it is the best / only option?

Thanks,
Cain.
0
Comment
Question by:mr_floydie
  • 10
  • 9
19 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39889750
Hi,

SQL Express - the log files generally are in the log directory that is in the instance directory.

The are just text files and should be able to be opened with Notepad or similar text editor.

Shrinking:
Yes versions of SQL express have a size limit the database. From the log file, it starts with the exact version edition etc. Can you please reply with that info so we can assess if shrinking your database is needed.

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39891943
Hi David,

thanks for your reply.

Just to clarify' the actual error message I get is as follows:

Bad public user name or password. Database Server Error. Shared memory Provider. Timeout error {258}

Slight oversight on my part - apologies.

Since I have little to no experience with SQL server 2005, can you tell me exactly where I can find the "instances folder". I will need the entire path (assuming it is the same for every computer) or at least a rough idea of where it should be. (C:\ or through SQL 2005 Studio Express interface).
Then i can send you the information requested.

Thanks,
Cain.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39893332
Hi Cain,

Really not sure what to expect on a server, but here is a shot from my workstation.

SQL Server Directories
So looking at
C:\Program Files\Microsoft SQL Server

The two instances directories are MSSQL 10_50.SQL2008R2 and MSSQL11.SQL2012

A couple of layers further down in those instance directories and you'll find the log directory.

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39910787
Hi David,

Thanks for the info. I have followed the path as suggested but cannot see any folders other than the "90" and "100" within "Microsoft SQL SERVER" so have hit a dead end.

I've contacted my IT help desk in the meantime to check if it was an admin privileges issue. The IT guy could not see any other folders so now an IT person is to revert with some assistance. To be honest I don't hold out much hope there.

Any advice would be appreciated.

Thanks,
Cain.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39913468
Hi Cain

From the root of each drive in a command prompt, I suggest you do a

dir /s mssql
Results from commandline
I've filtered off a lot of other stuff, but in the above you can see the paths I showed previously.

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39928647
Hi David,

no joy. Of the 3 drives I ran this on, C: contained information relating to MSSQL.1.

screenshots attached including MSSQL related folders.

Regards,
Cain.
Screenshots-of-directory-search-.docx
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39930339
Hi,

Open up the log folder
c:\program files\mssql\primavera\mssql.1\log

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39934050
Hi,

screenshot of LOG folder attached. It was not possible to open the trc files with notepad after selecting "All file types". Error returned was "Access Denied".
I have tried to upload the 4 x .trc files but this file extension is not permitted by Experts Exchange.

Regards,
Cain.
Screenshot-of-LOG-folder-within-.docx
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39936011
Hi,

The log file is errorlog. The 1, 2, 3, ... etc are the previous copies of the file.

The .trc files are trace files. The will open with profiler or similar tool

Now if you are looking for the transaction log file for database x then that is likely to be in the data directory.

HTH
  David
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mr_floydie
ID: 39936431
Hi,

Refer to screenshot of the data folder (Screenshot 1.0 in attached word doc).

I've tried to open the files but encounter some issues:

1. Most files are in use and cannot be opened. Refer to screenshot 2.0 for error.
The only files that could be opened by notepad (although I need the profiler to read correctly) were mmdb$primavera_LOG.ldf and mssqlsystemresource.ldf
Refer to error 3.0 when opened with notepad.

Is it possible to end the following processes via task manager without causing damage?
sqlbrowser.exe
sqlserver.exe
sqlwriter.exe
ssmsee.exe


2. Even if the files are accessible I have tried to download and install SQL profiler but I'm blocked due to an admin rights issue. I'll try to get that resolved with the IT help desk here.

I know you can only help with point 1 above. I'll try to sort point 2.
Is there some way I can send the *.ldf files in question, i.e. to a personal email address?

Thanks,
Cain.
0
 

Author Comment

by:mr_floydie
ID: 39936439
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39937933
Hi Cain,

Why do you want to open the SQL Transaction Log file? What do you hope to achieve?

The log file under SQL Express is the errorlog file in the log directory.

Shrinking is almost always a bad idea. But index maintenance is generally a good idea.

You have a number of questions in your question. What are we concentrating on here - the failed logon? Or the can't open the database?

I suggest that from a SQL tool like SSMS you check and see if the database has the autoclose property set to true. This should imho be false, and may fix the issue I think you describe.

Other settings:
Check that autoshrink is fales
Check the recovery model. If the model is full or bulk insert, are you taking transaction log backups regularly? Otherwise the model should be simple.

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39939034
Hi David,

I'm still trying to follow your initial request to get you information from the log files which is supposed to lead to you to ascertain if the log files need shrinking whcih may or may not solve the original problem which is the Failed login "Bad Public Username or password / failed Primavera Login" issue.

Now you must remember I have no experience with SQL so I really did not know which log files you wanted me to open since in the LOG folder itself there are LOG_213 to LOG215.trc files and ERRORLOG files. I should have read your previous post correctly but I misinterpreted this as needing to go into the transaction logs in the DATA folder,

I have attached the contents of the error log files as per your initial request to see if you can decipher anything from these. You can see ifrom the most recent ERRORLOG file that on 11Mar14 from 09:02 I attempted to login 5 times and only the sixth was successful. Today was one of the few times successfully starting up on the first attempt.

With regard to your suggestions. as I mentioned I really have no experience with SQL.
How do I check the settings such as if the database has autoclose / autoshrink set to true/false?
Following that, how do I change them to the options you mention?
Same applies to recovery model. How do I go about doing this and where can I see if I'm taking transaction log backups?
0
 

Author Comment

by:mr_floydie
ID: 39939037
Thanks,
Cain.
ERRORLOG.txt
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39940522
Hi Cain,

>>
With regard to your suggestions. as I mentioned I really have no experience with SQL.
How do I check the settings such as if the database has autoclose / autoshrink set to true/false?
Following that, how do I change them to the options you mention?
Same applies to recovery model. How do I go about doing this and where can I see if I'm taking transaction log backups?
<<

What do you have to interact with SQL? The standard tool is SQL Server Management Studio - SSMS. I forget what comes with Express Edition.

I'm sorry but if you don't know how to get to the server or database properties, then in this instance I suggest in terms of time, get a database consultant to take a look. Think of it as paying for a lesson. If you can get into a management tool, then I can start answering your questions. Maybe one of the other experts has an idea or two.

You could always download a trial of SQL 2005, install that on a workstation, and use that SSMS to connect to your SQL Express instance.

The log you attach shows that SQL is continually starting up database mmdb$primavera. Whether this is due to continually being autoclosed or not I can't say, but its where I'd start.

From the data directory, what are the file sizes - just out of curiosity.

Regards
  David
0
 

Author Comment

by:mr_floydie
ID: 39940728
Hi David,
yes it is studio express SSME I have access to but as for the way to go about it I would need a step by step guide - menu paths etc.
You asked about the file sizes in the data directory, I'm not at work now but the 1st screenshot in the attachment I sent yesterday entitled "Transaction Log File path" shows the sizes within the Data folder.
Thanks,
Cain
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39941096
Hi Cain,

SQL 2005 Express has a 4GB limit on the data file.
http://technet.microsoft.com/en-us/library/ms345154(v=sql.90).aspx

In SQL 2008 R2 this is raised to 10GB
http://stackoverflow.com/questions/6519621/sql-server-2008-express-edition-file-size-limitation-problem

The screen-shot shows that the data folder shows that your main database is very close to that limit.

So, yes, you should shrink the data file a little, then plan an upgrade - either to SQL 2008R2 Express Edition, or an edition upgrade (at the same time)

Step-by-step for SSMS (Hoping that the SSME is close)
In SSMS, find the Object Explorer.
In object explorer look for the connect and connect to this database instance, which is
WAVWL-xxxxx\PRIMAVERA (xx to hide the digits.)
Right click on the database, and select properties. In the options tab, look for the auto close, and auto shrink properties. Set them both to false.

HTH
  David
0
 

Author Comment

by:mr_floydie
ID: 39945632
Hi David,

I managed to gain access to the Studio Express by using the "sa" username and password and not "Windows authentication".
The property for auto close was set to "true" so I changed that. Auto shrink was already set to "False".

I will follow your advice and try to get the SQL Server 2008 R2 Express Edition installed. I actually tried to get this installed previously but the installation failed.
That is a concern for the IT department here.

Thanks for all your help and advice with this and for sticking with me! I greatly appreciate it.
I will allocate the full 500 points.

Thanks again,
Cain,
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39946227
Thanks Cain,

Let me know how it goes.

Regards
  David
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now