Transation log is full in QAS server

[Former Member]

Hi gurus

Need ur help. os windows 2003 database sql2005 sap ver ECC6.0. Transation log is full in QAS server. How can i shrink the transaction log of qas server. for that should we have to take full backup of database. please give me the step of shrink transation log file .

Thanks in advance

Regards,

Jayant

[Former Member · Accepted solution]

Hi,

please check these notes:

http://service.sap.com/sap/support/notes/363018

http://service.sap.com/sap/support/notes/421644

Regards,

Sven

[Former Member · Accepted solution]

Jayant,

The other thing you could do is to change the recovery model of the database from FULL to SIMPLE. Once this change is made, simply execute the following command:

DBCC SHRINKFILE ('Name_of_log_file', 'Value_to_be_shrunk_to');

and execute it.

Example:

DBCC SHRINKFILE ('ABC_LOG', 5000); --> will shrink the ABC_LOG file to ~5GB

*CAUTION* - Only change the recovery model of the database from FULL to SIMPLE if you don't need the ability to do point-in-time restores. Once the database is changed from FULL to SIMPLE, you can only restore your database back to the last known good FULL backup!!

Also, if you have database mirroring active, you'll have to break the mirror first before you can change the recovery model of the database.

Hope this helps!

Jason

[Former Member]

Hi All

Thanks for the help my problem is solved now with the help of you

Regards,

Jayant

[Former Member]

I'm pretty new in this forum but every now and then I see that shrinking database files, particularly the LDF files, is pretty common. It is not recommended to shrink both the MDF/NDF and LDF files because this is causing internal fragmentation in the databases. Note that SQL Server stores data in data files of 8KB pages. When you shrink a data file (MDF/NDF), it causes fragmentation on the index and data page level, thus, causing I/O performance issues. This also introduces external fragmentation on the disk level because of the autogrowth and shrink process The best approach is to size the database files appropriately as part of capacity planning.

The same thing about recommending switching the database recovery model to SIMPLE from FULL. Database recovery model should be considered as part of your disaster recovery plan as the SIMPLE recovery model does not support point-in-time recoverability. If your recovery point objective/recovery time objective (RPO/RTO) requires point-in-time recoverability, switching to SIMPLE recovery model from FULL is like violating your RPO/RTO. If your LDF files are growing larger than your MDF/NDF files, evaluate your disaster recovery plan to include a combination of FULL and LOG backups as well as sizing your database files accordingly to mitigate the excessive LDF file growth.

[Former Member]

Hi all ,

while i m trying to shrink DEVLOG1 and run query

dbcc shrinkfile ('devlog1',2000)

or

dbcc shrinkfile ('devlog1.ldf',2000)

it shows error

Msg 8985 level16 ,state1 ,line1

Could not locate file 'devlog1' or 'devlog1.ldf' .for database 'DEV' in sys database_files.the file either does not exist or was droped.

while i m trying with Microsoft SQL server Management studio then also the database is not shrinked

dev -> Tasks -> shrink -> files

File type - log

File name - PRDLOG1

Location - d:\DEVLOG1\DEVLOG1.ldf

shrink action - i select second option Reorganize pages before releasing unused space

It shows in File name PRDLOG1 because the database copied from prd server

Please help if any one face this type of problem.

Thanx in advance

Regards,

Jayant

[Former Member]

Probably when you did the copyback you forgot to change the directory structure as per development system. It is adviseable to change the directory structure as per the system.

Do the following steps.

1. Rename the Logfile name.

ALTER DATABASE <Database name> MODIFY FILE ( NAME = 'PRDLOG1', NEWNAME = 'DEVLOG1')

2. select * from sysfiles Save this query results.

3. Detach the database.

use master

go

sp_detach_db 'DBName','true';

4. Create a folder D:\PRDLOG1.

5. Attach the DB in new directory.

sp_attach_db 'DBName','filename1','filename2';

[Former Member]

the easiest way would be to back it up from the o/s level, right click on the database, backup and choose transactional backup, if you don't have enough space locally do a network backup..

[Former Member]

Simple. Please run the following command in your SQL Server:

BACKUP LOG <Database_Name> WITH TRUNCATE_ONLY

This will truncate the transaction log and write its contents to the database file.

I hope this answers your question.

[Former Member]

Hi,

Pls follow the steps

1. Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

Note In this statement, <DatabaseName> is a placeholder for the name of the database that you are backing up. In this statement, <BackupFile> is a placeholder for the full path of the backup file.

For example, run the following Transact-SQL statement.

BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

2. Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

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

Note In this statement, <FileName> is a placeholder for the name of the transaction log file. In this statement, <TargetSize> is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable.

For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.

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.

Hope it helps

Regards

KT

[Former Member]

Hi,

You have to backup the transaction log.

In case of emergency to unlock a QAS system, you can take the backup on disk, using SQL manager studio.

Regards,

Olivier

[Former Member]

Create directory in the partition where free space avaialbe.

and execute below command through microsoft sql server management console.

BACKUP LOG PIP to disk ='<directory name with pathname>\<filename>.bak' with INIT

DBCC SHRINKFILE('<log name')

ie.

BACKUP LOG PRG to disk ='G:\Logbackup\ttt1.bak' with INIT

DBCC SHRINKFILE('PRGLOG1')

Regards,

Nikunj Thaker