| Author |
Topic |
|
nasu
Yak Posting Veteran
50 Posts |
 Posted - 2007-12-20 : 04:03:14
|
| We have a maintenance plan including all our databases. It does all the backing up as expected but backups older than 7 days are not removed as we have set it to do.However, if we create a maintenence plan for a specific database, old backups of that database are removed as intended. Did anyone experience anything like this? We have had an SQL Server expert look at it and he says "this always works". |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-20 : 04:12:42
|
What is the RETENTION TIME value?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-20 : 05:07:55
|
| make sure you are running SQL Server SP4 at a minimum. There are tons of bugs with the maintenance plans and SP4 is the latest release.instead of using the maintenance plan for backups, you might want to use use Tara's isp_Backup stored procedure. you can find that script here http://weblogs.sqlteam.com/tarad/archive/2007/02/26/60120.aspxit is better than the maintenance plan in just about every way.-ec |
|
|
|
nasu
Yak Posting Veteran
50 Posts |
Posted - 2007-12-20 : 05:09:39
|
| I should have said I am not an experienced SQL Server user. Could not find any exact match for that in the help files. What is RETENTION TIME and where do I find it?I am using SP4. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-20 : 05:24:50
|
quote:
Originally posted by nasu
I should have said I am not an experienced SQL Server user. Could not find any exact match for that in the help files. What is RETENTION TIME and where do I find it?I am using SP4.
RETENTION TIME is configured in the maintenance plan wizard.Again, my advice is to not use maintenance plans since they completely suck. -ec |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-20 : 06:23:41
|
| In the log file, there will be an error. Can you please find the error and post it? I have found if you run out of disk space or if a backup fails for some other reason, the files will not be removed. You will get a 112 error if disk space is an issue. |
|
|
|
itsonlyme4
Posting Yak Master
109 Posts |
Posted - 2007-12-21 : 13:32:28
|
| This answer may be a little late but... Look at the properties of your Maintenance Plan - the retention date is located on the 'Complete Backup' tab where it says "remove files older than..." One problem with using one maintenance plan for multiple databases is that if one of the Backups fails for whatever reason then NONE of the old Back files get removed. Just keep an eye out for that |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2007-12-26 : 17:18:24
|
| Awesome advices saves my day again. Im starting to like it here. My backup drive became full as one of my developers decided to put a database offline, instead of detaching it. Which consequently filled up our drive over the holiday. Thanks a ton! |
|
|
|
nasu
Yak Posting Veteran
50 Posts |
Posted - 2007-12-28 : 08:59:44
|
| itsonlyme4 pointed me in the right direction. One of the databases were not successfully backed up and therefore the removal of old backups was not performed. There was an error in the log. If I remove the problematic database from the maintenance plan, old backups are removed as expected. Any idea what makes "Check Data and Index Linkage" fail for the qc_siteadmin_db database below? Does the maintenace plan try to set all databases to Single User mode before the check and that fails because there happens to be another user connected? That would explain why database backups occasionally are not successful (not only backups of the qc_siteadmin_db database). I have been under the impression that backups could be made without disconnecting users (or disconnecting users of the application that ownes the databases)... [9] Database default_vista_db: Check Data and Index Linkage... ** Execution Time: 0 hrs, 0 mins, 13 secs **[10] Database MERCURY_DASHBOARD_900: Check Data and Index Linkage... ** Execution Time: 0 hrs, 0 mins, 1 secs **[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'qcsiteadmin_db'[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.[11] Database qcsiteadmin_db: Check Data and Index Linkage...[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. The following errors were found:[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. ** Execution Time: 0 hrs, 0 mins, 1 secs ** |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-12-28 : 11:18:56
|
| There is no need to disconnect users to make a backup, and you probably do not need to check the database before making a backup.The database integrity check you are doing is failing because it attempts to put the database into single user mode, and it fails if there are any users in the database. If you want to use a maintenance plan to check your databases, it is best not to do it with your backups, because it will cause you backups to fail if it can’t do the check.Also, if you de-select the Integrity check option to “Attempt to repair any minor problems”, it will run the check without attempting to put the database in single user mode. This will ensure that the Integrity check will run to identify any problems. If it does find problems, you can repair the database manually using the appropriate DBCC CHECKDB options.I usually check databases weekly using the DBCC CHECKDB command, instead of using the maintenance plan Integrity check.CODO ERGO SUM |
|
|
|
|
Backups not being removed