SMSQL Not Deleting Snapshots

[israelmmi]

I started to notice that SMSQL was not deleting my Snapshots before/after a backup, even though my backup command had the paramater configured to do so:

new-backup -svr 'SQLServer' -d 'SQLServer', '0' -ver -verInst 'SQLServer' -mp -mpdir 'C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint' -RetainBackupDays 5 -lb -bksif -RetainSnapofSnapInfoDays 5 -trlog -noutm -mgmt daily

So I went to my backup job log, and noticed the following line:

[23:00:31.387]  WARNING: Deletion of the oldest snapinfo snapshot does not proceed, as the backup partially completed, backup of one or more databases was skipped.

I found in the SnapManager® 5.0 for Microsoft® SQL Server Installation and Administration Guide (although I am using v.5.1 already) on pages 176-177:

When you select databases at the SQL Server instance level and one of the selected databases cannot be backed up for an unexpected reason (such as the database being offline or in a loading state at the time of the backup), the full database backup operation progresses as follows:
❖ The backup report includes a message at the beginning of the summary section that indicates that the backup was only partially completed because one or more databases were skipped.

Going back to my SMSQL job log I see the following:

[23:00:22.479]    WARNING: Database [ReportServerSCOMTempDB] of [SQLServer] is not in valid configuration, and will not be included in this backup.

[23:00:22.495]    WARNING: Database [ReportServerTempDB] of [SQLServer] is not in valid configuration, and will not be included in this backup.

I never moved the tempdb for my SQL Reporting Services to a LUN since I figured it had the same rule as the "tempdb" database which I also left on Drive C.

So my questions are:

1. What is the best practice location for SQL Reporting Service TempDBs? Should I just move them to the same LUN as the Reporting Services DB?

2. How is it that "tempdb" is also on local disk but it works regardless, while if these are on local disk it does not? Is that some built-in exception that is ok?

3. Is there a way for the job to show something like that clearly or do I need to enable e-mail configurations for this? Is enabling an Event Log Filter Rule Alert for EVID 453 any better? What do other people do?

Thanks,

Reuv

[qzhang]

Since you did not specify the names of databases to be backed up in your backup cmdlet, SMSQL assumed you wanted to back up all the databases on that server, and there was a built in logic to avoid accidentally deleting backups (The logic can be improved to continue deleting snapshots if databases are in local drives).

The workaround for you is to specify databases you want to backup in the cmdlet explicitly.

Thanks,

-Qing

[israelmmi]

Well - I can't do that. The reason I specifically do NOT list databases is because every time I add/remove a database I then need to modify the script - something which I still think is funny for someone to have to do. So I enabled the '0' paramater to include all databases.

So I understand I should just migrate the temp DBs then for RS to LUNs?

[chadharter]

Just kind of curious if you figured out why you are having this issue.  I'm having the same issue on a test database that is causing the snapshots to not roll off.  It's configured exactly as the other databases.

[israelmmi]

I just moved those databases to my luns instead of keeping them on the local disk. Where are your DBs situated presently?

[bengelstad]

Did this resolve  your issue?

[abhisek]

Hi ,

Please go ahead and follow our best practice on SMSQL. Both ReportServer and ReportServerTempDB are to be treated as any other user database.

Keep the primary and log files of both reportserver and reportservertemp databases on separate luns.

i.e ReportServer and ReportServerTempDb primary files on LUN 1

ReportServer and ReportServerTempDb log files on LUN 2.

Regards,

Abhishek

[lagenevoise]

Hi,

A couple of month ago I create a sql script, witch one list SQL DB per localisation.

I'm would like have several type of retention regarding DB.

So it's possible to generate the command line without the ReportServerSCOMTempDB

If you want I can provide you the Script.

Regard,