SQL Log File

lovelyk

Dear All,

I have a problem with some of my client tha the back up will take longer time.,

My Database is in SQL server 2005.

with the first view I analyse that Log is taking so much of space.

Waiting for your reply.

garak

How looks your recoverymodel?
What for backupsjobs do you have?
How often you make a fullbackup?
Did you make a diff. backup?
How often you make a translog backup?

Other question: What for maintenance tasks do you have and how often runs this jobs?
Because some, needed, jobs like REBUILDING or REORGANIZING INDEXES - will cause heavy load on the TransLog.
So answer the question, read the post in subforum SQL/General, read the books online, search the forum for sQL Backup.

Regards

lovelyk

Dear,

I will clear you the scenerio as the database size is about 90 GB but within this the log file is itself the 72-73 GB. I want to reduce the size of the log file. as per the recovery model is concern it is full & the slient make the full bacup daily. the dont't make the translog & differential backup.

Kindly suggest some remedy to reduce the backup Time

Regards

kine

1) How the backup is done? It is done n some local disc or over network?
2) You can totally change the backup strategy - e.g. full backup once per week (on weekday), differential backup during oher days and transaction log few times per day...
3) There are tools which are creating compressed backups and speeding the backup in this way. See e.g. http://www.quest.com/sql-server/backup- ... overy.aspx

DenSter

lovelyk wrote:

the recovery model is concern it is full & the slient make the full bacup daily. the dont't make the translog & differential backup.

Then you should set the recovery model to simple. You would only need to do full backups and never have to worry about transaction log.

To make the transaction log smaller you need to do two things:
1 - take a transaction log backup. This will remove the active transactions currently in the log file. If you leave the recovery model to full, then you will need to schedule a regular TL backup. The actual file will remain the same size, it will just not be fully in use.
2 - if you want to make the actual file smaller, you can shrink the file. Read Books Online (Start, All Programs, SQL Server, Documentation & Tutorials, Books Online) for syntx and other instructions.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

bbrown

You don't mention how long the full backup takes. The size of the transaction log will have no bearing on the full backup. The performance of the source and destination media play a major role in backup performance. One client, that I work with, has a 240 GB database (150 GB used). They use database mirroring and a VSS backup system as primary methods (along with tran. log backups). They also do nightly full SQL backup which is used to rebuild a reporting database on another server. The SQL backup is written to a local RAID 0 array (3x72) and takes 20 to 25 minutes each night. As the database grows, this destination array will be upgraded.