(no) Fun With Xp_sqlmaint
Jul 25, 2000
I'm testing xp_sqlmaint from a SQL-Analyser window using an NT trusted connection. I can backup a database fine using
xp_sqlmaint '-D DumpDB -BkUpDB e:mssql7ackupDumpDBDir -BkUpMedia DISK'
However, attempting to backup the log using
xp_sqlmaint '-D DumpDB -BkUpLog e:mssql7ackupDumpDBDir -BkUpMedia DISK'
fails like a unprepared student. Removing the specification for back up media causes a syntax error. I've pored over BOl for a while and AFAIK, I am using appropriate syntax.
thanks in advance for your help.
doug olsen
View 1 Replies
Jul 20, 2005
Can the only thing you do is delete backup files with xp_sqlmaint, ordo you also have to backup the database?
View 1 Replies
View Related
Jul 20, 2005
I am running SQL Server 2000 SP3 on Win2k Server. I have just built anew maintenance plan that first backs up the database, then analyzesit for allocation and rebuilds indexes. Below is what I am using:exec xp_sqlmaint '-S SCDEV -U "sa" -P <my_sa_password> -D ScProd-PlanName Web_DB_Backup-BkUpDB <My_Path> -BkUpMedia DISK'exec xp_sqlmaint '-S SCDEV -U "sa" -P "my_sa_password" -D SCProd -CkDB-CkAl-CkCat -RebldIdx 99 -HtmlRpt <my_path>SCWeb_DB_Backup_Log.html"-DelHtmlRpt 5days'It seems that after I run this, the database size grew to 5x it'sregular size. I thought this had to do with the index rebuild. WhenI tested it, it ran for nearly 2.5 hours. I checked the datafile andit is ~40GB when it used to be ~8GB. I will have to try and shrinkthe database to get it back to size, but why did the data file grow soastronomically from a maintenance plan?Any help would be greatly appreciated.
View 1 Replies
View Related
Jan 29, 2008
I have a job that runs following stored procedure with following parameters in SQL Server 2000. This problem occured in a backend database onepoint run at Microsoft Operations Manager 2005 (Front end). The below procedure is used for reindexing job.
EXECUTE master.dbo.xp_sqlmaint '-PlanName OnePointReindex -RebldIdx 100 -WriteHistory'
This is the error I got. The following line comes with xp_sqlmaint failed.
[Microsoft SQL-DMO] Error 21268: [SQL-DMO]Row or column specified is outside the range of the specified query result set.
I am trying to resolve this but still not successful. Any help would be greatly appreciated.
Mike
View 3 Replies
View Related
Apr 1, 1999
On my account we have very separate authority between the NT system administrators and the SQL DBAs. Routinely in SQL 6.5 we would remove sa authority from the NT administrators group and add this authority to a NT database administrators group which we were members of using SQL Security manager.
We have tried to apply this same method to our implementation of SQL 7.0. We removed the sysadmin role from the BUILTINAdministrators group and added sysadmin to the DatabaseAdmin NT group which the DBAs and SQLAGENT account are members of. This appeared to work fine until we tried to run our database maintenance plans in SQLAGENT. When sysadmin role is revoked from BUILTINAdministrators the jobs in SQLAGENT using xp_sqlmaint fail with "select permission denied to sysdbmaintplans in database 'msdb' owner 'dbo'"
The SQLAGENT login is part of an NT group that has been granted sysadmin role in SQL Server. This should grant full access to SQLAGENT. What's up? Is the xp_sqlmaint using hardcoded security of some kind? I am confused. Any suggestions? We really need to revoke sysadmin from the BUILTINAdministrators group.
View 2 Replies
View Related
Sep 26, 2002
On our SQL2000 installation on a Windows2000 machine, our sql maintenance jobs run for about a week and then begin to fail with the following error:
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
The logs only indicate failure and the report file is not generated, nor is a maintenance plan history record generated.
When the call to xp_sqlmaint is failing I try it in Qeury Analyzer (QA) and it still fails. I can however still run the SQLMAINT.exe directly from a command prompt without error. It is just the calls to xp_sqlmaint that seem to be failing. If I simply restart SQL Server, the maintenance jobs run fine for a while again. Same success with the calls in QA to xp_sqlmaint. Any ideas on why this begins to fail? It is happening on 3 of 6 database servers running the same build.
Thanks, John.
View 9 Replies
View Related
