March 18, 2010 at 5:42 pm
This is driving me wild.
One SQL Server box. Two instances - SQL 2005 (main) and SQL 2000 (named) - both run under the same domain account, as do SQL Agents. On each, an SP runs backups to a folder on another server, where each instance has a subfolder and each database is a subfolder of that.
The SPs on each instance have identical commands using xp_dirtree to check if a subfolder for a database exists. Both work fine for a month.
Starting Monday, xp_dirtree in SQL 2005 instance stops working. xp_dirtree does not return any folders, causing other things to crash down the line. Yet, when called with the exact same path in SQL 2000 - which runs under the same domain account - no problem, all folders are there.
Is there a rational explanation or do I need an exorcist?
March 18, 2010 at 6:36 pm
Olga B (3/18/2010)
This is driving me wild.One SQL Server box. Two instances - SQL 2005 (main) and SQL 2000 (named) - both run under the same domain account, as do SQL Agents. On each, an SP runs backups to a folder on another server, where each instance has a subfolder and each database is a subfolder of that.
The SPs on each instance have identical commands using xp_dirtree to check if a subfolder for a database exists. Both work fine for a month.
Starting Monday, xp_dirtree in SQL 2005 instance stops working. xp_dirtree does not return any folders, causing other things to crash down the line. Yet, when called with the exact same path in SQL 2000 - which runs under the same domain account - no problem, all folders are there.
Is there a rational explanation or do I need an exorcist?
Has anything changed? Has anybody disabled this from surface area configuration?
Can you run xp_dirtree 'c:\' from SSMS?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 12:23 pm
Yes, xp_dirtree worked on local folder in 2005.
Anyway, mystery is solved, kinda. I found today that all SQL Server services are stopped. :crazy: This is a new server, and it turned out that the domain account it was running under was not set to "Password never expires". So it did.
It appears that while SQL Server 2000 kept chugging along until the password did expire and locked everything, SQL 2005 somehow (???) stopped the service account from connecting outside the local server for a week before the event. Once the password issue was resolved, the backup worked fine.
It's this sort of thing that will make me gray before my time.
Edited for clarity
March 22, 2010 at 12:26 pm
Thanks for posting back.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 22, 2010 at 12:41 pm
Olga B (3/22/2010)
Yes, xp_dirtree worked on local folder in 2005.Anyway, mystery is solved, kinda. I found today that all SQL Server services are stopped. :crazy: This is a new server, and it turned out that the domain account it was running under was not set to "Password never expires". So it did.
It appears that while SQL Server 2000 kept chugging along until the password did expire and locked everything, SQL 2005 somehow (???) stopped the service account from connecting outside the local server for a week before the event. Once the password issue was resolved, the backup worked fine.
It's this sort of thing that will make me gray before my time.
Edited for clarity
This is definitely something to remember. Thanks for posting back how you resolved it!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply