cancel
Showing results for 
Search instead for 
Did you mean: 

Cannot shrink log file 2 (PRDLOG1) because all logical log files are in use

Former Member
0 Kudos

Friends,

I am having 2 drives for MSSQL DB.

File system

G: SAPDATA 1,2 ,3 - size 1 TB (RAID 5)

H: PRDLOG1 - size 90 gb. (RAID 1)

When my PRD server is running all 90 gb of PRDLOG1 is full in H :drive & throws a n error in sap SNAP_NO_NEW_ENtry.

I understood i has no space to write log file & i created a new logfile in G: drive with name PRDLOG2 7 contuied with all the PRd activities with oyt any interruption.

after creating my file system as follows.

G: SAPDATA 1,2 ,3 & PRDLOG2 - size 1 TB (RAID 5)

H: PRDLOG1 - size 90 gb (RAID1)

as PRDLOG2 is on RAID5 performance is badly hitted with bottle necks.

******************************************************************************************************************************************

Now i have taken backup of full DB. & try shrink log PRDLOG1 & planned to move PRDLOG2 from G drive to H drive.

So my system will start working like before .

When i am trying t shrink file PRDLOG1using query dbcc shrinkfile(PRDLOG1, 50) it is running successful & i am getting following message.

Cannot shrink log file 2 (PRDLOG1) because all logical log files are in use.

1 rows affected.

if dbcc trows an error contact your systems administrator

how should i overcome this issue. as im basicaly an oracle guy i am finding hard to understand this messages.

Request to provide some support from any MS-SQL guys.

rgds,

rahul..

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Actually the below link helped in shrinking file & i have followed sap note....

625546 Size of transaction log file is too big

http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-fla...

Former Member
0 Kudos

Hi Rahul,

I had same issue and I stopped SAP and MSSQL completely. Restarted them and tried to shrink and it worked for me. I am not sure if you can bounce your PROD system, if you can bounce I would recommend to give it a try

Thanks,

Praveen.

Former Member
0 Kudos

I have no free space left where the PRDLOG1 is ..as u told -- I stopped sap & took restart of box & tried to shrink but no luck...

Former Member
0 Kudos

Hi Rahul,

I beleive you need to have free space on disk to perform shrink, can you move the LOG file to other location with more free space and then try shrinking it.

It is possible to shrink the transaction log file while the SQL Server database is online, however if transactions are taking place while the operation is running you may experience this error. Try running it in off hours the DBCC SHRINKFILE command in off hours, or shut down access to the database from other apps (SAP).

Make sure that there are no running transactions, by running the command on the database in question: DBCC OPENTRAN

Thanks,

Praveen

Former Member
0 Kudos

Hi Rahul,

The shrink command may not work dirctely on such a big file.

execute below command then shrink command.

BACKUP LOG <logfile name> WITH TRUNCATE_ONLY

Regards

Ashok Dalai

Former Member
0 Kudos

Hi ashok,

what happens if execute the following command. BACKUP LOG <logfile name> WITH TRUNCATE_ONLY

rgds

rahul

Former Member
0 Kudos

Please try this...

DBCC SHRINKFILE (<transaction_log filename>, <blocksize based on the size of the transaction log file>, TRUNCATEONLY)