MSSQL Server database Tranaction Log File full

[Former Member]

Dear all,

I am facing a problem the Transaction log file PXILOG1.ldf has been full.Please help me for this

Thanks

Kumar

[Former Member · Accepted solution]

Hi,

first check wheter datafile for Transaction log file PXILOG1.ldf is set for autoextend or not,

you can check that using sql server managemet console/studio (SQL2005)

run management studio/console navigate to your database PXI

right click on database -> properties -> file

check the autogrowth tab for tranasaction log file , do the relevant setting for that

regards,

kaushal

[Former Member · Accepted solution]

Dear Kaushal,

I have check this the PXILOG1 is autoextend .My problem is my HDD space is full and i want clear the Log file Please help me for this

My database file size is this

PXIDATA 1 is 43 GB

PXIDATA 2 is 38 GB

PXIDATA 3 is 36 GB

PXILOG 1 is 203 GB

So i want clear my PXILOG1 file.

Thanks

Kumar

[Former Member · Accepted solution]

Hi,

check following sap note

Note 363018 - File management with SQL Server

also read following might help you

http://support.microsoft.com/kb/873235

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html

regards,

kaushal

[Former Member]

Hi,

Run the following Transact-SQL statements in Query analyzer:

1. Clear log file (if not empty) :

BACKUP LOG DatabaseName WITH NO_LOG

2. Shrink the transaction log file.

DBCC SHRINKFILE (<FileName>,<TargetSize>) WITH NO_INFOMSGS

3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive

4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.

Regards,

wojtek