6.3 always asks to upgrade cache, & wipes all server History

PDinCA · January 13, 2010 3:57PM

Running 6.3.0.48 on Windows 7 against SS2008 SP1 under WS2008 and SS2008 under WS2003 and SS2000 under WS2003.

Each time I fire up the UI, I get the request to upgrade the cache, which I do.

When the UI presents the servers, there is absolutely NO History for any of the servers.

I know that the Jobs are running as I'm monitoring each server with SQL Response and one job just failed, but that's not visible via SQL Backup, but is visible in the JOB History via SSMS.

This is a brand new W7 install, thereby a fresh install of SQL Backup, having added 2 Groups and the relevant Production and Development servers to each (3 Prod, 3 Dev for now).

What's up?

RBA · January 14, 2010 12:52PM

I'm not sure if this is what you're experiencing, but if the local data cache is present but inaccessible (locks, permissions etc.) you'll experience symptoms such as you describe.

The first thing I'd do is have a look to see if anything is happening to those files via Process Explorer's handle search or a similar tool.

Regards,

PDinCA · January 14, 2010 1:36PM

From a permissions perspective, I have full admin rights on this box. I also have "Effective Permissions" of "Full Control" all the way down the c:\...\AppData\ folder tree, including the localDataCache.dat file.

I am concerned that SQL Backup should have issues every time I use it. I only have Microsoft's Security Essentials running, alongside Windows Defender. I do not have Symantec's products, like I had when this box was an XP Pro machine. I have excluded C:\Users\<<user>>\AppData\Local\Red Gate\ and the child \SQL Backup folder from monitoring by Essentials.

These actions have had no effect at all.

On firing up SQL Backup, again, I chose "Later" on the latest invoke of the UI and now have 1.dat, 2.dat, 3.dat and the localDataCache.dat files in the \Server Data folder. Within the UI:

1. Expanded the 64-bit Server node. It initially displayed the history in green with what appeared to be correct icon-heights (transaction log backup - short and green). NOTE: Correction - It appears that "History" presentation is "inconsistent", SQLB doesn't always "wipe server history". 2. The node automatically refreshed. Now the markers are full height, and grey, and each says I have 2 items that were successful against every database that should have activity. However, there were NOT 2 items, just ONE, and "grey" means "what?" 3. I closed the UI. 4. I opened the UI -IT EXCEPTIONED!!!:

SQLBackup

Unable to open the database file
unable to open database file
WorkerExecutionException

   at RedGate.SQLBackup.Engine.ConfigurableThreadPool.a&#40;Exception &#41;
   at RedGate.SQLBackup.Engine.ConfigurableThreadPool.a.b&#40;&#41;
   at System.Threading.ThreadHelper.ThreadStart_Context&#40;Object state&#41;
   at System.Threading.ExecutionContext.Run&#40;ExecutionContext executionContext, ContextCallback callback, Object state&#41;
   at System.Threading.ThreadHelper.ThreadStart&#40;&#41;

----------

SQLiteException

Unable to open the database file
unable to open database file

   at System.Data.SQLite.SQLite3.Reset&#40;SQLiteStatement stmt&#41;
   at System.Data.SQLite.SQLite3.Step&#40;SQLiteStatement stmt&#41;
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery&#40;&#41;
   at g.g&#40;Server &#41;
   at g.b&#40;Object &#41;

THIS PRODUCT IS BROKEN!

"Process Explorer" is a black box to me. What EXACTLY is one supposed to do to "...see if anything is happening to those files via Process Explorer's handle search or a similar tool."?

Also, frequently, when I expand my Production Group, the 64-bit SQL Cluster shows a red-X, and I get:

System.NullReferenceException: Object reference not set to an instance of an object.
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at RedGate.Shared.SQL.Server.SQLServer.GetDatabasesEx(Boolean forceRefresh)
at RedGate.Shared.SQL.Server.SQLServer.GetDatabasesEx(String server, Boolean integratedSecurity, String username, String password, Boolean forceRefresh)
at RedGate.SQLBackup.Engine.Server.a()
at RedGate.SQLBackup.Engine.Server.RefreshDatabases()
at RedGate.SQLBackup.Engine.Server.RefreshServerInformation()
at RedGate.SQLBackup.Engine.Server.OpenConnection(Boolean updateData)

It appears to clean itself up almost immediately, but surely this is not correct behavior...

RBA · January 15, 2010 4:55AM

Hi,

I'm sorry that you're experiencing all this. The second exception I presume is of the form that does terminate the application and instead appears in the error explanation popup if you click the red X? If this is the case, then an unexpected communication break between SQB and SQL Server occurred.

Since the other issue affects you more prominently, we should try to understand that first.

Process Explorer is a Microsoft Sysinternals tool that gives you an overview of the processes running on your computer. It acts as a sort of extreme version Task Manager and the feature I find most useful is its handle search. It does not require installation to run (it comes as just an .exe pretty much) and the handle search can be accessed by pressed CTRL-F. This pops up a search dialogue where you can enter a filename (such as localDataCache.dat, 1.dat etc.). In the results panel you'll see the full list of processes that currently have that file open, including internal windows processes like svchost.exe. If there are any processes other than SQL Backup accessing the local data store, then this might explain the undesirable behaviour.

Since, even with a fresh install, the UI pulls history from the remote SQL Backup Agent it might also be worth checking the validity of those records. The easy way to do that would be to install the UI on another workstation and seeing if it can display the backup history as expected. If it cannot, then we know that the backup history of one or more of your servers is not in a state that SQL Backup can currently handle. The data.sdf (SQB Agent's datastore) might be corrupted or have confusing entries. In this case, you should delete this store and try again.

As for the unexpected grey entries, could you please provide a screenshot?

Regards,

Brian Donahue · January 15, 2010 8:15AM

You may want to try running Red Gate Support's SQL Backup data store repair tool on the SQL Server. This can diagnose problems with, compact, and (attempt) to repair the local data store.

ftp://support.red-gate.com/utilities/Re ... aStore.zip

Second, I have also observed that sometimes, the console cannot connect to a server when it is first started, but this clears up when a refresh occurs. This happens when querying the server for database names, and for some strange reason a query result (most likely a database name) comes up blank:

SELECT name, cmptlevel, sid, status FROM dbo.sysdatabases ORDER BY name

To know for sure what value is missing, we would probably need to run a debugger against SQL Backup UI. I can tell you how if you're interested.

PDinCA · January 15, 2010 1:28PM

I tried to run the repair utility on my local machine as the readme.txt says the utility will attempt to repair the "local cache". Somewhat predictably, as SQL Backup SERVER Components are not installed on my laptop, I was unable to repair anything for (local) or SQLEXPRESS instances.

I copied the utility to the SQL cluster and invoked the .exe. NO instances were listed, which is concerning as there definitely is a Production SS2008 instance on the box. I overtyped the instance area with (local) and hit Verify:

See the end of this message for details on invoking 
just-in-time &#40;JIT&#41; debugging instead of this dialog box.

************** Exception Text **************
System.Runtime.InteropServices.COMException &#40;0x800706BE&#41;
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal&#40;Int32 errorCode, IntPtr errorInfo&#41;
   at System.Management.ManagementObject.InvokeMethod&#40;String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options&#41;
   at RepairSQBDataStore.Program.ClusterResourceOnOff&#40;String InstanceName, Boolean BringOnline&#41; in C:\Users\brian.donahue.RED-GATE\Documents\Visual Studio 2008\Projects\RepairSQBDataStore\RepairSQBDataStore\Program.cs:line 154
   at RepairSQBDataStore.Program.StopSqlBackupAgent&#40;String InstanceName, Int32 TimeoutMs&#41; in C:\Users\brian.donahue.RED-GATE\Documents\Visual Studio 2008\Projects\RepairSQBDataStore\RepairSQBDataStore\Program.cs:line 126
   at RepairSQBDataStore.Form1.butVerify_Click&#40;Object sender, EventArgs e&#41; in C:\Users\brian.donahue.RED-GATE\Documents\Visual Studio 2008\Projects\RepairSQBDataStore\RepairSQBDataStore\Form1.cs:line 154
   at System.Windows.Forms.Control.OnClick&#40;EventArgs e&#41;
   at System.Windows.Forms.Button.OnClick&#40;EventArgs e&#41;
   at System.Windows.Forms.Button.OnMouseUp&#40;MouseEventArgs mevent&#41;
   at System.Windows.Forms.Control.WmMouseUp&#40;Message& m, MouseButtons button, Int32 clicks&#41;
   at System.Windows.Forms.Control.WndProc&#40;Message& m&#41;
   at System.Windows.Forms.ButtonBase.WndProc&#40;Message& m&#41;
   at System.Windows.Forms.Button.WndProc&#40;Message& m&#41;
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage&#40;Message& m&#41;
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc&#40;Message& m&#41;
   at System.Windows.Forms.NativeWindow.Callback&#40;IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam&#41;


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3603 &#40;GDR.050727-3600&#41;
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
RepairSQBDataStore
    Assembly Version: 1.0.0.0
    Win32 Version: 1.0.0.0
    CodeBase: file:///C:/Install/Red%20Gate/CacheRepair/RepairSQBDataStore.exe
----------------------------------------
System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 &#40;netfxsp.050727-3000&#41;
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 &#40;netfxsp.050727-3000&#41;
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 &#40;netfxsp.050727-3000&#41;
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Management
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 &#40;netfxsp.050727-3000&#41;
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
System.ServiceProcess
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 &#40;netfxsp.050727-3000&#41;
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.ServiceProcess/2.0.0.0__b03f5f7f11d50a3a/System.ServiceProcess.dll
----------------------------------------
System.Data.SqlServerCe
    Assembly Version: 3.5.1.0
    Win32 Version: 3.5.5692.0
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Data.SqlServerCe/3.5.1.0__89845dcd8080cc91/System.Data.SqlServerCe.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time &#40;JIT&#41; debugging, the .config file for this
application or computer &#40;machine.config&#41; must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

&lt;configuration&gt;
    &lt;system.windows.forms jitDebugging="true" /&gt;
&lt;/configuration&gt;

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

I'm obviously doing something wrong! The cluster is 64-bit BTW.

PDinCA · January 15, 2010 1:40PM

As far as running SQL Backup UI on another box goes, I can run it on the 64-bit WS2008/SS2008 box. Everything appears INITIALLY normal and I'm not solicited to upgrade the cache each time, and the History is complete and correctly represented (color and height of lines). HOWEVER, on the first automatic Refresh, the history lines all go full-height and grey! I'll attempt to screen-capture and email to you...

The Exception I copied re the failure to open the database file DOES result in a complete failure of the UI. Perhaps, rather than exception and die, the UI should "give an informational status area, perhaps in the UI's status area at the foot of the window, and retry the operation"??? To die like this is rather unfriendly in light of the problem being an "unexpected communication break" (to quote you), which we all experience from time to time... "Fix pending" perhaps?

PDinCA · January 15, 2010 1:45PM

Screen-shot emailed.

Grey-full-height occurs on first and subsequent refreshes of an expanded Server - ANY server.

Brian Donahue · January 15, 2010 1:47PM

Obviously the utility needs to be tested on 2008 x64 clusters (I wrote this and sorry to say I'm not a "professional programmer"). You also need to have sufficient rights to WMI and the fact that the instance didn't show up means either you don't have rights to view/manage the WMI object or Microsoft have rearranged the schema for the MSCluster resources again -- but I did test this on a 2008 32-bit cluster.

You can do the same things using SSMS if you have SQL Server 2005, but if you try to open the SDF as a SQL Compact database with SSMS2008, it will try to upgrade the data store and you don't want to do that!

PDinCA · January 15, 2010 1:50PM

Thanks for the unexpectedly rapid response and warning about SS2008.

If your tech squad would like to remote in, like last time, I'm happy to oblige...

Enjoy the weekend.

RBA · January 18, 2010 4:50AM

Hi,

I received your email this morning depicting the grey bars. These represent backup or restore operations that are too close together in time to represent with the current scale. If you zoom in far enough, they should eventually resolve into one or more green, blue or red coloured markers as appropriate. E.g.

I suspect that the UI is functioning correctly and it is the server-side data store that is problematically returning additional, erroneous activity history. If two operations are reported as happening at the same time, then naturally no amount of zooming will reveal the contents of the greyed operations.

SQL Backup also inspects SQL Server's backup history tables (e.g. msdb.dbo.backupfile, ...restorefile, etc) which, for whatever reason, may contain entries SQB is not familiar with.

If you could email or link me a compressed backup of your msdb and a copy of the data.sdf from one of your servers this might allow me to set up an instance that displays the same problems you're experiencing.

To temporarily alleviate the problem, you can purge the history by deleting the following (in order). This should not interfere with log shipping.

1) the contents of the five backup related tables and three restore related tables in MSDB
2) the data.sdf (whilst the SQB service is not running)
3) the local cache on any workstations with the UI installed

Regards,

PDinCA · January 18, 2010 12:29PM

Location of the data.sdf file, please? Searched the Production cluster's c: drive and no file found...

RBA · January 18, 2010 12:52PM

Hi,

On a cluster the data.sdf will be on the shared disk resource. It's exact location is referenced by

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\[INSTANCE]

(on a 32-bit machine, remove "Wow6432Node\").

As per your email, the relevant MSDB table names are:

msdb.dbo.backupfile
msdb.dbo.backupfilegroup
msdb.dbo.backupmediafamily
msdb.dbo.backupmediaset
msdb.dbo.backupset
msdb.dbo.restorefile
msdb.dbo.restorefilegroup
msdb.dbo.restorehistory

Regards,

PDinCA · January 18, 2010 3:00PM

Slight problem...

Step 2 of your "temporary" solution is being blocked...

To temporarily alleviate the problem, you can purge the history by deleting the following (in order). This should not interfere with log shipping.

1) the contents of the five backup related tables and three restore related tables in MSDB
2) the data.sdf (whilst the SQB service is not running)
3) the local cache on any workstations with the UI installed

I stopped the SQL Backup Agent and went to the C:\ProgramData\Red Gate\SQL Backup\Data\(LOCAL) folder to rename the data.sdf but was prevented. How does one free up the file as I'm stymied trying to access the Compact Edition in any way in an effort to, perhaps, run some DELETEs... Obvious lack of CE knowledge exhibited here :?

RBA · January 19, 2010 4:47AM

Hi,

What was the message given to you when you tried to alter the file? If it's a lock, the Process Explorer application linked above should tell you what's currently locking the file. You can then decide to either terminate that process or if possible work around as best you know (i.e. if it's anti virus, configure to leave data.sdf alone).

Regards,

PDinCA · January 19, 2010 1:23PM

Despite having stopped the SQB Core Service, it restarts itself and re-locks the data.sdf file...

DISABLING Restart means that any scheduled SQL Backup Job will CRASH!

SQL Backup failed with exit code: 5148  SQL error code: 0 &#91;SQLSTATE 42000&#93; &#40;Error 50000&#41;.  The step failed.

Is there opportunity here for a more friendly message, as in "The SQL Backup Service is not running on the Server, backups cannot be performed."...?

Anyway, the UI on the 64-bit cluster appears to be showing correct information now.

HOWEVER, the UI on my Windows7 box just barfed (a.k.a., DIED), again, despite my having deleted all local cache files just after I deleted them from the cluster:

SQLBackup

Unable to open the database file
unable to open database file
WorkerExecutionException

   at RedGate.SQLBackup.Engine.ConfigurableThreadPool.a&#40;Exception &#41;
   at RedGate.SQLBackup.Engine.ConfigurableThreadPool.a.b&#40;&#41;
   at System.Threading.ThreadHelper.ThreadStart_Context&#40;Object state&#41;
   at System.Threading.ExecutionContext.Run&#40;ExecutionContext executionContext, ContextCallback callback, Object state&#41;
   at System.Threading.ThreadHelper.ThreadStart&#40;&#41;

----------

SQLiteException

Unable to open the database file
unable to open database file

   at System.Data.SQLite.SQLite3.Reset&#40;SQLiteStatement stmt&#41;
   at System.Data.SQLite.SQLite3.Step&#40;SQLiteStatement stmt&#41;
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery&#40;&#41;
   at g.g&#40;Server &#41;
   at g.b&#40;Object &#41;

To be clear:

1. Followed each step of your "temporary" fix. 2. Step 3 was executed on the cluster, then on the W7 box. 3. The SQB Service was restarted. 4. The UI was started on the cluster, waited until activity occurred and verified the presentation was correct. 5. Closed all programs on the cluster for my session and logged off. 6. Opened the UI on my W7 box. 7. The "usual" red-X appeared on the cluster (the one I'd just successfully fixed). 8. The UI Exceptioned.

RBA · January 20, 2010 5:42AM

I should add that any clustered service will be monitored by the clustering service which will automatically try to restart it if it's seen to have failed. To prevent this and turn off such a service you have to do it from cluster failover manager (right click the SQL Backup resource and click 'take offline').

The issue with the red 'X' is being looked at (this glitch has been seen to occur on very new / fast computers).

6.3 always asks to upgrade cache, & wipes all server History

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

6.3 always asks to upgrade cache, &amp; wipes all server History

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk

6.3 always asks to upgrade cache, & wipes all server History