Solved

Help reviewing possible master database corruption

Posted on 2014-11-25
19
179 Views
Last Modified: 2014-12-02
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!
0
Comment
Question by:PetEdge
  • 7
  • 7
  • 5
19 Comments
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 40466512
Can you connect to the database and perform any kind of query (SELECT, INSERT, UPDATE & DELETE)?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 375 total points
ID: 40468092
>>
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 375 total points
ID: 40468097
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
 

Author Comment

by:PetEdge
ID: 40473677
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40473806
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 375 total points
ID: 40473817
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
 

Author Comment

by:PetEdge
ID: 40473984
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40473999
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
 

Author Comment

by:PetEdge
ID: 40474204
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40475356
Why do you want to modify the file name?
0
 

Author Comment

by:PetEdge
ID: 40476132
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40476145
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40476303
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
 

Author Comment

by:PetEdge
ID: 40476629
Scott,

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

Karen
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40476671
Try also:
EXEC PEQ.sys.sp_helpfile PEQDATA2

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

Author Comment

by:PetEdge
ID: 40476690
Here are the results:

4
Victor - Those already are the database names :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40476770
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
 

Author Comment

by:PetEdge
ID: 40476871
Changing to another logical name entirely, and then back to my desired one, worked!  Thanks so much!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40476900
No problem ... I think SQL gets "confused" sometimes about logical names, I think I ran into that once too.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

18 Experts available now in Live!

Get 1:1 Help Now