Enourmous SQL traffic generate lots of trn file

Question

Post reply

Enourmous SQL traffic generate lots of trn file

Sylvain Coutu

Ten Centuries

Points: 1077

May 30, 2005 at 8:42 am

#109418

Hi All,

I have a new product that we installed recently that is creating a lot of logspace. I have been aware of this problem since the begining and I'm looking for solutions within the EM product ( I do not want to buy any other third party tools ).

Here's the problem.

Operation	time	ldf size	mdf size	trn name	trn size	.bak name	.bak size
db backup	06:00	1000	100	n/ap	n/ap	myDB2005-05-30.bak	33
tran backup	07:00	1000	100	myDB200505300700.trn	200	n/ap	n/ap
tran backup	08:00	1000	100	myDB200505300800.trn	500	n/ap	n/ap
tran backup	09:00	1000	100	myDB200505300900.trn	600	n/ap	n/ap
tran backup	10:00	1000	100	myDB200505301000.trn	300	n/ap	n/ap
tran backup	11:00	1000	100	myDB200505301100.trn	500	n/ap	n/ap
tran backup	12:00	1000	100	myDB200505301200.trn	700	n/ap	n/ap
tran backup	13:00	1000	100	myDB200505301300.trn	100	n/ap	n/ap
tran backup	14:00	1000	100	myDB200505301400.trn	400	n/ap	n/ap
tran backup	15:00	1000	100	myDB200505301500.trn	500	n/ap	n/ap
tran backup	16:00	1000	100	myDB200505301600.trn	300	n/ap	n/ap
tran backup	17:00	1000	100	myDB200505301700.trn	200	n/ap	n/ap

Total Space On file server		1000	100		4300		33	5433

So I have gigantuous log file backup. I want to minimise the total space taken on the file server while assure my boss that the down time in case of failure would be short. Any idea ?

I usualy support DB that have relatively small logs and I use the following strategies : I backup the db once per day and a transaction backup every hour or so. But in this case I have close to 4 gig for a db that has only 33 m. I am lost here please help me :0) In case of failure I would need to restore all these log and I am not sure that this will complete very fast !!!

Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Have you performed a shrink on the log file? Performing a transaction log backup doesn't not shrink the log file. Make sure there are no open transactions (DBCC OPENTRAN) and then use the DBCC SHRINKFILE command.

The 4gb of transaction log space is not necessarily actively being used. Read up on virtual log files in Books Online. Because not all of the space is being used the log restores are actually pretty quick.

--------------------
Colt 45 - the original point and click interface

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 2

The log file (.ldf ) is always 1000 m and each time I perform a transactional backup space within this file is available. The space taken ( transactions ) is move to a .trn file ( these become the backup log file ). In the case of a restore I need to restore the db and then reapply the backup log ( .trn ) and the current log ( ldf ).

The 4 gig is the total amount of ALL the .trn plus the current .ldf. The current .ldf is always 1000 m.

Shrinking the .ldf file will not change the outcome.

Thanks

Steven L Durfey SSC Veteran Points: 240 More actions · Answer 3

Try using a differential instead of the Tlog backup.

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 4

Will differential allow me to restore point in time?

philcart SSC-Forever Points: 47796 More actions · Answer 5

Sorry I misread your original post.

Differential won't allow a point in time restore, but you perform differential backup sin conjunction with transaction log backups then you'll reduce the number of transaction log backups you need to keep on the file server. You'd have to test this before you were able to determine if it saves any space though. You might just take up the space in differential backups instead.

What is the issue with keeping that amount of backups anyway? We keep approximately 35-40gb of SQL backups on our files server on a rotational basis. If you really have to reduce the space consumed by the backups, you'll have to look at a third-party solution. You'll need either a tool that reliably compresses and decompresses the files after the backup (eg: WinZip), or a tool that compresses the data on the fly (eg: SQL Litespeed).

On another track, I'd be interested to know what sort of application generates 700mb worth of transaction data in an hour but doesn't save any of it in the database

--------------------
Colt 45 - the original point and click interface

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Can't wait to get an answer to that one (sort of application generates 700mb worth of transaction data in an hour but doesn't save any of it in the database).

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 7

It's a project management tool . It's not a very well known application.

I still have to get together with the software support to see if theres a way to reduce this beast appetit. So I will not give out any names.

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 8

Thanks Phil you have confirmed my theory that theres no other way in EM.

I never thought of winzip to compress I guess I never had this problem before :0) Does it compress to a good ratio.

We will look into Veritas backup agent for SQL Server. We already use it for our Oracle but I never used it for SQL server any of you have thoughs on this ?

philcart SSC-Forever Points: 47796 More actions · Answer 9

Personally, stay away from backup agents, once bitten twice shy.

--------------------
Colt 45 - the original point and click interface

Sylvain Coutu Ten Centuries Points: 1077 More actions · Answer 10

Sylvain Coutu

Ten Centuries

Points: 1077

June 1, 2005 at 7:00 am

#562150

Horror story ?

philcart SSC-Forever Points: 47796 More actions · Answer 11

Let's just say that management came around to my way of thinking after losing a days work

--------------------
Colt 45 - the original point and click interface