Post-Move of SQL Server 2008 R2 System Database Files

Needed to move the SQL System and user database files to a new drive with more space.  New drive has 1TB of disk space available.

Following instructions from this URL [https://www.youtube.com/watch?v=QAlgpxjJCiM], I moved the System Database files and then moved the master database successfully.

After server reboot SQL fails to launch.

I've followed instructions from below URL(s) to try and fix the issue but all end with negative results:

http://mssqlwiki.com/tag/tempdb-corrupted/

http://dba.stackexchange.com/questions/39391/moving-msdb-database

http://www.sqlservercentral.com/Forums/Topic367977-146-1.aspx

http://www.sqlservercentral.com/Forums/Topic1266327-146-1.aspx

http://sqlserver-help.com/2014/08/07/tips-and-tricks-os-error-32the-process-cannot-access-the-file-because-it-is-being-used-by-another-process/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1a3df77b-9ce5-473a-8126-2e2b3fb73c2a/fcbopen-operating-system-error-32the-process-cannot-access-the-file-because-it-is-being-used-by?forum=sqldatabaseengine

http://sqltrends.blogspot.com/2013/03/database-cannot-be-opened-due-to.html

https://www.mssqltips.com/sqlservertip/2581/sql-server-error-18456-finding-the-missing-databases/

I've attached a SQL Trace file in XML format.  Within the XML file are the T-SQL commands that I ran and the results.


01.

Logged into MSSQL SMS and connected to database instance (MSSQLSERVER)

02.

Executed the following command on database instance (MSSQLSERVER)\MSDB
alter database msdb set read_write
Command Failed

03.

Executed the following command on database instance (MSSQLSERVER)\MSDB
use master
alter database msdb set online
Command Failed

The primary errors that I'm getting are:
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Unable to open the physical file "E:\SQL_DB\Management_DATA\tempdb.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
Help-File.xml
James_HollomanAsked:
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.

Deepak ChauhanSQL Server DBACommented:
Check the State_desc column in sys.databases system view.

Select state_desc from sys.databases where database_id=4

If value if state_desc is recovery_pending

restart SQL services.
0
Scott PletcherSenior DBACommented:
Look in the SQL Server error log and see where it expects msdb to physically be.  Verify that the files are in that folder.  If not, move them to that location before starting SQL. Also verify that the SQL Server service account has full authority to that folder.  Do the same for tempdb, except that you can delete any old tempdb files when SQL is not running.
0
David ToddSenior DBACommented:
Hi,

If I'm to guess, you've physically moved master and that's okay, but SQL thinks you've moved msdb as well, but that you haven't moved yet.

HTH
  David
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

James_HollomanAuthor Commented:
Scott - Cannot access the SQL Error Logs.  SQL Server Agent will not open.  Agent showing the following error (SSMS "SQL Server Agent (Agent XPs disabled)")

David - I moved all System databases (model, msdb, tempdb). (Following instructions from this URL [https://www.youtube.com/watch?v=QAlgpxjJCiM])

Deepak - I will try that today when I go into work and will reply back.
0
Deepak ChauhanSQL Server DBACommented:
You can check the msdb database status as well.

select state_desc, * from sys.databases where id=4

Check the database file location in system catalog and make sure file exists on the location which showing in physical_name column..

select name, physical_name from sys.master_files
 where database_id=4
0
Scott PletcherSenior DBACommented:
The error logs are flat files on disk.  You can access them via Windows using NotePad, WordPad, or other editor of your choice.
0
James_HollomanAuthor Commented:
1. Ran Select state_desc from sys.databases where database_id=4
Results=recovery pending.  Restarted services/restarted server and re ran Select state_desc from sys.databases where database_id=4.
Results still = recovery pending.

2. Select name physical_name from sys.databases where database_id=4
Result=Name                 Physical_name
              MSDBData       E:\SQL_DB\Management_Data\tempdb.mdf
              MSDBLog         E:\SQL_DB\Management_Data\templog.ldf

If I'm reading this correctly its point to the wrong database and log file.

3. Today's error log is only showing successful/non-successful connections since midnight for user database.
0
Deepak ChauhanSQL Server DBACommented:
Problem is in point 2.

Seems you have updated wrong files in system tables.

Execute below command by changing the FILENAME parameter value with correct fiile name of msdb database

These files will be MSDBdata.mdf and MSDBlog.Ldf but please check the name first.

 ALTER DATABASE MSDB
 MODIFY FILE( NAME = MSDBData, FILENAME = '<Location>\os_file_name.mdf')
 
 Go
 ALTER DATABASE MSDB
 MODIFY FILE( NAME = MSDBLog, FILENAME = '<Location>\os_file_name.Ldf')
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
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 2008

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.