MS SQL server 2005 database hard disk (.mdf &.ndf)space is full

[Former Member]

HI All,

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

Thanks

[Former Member]

HI All,

I have been added new data file(.ndf) to the new drive.

Procedure:

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 .

Thanks

[former_member193399]

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.

[Former Member]

1) Take a backup

2) Detach the database

3) Move 1 or 2 data files from old drive to new drive

4) Attach database

So that you can have managed db files and space on both the disks.

Regards,

Nick Loy

[Former Member]

Hi NIck,

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.

Thanks

[Former Member]

Hi RK,

Please find DBACOCKPIT attachment

DBAcockpit.txt.zip

[Former Member]

Yes,

You can copy the data file from one drive to other or backup and restore method (If file is too large).

Regards,

Nick Loy

[former_member193399]

you said u created 2 ndf files in the new drive. but i see only one new file in the new drive. looks like you had 3 files in the old drive and you created only one file in the new drive. is that correct?

[Former Member]

Hi RT,

Thanks for your response.

Yes exactly 3 old data files 1.mdf and 2 .ndf ,created one new .ndf file new drive.

Please sugegst & provide your valuble inputs above procedure is correct or not ,infutre any problems will arise .

Please suggest

Thanks

[former_member193399]

The following steps would address the drive space issue and free up some space in the old drive

detach database

move data 2.ndf to new drive

attach database

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.

[Former Member]

Hi RT,

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.

Thanks

[Former Member]

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.

Regards,

Nick Loy

[Former Member]

Hi Nick,

Thanks for your response.

I am ahaving some doubts deatching the databse please clarify the below points.

1)Detaching database while running the SAP system or we need to down the system

2)is their any other prerequsites

Please suggest your valuble inputs

Thanks

[Former Member]

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

Regards,

Nick Loy

[Former Member]

Hello All,

One more suggestion required from please suggest.

I f i shrink data file in mssql server 2005 database is their any impact on data or any other cricticalities happend in future .

Kindly suggest

Thanks

[Former Member]

It doesn't impact the data or any object, however it is not a recommended one.

Google the word "DBB shrink" to find the links.

DBCC shrink operation is a time consuming job, takes a long time.

Regards,

Nick Loy

[Former Member]

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.

[Former Member]

Hi All,

After detach and attach databse R3trans-d is ending retiurn code 0012.

Translog error:

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

Advance thanks

Thnaks

[Former Member]

Hello All,

Please provide your valuble inputs.

Its very urgent

Thanks

[former_member184473]

Hello,

The R3trans is a test valid for the ABAP stack, not for Java.

Are the information in the configtool correctly stored?

Regards,

Eduardo

[Former Member]

Hi Eduardo,

Thanks for the information.

Yes configtool stored correctly.

Anyways thanks for your help

Thanks

[former_member184473]

Hello,

Are you still facing any issue?

Regards,

Eduardo