cancel
Showing results for 
Search instead for 
Did you mean: 

BIG PROBLEM transaction log does not shrink after full database backup

Former Member
0 Kudos

Hello

I have a BIG PROBLEM i.e.MS sql transaction log does not shrink after full database backup.

I am only few days before going live . the server is running half year.

I would like to switch off transaction log on development and shrink with full database copy or transaction log backup(or anyhow)

I am performing backup with Enterprise manager.

I also restarted server few days ago. but still the transaction log does not shrink after database backup. I tried also only transaction log backup.

Please help me

Accepted Solutions (0)

Answers (1)

Answers (1)

rolfc_weber
Contributor
0 Kudos

Hi,

Transaction log (on MS-SQL) does only shrink if you do this manually.

The impotent is the fill % of the file(s), look at transaction DB02 that will tell you.

Do newer switch off transaction logging, as you will get big problems if you have to recover the database (for whatever reason) later on.

If you still want to shrink the transaction log file, take a look at sap note 363018 or 1002099 as they describe how to do.

regards

Rolf

Former Member
0 Kudos

You have two options to shrink the transaction log:

Prerequisites:

- The transaction log cannot be full. Backup your log or set the recover mode to simple.

1st way - Using DBCC SHRINKFILE(FILENAME,newsize) from the query analyzer.

- In the enterprise manager ( sql2000 ) or management studio (sql2005) left mouse click in the name of the database, tasks, shrink files, select the file type log, "reorganize pages before releasing unused space" , shrink file to "your desired size"

2nd way. Use this if the first doesn't work.

Stop the sql server.

Detach de database.

Rename the log file.

Attach the database and erase the log file in the file selection. ( Sqlserver 2005).

If all is ok, the dabase is attached with a new empty log file with a minimum size. I recommend to extend the size of this log file.

Best regards.

Former Member
0 Kudos

Hi!

You peolpe did not mention DB13:transaction log backup.

Will this action at DB13 do a shrink on the LOGFILE?

Thanks!

rolfc_weber
Contributor
0 Kudos

Hi,

No, it's a transaction log <b>backup</b>, and log backups does never shrink the size of the file, only empty it.

If you want to shrink the size of the log, you have to do it manually in one of the ways already described....

Regards

Rolf

0 Kudos

Why do you want to shrink the logfile?

You should always have a logfile which is big enough to hold the entries between two log backups, even in top workload times.

For performance reasons you should NOT see automatic growth of the logfile during normal operations.

The only motivation for shrinking a logfile is:

- Shrink away unusual filling.

- Shrink and re-enlarge a logfile which has grown in very little increments.

HTH Rudi