"sqlmaint.exe failed" when using xp_Sqlmaint
-
I am trying to execute this command, and getting the following error:EXECUTE master.dbo.xp_sqlmaint N'-D ISIS_PRO -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 7days -BkExt "BAK"'This works beautifully for 95% of the machines I've tried, but a small percent get no backup files created, and the following error message:"Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."If I run SQLMAINT.EXE from a command-line with the same options, it works fine. I was able to "fix" this for one user by setting his MSSQLSERVER and Agent services to run under an "Admin" account, but did not have the same success with another account.Does anybody know what rights/priveledges are necessary for the Local System to run a backup (to a local hard-drive), and how to check it? All replies are appreciated! I have had a terrible time finding helpful info.
-
Right-click on the maintenance plan and select "Maintenance Plan History". This is where you'll find much more descriptive error messages.
--------------------
Colt 45 - the original point and click interface
-
I was getting this same error and just figured it out yesterday. Seems my maintenance plan covered multiple databases, one of which had a SIMPLE recovery model. Part of the maint plan was to perform a transaction log backup but when it tried to do this for the db with the SIMPLE model, it failed with the error you mentioned. I just took this db out of the plan, made another plan that didn't to a tlog backup and everything worked like a champ when the plans ran last night... Steve
-
obviously the job can only succeed if is's account hat write permissions for the directory where the backupfiles will reside. You should verify this!
Karl
Best regards
karl -
dangit. I will type this again, since it seems that I lost the response I had just typed... OKThanks for the suggestions guys, HOWever, I am no further along in my search than when I first started.- I do not have a DAtabaseMaintenace plan. The machine that is giving me the error is actually MSDE2000, and the job was created using T-SQL. Nevermind that though, because if I just run the SQL command that I posted earlier in SQL Query Analyzer, it fails with the same error.- Only one Database is specified ("...-D ISIS_PRO...") and it is Simply Recovery. I don't think that should be a problem.- if someone will tell me how to "Verify This!": "is's account hat write permissions for the directory where the backupfiles will reside" I'll be grateful - both SQLSERVER and SQLSERVERAgent are running under Local System.Interestingly enough, i tried running that command from a different server and adding a "-S <troubleserver>" this morning and it worked! I'm confused.
more ideas please! -
explorer - right mouseclick on the DefDir- properties - security
Is SYSTEM visible?
has SYSTEM the write-permission?
(by the way, what is UseDefDir? Default Directory?
Best regards
karl -
Yes, -UseDefDir reads the value from this registry location:HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\BackupDirectoryWhich, typically is "c:\Program Files\Microsoft SQL Server\MSSQL\Backup", and I know that is set correctly.There are no special permissions set for the folder. The OS is Win XP, "sharing" is not selected, and local Administrators have full permission.
-
SO, to boil this all down, it appears that I can not do ANYTHING with xp_sqlmaint. Backup, check DB, rebuild index, etc. it all gives me the same stupid "sqlmaint.exe failed" error.Is there some kind of permission or priveledge that has to be set for this to work?? and why does it appear to occur on random machines?i guess it's time to contact MS directly.
-
Josh,
Please view Microsofts Knowledge Based article by clicking on the link below:
I hope this helps.
Pete
Peter M. Florenzano
Database Administrator
-
sorry Peter, that doesn't really help. Please note that I am not invoking some DB Maintenance plan. This is a one-time deal, specifying the DB and several options.Anybody out there got any more info on this lately? I'm going to get back on the horn with MS to see if they've figured this out in my absence.
-
The verdict is in!!! the lovely folks at MS support have diagnosed my problem! (with the xp_sqlmaint procedure call anyways) It appears that all my pain and suffering has been caused by the installation of MSDE. As it turns out, the MSI that our application uses for installation is not installing MSDE for ALLUSERS. (which, by the way, is the property that I have been told to set when installing MSDE. [ALLUSERS]) Thus, when I used the admin/User name of the person who installed MSDE for the Login for the MSSQLSERVER service, everything was hunky dory, but when I switched to Local System it barfed.The workaround that we came up with was to use a little xp_CmdShell magic to run regsvr32 on SQLDMO.DLL. After registering the SQLDMO file (in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory), I am able to execute xp_sqlmaint for any user while MSSQLSERVER is logged in as Local System. Hooray!!!Now, if only the guy who was creating our Wise installation packages had fixed the ALLUSERS problem before he left the company! But he didn't... so I'm stuck disassembling the installation package and fumbling with WISE to figure out where exactly one would set that option. But at least I've got my original question answered.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply