Post reply

Databases in shutdown mode,SSIS script cannot read folder on mapped drive

  • October 3, 2012 at 6:52 am

    #265129

    Problem one: Databases in shutdown mode

    We are migrating from our current production environment from sql 2000 to 2008 r2 in about two weeks. Like the old environment the new environment has mapped drives attached of which one (N drive) contains the mdf and ldf files for 3 secondary databases. Just by chance I tried to expand the nodes of the databases and received an error that the files could not be located. I discovered that all three were in a shutdown mode status which is not even a status in the database properties. I backuped up the same database on the production machine, copied them over to the new machine and restored them without issue. It appears that somehow there was a disconnect between the letter drive and sys.databases. I need a proper way to test for that condition and reconnect files to the database. Is detaching the databases in shutdown mode then attaching the database again be a solution?

    Problem two: SSIS script cannot read folder on mapped drive

    This is the second time it happened on our new production environment as mentioned above. We are running everything in parallel. We have a vbscript task in the package that use system.io.getfolder to get a folder on a mapped drive and do something with the files in the folder. The package fails because it cannot find the folder. This task has been running for months months and suddenly it fails. The only way I have found to fix the problem is two reboot the machine which is not desirable since it will be in production shortly. Any thoughts on this issue?

    Thanks in Advance

  • October 3, 2012 at 7:03 am

    #1544885

    For the databases in shutdown mode, check that Auto-Close is set to OFF, unless you're using SQL express.

    For the random file locking, do you have a file backup system locking files on the machine, or maybe anti-virus?

  • October 3, 2012 at 7:17 am

    #1544895

    Yes AUTO close is off.

    Yes, I have a SSIS packages that does backups to that drive and folder. If the backup process locks that folder why would it not release it and still have that ssis(another package) step fail six hours later when run manually? Is there a way to release the lock?

  • October 3, 2012 at 7:28 am

    #1544907

    What database status do you get from

    select name, status from sys.sysdatabases

    Not sure about the locking issue, but you could use sysinternals process explorer to check what is causing the lock:

    http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

  • October 3, 2012 at 7:46 am

    #1544918

    They are all in a normal state as I rebuildt yesterday. I going to write a script to check on db status and notify accordingly. The mapped drives are SAN based so we are checking with Compellant also on what they see.

    I am familiar with sysinternals..thanks

  • October 4, 2012 at 7:23 am

    #1545520

    TJ-356724 (10/3/2012)

    Problem one: Databases in shutdown mode

    We are migrating from our current production environment from sql 2000 to 2008 r2 in about two weeks. Like the old environment the new environment has mapped drives attached of which one (N drive) contains the mdf and ldf files for 3 secondary databases. Just by chance I tried to expand the nodes of the databases and received an error that the files could not be located. I discovered that all three were in a shutdown mode status which is not even a status in the database properties. I backuped up the same database on the production machine, copied them over to the new machine and restored them without issue. It appears that somehow there was a disconnect between the letter drive and sys.databases. I need a proper way to test for that condition and reconnect files to the database. Is detaching the databases in shutdown mode then attaching the database again be a solution?

    Check with your storage and windows admins to find out why the tsorage path under the databases disappeared. You don't this happening often

    TJ-356724 (10/3/2012)

    Problem two: SSIS script cannot read folder on mapped drive

    This is the second time it happened on our new production environment as mentioned above. We are running everything in parallel. We have a vbscript task in the package that use system.io.getfolder to get a folder on a mapped drive and do something with the files in the folder. The package fails because it cannot find the folder. This task has been running for months months and suddenly it fails. The only way I have found to fix the problem is two reboot the machine which is not desirable since it will be in production shortly. Any thoughts on this issue?

    Thanks in Advance

    This sounds very much like a permissions error, check the ACLs first

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • October 4, 2012 at 7:49 am

    #1545539

    We are having compellant look into it. The only thing that happened Monday morning was the restoring of the database from production system in preparation of the switch to 2008.

  • October 4, 2012 at 7:52 am

    #1545546

    We reviewed and added additional privileges.

Viewing 8 posts - 1 through 7 (of 7 total)

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