Help reviewing possible master database corruption

As part of a QA environment restore, database and system files were moved to newly created drives for optimal performance and the recovery mode was changed to simple.

I was able to backup the database once, successfully, then on  the second time, received this error:

Description: Executing the query "BACKUP DATABASE [PEQ] TO  DISK = N'\\sapdeploy101\..." failed with the following error: "The transaction log for database 'PEQ' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

       Note:  Log_Reuse_Wait_DESC says 'NOTHING'

When trying to shrink the log file manually - Tasks, Shrink Files, Log, I get a dialog box:

Input string was not in a correct format (mscorlib)

Then I see the Shrink File Screen - PEQLOG1, but the physical location is messed up:

Database:  PEQ
File type:  Log
File Group:  N/A
File name:  PEQLOG1
Location:  M:\PEQDATA1\PEQDATA1.MDF


Here are locations from sys.sysdatabases - note LOG is in it's own folder.

PEQDATA1      M:\PEQDATA1\PEQDATA1.MDF
PEQLOG1        L:\PEQLOG1\PEQLOG1.LDF
PEQDATA2      M:\PEQDATA2\PEQDATA2.NDF
PEQDATA3      M:\PEQDATA3\PEQDATA3.NDF
PEQDATA4      M:\PEQDATA4\PEQDATA4.NDF


Is there a registry location I can check for database file locations?


Another example:  I cannot execute an ALTER DATABASE statement to alter the Log file (or any other database file):

alter database PEQ modify file (name='PEQLOG1', filegrowth=500MB)

Error:

Msg 5041, Level 16, State 1, Line 2
MODIFY FILE failed. File 'PEQLOG1' does not exist.

If I try to do this by right clicking the database name, and choosing Files, and making a change to either the LOG file or other files, I receive an error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Alter failed for Database 'PEQ'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4321.0+((KJ_SP2_QFE-GDRCU).140709-1550+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

MODIFY FILE failed. File 'PEQLOG1' does not exist. (Microsoft SQL Server, Error: 5041)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4321&EvtSrc=MSSQLServer&EvtID=5041&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


The file most definitely does exist in L:\PEQLOG1

Here is the result of a DIR:

L:\>dir/w
 Volume in drive L is LOG
 Volume Serial Number is D879-37F1

 Directory of L:\

[LOG]     [PEQLOG1]
               0 File(s)              0 bytes
               2 Dir(s)  101,656,219,648 bytes free

L:\>cd peqlog1

L:\PEQLOG1>dir/w
 Volume in drive L is LOG
 Volume Serial Number is D879-37F1

 Directory of L:\PEQLOG1

[.]           [..]          PEQLOG1.LDF
               1 File(s)  5,477,761,024 bytes
               2 Dir(s)  101,656,219,648 bytes free





The file locations all appear to be correctly specified.

name                  current_file_location
master                  J:\SYSTEMDB\master.mdf
mastlog                  J:\SYSTEMDB\mastlog.ldf
tempdev                 K:\TEMPDB\tempdb.mdf
templog                      K:\TEMPDBlog\templog.ldf
modeldev      J:\SYSTEMDB\model.mdf
modellog                       J:\SYSTEMDB\modellog.ldf
MSDBData      J:\SYSTEMDB\MSDBData.mdf
MSDBLog                  J:\SYSTEMDB\MSDBLog.ldf
PEQDATA1      M:\PEQDATA1\PEQDATA1.MDF
PEQLOG1        L:\PEQLOG1\PEQLOG1.LDF
PEQDATA2      M:\PEQDATA2\PEQDATA2.NDF
PEQDATA3      M:\PEQDATA3\PEQDATA3.NDF
PEQDATA4      M:\PEQDATA4\PEQDATA4.NDF
dbaDB                        M:\DATA\dbaDB.mdf
dbaDB_log      L:\LOG\dbaDB_log.ldf


I'm stumped!
PetEdgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you connect to the database and perform any kind of query (SELECT, INSERT, UPDATE & DELETE)?
0
Scott PletcherSenior DBACommented:
>>
Here are locations from sys.sysdatabases - note LOG is in it's own folder.
...
 PEQLOG1        L:\PEQLOG1\PEQLOG1.LDF
...
<<

That's an ancient, and deprecated, view.

Just in case, run either of these and check the results:

EXEC PEQ.sys.sp_helpfile
SELECT * FROM PEQ.sys.database_files

Let's verify PEQLOG1 is really there.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Since the L: drive is not full, the only logical reason for the log to say it is "full" is if:

1) autogrow is not allowed
and/or
2) there is a size limit specified and the log has reached it.

Either of the results above will help determine that, although "helpfile" is a bit easier for that :).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PetEdgeAuthor Commented:
My apologies for the delay in responding to this.

Vitor  - Yes, I can run a select from SSMS to the database, and connect to it through the front end.

Scott -  I've attached the results of the two queries as a screenshot, as well as the GUI again.  Note that File ID 1 has a PEQ name but the others all have PED.  This could have been due to an error on my part, through the restore, as I also built a PED environment (different server).  

It's very odd that the GUI results for these files are different.    Do you have a suggestion of what to update?Query results
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only SELECT's? What about the write statements (INSERT, UPDATE & DELETE)?

Did you realize that the size of your log file isn't the same as showed by the command prompt?
Directory of L:\PEQLOG1

 [.]           [..]          PEQLOG1.LDF
                1 File(s)  5,477,761,024 bytes


And you may want to rethink the data growth strategy since you have set 10%. By now you already have data files with 90GB meaning that next time they'll grow 9GB.
0
Scott PletcherSenior DBACommented:
Since pre-formatting half a gig at one time might put a strain on the disk subsystem, let's reduce the growth to 250MB rather than 500MB:

alter database PEQ modify file (name='PEDLOG1', filegrowth=250MB)
0
PetEdgeAuthor Commented:
Thank you both again.  

Vitor - I'm not sure why the log file has changed.  

The database is functioning fine from the front end (I just confirmed with a transaction which updates records).

Scott, I was able to run your Alter database command, to change growth to 250 MB,  and am now backing up the database.  (Note too  that I found my scheduled full backup job had run successfully each night since I created this post).
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, your initial issue of transaction log for database 'PEQ' is full was resolved?
Did you check for the existence of transaction log backups?
0
PetEdgeAuthor Commented:
I am able to back up the database, which was my biggest concern; however I still have a discrepancy between file names.  I did not check for t log backups; database is in simple mode.  


I still have something odd going on.

Ran the below, which worked:
ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDLOG1.LDF', newNAME = 'PEQLOG1')
ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDDATA2', NEWNAME = 'PEQDATA2')

Here are current results of :
EXEC PEQ.sys.sp_helpfile
 SELECT * FROM PEQ.sys.database_files

Logical file names off
Note how when I try to rename the other files, I get an error that the logical file names are in use.

ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDDATA2', NEWNAME = 'PEQDATA2')

Msg 1828, Level 16, State 3, Line 2
The logical file name "PEQDATA2" is already in use. Choose a different name.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you want to modify the file name?
0
PetEdgeAuthor Commented:
It's cleaner and prevents confusion.  I have three versions of this database (on three different servers).  Production, QA and Dev.    During the refresh, I restored from backup, renaming the logical and physical file names to match the environment.  PEP, PEQ, PED.  

Also - I think I misstated one point above, in my last post.

I was able to run this:
ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDLOG1.LDF', newNAME = 'PEQLOG1')

But not this:
 ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDDATA2', NEWNAME = 'PEQDATA2')
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you want to normalize the names you should start with renaming the database PED, PEQ and PEP.
For the future you can do that during the restore operation (check WITH MOVE option).
0
Scott PletcherSenior DBACommented:
Again, verify the logical file names in use in that db:

EXEC PEQ.sys.sp_helpfile
--You should see that 'PEQDATA2' is already in use on a different file.
--You will need to rename that file first, then rename 'PEDDATA2'.
ALTER DATABASE PEQ MODIFY FILE (NAME ='PEDDATA2', NEWNAME = 'PEQDATA2')
0
PetEdgeAuthor Commented:
Scott,

That's the issue!  It is not in use.  PEQDATA
Thanks,

Karen
0
Scott PletcherSenior DBACommented:
Try also:
EXEC PEQ.sys.sp_helpfile PEQDATA2

ALTER DATABASE PEQ MODIFY FILE ( NAME = PEDDATA2, NEWNAME = PEQDATA2 )
0
PetEdgeAuthor Commented:
Here are the results:

4
Victor - Those already are the database names :)
0
Scott PletcherSenior DBACommented:
Bizarre, since that's contradictory.  I guess you'll just have to pick a different logical name.  Maybe you can cycle thru logical names and get back to PEQDATA2.
0
PetEdgeAuthor Commented:
Changing to another logical name entirely, and then back to my desired one, worked!  Thanks so much!
0
Scott PletcherSenior DBACommented:
No problem ... I think SQL gets "confused" sometimes about logical names, I think I ran into that once too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.