Recently, one of my dear friend was kind of pushed to take the position of DBA in his organization due to lack of resources. Because of the need of the application, he created a new database and was setting up the Recovery Strategies for the database.
He set the database to Full Recovery Mode and then he tried to set up the job to take up the Transaction Log Backup after every 15 minutes. When he tried to execute the Job for testing purpose it FAILED.
He called me up and told the scenario and I asked him to take the Full Backup of the Database first and then he should run the Job to take the Transaction Log Backup.
As predicted, it worked. But I thought it would be better to create a Blog for others as well.
When we create a New Database we should always check the location and size of the data file (mdf) file and log file (ldf). Then we should back up the database. Remember, until we take a full database backup, the log file will not act like a typical log file in a database which is set to FULL Recovery Mode. We cannot even perform a Transaction Log backup until we have taken a Full database Backup. If we try then we will get the following error message:
Until, first FULL backup of the database is taken, the database is acting as if it is in the Simple Recovery Mode and the Transaction Log will get regularly truncated at checkpoints.
I have seen many times even experience DBAs forget the small point. Please keep this point in mind when creating any new database and setting the Recovery mode.
Till then Keep Learning !!