December 10, 2008 at 11:34 pm
Hi,
I have taken over this 2005 server from a previous DBA and have noticed that for a certain DB, there are 2 MDF and 4 LDF files on the hard drive for this one DB.
According to the DB properties (right click - properties - files), only 1 MDF and 1 LDF is in use.
Location: D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data.
The other files are all in different locations and/or drives.
How can I determine if any of the other files are being referenced at all.
I don't just want to delete these files - just to discover that they were actually used/needed.
December 10, 2008 at 11:49 pm
Execute following command e.g. pubs database
sp_helpdb pubs
It will show all the details of the file used by the specific database.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 11, 2008 at 12:09 am
I have used that SP and it has confirmed the 2 locations:
D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName.mdf
D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DBName_Log.ldf
This will seem like a stupid question, but is it possible that those other MDF and LDF files could be used/referenced by anything else - besides the actual DB?
December 11, 2008 at 12:26 am
[font="Verdana"]For each database, only 1MDF will be in use. If additional datafiles are needed, they will have the extension NDF. You can have multiple log files(LDF). But, from your scenario it looks like the ones in the D:\ drive are the only ones being used. To be on the safe side, just copy all the other mdf and ldf files for this DB to one location, take a tape backup of that location and put them in a zip file.
Regards,[/font]
December 11, 2008 at 12:27 am
Casper (12/11/2008)
This will seem like a stupid question, but is it possible that those other MDF and LDF files could be used/referenced by anything else - besides the actual DB?
If sp_helpDB doesn't refer to those files, they are not part of that database.
Query sys.masterfiles to see if those files are referenced by any DB on that server. If not, they're probably from databases that were detached from the 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
December 11, 2008 at 12:32 am
You need to check whethere same file is used by other instance or databases.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 11, 2008 at 12:36 am
free_mascot: how would I check that?
December 11, 2008 at 12:40 am
I have used the following statement:
exec sp_msforeachdb 'select ''?'', filename from ?..sysfiles'
it lists all my DBs and the individual mdf and ldf file per DB, and the extra mdf and ldf files for that other DB are not listed at all - only the 2 on the D drive.
So is it safe to say that those files are not used?
December 11, 2008 at 12:43 am
As mention by GilaMonster exe following T-sql
select * from sys.master_files
search the specific file.
Execute the above query on all the available instances.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 11, 2008 at 12:49 am
Thanks for all your replies - appreciate it!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply