MS SQL On sap

[Former Member]

Can i relocate .ldf file as it is increasing regularly and the partion has no space

Rupesh

[Former Member]

my situation is i have four drives C, D, E, and F f has PRDADTA1, PRDDATA2, PRDDATA3 AND PRDLOG1

and F drive is about full can i remove PRDLOG1 from F and relocate it to C beacuse C has alot of space please help me in this regard

[Former Member]

Hi,

what do you mean by remove ? ( cut paste is ok or copy paste and remove)

you can put PRDLOG1 on any of drive which has enough free space.

regards,

kaushal

[Former Member]

exactly i mean to say cut and paste only. so i can do the same with other files also

[Former Member]

Hi,

why are you confuse?

you can do with any of data file just follow the method which i suggested earlier.

regards,

kaushal

[Former Member]

i am canfused because i am playing with files first time and i dont want to destroy the production server.

any way today i am going to do it.

thanku very much for answring.

Rupesh

[Former Member]

Hope I'm not too late. Here are the exact steps.

1. Run the following SQL command, you can do this while SAP is up. It doesn't take effect until you cycle SQL engine:

alter database <yourSAPDBName> MODIFY FILE (NAME = PRDLOG1, FILENAME = 'C:\path....\PRDLOG1.ldf')

2. Stop SAP and shut down SQL.

3. Copy the file PRDLOG1 from F:\currentpath to C:\the path you used in step 1 above.

4. Start SQL and SAP.

Done.

Ongoing:

1. Make sure you setup LOG DB backup if you are running your DB in full mode.

2. Dump the files to C: since you have space there.

3. Depending on the space on C:, you may adjust the retention period for the tlog files. This will keep the file PRDLOG1.ldf from growing out of control

[Former Member]

Thanks for reply i did it

[Former Member]

thanku very much i have done it

[Former Member]

that means if the current location is 'l:\PRDLOG1\PRDLOG1.ldf' i can make it 'C:\PRDLOG1\PRDLOG1.ldf' and sap will work.

i am trying it now the exact process

[Former Member]

HI,

yes,

check my previous answers in your case filename may be different.

regards,

kaushal

[Former Member]

Hi,

yes

check Note 363018 - File management for SQL Server

regards,

kaushal

[Former Member]

i have seen that note but is there some more process for that

[Former Member]

Hi,

what more process?

following is example which is given in note

first stop the sap system.

a) Change to the master database and then disconnect the required database from the SQL Server.

For example:

use master

go

execute sp_detach_db 'PRD', 'true'

The database is now no longer visible in the Enterprise Manager and no longer available for the SQL Server.

b) Now use the NT Explorer to move the database files to your new storage location.

c) After you move the files, attach the database to the SQL Server using the sp_attach_db command. You must specify the full path for all the database files here.

For example:

use master

execute sp_attach_db 'PRD',

'd:\PRDDATA1\PRDDATA1.mdf',

'e:\PRDDATA2\PRDDATA2.ndf',

'f:\PRDDATA3\PRDDATA3.ndf',

'l:\PRDLOG\PRDLOG.ldf'

The database can now be seen in the Enterprise Manager again and is available through the SQL Server.

regards,

kaushal