SQL Server Performance Forum – Threads Archive
First T-Log size
Env: Win2000 + SQL-Server 2000,Backup: everyday at 8:30pm
T-Logs: every 1 hours starting at 7:00 am till 8:00pm
Recovery model: Full I noticed my TLogs size are as follow:
07:00 2400.0 MB
08:00 20.0 MB
09:00 4.3 MB
10:00 3.5 MB
11:00 2.8 MB
12:00 6.4 MB
13:00 3.4 MB
14:00 3.0 MB
15:00 17.0 MB
16:00 9.3 MB
17:00 2.5 MB
18:00 0.4 MB
19:00 0.3 MB
20:00 0.2 MB
07:00 2370.0 MB
Question is why the first log size in the morning is about 2 GB while there is no transaction since the backup is taken at 8:30pm previous night? All I run is maintenance plans during night.
CanadaDBA
But if maintenance plan include Reindex, log grows.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Yes, I do Integrity checks and Optimizations every night.
quote:Originally posted by LuisMartin
But if maintenance plan include Reindex, log grows.
CanadaDBA
To add to this, the reindexes (optimizations) reorder all the indexes. Every single one of these transactions have to be recorded. Because of this, your situation is pretty common. My first round of transaction log backups are usually over 45gb. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
What if I do Integrity check and Optimization first and then get backup. i.e.:
7:00pm End of the users work day
8:00pm Integrity check
8:30pm Optimization
9:00pm Backup and starting from midnight, get a TLog every one hour up to 8pm. This way, I won’t have a TLag with reindex information. Am I right? DO you suggest me to get a backup before Optimization and Integrity check? Note that I have previous night (9pm) backup plus all TLogs (12Am to 8pm). Thanks, CanadaDBA
I would do the full backup before the optimization and integrity checks personally. There is almost no chance of anything blowing up due to these. Before them though, you know everything was working. Just an error on the side of caution. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
I suggest Integrity first, backup and then Optimization, because Integrity is neccesary for good backup, but Optimization no. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Well, its a debatable topic and is purely depends upon the enterprise to enterprise.
At our end I normally run backup first and Optimization tasks, finally another backup to replace pre-opti. checks file. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
same here. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
I set the server to backup first, then Integrity check and Optimization, and at last another backup. These all starts from 9pm and ends 11pm. I have set the T-logs to 12am to 8pm. So, During maintenance, no T-Log is created. And because I ran another backup (Full Recovery) after maintenance, I avoid producing a big first T-Log size. There are no user working with the DB after 8pm. Thanks, CanadaDBA
You actually have time and space to do that? <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> I need to buy SQL LiteSpeed.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
]]>