• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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.
0
pma111
Asked:
pma111
2 Solutions
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now