Solved

Maintenance plan failed - db in single user mode

Posted on 2006-06-28
6
604 Views
Last Modified: 2007-12-19
I have a maintenance plan for a large DB (100+ GB) that failed last night.  I checked on it this morning and the DB is in single user mode.  I have since went into the plan and unchecked Attempt to repair minor problems.  However, the process is still running.  How can I kill the maintenance plan to put it back in multi user mode?  And are there any adverse side effects to doing that?  I assume that at that point, I can sp_dboptions 'myDatabase', 'single user', 'false'.

-Steve
0
Comment
Question by:BigMonkeyHead
  • 4
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
TRACEYMARY earned 250 total points
ID: 17000545
go to sql query and do sp_who2

you should see in there backup........in the command.
you then can delete that process by kill spid where spid is the number.

Or you can try stop the job in em.
If it is a backup that could take a while to clear...by the sp_who2 will tell you if it is running.

Attempt to repair minor problems - why you do this ? what was the maintenance plan ?
0
 
LVL 1

Author Comment

by:BigMonkeyHead
ID: 17000645
I have used sp_who2 to see the job - disk i/o changes each time the sp is ran (so it's still running).

I wasn't the one who set up the plan - it's supposed to simply be a backup.

Is there anything bad that can happen if I kill spid?
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17000705
If it is still running............and the disk i/o is changing then it is still progressing.
How long as it been running..........(is there enough space for the backup to be done....)

Are there any locks on the database at all?

If you can leave it for a while...i would do that...............

I have in pass deleted a backup and had no problems..........(but if you can leave it then i would until say end of today and see if it has finished....? ) is that possible.....
Is the backup bak grewing....in size ...as it backups.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17000739
Oh i see its doing a optimization attempt to repair and minor issues.......and a backup....
on a 100 gig database...........hmm that could take some time....

Is there any thing on the error logs by anychance.....and check the locks.....also.



0
 
LVL 1

Author Comment

by:BigMonkeyHead
ID: 17000744
It started about 10 hrs ago - and the maint plan history shows it tried to repair 19 seconds in.  I was working with our sys admin - he killed a 3rd party backup job that was hanging too.  Next time I checked sp_who2, it was done.  I then changed it to multi user and it's fine.

Thanks for your help - I'll look into kill spid to see what it might do.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17001080
No problem...if it seems to be running just do not kill....that my kind of rule...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Mirror and Replication 5 26
SQL Server 2012 Row Selection 2 31
Report Builder 9 31
How toselect unique values 3 10
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now