Transaction log file increases to 10 Gb

[Former Member]

Hi All,

In Production Server following partitions,

D : 10 Gb for Transaction log

E : 300 Gb for Database file

I executed the SGEN tcode in background due to this log file PRDLOG1.ldf size occupied all 😧 10 Gb due to this my Database was down. So I moved it to the E: drive. After that I am able to start database and SAP.

Now I want to move back this Transaction log file PRDLOG1.ldf file back to D : drive. But due to size restriction I unable to do this.

Please tell me how to reduce Transaction log file size. I already tried shrink file procedure. But no use.

[Former Member]

Else just go to shrinkdb option from the right click. select files, select the log file, enter the size and let it rip.

[Former Member]

Hello Kevin ,

After executing command BACKUP LOG<SID> WITH TRUNCATE ONLY

It will only allow you shrinkbd from the right

Regards,

Santosh

[Former Member]

Sorry, I'm not sure what you mean "It will only allow you shrinkbd from the right"

[Former Member]

Hello Kevin ,

After executing command BACKUP LOG<SID> WITH TRUNCATE ONLY

It will only allow you shrinkbd from the right .

The Command BACKUP LOG<SID> WITH TRUNCATE ONLY in mandatory to shrinkdb .

Regards,

Santosh

[Former Member]

I dont understand your english.

He doesnt want or need to 'shrink' the DB nor should you. He wants to shrink a FILE. The LOG file.

The instructions are given in the thread above on how to 'shrink' a file.

TRUNCATE removes all the data from the log file - this is not a good idea - this is what a TL backup is for. If you truncate the log you CAN NOT recover the system past the last TL backup.

[Former Member]

Hello Kevin ,

Check your posting you only mention :

" Else just go to shrinkdb option from the right click. select files, select the log file, enter the size and let it rip. "

If you want to shrink file using this method you have to execute the command

BACKUP LOG<SID> WITH TRUNCATE ONLY

Then only you can shrink file using :

" Else just go to shrinkdb option from the right click. select files, select the log file, enter the size and let it rip. "

Regards ,

Santosh

[Former Member]

I would also look into SQL recovery models - <a href="http://msdn2.microsoft.com/en-us/library/ms175477.aspx">SQL Recovery Models</a>

We change the recovery model from Full to Simple when doing things like SGEN or a Client Copy. It will not log as much info then and will not cause the LDF to grow.

But if you understand the recovery models, you will understand how to control the LDF size.

[Former Member]

Hello Drew

Yes ! That is true .

Regards ,

Santosh

[Former Member]

Hi

Use simple recovery model instead of full

Regards,

Clifford

[Former Member]

Hi Daniel,

In order to reduce and restrict the Transaction Log growth run the following MS-SQL queries through SQL Server Management Studion(MS-SQL 2005)/Query Analyser(MS-SQL 2000) by selecting database instance(SID)

1. BACKUP LOG<SID> WITH TRUNCATE ONLY

2. DBCC SHRINKFILE(transaction_log_name(SIDLOG1),2000)

After running these commands the transaction log will be reduced to 2 GB.

Hope this will solve your problem.

Regards

Rayees Palot