We are having SAP ECC 6.0 with EHP4 & Sql server 2005 database in windows platform.The problem is my windows hard disk (only .mdf & .ndf) are full.Total size allocated 200 GB now the free space is only 8GB.My comapny provided another HDD with space 300 GB.
Please give me the suggestion how to add data file in another new hard disk .
Please provide me your valubale inputs
I have been added new data file(.ndf) to the new drive.
SQL server management studio
Select the Data base and right clik -->properties --->files -->add new data file (.ndf)
Right now i am having 4 data files nad one log fille (including new data .ndf file) in that 1 .mdf and 3.ndf files .
Please suggest above procedure is correct or not? if not suggest any other possible ways & also provide me any complications in future for the above procedure .
Is this your production database?
Did you add the two new ndf files in the new drive? Where is your log file located and what is the file size of it?
Please paste the information from dbacockpit (tcode)->Space->Overview->(files tab)
if you created the two new files (ndf) in the new drive then the data is filled based on the available free space in the files. so distributing the data file evenly is the key thing here. what i suggest in this scenario is move the old ndf to the new drive and either delete the 3rd and 4th ndf file (distribute the data to other data files before deleting ssms->shrink data->files and select option migrate data to other files) or move the 3rd ndf file to the old drive.
Thnaks for your response .
Moving database files(.ndf)means manually coping data files one disk to another disk (new disk).
Please correct me if anything wrong in my above statement.
Recently i follwowd the below procedure in one of my server and it working fine now .Please sugegst this procedure is correct or if any complication in future
1)added new data file(.ndf) in new drive
SQL server management studio
select the database and right clcik...............>Properties ...>files.....>add new data file (.ndf)
Right Now i am having 4 data files and one log file incliding new data file(.ndf).In that one new .mdf and 3 .ndf files .
@)R.K: log files is located in another disk having lot of space 70 GB if the disk is full i am doing the shriking unsused space option.
Please check the attachment DBACOCKPIT and suggest if any thing wrong in my above statements.
The following steps would address the drive space issue and free up some space in the old drive
move data 2.ndf to new drive
The ideal would be:
performing above steps
delete data4.ndf after migrating the data to other data files
delete data3.ndf after migration the data to other data files by
this time you should see data1 and data 2 equal size in two different drives.
create data 3.ndf in old drive,
create data 4.ndf in new drive
with the above you have alteast the IO is balanced between two drives but the file sizes will not be same ever. Also your log file is big. i am assuming it is a production system. so atleast have an hourly log backup, daily differential backup and atleast twice a week full backup. you will not have the log file growing issue and you don't have to truncate it. increase the file size in advance so that the files don't have to grow on demand.
to make all the 4 files in equal size, you have to perform hetrogeneous system copy. But the above should address your immediate needs.
Thanks for your response.
I need your suggestion reagrding already i have created new .ndf file in new drive .
Can i continue with this .ndf file are i need to perform the above suggested activites.
Kindly suggest above (new .ndf filr created in new drive) it will arise any in furture problems.
Adding new ndf never creates any issue until unless you have managed space and good resources (for better performance).
But how you manage your space on old disk?
It will become a challenge anyway, so in your case do not add new datafile just split the existing datafiles into different drives for better managed space.
While detaching and attaching, your appliation should be down.
And there are no prerequisites for a simple detach and attach but keep a backup of all your datafiles before you move from one location to other.
I suggest you to copy (instead of move) and attach the database with new location then remove the source file once everything is fine.
Ex: Let's assume A, B and C are the datafiles location in Disk X,
First detach the database, then copy C from X to Y (new Drive)
Then attach database with files A, B (of X) and C (of Y)
After initial checks remove the C from disc X
Shinking a SQL Server database is not recommended due to both performance and internal/external fragmentation issues. Here's an analogy of how the database shrink process works in the background.
After detach and attach databse R3trans-d is ending retiurn code 0012.
Error could not load library 'dbmsslib.dll'
2EETW169 no conect possible :'connect failed with DBLI_RC_LOAD_LIB_FAILED"
But i am login the system (its a portal system) and working fine .
Kindly suggest and provide your suggestions