troubleshooting Question

Help reviewing possible master database corruption

Avatar of PetEdge
PetEdgeFlag for Afghanistan asked on
Microsoft SQL Server
19 Comments4 Solutions359 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros