large transaction files

[shebmeister]

SQL 2005 on a 64 bit W2k3 Server.
Database was in simple mode, last week changed to Full.

In nightly maint plan last step is full backup (~ 24 GB)

At 5 AM we run transaction log backups every hour until 10 PM
First .trn file is always ~24 GB. All .trn files after that reflect transaction sizes

Why is the first .trn file so large?

 

[mrdenny]

Are there any index rebuilding jobs in the maintenance plan?

Full backups don't remove the entries from the transaction log?

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

 

[shebmeister]

yes, there's re-index job before the full backup in the same maintenance plan.

 

[shebmeister]

Thanks Señor Denny (mrdenny)

Taking the Re-index out of the maintenance plan resolved the issue. I appreciate your response.

the sheb

 

[ramam1]

you don't want to never reindex buddy

 

[mrdenny]

Put the index step back. Then configure the log backup to run on the schedule through out the night.

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

 

[ramam1]

Why did you change to Full?