SQL Server Performance Forum – Threads Archive
Backups and Transaction logs
Hello, I am having a tough time with backups and the log files. I have a maintenance plan that backs up the DB once a day and a transaction log backup every 6 hours. However, the transaction logs are huge (at times, 2 gigs every 6 hours), I thought when I backed them up, the truncated. I am quickly running out of my alotted 40 gigs of drive space. Could someone please explain how I can backup a database and the transactions that allows for full recovery but minimizes the transaction logs size? Should I not be using the wizards and write jobs? Thanks in advance ! JA couple questions. Are your transaction logs incrimental or differential? Do you have any ‘non-logged’ activity going on (bcp, select into)?
You may consider taking log backups every 1 hour and then move these backup files immediately to a different location, so that you can freeup the space being occupied by these backup files.
Also make sure you specify WITH INIT clause in the Tlog backup so that every time the device is initialized rather than appending the log. As specified you have to confirm what kind of activity is place on the database between those 2 hours gap of Tlog backups.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thanks for the replies. I didn’t know there were inc. or diff. transaction log backups. And no, there isn’t any ‘non-logged’ activity. Actually, it isn’t my data, the ISP I work for is just doing care-n-feeding for the company.
I don’t think the wizard is truncating the commited transactions before backing up. Does the wizard not do that for you? It just seems like the logs are huge for the type of app. Thanks J
As a test can you execute the backup Tlog job manually by executing BACKUP LOG dbname to disk=’backupname’ WITH INIT (check books online for exact syntax. Ensure the changes in the size between 2 log backups. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>