August 28, 2012 at 2:57 am
Hi
I was wondering if anyone has come across the following issue before. We recently moved locations and all our servers physically moved as well. Since then on one of our SQL 2005 servers the databases have stopped updating the correct mdf file and now all changes seem to being saved in the msdb mdf file.
example
test.mbf is not being updated for the test database but the msdb.mdf is increasing in size far beyond what it should be.
Any ideas would be appreciated
Thanks
Andrew
August 28, 2012 at 3:09 am
The data will go into the data files that define the database. So if the test DB uses Test.mdf and you make changes to the Test database, they go into the Test.mdf.
If you're looking at the file update dates in explorer, those are always wrong, SQL locks its files open exclusively, the modification date doesn't change until the database is closed, usually by SQL shutting down.
MSDB is used by SQL Agent, so any jobs, any backup history will go in there. Also if you are using service broker and there's anything not working, the messages queue up in MSDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2012 at 3:17 am
Thanks, that explains that. Everything was still working fine I just thought there was an issue with the datafiles. Probably need to schedule a regular reboot to install updates on the server anyway.
Thanks for the quick response.
Andrew
August 28, 2012 at 3:30 am
In general SQL Servers shuold not need restarting often. Patches and the like are the exception, but other than those there's no reason to restart a server running SQL Server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply