July 23, 2007 at 10:21 am
I am testing a maintenance plan for backups, using Win 2003 R2 Standard SP1 and SQL Server Standard SP2. I have it set for a full backup daily at midnight, and a tran log back every three hours.
The server is running, but idle. I am not doing any work at all in the database, and there are no apps running.
However, the size of the tran log backups look like this:
1076KB on 7/22 at 3:00 AM
2143KB on 7/22 at 6:00 AM
1627KB on 7/22 at 9:00 AM
81KB on 7/22 at 12:00 PM
16KB on 7/22 at 3:00 PM
81KB on 7/22 at 6:00 PM
16KB on 7/22 at 9:00 PM
16KB on 7/23 at 12:00 AM
81KB on 7/23 at 3:00 AM
16KB on 7/23 at 6:00 AM
16KB on 7/23 at 9:00 AM
If there is no activity in the db, why would the size of the tran log backups vary like this?
July 23, 2007 at 11:49 am
Maintenance tasks scheduled? Index mainteannce? Try running a trace during the time windows to determine what is causing the Tlog backups to grow.
A.J.
DBA with an attitude
July 23, 2007 at 3:13 pm
There are no other maintenance tasks scheduled, other than the full backup at midnight & tran log backup every 3 hours. The tran log backup Subplan_1 consists of 1) History cleanup (older than 7 days) and 2) backup tran log (release space back to the OS).
The tran log itself is 1024KB and has not changed.
Once upon a time I was running Oracle 10g and it does "self-tuning" operations, I wonder if SQL 2005 also does something like that.
July 24, 2007 at 7:31 am
Are you sure that the log file is just a GB and the backup is 2Gb in size this can't be just check with perfmon what is happening also you can use third party tools to track the file usage.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 25, 2007 at 5:46 am
Check your database options. AutoShrink on? AutoClose on? Auto Create Stats on? What about your Transaction log checkpoint stuff?
Chances are the reason the first 3 logs were so huge was because you either restored a database that hadn't had a transaction log backup / checkpoint done in a long while. As far as the others go, I'm not quite sure, but something has to be accessing the database, even if it's just for read kind of stuff.
You sure no one is linking access db tables or doing replication from this database (or anything else of this nature)?
Run sp_who2 to check all connections. This will tell you what system processes are running and what user logins are connected (including yourself). This is a little bit faster than running Profiler and will give you a basic idea if there's anything you should be worried about. But it's also less informative than Profiler, so if you don't see a lot with the command, try Profiler as someone else suggested.
July 25, 2007 at 6:06 am
our main database full backup is taking 2 hour time and scheduled @ 1 am and transaction log backup scheduled 24 hr @ the interval of 10 min.
is it possible to take log backup during full backup was running at mid night or i have to change the schedule for log backup?
pl.
July 25, 2007 at 6:12 am
You can't do a log backup and a full backup of the same database at the same time. One will be held up until the other finishes and they may deadlock each other. So it's safer just to reschedule the log backup.
July 25, 2007 at 6:21 am
error:
backup device 'w:\backup\......bak' failed to crate.
operating system error=32(error not found)
some days this error occurred during backup...server: clusterd.
pl.
July 25, 2007 at 6:24 am
Naqaraj, you should really open up a new thread about this error instead of piggy-backing on a thread with a different subject.
People who read this thread are expecting to discuss William's topic, not your error.
July 25, 2007 at 6:42 am
Most importantly, only 10-15 persons monitor this thread VS a few 100 000 monitor new threads. So odds are on your side when you start a new thread.
July 25, 2007 at 7:05 am
This server is on my home office peer-to-peer network, so I am certain there is nobody else in there. But I will check the auto-close/shrink etc settings thanks for the tip.
This was a "bare metal" install, starting with deleting the hard disk partitions, reformatting and then installing the OS, SQL, service packs, and a zillion "critical updates" from MS.
Aside from the educational value for myself, I'm running this test because my customer upgraded to 2005 and the server vendor is using Backup Exec and they say we don't need a SQL maintenance plan for backups, but I'm convinced that it is not getting the job done. I did take a full backup from production & restore it to this server, so your statement about "restored a database that hadn't had a transaction log backup / checkpoint done in a long while" makes me think I'm correct about Backup Exec.
BTW yesterday I powered down the server during a severe thunderstorm, and the subsequent tran log backup was 163KB but then it settled back down to either 16 or 81. Does that make sense?.
July 25, 2007 at 7:32 am
Does Backup Exec actually make SQL database backups or is it backing up the .mdf and .ldf files from the server?
If the later, then your client has a problem. Databases can't be restored from their .mdf and .ldf files and any backup made on those files with the database is still connected to the server is corrupted because the files are in use and unreadable by other software.
The power down issue actually does make sense. When the services stop and start up again, they go through checks on the databases, closing out open objects, updating system tables, generally making sure that everything is happy and "stored" properly. This could potentially inflate the transaction log with uncommitted transactions. Then the log goes through the commit phase where it checks to make sure everything has been committed to the database that needs to be committed and if there's nothing there, it deflates back down.
Now the question is, did my response make sense?
July 25, 2007 at 7:32 am
Sugesh, Let me clarify - Immediately after restoring the db from production, the ldf was around 220 Megs. I didn't note the size after the first backup, but it is presently down to 1 Meg ( 1024KB ) and has stayed there. However, the tran log backups are varying in size. That's why I'm confused.
July 25, 2007 at 7:34 am
Oh, I forgot to mention...
Just because the client is doing full backups doesn't mean they're doing transaction log backups. You can do one without the other (though it's not advisable). And if you don't do a TransLog backup, you don't get a checkpoint and the transaction log doesn't clean itself up. It just keeps getting bigger and bigger until you run out of hard drive space.
And this is regardless of how many complete database backups are done on the DB.
July 25, 2007 at 7:45 am
Yes, it does make sense, thanks.
I was outside the loop on the actual server upgrade, but a non-IT person repeated a statement from the vendor about how "Backup Exec can back up open files" which sent a chill down my spine, for sure.
Apparently the Backup Exec is backing up the mdf and ldf files which I agree is pointless. These guys are supposed to be MS Certified Gold Partners, you'd think they would know better.
Thanks for everbody's help on this, I have enough info to proceed with the plan.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply