regarding the data file *.mdf
Hi,
I have 2 data file configure for my database and currently with below configuration
data_historyA.mdf -- data size ig 10gb and still have plenty of free space in disk F
data_historyB.ndf -- data size is 11gb and disk is running out of space drive E
my question is, will the database become inaccessible if disk E getting full? or will sql only fill up data_historyA.mdf only?
I have 2 data file configure for my database and currently with below configuration
data_historyA.mdf -- data size ig 10gb and still have plenty of free space in disk F
data_historyB.ndf -- data size is 11gb and disk is running out of space drive E
my question is, will the database become inaccessible if disk E getting full? or will sql only fill up data_historyA.mdf only?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
to get that information : simply run this statement
EXEC sp_helpdb <your database name>
it will return 2 recordsets, the second one listing the files, with in the last column the 'purpse' of the file
for example : (hope formatting survives a bit)
tempdev 1 G:\MSSQL\Data\tempdb.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
templog 2 G:\MSSQL\Logs\templog.ldf NULL 52428800 KB Unlimited 10485760 KB log only
tempdev2 3 G:\MSSQL\Data\tempdb1.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
tempdev3 4 G:\MSSQL\Data\tempdb2.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
tempdev4 5 G:\MSSQL\Data\tempdb3.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
EXEC sp_helpdb <your database name>
it will return 2 recordsets, the second one listing the files, with in the last column the 'purpse' of the file
for example : (hope formatting survives a bit)
tempdev 1 G:\MSSQL\Data\tempdb.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
templog 2 G:\MSSQL\Logs\templog.ldf NULL 52428800 KB Unlimited 10485760 KB log only
tempdev2 3 G:\MSSQL\Data\tempdb1.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
tempdev3 4 G:\MSSQL\Data\tempdb2.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
tempdev4 5 G:\MSSQL\Data\tempdb3.mdf PRIMARY 15728640 KB Unlimited 2097152 KB data only
is ndf a DATA file (like .mdf) or a LOG file (like .ldf) ?????? I seem to remember additional logfiles are called .ndf, but data files 'keep' the .mdf extention...
If it's a logfile, it contains different information than the .mdf file and it will need some extra room (either on E: or by creating a secondary log device on another drive) or you will run into the error : "Error log full, please create a backup and truncate the the transaction log" (or something like that, I try not to see that error too often =)
On the other side : do you really need a log file that keeps growing & growing (11Gb is a lot).
=> see different questions and the books online for 'RECOVERY PLAN' (you're probably on FULL and I wouldn't be surprised if 'SIMPLE' is all you really need... )