| Author |
Topic |
|
tincupal
Starting Member
24 Posts |
 Posted - 2008-08-07 : 13:00:01
|
| Hi All,I've created a SQL Enterprise Manager database maintenance plan to backup the databases nightly. However, the log file is not getting cleared as part of this process. I don’t see an option in the maintenance plan to reset the log file. How can I clear the log file as part of the backup maintenance plan or do I have to create a separate script to clear the log file?Thanks, Al |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-07 : 13:07:34
|
| Either start backing up the transaction log (it's available in the maintenance plan) or change your recovery model to SIMPLE. We cover this topic at least once a day, so feel free to do some searching.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tincupal
Starting Member
24 Posts |
Posted - 2008-08-07 : 14:01:22
|
| Hi Tara,Ok thanks, I'll do some searching. One more question: will backing up the log file automatically clear it?Thanks, Al |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tincupal
Starting Member
24 Posts |
Posted - 2008-08-11 : 11:57:02
|
| I turned on log file backup but the only log file being backed up is for the model database. All other databases are getting error “Backup can not be performed on this database. This sub task is ignored.”The log files are in a separate folder from the database. Is this a problem?The model log file is in the same folder as the database. But so is master and pubs and they are not being backup.Any ideas?Thanks, Al |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 12:00:45
|
| Your Recovery model of databases should be Full or Bulk-logged to backup transaction log. |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2008-08-11 : 12:30:03
|
| I'm using a database maintenance plan where I'm backing up all databases. |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2008-08-11 : 12:32:00
|
| Where would I find the recovery model? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 12:34:02
|
quote:
Originally posted by tincupal
I'm using a database maintenance plan where I'm backing up all databases.
Doesn't matter? Only Model has Full Recovery model by default coz that's the model for new database you create. |
|
|
|
tincupal
Starting Member
24 Posts |
Posted - 2008-08-11 : 12:42:19
|
| Ok, so I still have my original problem. Can I truncate log files during a backup maintenance plan or do I need to run a special script? The databases are from a 3rd party software package.Thanks, Al |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-11 : 12:54:44
|
| Find your recovery nmodel for all databases using this:SELECT Name,DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]FROM master.dbo.sysdatabases |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-11 : 22:17:29
|
| If you like to keep simple recovery model for those dbs, you don't need log backup then. |
|
|
|
|
Clearing Log File