Back up Transaction log 1

[netnut1234]

Hi,

I have a database that I am backing up via SQL maintenance plans.
The database is running on SQL 2005 server and is in Full recovery mode.
I created 2 maintenance plans the first to backup the transactions logs, which is done Monday to Thursday, then a full backup of the database is scheduled to run on a Friday.

I ran the first transaction log back and it failed, as I needed to run a full backup first, which I did, then ran the transaction log backup, it completed successfully. Both the maintenance plans were running for about a week, I was then got an error when trying to backup the logs, not enough disk space. The server guy informed me that there was no disk left on the server. I than shrink the database and others to create more space, this helped, but not much. Another guy from the server team said about running a transaction log backup but with truncate_only as this would commit the log. So I ran it and a shrink.

This worked a treat, I was able to gain a lot of space on the server. When the transaction log ran last night, it failed, the error that comes back is,

Executing the query "BACKUP LOG [%databasename%] TO DISK = N'E:\\Backup\\%databasename%_backup_200906302157.trn' WITH NOFORMAT, NOINIT, NAME = N'%databasename%_backup_20090630215737', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So I though, as I had done a truncate_only backup, that I need to run a full backup first, which I did and that completed successfully. I then ran the transaction log back up and again it fails with the above error.
I have looked about and it says about the database being in simple mode, but the database is in full recovery mode, I think it has something to do with the truncate_only log backup that I ran, but I am not sure.

Can anyone assist me with resolving this issue please.

Thanks in advance,
Tim

 

[mrdenny]

When you run the BACKUP LOG with TRUNCATE_ONLY you broke the backup chain. At this point you would need to do a full backup before you'll be able to do anything with the transaction log restores.

Based on your backup setup, you should probably switch your database to SIMPLE recovery and just do a full backup every day. FULL recovery is used when you need the ability do to a point in time restore of the database. If you don't have that need then the SIMPLE recovery mode will be just fine.

If you do need the ability to do a point in time restore, then you'll probably want to backup the log more often than once per day as you can only restore up to the most recent log backup that you took which would be the night before. So if your database became corrupt or lost at the end of the business day you'll have lost all the data for that day.

As for your sysadmin telling you how to manage the SQL Server, he's a sysadmin that doesn't understand how SQL Server works (as he gave you bad advice about truncating the transaction log and shrinking the log file) so he probably should stop doing that.

Shrinking database files does nothing but hurt performance as it is the #1 cause of database fragmentation that there is. Transaction logs should pretty much never be shrunk as it causes all sorts of performance problems within the file and the Virtual Log Files within the file.

Now, I'm assuming that you are backing up your SQL Server databases to the same disk as the database it self? That's sort of pointless. The point of the backups is to recover the database in the event of a system failure. If the system fails then you won't be able to restore the backups because they won't be available either. At the very least you need to move them to a different drive on the server, but even better would be to move them to a different server by backing up over the network so that if the SQL Server fails, you can still access your backups.

Sorry to be to ruff on you (and long winded) but when I see this much wrong, I feel that something must be done.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[netnut1234]

Hi Denny,
Thanks for your post, and the information about the error and how/what should be done to resolve the issue.
I changed the recovery type back to SIMPLE. Now I complete a Full backup each night and this works for well for me. I have been doing this now for nearly 3 weeks.
Thanks
Tim

 

[mrdenny]

No problem. Glad to help.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog