Tansaction Log File of SQL Server

[former_member249185]

Dear Guys,

We have implemented ECC 6.0 on Windows 2003 Server / MS SQL 2005 platform. For backup purpose we have bought Tivoli. Some reason we cant backup of Database from DB13. Our Transaction log file is goin to increase, when after taking the backup of Transaction log, I shrink the log file but size of log file never decrease. So size of log file increase and free size on disk decrease. Second issue is, Can I shirnk the log file when SAP instance run ...??

So help me out in this 2 issues.

Thanks & Regards,

Charanjit Singh.

[Former Member]

Next time you have sap stopped.

Backup the TL, stop MSSQL, start MSSQL, change the recovery type to simple, shrink the log file to the required size, change log type to full. Start SAP.

You dont always need to do these thing, depends on what mood SQL is in.

[Former Member]

Dear Charanjit ,

To truncate the transaction Log the following MS-SQL queries through Query Analyser

BACKUP LOG<SID> WITH TRUNCATE ONLY

DBCC SHRINKFILE(transaction_log_name(SIDLOG1),2000) OR using Management Console select shrinkdb option from the right click. select files, select the log file, enter the size and let it rip.

It is always recommanded no user should access direct Database while truncating Log .

Regards ,

Santosh

[former_member193399]

Hi,

The log space is usually huge after a fresh install. You can use the following script to shrink the log file. Make sure you take a full backup of the database before you run. If you running in production system make sure you run during the scheduled downtime after a full backup.

1) BACKUP LOG <DB_NAME> TO  [<DEVICE_NAME>] WITH NOFORMAT, NOINIT,  
NAME = N'<DESCRIPTION>', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 --backup transaction log to a backup device
GO

2) USE <DB_NAME> --switch to the database
GO

3) DBCC SHRINKFILE (N'<LOGICAL_LOG_FILE_NAME>' , 2024) --shrink the file to 2gb (2024). use sp_helpdb to find out the logical file name
GO

4) sp_helpdb <SID> --view the new size
go

5) dbcc loginfo (<SID>) -- a status of 2 means transaction is dirty.

Repeat steps 1 thru 5 until log size shrinks to your desired size.

The step 3 value of "2024" is 2gb which tries to shrink the log file to 2MB. But it is not guaranteed. That is why you have to run it multiple times.

The step 5 displays output of the transactions in the log file. The status of "2" means it is dirty. If you see this value at the end of result set then there is less chance of shrinking the log file. if you see the value of "2" in the middle of the result set then there is likely chance of shrinking the file. "DBCC LOGINFO" is an undocumented command in SQL Server. But it is a favourite command of all DBAs'.

I hope this helps.

RT

Message was edited by:

R.T.

Message was edited by:

R.Th.

[clas_hortien]

Hi,

the log can only be truncated up to the oldest open transaction. You can check

this transaction with

dbcc opentran

Only when this open transaction commits or rolls back the log can be truncated further.

Yes, you can shrink the log, while SAP is running.

Regards

Clas