Problems running Maint. plan thru OSQL

Question

Post reply

Problems running Maint. plan thru OSQL

Guy Staat

SSC Journeyman

Points: 85
More actions
February 4, 2004 at 3:04 pm

#83385

I just want to say that I am not a DBA. I have been thrust into the position with absolutely no experience or training as a DBA. Go Figure!
I created a DB maintenance plan. I found that 2 jobs were created for my maintenance plan, Integrity check and Optimizations. I went into those 2 jobs and cut and pasted the actual EXECUTE command from each into a SQL script. Now I am trying to run this script from a remote server using OSQL. The problem I am having is that I am getting an error message saying that the DB needs to be in single user mode. I thought I had read something that said the database would be put into single user mode automatically by the maintenance plan. I have verified that no other users are connected to the DB. This is on a test box where no one else really has access to. Are there some system processes running that may be causing my problem? I have even added a ALTER DATABASE command to set it to single user to the script and it still doesn't work! Is it because I am running it from a remote server? Any thoughts or ideas would be greatly appreciated!
Guy Staat

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

"Now I am trying to run this script from a remote server using OSQL."

How did you run it fro remote server? Please post the code.

Guy Staat SSC Journeyman Points: 85 More actions · Answer 2

The code below is from a batch file I created to run the maintenance plan. The batch file gets executed by our Veritas backup software after the backup runs.

\\svr-csdb\sqlbin$\osql -U dbmaint -S svr-csdb -d TG_Data -i c:\dbmaint\SQLScripts\TG_Data_Maint_Plan.sql -o c:\dbmaint\LogFiles\TG_Data_Maint_Log.txt

The code below is from the SQL script TG_Data_Maint_Plan.sql which is the input file for the above batch file.

ALTER DATABASE tg_data SET SINGLE_USER

GO

EXECUTE master.dbo.xp_sqlmaint

N'-PlanID 29D43295-CAFF-4D07-887C-DC6E2E050251

-Rpt "E:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan12.txt"

-WriteHistory

-CkDBRepair '

GO

EXECUTE master.dbo.xp_sqlmaint

N'-PlanID 29D43295-CAFF-4D07-887C-DC6E2E050251

-Rpt "E:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance Plan10.txt"

-WriteHistory

-RebldIdx 10

-RmUnusedSpace 50 10 '

GO

ALTER DATABASE tg_data SET MULTI_USER

GO

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

It looks like xp_sqlmaint is going to open another connection to the database but database has been set to single user mode.

Guy Staat SSC Journeyman Points: 85 More actions · Answer 4

So should I remove the ALTER DATABASE commands from the script? Remember, my problem to begin with was that I was getting an error message that said the database needed to be in SINGLE USER mode. Thats why I added the ALTER DATABASE commands to the script.

Guy

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

You really don't have to set database in single-user mode to perform reindex and integrity check without repair. If you does find database integrity errors by run integrity check, then you should perform the repair by setting database in single-user mode and run dbcc checkdb from QA.

steve block SSCertifiable Points: 7698 More actions · Answer 6

Just a thought. When you edit a maintenance plan and 'Attempt to repair any minor errors' is checked, the integrity checks are run in single user mode. Check to see if this is set in your maintenance plan.