How to set no archive log in MS SQL

[Former Member]

Dear Gurus,

I'll already did the support package upgrade and need to run the SGEN.But before that I'll need to set the archive log in MS SQL 2005 database.

Kindly please help me,how to set "no archive log " in MS SQL .Then I can run the SGEN.After that how to revert back to "enable archive log mode".

All the posting told about the archive log with Oracle.

Thanks

/Shah

[markus_doehr2 · Accepted solution]

Set the archive mode to "simple" (using Management Console). Be aware that the archive log is still written but cyclically overwritten.

After your SGEN is finished switch it back to FULL.

Markus

[Former Member · Accepted solution]

Thanks...

Last time I'll truncate my log using "backup with no_log command.Then i saw my log like not moving or stuck.why it happen and how to fix it become normal.

After i run the SGEN then i'm out of space in database,why this could happen and how to resolved it.

Kindly please help...

Thanks

/Shah

[markus_doehr2 · Accepted solution]

> Last time I'll truncate my log using "backup with no_log command.Then i saw my log like not moving or stuck.why it happen and how to fix it become normal.

What do you mean with "not moving or stuck"?

> After i run the SGEN then i'm out of space in database,why this could happen and how to resolved it.

SGEN generates code and writes that to the database. It's normal that it will increase the used space.

Markus

[Former Member · Accepted solution]

thanks..

my transaction log still as the same size but the modified date has been change..how..

[markus_doehr2 · Accepted solution]

> my transaction log still as the same size but the modified date has been change..how..

Yes - because the database process reads and writes to it.

I suggest you read a good book on SQL Server to get a better understanding about the concepts behind. It's very difficult to explain things as they happen if you have no clue about what the database is actually doing (no offense).

The online version of the SQL Server book is a good start:

http://technet.microsoft.com/en-us/library/ms130214%28SQL.90%29.aspx

Markus

[Former Member · Accepted solution]

Thanks..

Need to ask one question...if i want to truncate the sap sql transaction log,whats its the MINIMUM size i could truncate it.

Kindly please advise..

Thanks

/Shah

[markus_doehr2 · Accepted solution]

The log needs to be at least as big as the longest/biggest transaction without a commit.

Markus

[Former Member · Accepted solution]

sorry sir i don't get it....can you give me the details example..

TQ

/Shah

[Former Member · Accepted solution]

Hi Shah,

In MS SQL Server, Transaction log is used to write log files.

For eg., Intially if you allocate 10GB to the Tr.Log and can set the limit based on your requirement, say as 11GB. It means it wont grow beyond the point 11GB.

If you take a transactional log backup, the data present in the 10GB file is freed up, but the size of the file would remain same.

This can be truncated by shrinking the log file.

1. Open SQL Mgmt studio.

2. Right click on the DB(SID)> Tasks>Shrink-->Files.

3. Choose "Log" in the file type and "Log File Name" in the Filename column.

4. Shrink Action should be "Release Unused Space"

5. Then Click Ok. The unused space will be released.

The Transaction log can be switched off by changing the Recovery Model to "SIMPLE"

1. Click on the DB(SID)

2. Properties>Options>Recovery Model.

3. If the recovery model is set to Simple, the Transaction log wont be written.

Hope this would help you. Revert for any other clarification.

Regards,

Kamesh

[Former Member]

Hi Shahril Ariffin ,

You should schedule backup for the transaction log (.LDF) every 5 or 10 minutes.

You can do this through Maintenance plan.You can get easily by checking the following link.

Below link is actually to schedule the database backup.But you can find an option to schedule the transactionlog backup at 7th screen shot.

[Creating a SQL Server Maintenance Plan|http://www.databasedesign-resource.com/sql-server-maintenance-plan.html]

Just check Maintenance plan in you sql, you will get an idea.

If you wont plan for transaction log backup, it will grew continuously.

You can user the option "Generate all the objects of selected software components" go to next screen and select for which components you had applied patches or you can select all. Depend on your database size, sgen will run.

Thanks & Regards,

Nagendra.

[Former Member]

As Nagendra said.

You don't have Archive logs the same way as in Oracle.

When running SGEN, make sure that the transaction logs is large enough

and/or that you run the backup of the transaction logs in a shorter intervall, e g every 5 minutes.

If you want to switch off the logs, you can enter the database in simple mode

BUT

You must have a full backup before

AND

Make another full backup when the mode i switched to "Full" again,.

I always run the first option: backup tlogs every 5 min during SGEN.

[Former Member]

Thanks..

But which SGEN menu i need to choose.and how to set the backup like 5 minutes.i'm very weak in sql.

the transaction log means the normal one right..xxx.ldf.

Thanks.

/Shah

[Former Member]

Hi Shahril Ariffin,

There is no such concept like archive log mode in SQL as exists in Oracle.

You can run SGEN.

Thanks & Regards,

Nagendra.