ONTAP Discussions

SMSQL "AUTO_SHRINK is not a recognized SET option" error after upgrade to version 5.1

marcconeley
3,740 Views

Hi everyone,

I am running an SQL 2008 server on Windows 2008 R2 and after ugprading SnapManager from 5.0R1P2 to 5.1 I am getting the following errors logged in the SMSQL backup logs:

[05:03:31.460]  ALTER DATABASE MYDB__Clone SET AUTO_SHRINK OFF
[05:03:31.462]  Incorrect syntax near '-'.
'AUTO_SHRINK' is not a recognized SET option.
[05:03:31.463]  Failed to disable AUTO_SHRINK option.

The backups are completing, and showing as fully verified backups, but it seems that new version of SMSQL is mounting the snapshots for verification, and then running an "alter database" command against them which isn't actually valid.

Is anyone else experiencing this problem?

It looks to me like a bug in 5.1.

//Marc

4 REPLIES 4

qzhang
3,740 Views

Set Auto_shrink off during verification is an expected behavior in 5.1. The issue here is that the new database on the verification server instance does not recognize AUTO_SHRINK option for some reasons. Is the verification server a local server? You could try to run this T-SQL "ALTER DATABASE dbname SET AUTO_SHRINK OFF" manually to check to see if this is MSFT issue or not.

Thanks,

-Qing

marcconeley
3,740 Views

The verification server is the local SQL 2008 server in this case.

But surely any "alter statement" will fail on verification as the snapshot LUNs being mapped, and which are used for verification are read-only??

If I manually run this SQL on our server (I just tested) it executes without error on all of the databases.

(also, the "auto shrink" option is already set = FALSE on all the databases located on this server.

mg
NetApp
3,740 Views

Hello,

Here's my contribution:

I just had the same error message while trying to migrate Sharepoint databases. It worked for most of them, but some indicated a failure:

ALTER DATABASE Managed Metadata Service_fbc105bae1454273bd7d3180cc1e040a SET AUTO_SHRINK OFF
Incorrect syntax near 'Metadata'.
'AUTO_SHRINK' is not a recognized SET option.
Failed to disable AUTO_SHRINK option.
Failed to attach database from new file paths, re-attach the original database files...

=> for this one, I think we're missing quotes around database name (because it has a space in it)

ALTER DATABASE SharePoint_AdminContent_690fc0f1-8783-4679-a0c4-47819b205f4c SET AUTO_SHRINK OFF
Incorrect syntax near '-'.
'AUTO_SHRINK' is not a recognized SET option.
Failed to disable AUTO_SHRINK option.
Failed to attach database from new file paths, re-attach the original database files...

=> no clue for this one, SQL seems to be unhappy with the dash in the name

ALTER DATABASE User - Profile DB SET AUTO_SHRINK OFF
Incorrect syntax near the keyword 'User'.
'AUTO_SHRINK' is not a recognized SET option.
Failed to disable AUTO_SHRINK option.
Failed to attach database from new file paths, re-attach the original database files...

=> for this one, I think we're missing quotes around database name (because it has a space in it)

ALTER DATABASE User - Sync DB SET AUTO_SHRINK OFF
Incorrect syntax near the keyword 'User'.
'AUTO_SHRINK' is not a recognized SET option.
Failed to disable AUTO_SHRINK option.
Failed to attach database from new file paths, re-attach the original database files...

=> for this one, I think we're missing quotes around database name (because it has a space in it)

ALTER DATABASE WebAnalyticsServiceApplication_ReportingDB_0f2d1027-76be-4c49-878b-b46edff389b4 SET AUTO_SHRINK OFF
Incorrect syntax near '-'.
'AUTO_SHRINK' is not a recognized SET option.
Failed to disable AUTO_SHRINK option.
Failed to attach database from new file paths, re-attach the original database files...

=> no clue for this one, SQL seems to be unhappy with the dash in the name

So the problems above seem to be related only to the database name, we should probably quote the db name when running SQL statement...

The strange thing is that all databases got migrated to NetApp LUN in the end.

Kind regards,

Michel

qzhang
3,740 Views

To workaround this issue, add a REG_DWORD value named "NotOffAutoshrink" under HKEY_LOCAL_MACHINE\SOFTWARE\Network Appliance\SnapManager for SQL Server\Server

And set its value to 1.

Thanks,

-Qing

Public