Post reply

Strange SQL 2K5 behavior

  • June 21, 2008 at 11:25 am

    #124468

    This one has some history so it's a bit long. If you want to get right to the meat of it scroll down to Current Issue.

    --History--

    We have a SQL 2005 SP2 Standard server running on Win2K3 Enterprise. For many months we have been backing up to a UNC path. Recently we started getting "Verify" errors on the backups (trans log and baks). Also, the files could not be successfully restored when they that error. (Our dev team does a lot of restores from most recent backups.)

    This issues started on a SQL 2k5 server but now another SQL 2K server is doing the same thing, intermittently! Sometimes the email alert says Failure then a minute later sends a Success report.

    Now on to unrelated info:

    We have an SSIS package that loads data nightly from a flat file directly into temp tables that are then parsed and loaded into our DB (payments). This process has been running successfully for many months. Part of the import also emails results to db admins.

    Yesterday morning we noticed that the payments had gone in TWICE. The temp tables were still there and sure enough, every line of the flat file was loaded twice (flat file was fine). To top it off, the emails that went out about the import process mixed up numbers and info between two of the vendors that send these flat files even though the processes are looped and therefore should not overlap. Ugh.

    Also yesterday we started getting two or three emails for each notification. From everything like jobs to SQL reports... etc. We tested database mail by running commands in query window. 1 email. Then made a JOB to send us an email. 1 email again.

    --Currrent Issue--

    So now we have an issue with backups and notifications it seems. We found out that SQL 2K5 does NOT support writing/verifying backups to a UNC path. So we moved them local. NOW we are getting failure and success messages for the same job and the backups are DOUBLE IN SIZE. In other words, System DB was 2 GB and now it's 4 GB. ??? Successful backups are correct size and we get ONE email stating success.

    And another query ran last night as a job that emails results of any payments did not get checks. We got a HUGE report but the paymentids did NOT match our process IDs. When we ran the query stand alone it returned no results!

    And we are still receiving 2-3 emails for notifications that come from Jobs.

    Emails from another server also come in twice so we suspect a mailbox issue for that.

    Needless to say we are a bit worried. Anyone seen any symptoms like this before? It's all over the place but i figured i would put all the info in.

    Thanks for any and all responses.

    Andy

  • June 21, 2008 at 11:36 am

    #832254

    This only addresses one small facet: as for the failed backups being double in size, be sure the INIT option is used, otherwise the 2nd attempt will get appended to 1st if the backup filename is same.

  • June 21, 2008 at 11:43 am

    #832255

    I've never heard that 2005 won't backup to UNC paths. I just checked, and I can do this no problem. So a couple things might be cause:

    1) security - can the service account access/write to the specified UNC path? Test this by connecting with the service account (in SSMS or query analyzer, whatever) and then try to backup master to the same UNC path. If there's a security problem you'll see it right away.

    2) timeout? check this out: http://geekswithblogs.net/scarpenter/archive/2006/02/15/69570.aspx

  • June 21, 2008 at 11:43 am

    #832256

    thank you. will check the setting.

    andy

  • June 21, 2008 at 11:44 am

    #832257

    1) I should have said "backup master explicitly with the backup database command in the query window"

  • June 21, 2008 at 11:52 am

    #832258

    hi mike

    thanks for your help.

    well, it worked fine then all of a sudden different servers started exhibiting the same behavior. we had a network guru tell us that MS does not support UNC in SQL 2005 but it works fine for SQL 2000. Go figure.

    I'll show the reg settings changes to our Infrastructure mngr.

    Thanks again

    andy

  • June 21, 2008 at 12:16 pm

    #832261

    Andy,

    First thing I'd do is run a DBCC CHECKDB against the databases you're backing up.

    Second, since this started occuring recently I'd be looking at anything that was updated recently, such as AV or anything that filters IO operations.

  • June 21, 2008 at 1:31 pm

    #832276

    Another thing you can do, and should do is:

    ALTER DATABASE SET PAGE_VERIFY CHECKSUM;

    Then, include the checksum option in your backups:

    BACKUP DATABASE ... WITH CHECKSUM;

    Not only with the backup create a checksum for the backup itself, but it will also validates the checksums on every page.

    Jeff

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • June 23, 2008 at 3:03 pm

    #833033

    Slight update to this...

    we've narrowed it down to SQL jobs running twice, or even three times, right on top of each other. That explains the dual entries in a temp table from SSIS package called in job and bad email info reading from a global temp table within the stored proc in the package.

    Double backups the same story... running simultaneously with the setting to "Append". We are adjusting these jobs but still does not explain why jobs are running multiple times.

    Even though a job was DISABLED, it ran! The schedule was still enabled. It's almost as if the SCHEDULE and the JOB both run. I know that makes no sense...

    We have a ticket open with MS. DBCC CHECKDB is ok for MSDB.

    New jobs do NOT run multiple times. Only existing jobs at the time of when this started (last Thursday night). MS is still focusing on the granular jobs and we have since explained (many times) that this is happening to ALL JOBS. Trying to escalate now...

    Craziness...

  • June 23, 2008 at 5:35 pm

    #833089

    SOLVED.

    We have a server with WanSync software on it that had the services DISABLED. SOMEHOW when we shipped it to our offsite location for syncing the services were set to Automatic. Perhaps the change of IP, scenario being broken, not sure yet. We're consulting with WanSync folks.

    Thanks for the replies. Glad there is an explanation finally...

    andy

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply