SQL and Transaction Logs

[NATD]

Hi,

I've having trouble getting this clear in my head after pouring over Books Online for the past 30 mins.

Basically, I've got a SQL Server which gets a full backup each weeknight, but the transaction logs aren't clearing.

I'm using the default subclient which is autodetecting the various databases on my server. I've restored data from time to time and generally think thinks are OK, other than these transaction logs which have now run me out of space.

ERROR CODE [30:325]: Error encountered during backup. Error: [ERROR: [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'dbFranchise' is full. Back up the transaction log for the database to free up some log space.

In my mind, doing a FULL should clear the logs, just like it does on my Ezchange servers.

Can someone explain it to me and tell me the best fix?

 

[birky]

Hi

Full or differential backups don't truncate transaction logs for SQL...you need to run a transaction log backup (which really it is best practise to run periodically throughout the day so you can restore to a recent point-in-time if required)

Hope this helps



Birky
CommVault Certified Engineer

 

[Frbutler]

I run hourly and every other hour tlog backup on my SQL

Or you could talk the DBA's into doing a local dump using the native SQL backup. then just pickup the tlogs when you do the file system backup at night.

I gave all my maglib space to the DBA's and had them mount it to the local SQL server. They do local dumps of the tlogs and DB's then kick off a full backup of the file system once the dumps are done. I have CV sending them a CSV report of "Protected objects" which they parse via a script and use that to delete the dumps to free up space.

This allows me 2 things
1. No SQL agent=no maintenance costs
2. Can restore dump files anywhere they want without worrying about SQL versions and client versions.

 

[mrfokker]

Somewhat related to this topic. We had a SQL DBA here today and he asked me what the Idata agent for SQL does before it attempts to backup a database. I was unable to answer. Does anyone know what pre/post check the database agent does to confirm the integrity of what Commvault backs up?

 

[Frbutler]

the commvault SQL agent issues only Microsoft API commands to SQL so... whatever the "backup XX database" command would be in SQL native, is what CV issues to preform the backup.

If you look in the SQL log (Client) you will see all it does it change the destination using VDI. It issues the same SQL command that the DBA would do if he were backing it up to a .bak file on the local drive.