SQL 2012 Management Studio hangs clicking Files menu during database restore

by Joe Payne 25. Julie 2014 10:22

I’ve had this nagging issue for months now.   Every time I go to restore a database from a backup file, I cannot click the Files menu in the left sidebar.   This didn’t happen in SQL Server Management Studio 2008.    But it happens every time in SQL Server Management Studio 2012.

I enjoy using SSMS, it works well for my needs.  So it got very frustrating that it would lock up when I needed it most.

I finally took the time to research and thought maybe a newer version had been released since I downloaded it.  So installed the SQL Server 2012 Service Pack 2 update and sure enough, it fixed the problem.

The download link for the Service Pack is http://www.microsoft.com/en-us/download/confirmation.aspx?id=43340

Tags: , ,

Tech Support | Personal

AbleCommerce Gold How To Clean Up Anonymous Users

by Joe Payne 14. April 2014 15:45

Even though Able Gold has a manual cleanup option in the Maintenance page, it doesn’t always work well.  The problem arises from how Able must delete each user individually.  Not such a big deal when you have 500 users to clear out.  

It’s a very different story when you have 2,000,000 unwanted users.   A SQL query can delete all of the unwanted records in a single command.

Below is the updated query to work with Able Gold schema.  Obviously change the dates to something more recent. 

If the queries do not remove as many records as you expected, you might want to remove the “AND (AffiliateId IS NOT NULL)” criteria if you don’t care about affiliate reporting.

use <yourdbname>
DELETE FROM ac_Baskets 
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009'))
 
DELETE FROM ac_Users 
WHERE StoreId = 1 AND IsAnonymous = 1 
AND (AffiliateId IS NOT NULL ) 
AND (LastActivityDate IS NULL OR LastActivityDate <'June 30, 2009')

Tags: , , ,

AbleCommerce Gold | Tech Support

SQL Studio Management Expand Databases Slow

by Joe Payne 14. April 2014 10:53

So on my development PC, I’ve noticed that it takes 2-3 minutes for the Databases node to expand in my SQL Server Studio Management 11.0.2100.60

I don’t remember exactly when it started.   But it’s been several months I think.  It was just too easy to ignore since I could alt-tab and do something else while it worked through the delay.

Today I decided to research it and determined it’s because some databases have auto_close turned on.   This causes the SQL server to have to start up the database before it can render it in the Databases node.    This creates the significant delay when you have several databases configured for auto_close.

A quick way to fix all of them at once time AND set recovery mode = simple (anything more is useless in my dev environment), use this query:

USE MASTER
declare
    @isql varchar(2000),
    @dbname varchar(64)
    
    declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
    open c1
    fetch next from c1 into @dbname
    While @@fetch_status <> -1
        begin
        select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        select @isql='USE @dbname checkpoint'
        select @isql = replace(@isql,'@dbname',@dbname)
        print @isql
        exec(@isql)
        
        fetch next from c1 into @dbname
        end
    close c1
    deallocate c1

Tags: ,

Personal | Tech Support

AbleCommerce Gold Upgrade errors from 7.0.7

by Joe Payne 14. Februarie 2014 11:00

Ran into an odd timeout issue today.  It was disguising itself as an nHibernate problem during the upgrade of an AbleCommerce 7.0.7 store to AbleCommerce Gold R6.

 

The initial error was complaining about nHibernate null Id issues with Catalog.Webpage.  Which made no sense since the upgrade is converting the data, not adding to it.

So I dug into the /install/upgrade.aspx page and saw that indeed new web pages were being added.  And the error was crashing when store.settings.Save() was being called.

I eventually noticed an “errorList” string array variable and exposed it via debug.   It showed me that the SQL upgrade scripts were causing a timeout.   This was silently crashing the upgrade.

After a quick Google search, I found the solution.  In the RunScript() routine within Upgrade.aspx.cs, you have to increase the command timeout value.   Apparently the database I was upgrading was enormous and my local SQL server isn’t really known for speed.  Combine the two factors and you can easily result in timeouts.

Note the addition of setting the CommandTimeout parameter below:

try
{
    SqlCommand command = new SqlCommand(sql, conn);
    command.CommandTimeout = 300;
    command.ExecuteNonQuery();
}

Tags: , , , ,

AbleCommerce Gold | Tech Support

How to remove full text catalog index from SQL 2005 database

by Joe Payne 23. Januarie 2014 09:00

When moving an older AbleCommerce 7 database from SQL 2005 to a newer SQL server, you might run into errors with the ac_searchcatalog full text index catalog file.   SQL 2008 doesn’t store FTS the same way it was done in SQL 2005.

The issue usually appears when you try to restore the database backup from 2005 to 2008 or greater.

To fix it, you have to remove the FTS file associated with the database.   But it’s not part of the SQL backup, so you have a problem if you didn’t copy the index file separately.   Here’s how to get rid of the index file reference.

Go into the old SQL server and run as follows:

USE <dbname>

SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0


For each returning table name (which are the tables with Full-Text Index), run this command:
EXEC sp_fulltext_table ‘tblName’, ‘drop’


After all table index references are dropped, get rid of the Full-Text Catalog with:
DROP FULLTEXT CATALOG catalogName

After this is done, don’t forget to remove the file by right-clicking the database, choosing Properties, and then click Files.

Tags: , , ,

AbleCommerce Gold | AC7 Articles | Tech Support

Month List