SQL log file size is extending rapidly

[Former Member]

Hello All,

We are using ECC 6.0, our database is SQL 2005 & operating system is Windows NT 4x AMD64 L.

Our database log file size is increasing rapidly, now its size is more than the all 4 data files (near about 300gb).

Last week I tried to shrink log file but it didn't worked.

Now less space is remained on disk, please help me.

Now the system is started giving dump at the time of log in, & the dump is like "START_CALL_SICK ".

I am attaching dump error text file.

Please help why is this happening.

Thanks in advance

Mahendra

Dump Error.TXT.zip

[former_member188883 · Accepted solution]

Hi Mahendra,

Try following options

1) Please take a backup of log file and then perform shrink

2) As the log file size is higher

   you can reset the initial size of either a data file or a log file using SHRINKFILE. DBCC SHRINKFILE({logical file name}, target_size); This will shrink the file to the target size and reset the initial size to the target size.

3) Add another log file

Hope this helps.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Deepak,

Thanks for your reply.

But I had done this activity already.

I have backed up log file & shrink the file but it didn't worked for me.

How can i add another log file?

Can i delete old log file after adding new log file.

Thanks

Mahendra

[Former Member · Accepted solution]

Hi,

I have backed up log file & shrink the file but it didn't worked for me

What is the result? It shrinks the log and release all the space (for all committed transactions).

How can i add another log file?
Can i delete old log file after adding new log file.

You can add another log file by following below steps. but in your case, this is not the right solution because you have good amount of log file configuration for your database (now its size is more than the all 4 data files (near about 300gb)).

Open SQL server management studio > Expand database > Right click on database > Select Files > Click on Add > Give the input parameters (Logical file name, path, initial size etc.) click on OK

If system is not allowing you to shrink the log file, it means you have active transactions in system which are continuously using your log file.

Regards,

Nick Loy

[Former Member · Accepted solution]

Hi Nicky,

The file size was as it is after shrinking, nothing has changed.

I have locked all users while shrinking log file, so it should have allowed me to shrink, but it didn't.

What will happen if i will create new log file & delete older one?

Thanks & Regards,

Mahendra

[Former Member · Accepted solution]

Hi,

If you can - Take a restart of database, then shrink log file. This process will release the entire space from your log file.

On your query - Yes, you can add new log file (first you have to restrict the primary log file growth and then add new log file, so that once it reaches the threshold it starts utilizing the newly created one). This is just a temporary solution to accommodate the additional log space for long running transactions. In this case also you have to delete the newly created log file once your operation is complete, you cannot delete the primary log file (SQL doesn't allow you to delete the primary data/log file).

Refer the below blog on how to delete the primary log file.

Sql Server Error: The primary data or log file cannot be removed from a database | Playing with data...

Regards,

Nick Loy

[former_member188883 · Accepted solution]

Hi Mahendra,

What will happen if i will create new log file & delete older one?

Do not delete the old one as it will lead to data loss.

This is a temporary solution to avoid system being dumping. Ensure you restrict the size of log file.

Once the system is stable you may work on shrinking activities as suggested.

Hope this helps

Regards,

Deepak Kori

[Former Member · Accepted solution]

Dear Nicky,

I have restarted the database also.

Still it didn't work for me.

I have read the link given by you.

Can you tell me any permanent solution.

Thanks & Regards,

Mahendra

[Former Member · Accepted solution]

Thanks Deepak,

But I don't have space on my disk & shrinking is not working in my case.

So is there any other solution?

Regards,

Mahendra

[Former Member · Accepted solution]

you need to take a backup of you log file using database tool then you can shrink.

[Former Member · Accepted solution]

Hi Sunil,

Thanks.

I had taken backup through SQL management studio.

How many time should i take backup of log files?

Because through DB 13 it is giving error.

Regards,

Mahendra

[Former Member · Accepted solution]

one time log file backup is enough to shrink the file.

You sure the logs are not getting filled up again after you taken the backup? Is there any large activity going on the database?

[Former Member · Accepted solution]

Hi Sunil,

After taking backup of transaction logs can i directly shrink log file?

Or shall I stop MMC & lock the all sap users first & then perform this activity?

Regards,

Mahendra

[Former Member · Accepted solution]

you can shrink the log file online.

FYI - If the database is in recovery model ‘FULL’, then change the recovery model to ‘SIMPLE’. Perform shrink to the log file, then change back to recovery model ‘FULL’ (make sure you have a successful backup before you perform this activity)

[Former Member · Accepted solution]

Hi,

There must be long running transaction (Rollback session) which is causing to this.

If you want to keep the activity ON until the completion, just add another log file in different drive where you have enough space (Restrict the growth of primary log file before this).

Once the long running transaction is complete then system allow you to shrink the log file & You can delete the newly created log file also.

Backup doesn't enable the shrink operation, if you have active transaction running.

If you stop the SAP and database, it allows you to shrink the log file (Post restart). But ensure that no active transaction before you shrink the log file.

If rollback session is active, then do not shutdown your database as it will get into recovery mode after restart (Recovery will happen automatically but it takes its own time).

Regards,

Nick Loy

[Former Member · Accepted solution]

Hi Sunil,

I have done as per your reply & did this activity on development server & it works for me.

Thanks a lot.

I have one query that on my production server log file size is 274 GB.

So can i shrink it up to 10GB?

And one more that full database backup through SQL management studio, can I rely on that & perform this activity on production?

Regards,

Mahendra

[Former Member · Accepted solution]

Hm! the log file size depends on case to case. you need to find out how much is used out of 274 GB. You can check in DB02 under files.

I suggest, you must schedule frequent log backup (may be 2 hrs frequency) rather than daily once.

[Sriram2009 · Accepted solution]

Hi Mahendra

Once the Daily backup gets completed then start the MS SQL truncate Log backup. So that you disk space will back to minimum size. This is just a recommend

In our case we are using the Symantec backup exec 2010 once our backup get completed then the process will start for truncate log backup to tape or disk.

Regards

Sriram