SnapManager for SQL databases shown as unknown and backups are skipped

[heinowalther]

Hi there

I have an SMSQL 6.0.1 installation on a SQL 2008 server.  All databases are located on the same LUNs (K: for DB and G: for LOGs) N: for SnapInfo

Everything by the book.

When browsing the "Backup" option in the GUI, I can see that most of the databses are online and I can select them, but there are also 5 databases which I cannot select, and they are shown with the (Unknown) behind their name.

When backing up the server via schedule where I select the whole server, in the logs the databases mentioned are "Skipped".

Only difference I can see is that they are SQL Version 2000 instead of 2008, but I tried to create a new database with also version 2000 which are backed up OK.

This is not mentioned anywhere in the manuals 😞

I also tried to take a backup using SQL and dump to a file, which works OK, so I guess the databases are OK and can be backed up.

The databases are in "Single" mode...

They are fairly small databases...

In the debug logs I also cannot find anything...

Has anyone hit this problem ?

/Heino

[olson]

Greetings,

          Operational databases should not be in single user mode. You may want to check with your dba's to understand if they are performing a restore or batch operation that requires single user operations. Also, Check the permissions on the database to ensure proper rights have been granted to the snapmanager user.

Hope this helps,

John

[heinowalther]

Hi John

I forgot to mention, that I have also checked the user rights, both inside SQL where I have even given the user explicit dbowner rights to the databases in question, and also on the database files themself.

Please note that I am able to login with the SMSQL service user, and do a backup from the SQL Server GUI...

What is more perplexing is that I have found out, that when using the Scheduler where I point to the SQL Instance itself, and no specific databases as in the SMSQL GUI, one of the 5 databases in question is actually backed up...  but still remains "Unknown" in the SMSQL GUI..   I am able to go under restore, and see the backup made... 

I will include screenshots, and logfiles to support all this, in a short while.

/Heino

[heinowalther]

Here is a cutout of the debug logs:

[05/15 10:35:23 136] Skip database [db1] for the following reasons: pDatabase->bBad_DBs = 1, pDatabase->cFGs = 0, pDatabase->cLogFiles = 0

[05/15 10:35:23 151] Skip database [db2] for the following reasons: pDatabase->bBad_DBs = 1, pDatabase->cFGs = 0, pDatabase->cLogFiles = 0

[05/15 10:35:23 151] Skip database [db3] for the following reasons: pDatabase->bBad_DBs = 1, pDatabase->cFGs = 0, pDatabase->cLogFiles = 0

[05/15 10:35:23 151] Skip database [db4] for the following reasons: pDatabase->bBad_DBs = 1, pDatabase->cFGs = 0, pDatabase->cLogFiles = 0

I have tried to google this, but cannot find anything...

Here is how it looks from SMSQL GUI...

Ignore tempdb which is on local non netapp disks..

And trust me.. the databses marked as Unknown all have their files on the same disks as the other databases which works...

Very strange 🙂

[welch]

Heino

Try running through the configuration wizard again without changing anything and see if it lets you complete it.  This will tell you absolutely if there is a configuration issue. 

Also, I assume your databases are in Single User Mode per your original comment.  If they are, SMSQL can not back them up in this mode.  Try removing the Single user mode and running the backup again.

[heinowalther]

It's no in Single User Mode..  it's in "Simple Logging" mode 🙂  And SMSQL is able to backup databases in simple mode just fine...

The strange thing is that I have tried to backup the databases via the SQL Server Manager, works just fine... I then deleted the database, and tried a restore.. also works fine.

But SMSQL still things the database is "faulty" and will not back it up...

I have tried to upgrade the database version from 2000 to 2008, which also didn't make any difference...

I am able to select data out of the database...

There are no errors in the SQL Server logs...

If I set the database offline, then online again, and do a Refresh just after via SMSQL, the database sometimes shows OK, and I am able to back it up... but after a few minutes, it goes back to the same state...   again NO errors anywhere...

Very very strange issue 🙂

[heinowalther]

By comparing the working databases against the non-working I could see that a SQL Option called "Auto Close" was set to "True" on the non-working databases, and to "False" on the working ones...  I have now changed this on the failing databases, and managed to do a backup...  but I then found one of the working databases had this option set to "True"... so maybe it's not the whole truth...   I will keep an eye on this for a few days and get back with a status....

I have no idea what this "auto close" does, I could imagine it closes non-active connections...or maybe the database ?    Would explain why some of the databases works even with this set to "true".. if there is a certain activity on the database all the time, it may not close at all... 

I will investigate this further, and update this later 🙂

/Heino

[olson]

Hello,

I dont think any of the options you have described are causing your issues. I think you have a problem with where your databases are placed on the disk. You can resolve this issue by creating a new volume. Then use snapdrive to create a lun for this sever. Once you have a new lun on a new volume move the databases using Smsql cinfiguration wizard to the new lun. You will want to ensure there is enough space extra space in the volume to hold the snapshot backups normally 2.5 to 3.5 time the disk you need.

Good luck

John

[heinowalther · Accepted solution]

Hi John

I have looked up the "Auto Close" feature, and it is indeed a feature for servers with lagging resources, so if there are no active connections to the database, SQL closes it down, to save memory.

As described I had some databases which had this option set to True, and worked, but on further investigation it turned out that they had some activity, so the database wasn't closed.

Sadly when SMSQL comes along to do a backup, it does not connect to the database, or at least not in a way which opens the database, or maybe it closes right after, I don't know, I can only tell what I am seeing.

After setting this "Auto Close" option to False, the databases showed up normal in SMSQL, and I am able to do backups of them with no problems.

As to the placement of the database files, they are all on the same LUN, except of cause master/model/msdb and tempdb, done by the book, and as mentioned earlier I was able to do backups of the other databases on the same LUN.

But I would call this "solved", may even request an enhancement on NetApp's part, maybe they could connect to the database in a way that would allow the backup to run, and then let SQL close down the database...  but not sure if this is actually a SMSQL issue or a SQL issue....   one thing is for sure, it's not mentioned in the manuals or Now anywhere I was able to find it...  but I guess I'm used to that 🙂

/Heino