Information about Transaction Log file

[baby_fabrice]

Hi ,

1)We have finish the upgrade of our DEV system and the transaction log file is very big (bigger that the data file ^^....) and i don't kno what to do with this file.

We have to do a backup strategie this week and i know that in the SQL server management studio we can done maintenance plan ( backup...)

But i have see nothing in the SQL server managerment studio for deleting transaction log file...

2)In the DB02 dababase Check there is a warning because the transaction Log file are on the same device than tempdb, we have other disk on the serveur and i don't know how to manage and and change destination for this LOG file....

Thx for help

Regdards

Edited by: BABY Fabrice on Apr 18, 2010 10:13 PM

[baby_fabrice · Accepted solution]

Thx for your help.

What is the SQL command for list of the file and the location (disk) plze i'm not sure i have enter the fisrt time the good folder...

[clas_hortien · Accepted solution]

Hi,

i'm not sure if i understand the question correctly. If you want to see the files of the database you can use

use DV1

exec sp_helpfiles

go

Or you use

use master

select * from master..sysaltfiles where name = 'DV1'

go

Best regards

Clas

[baby_fabrice]

In fact all is OK now.

i have put a bad name for the phisycal name and that's why the DB don't start....

But really thx agin.

[baby_fabrice]

Thx for your help.

I have shrink the log file with the correct command.

Bu i have an other problem :

I have see in MS documentation that for move a file i have to do this command :

ALTER DATABASE DV1 MODIFY FILE ( NAME = DV1LOG1 , FILENAME = 'L:\DV1LOG1 )

But after i have make restart of database it's impossible to connect on SAP instance.

HELP!!!

[clas_hortien]

Hi,

have you copied the log file to this new location ?

This is the procedure to fix this:

- ensure that the log file is at this location with the given name

- run this script in a new query:

use master

exec sp_resetstatus 'DV1'

go

- shutdown and restart the SQL Server

Now the database should be online again and the SAP system should be able to connect.

Best regards

Clas

[clas_hortien]

Hello,

you must not delete transaction files, they are necessary for the logical consistency of your database.

See the note 363018 how to shrink the file.

Best regards

Clas Hortien