sql log backups and space saving

can anyone tell me in layman's terms how backup up transaction logs saves space? Once backed up are they deleted and a fresh one created? Also is there a default value for the transaction logs maxsize? What is this value in GB? This is SQL 2005.
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi pma,

First thing to know is that backup transaction log are only available for databases with full or bulk_logged recovery model.

Second thing is that backing up a transactiong won't create a new file but will empty the actual transaction so the file will have more space to receive new data. That's how allows saving space because it going to reuse the now emptied space to write data on it. If you won't backup the transaction log the file will grow until the limit of the disk (if autogrowth is enabled).

This takes us to the next question, that is the default value for maximum size is 2TB but you can reconfigure it using the next command (in the example I set it to 2GB):

ALTER DATABASE [My_DB] MODIFY FILE ( NAME = N'My_DB_log', MAXSIZE = 2147328KB )

Open in new window


Hope that I could answer your questions.
Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Good explanation given above
Important detail for clarity: the space will only be marked reusable the physical file will not actually shrink.
Often newbie dba implement a regular shrink of the log files which results in waste of io
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.