SQL 2005 Backup problem

[wbodger]

I am setting up the maintenance for a SQL 2005 box. When I go into the properties of the database the Recovery Model is set to Simple. When setting up the backup, I choose Full. The database is ~ 8 GB and the Log file is closer to 14 GB than 13. I thought that using the Simple Recover Model would truncate the Log file after each backup? It does not appear to be truncating the log file, am I missing something?

wb

 

[gmmastros]

You need to backup the log file separately.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

OK. I figured that I had misunderstood something, because I thought that with the Simple Recovery model the log file was truncated with the backup since the simple recovery model precluded point in time recovery.

thanks,
wb

 

[SQLBill]

With Simple recovery mode you shouldn't have to backup the log file. However, several things can cause your log file to expand. One thing I would check is - are you doing reindexing after the backups? If so, a reindex can cause the log file to grow larger than the data file size. Consider doing reindexing before a backup is done.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[wbodger]

I am not re-indexing after the backup, however, I did go and look at the log file usage and I am only using a fairly small percentage of those 14 GB of space that it is reserving I am only using a very small percentage of that space. So, does SQL Server take that most size that it has ever needed and reserve that amount of space so that it is ready for any situation?

wb

 

[SQLBill]

Maybe when you created the database you initially created the log file with 14 GB. It won't shrink it past what it was initially created as.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875