Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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!
0
PetEdge
Asked:
PetEdge
  • 7
  • 7
  • 5
4 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now