Daves Blog

There may come a time that you get errors, indicating corruption, in to your MS-SQL database.  Do not panic, as most of the time these are recoverable.

The first place to start is using the database check utility, and that is what I am going to discuss today.

The MS-SQL database check utility is DBCC.  There are several options that can be run via DBCC, but the place to start is with the checkdb.  The checkdb function of DBCC checks several different areas of the database.  The MS-SQL documentation states that checkdb:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

That is a pretty thorough checking of the database, and should uncover any issues with corruption, or database consistency.

The basic command is “DBCC checkdb(‘Database-name’)”.  So, if you have a database called “MyDatabase”, running “DBCC checkdb(‘MyDatabase’)” will do all of the checking mentioned above on your database.

I will warn you, however, that you will get a report on every table, index, and such, as to what checkdb finds.  Since we are concerned here with finding any problems, and not looking at a list of the possibly hundreds of tables that do not have any issues, let’s tell DBCC and checkdb not to report those.  This is done with the “WITH NO_INFOMSGS” clause.  So, the full command is now “DBCC checkdb(‘MyDatabase’) WITH NO_INFOMSGS”.  Now, what you will see is information for only tables that have any corruption or consistency issues.  Hopefully, you do not see anything reported, which means that no problems were detected.  If there are problems detected, then it will also report information as to what the problem is, and the minimum repair level that would be required.  Getting into doing those repairs will be the subject of a future blog.

There may come a time that you get errors, indicating corruption, in to your MS-SQL database.  Do not panic, as most of the time these are recoverable.

The first place to start is using the database check utility, and that is what I am going to discuss today.

The MS-SQL database check utility is DBCC.  There are several options that can be run via DBCC, but the place to start is with the checkdb.  The checkdb function of DBCC checks several different areas of the database.  The MS-SQL documentation states that checkdb:

• Runs DBCC CHECKALLOC on the database.
• Runs DBCC CHECKTABLE on every table and view in the database.
• Runs DBCC CHECKCATALOG on the database.
• Validates the contents of every indexed view in the database.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

That is a pretty thorough checking of the database, and should uncover any issues with corruption, or database consistency.

The basic command is "DBCC checkdb(‘Database-name’)".  So, if you have a database called "MyDatabase", running "DBCC checkdb(‘MyDatabase’)" will do all of the checking mentioned above on your database.

I will warn you, however, that you will get a report on every table, index, and such, as to what checkdb finds.  Since we are concerned here with finding any problems, and not looking at a list of the possibly hundreds of tables that do not have any issues, let’s tell DBCC and checkdb not to report those.  This is done with the "WITH NO_INFOMSGS" clause.  So, the full command is now "DBCC checkdb(‘MyDatabase’) WITH NO_INFOMSGS".  Now, what you will see is information for only tables that have any corruption or consistency issues.  Hopefully, you do not see anything reported, which means that no problems were detected.  If there are problems detected, then it will also report information as to what the problem is, and the minimum repair level that would be required.  Getting into doing those repairs will be the subject of a future blog.

CCS recommends using the licensed versions of Microsoft SQL server products to get the full feature set and the most current patch level support.  In some cases, the SQL Server Express Edition (SSE) may be adequate for your needs.  As with most community version or express version software there are limitations.  Often this is a major feature like automatic task scheduling or a critical monitoring capability.  There limitation that SSE has is you cannot run the SQL Agent and schedule jobs.

To automate the SQL maintenance with SSE, you use stored procedures, combined with the Windows task scheduler.  I create stored procedures to do the maintenance tasks that I want to do, such as rebuilding indexes, updating statistics, and backing up the databases.  Personally, I prefer to do each task as a separate stored procedure.  This avoids the monolith procedure that is a nightmare to maintain.  These would be the same stored procedures that I would use for a full SQL server implementation, where I can use the SQL Server Agent to schedule jobs.

At this point, for SSE, I make one additional stored procedure.  This is one simply calls each of the maintenance procedures in turn.  Instead of creating a job with multiple steps, you simply call this procedure to perform all of the SQL maintenance tasks.

Now to be able to schedule the maintenance procedure to automatically run.  The key here, is to install the SQL command line utility (sqlcmd).  This utility allows you to execute SQL commands from the Windows command line.  It is simply a matter of using “sqlcmd” to execute the stored procedure which calls all the maintenance tasks.

The final piece to the puzzle, is to create a batch file with PowerShell or another tool that runs the sqlcmd line.  Once the batch file is done, use the Windows task manager, and scheduling the batch file to be run at the desired time.

Although this approach is easy in concept there can be several syntactical gotchas and quirks of the task scheduler to work through.   For example, Windows and SQL security requirements.  If you wish to explore the SSE alternative contact CCS support.

Dave.

In order to keep your Counterpoint SQL system running efficiently, there are a few maintenance tasks that need to be done.  These need to be performed on an on-going basis.

First, backing up all databases should be a top priority.  Although this does not directly improve the performance of Counterpoint SQL, you will be very glad to have these in that rare case that your SQL database gets corrupted beyond repair.  It does not happen often, but it can happen.  Much better to make database backups at least once a day.

Maintenance for Counterpoint SQL performance, two things that we have found to have the biggest impact on performance, are indexes and statistics.  What we have found, is that in order to keep Counterpoint SQL performing well, rebuilding indexes and updating statistics needs to be done frequently.  In fact, I recommend that it be done on a nightly basis.  Doing this frequently allows your SQL server to locate records in the most efficient manner.  Sometimes it is nothing short of amazing to see the difference in the speed of your lookups, just by doing these two tasks.

Something else that is often recommended, is shrinking the database.  I do not recommend this, unless a large amount of data has been purged, and you are looking to recover the space.  If you do shrink your database, make sure that you allow ample empty space.  If you shrink it, without allowing the empty space, your SQL server will incur significant overhead because it will frequently be needing to increase the database size automatically in small increments.

We have put together the SQL scripts to setup and schedule these tasks.  By scheduling these to run every night, your Counterpoint SQL performance will be kept at a high level.  Contact us to set them up on your system.

Dave.

There are a couple of tuning settings that you should change keep your Counterpoint SQL installation running efficiently.  Starting with the SQL server itself, there are a couple of settings that can help.

First, is the memory usage.  The default, is a minimum of zero, and a maximum of all memory.  It is much better to set these to specific values.  Since the operating system should have a couple of gigabytes to work with, I like to set the minimum and maximum a couple gigabytes below the installed memory.  So, say given eight gigabytes of RAM, I would set both the minimum and maximum memory to 6,144 (1,024 x 6) megabytes.  By setting both the minimum and maximum to the same value, the need for the server to try to dynamically allocate and deallocate memory is alleviated.  It can just work on processing the SQL transactions.

The second setting that I try to use, is the SQL priority boost.  If you are running a dedicated SQL server, and are not using the same computer for other processing, then definitely set the priority boost.  I will give the SQL programs a slight edge on using resources over other processes that run.  These other processes would be some of the Windows housekeeping, and background processing.

If you are using your SQL server for other processing, also, then using the SQL priority boost would need to be evaluated.  Many times it can be turned on, without significant impact on the other processing that you are doing.  Turn it on, and see how it goes.  If it does slow your other processing too much, it can be turned off again.

Dave.

The bad guys are at it again, with a really simple, yet often successful, scam.  The scam is setup when someone scans and prints a barcode for one item, and then pastes that barcode over the barcode for a more expensive item.  At checkout, when the barcode is scanned  of course it rings in as the less expensive item.

Some scammers will take it even further.  They look for discarded receipts, with a high end product on it.  Then, they buy that high end product, using the barcode scam. Later, they remove the fake barcode and return the item using the receipt they found.  Of course during the return, since the real barcode is being used, the item is returned for the real price, and the scammer pockets the difference.

In this case, your sales people need to have at least a rough idea of what your items sell for, especially the higher priced ones.  It is not a matter of knowing all the prices, but if an item comes up for $19.99, and the sales person knows that it is actually over $100.00, that will should set the bells of.  Just being able to recognize that there is a large discrepancy is enough.  After all, the scammer is probably not going to try to scam an item that sells for 79.99, at 75.99, but more likely for something like 14.99.

This scam brings up, yet again, how important it is for your sales people to be aware.  They are, after all, your first line of defense against many types of loss.  

Dave.

I recently worked with a customer who had their Offline Ticket Entry (OLTE) stop updating.  When investigating the cause, I found that they had renamed the workstation that was running OLTE.  While changing the name to something more meaningful within their network structure made sense, OLTE immediately quit communicating with the server.

The ultimate reason for this, turned out to be that CP-Services is using the internal SQL server name.  Since this name was set in SQL when SQL Server installed, it was the original name of the workstation.  So, when CP-Services started the process of synchronizing with the main server, it was attempting to connect to a SQL server at the original workstation name, which of course did not exist.

In this case the resolution was simple enough, in changing the internal server name with SQL Server on the workstation.  However, this does point out how a seeming innocuous change in one area, can throw other parts of your system into a tailspin.  The best course of action, before changing anything within your network, is to fully research and evaluate the system, or systems, that you propose to change.  Identify any software that could possibly be affected, and the role that software plays in your organization.  Then, once that change is made, test all of those pieces of software to make sure they are still operating as desired.

Remember that CCS Retail Systems Technical Support is always available to help you.  You can reach us at our support department or 800-672-4806.

Dave.

With a little good luck, you will never run into having a corrupt SQL database.  With a little bad luck, it will happen.  Then the question becomes how bad is the corruption, and can it be repaired.

One option, of course, is to restore the database from a good backup (which you should always have).  The second option is to try to recover the database.  The first step, is to check the database, and determine what is needed to repair it.  This is done with the "DBCC CHECKDB" command:

     DBCC checkdb() WITH NO_INFOMSGS

The "WITH NO_INFOMSGS" greatly simplifies the output, as only the messages relating to the corruption are displayed.  In the majority of cases, this will be that a table is corrupt, and it will state what level of recovery is required.  The level of recovery is used in the actual recover command, that I will get to shortly.  In my experience, the "REPAIR_ALLOW_DATA_LOSS" is usually given as the minimum recover level that will work.  

If it is acceptable to try the recovery, with the possible loss of, usually, a small amount of data if any, then the repair can be attempted.  If it is not, then the only option at that point would be to restore from a good backup.

To attempt the data recovery, the first thing to do is to backup your database as it is.  First, the database needs to be set to single-user:

     ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then, run the "DBCC CHECKTABLE" command, for each table that was reported as having corruption:

     DBCC checktable(,REPAIR_ALLOW_DATA_LOSS)

When the table check finishes, it will report whether or not it was able to repair the table.  If repairs were successful, then the database needs to be put back into multiuser mode:

     ALTER DATABASE SET MULTI_USER

Your database is now functional again.  The only question remaining, is what, if any, data was lost.  For this, you will have to determine how and what to check for.

If you have a retail store, you already know that there is a lot of training required for most of your new employees.  I am often amazed, however, when a store employee does not know how to log into the computer, or register, that they need to use for their job.

It appears that many times these computers are left logged in all of the time.  This is a very bad practice, for several reasons.  Most of which I have gone over before, but include security risks, and an increased risk of corruption in the event of a power loss or system crash, due to more files being open.

For those that do log out, most have their Windows system set to default the username to the last one that logged in.  So, users simply have to type in the password to log in.  These users know the password, but a large number of them do not know the correct username to use.  In that case, if someone else logs in, say the administrator for system maintenance, the user then can not get logged in because they do not know what to change the user name to.  Or, in fact, that they need to change the username.

This is such a basic part of what employees need to do to perform their duties.  It should be stressed to employees how to properly log in.  Also, it should be part of the “refresher” training that employees should be having periodically.

No one wants to experience corruption anywhere on their system, especially in their SQL databases that are used for business critical applications.  Unfortunately, it does happen at times, though.

One of the most common causes of corruption of the database files that I see, is improper shutdown.  To many people, “rebooting” means to power the computer off, and then back on.  This is a tremendously good way to corrupt a database.  Many times, the reason for restarting the computer is that the user thinks that the database is hung.  In fact, a large percentage of the time the database is not hung, but is instead extremely busy.  This could be due to a poor query, or a scheduled job.  Whatever the cause, what is really happening in these cases is that the database is so busy trying to process, that it can not keep up with other ongoing requests.  Then, in the middle of all of that reading and writing, the power is shut off, and the database server has no chance to exit gracefully.

Of course there are times that a server does truly freeze, and there is no choice but to power off.  That should only be done if there is no response of any kind, and that sufficient time is allowed for a response.  That is another key:  Allowing enough time for a response.

Too many times, a user will actually try to do a proper shutdown, but when the shutdown is not done in a short amount of time, say 30 seconds, they assume that the server is “hung” and power off.  If it is the case where the database server is extremely busy, what usually is in fact happening, is that the database server is trying to get to a point where it can quit.  In other words, it is busy writing information that gives it a checkpoint, and losing power at this point is one of the best ways to corrupt a database file.

If there is any response at all, and you start a shutdown, try to determine if there is activity going on.  Does the system still respond to mouse movements? Do characters show when you hit keyboard keys?  Is the disk activity light flashing at all (even if just barely), and not just on solid?  All of these are indications that the system is still running, and simply overloaded with work.  In that case, give it more time to finish, and you may avoid any corruption in your database files.