Hello,
I'm hoping someone here can shed some light on this situation that has
me a little bewildered. My company has inherited a SQL 2000 server with a
number of databases on it. The drive these databases are on is slowly
running out of space so I decided to inspect the drive a little more closely
to see if there was any space that could be freed. What I have discovered
has
me a little confused. See, the SQL server has been installed on the D:\
with all the databases pointing to "D:\Databases". However, there also
seems to be a copy of the database file in "D:\Program Files\Microsoft SQL
Server\MSSQL\Data". I figured that perhaps the old DBA had just copied the
"D:\Databases" from "D:\Program Files\Microsoft SQL Server\MSSQL\Data" in a
schema change or something but when I tried to delete one of the files I
found that both sets of files in both directories were in use.

Can someone explain how this is possible and perhaps the reasoning
behind
it. It seems like a waste of space to me.

Thanks,
Scott

You should see which files are in use by using

SELECT * FROM master.dbo.sysaltfiles;

You should also check to see if you have more than one instance of SQL
Server running.

Just blindly clicking on a file and deleting it may not be the smartest
thing to do, whether it is currently in use or not.



On 8/11/09 1:09 PM, in article gBhgm.130268$qx1.24625 (AT) newsfe04 (DOT) iad, "Scott"
<Scott (AT) Verifpoint (DOT) com> wrote:

Yes, I have looked at this to see which file is "in use". However, both
sets of files seem to be current and in use despite what SQL says. It is
almost as if the database files are being mirrored between the two
directories. They match in every way...size, creation date, modifie date
and even growth rate. Even if I create a new database and specifiy the file
to go in "D:\Databases" it ends up in both. I'm confused about how this
even got set up this way and if there was a special thought behind it.

-Scott

"Aaron Bertrand [SQL Server MVP]" <ten.xoc (AT) dnartreb (DOT) noraa> wrote

It sounds like someone created a mount point that points to the original
location. If that is the case it is not duplicate data or using extra space.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Scott" <Scott (AT) Verifpoint (DOT) com> wrote

I thought that might be the case aswell but I do not see any mountpoints in
the system. There is just one hard disk with two partitions, C and D.

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Scott (Scott (AT) Verifpoint (DOT) com) writes:
I think Andy used the incorrect teminology. They are not mountpoints, they
are just muliple links to the same directory. And as Andy says, save the
extra node for the link, there is no duplicate space wasted.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

How can I check for these? I do not think this is the case because the
amount of space used on the drive doesn't add up if it were but I'd like to
check just to make sure.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

What happens if you create a text file in that location using Notepad. Does
it show up in both places?
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Scott" <Scott (AT) Verifpoint (DOT) com> wrote

Why yes, yes it does. So I guess this isn't something that has been set up
in SQL but something set up in the OS. I took a second look through the
installe dprograms and I didn't see anything that would mirror the two
directories. Any thoughts on what could be doing this?


"Rick Byham, MSFT" <rickbyh (AT) microsoft (DOT) com> wrote

Yes that would have been my next suggestion as well and should answer that
question. But the bottom line is that sql server does not have the
capability to have duplicate data files so there has to be a more simple
explanation.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Rick Byham, MSFT" <rickbyh (AT) microsoft (DOT) com> wrote