pereira_allwyn
asked on
Change WSUS database datafiles path
hi,
Need help in changing the path of the data files and Transaction logs of the database used for WSUS server. The orginal location of the datafiles is on a volume that is almost full, hence I'm trying to move the data files and transaction logs to another volume.
When I try to take the database offline, I get an error stating that Error 5070: Database state cannot be changed while other users are using the database 'DB NAME' ALTER DATABASE statement failed.
sp_dboption command failed.
I have also tried stopping the Update services and then trying to take the databse offline.
Need help in changing the path of the data files and Transaction logs of the database used for WSUS server. The orginal location of the datafiles is on a volume that is almost full, hence I'm trying to move the data files and transaction logs to another volume.
When I try to take the database offline, I get an error stating that Error 5070: Database state cannot be changed while other users are using the database 'DB NAME' ALTER DATABASE statement failed.
sp_dboption command failed.
I have also tried stopping the Update services and then trying to take the databse offline.
I'm basing this on SQL 2000:
Stop the Update Services service to bring down WSUS.
Use the sp_who command in Query Analyzer to find out who's connected. Also, in Enterprise Manager, you could go to Management, Current Activity, Process Info and then sort by database to see if there are connections (and it will provide more details if so).
If you want a quick way to kill off connections, execute this set of commands (if you're forced to):
ALTER DATABASE databasename
SET single_user WITH ROLLBACK IMMEDIATE
At this point you should be able to detach the database, move the log/data files as you wish, and then reattach it with the new locations.
Stop the Update Services service to bring down WSUS.
Use the sp_who command in Query Analyzer to find out who's connected. Also, in Enterprise Manager, you could go to Management, Current Activity, Process Info and then sort by database to see if there are connections (and it will provide more details if so).
If you want a quick way to kill off connections, execute this set of commands (if you're forced to):
ALTER DATABASE databasename
SET single_user WITH ROLLBACK IMMEDIATE
At this point you should be able to detach the database, move the log/data files as you wish, and then reattach it with the new locations.
sorry i was meening deattache then change the location then reattache
ASKER
The thing is I've tried to detach, I've tried to take it offline and I've tried to take it offline. It does not allow me to do any of these things.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was a good one. I tried stopping the IIS too, and that did not work either.
1. Close any Internet browser that is connecting to the WSUS console.
2. Stop the IIS Admin service and the Update Services service:
a. Click Start, point to Programs, point to Administrative Tools, and then click Services.
b. Right-click IIS Admin Service, and then click Stop.
c. Right-click Update Services, and then click Stop.
2. Stop the IIS Admin service and the Update Services service:
a. Click Start, point to Programs, point to Administrative Tools, and then click Services.
b. Right-click IIS Admin Service, and then click Stop.
c. Right-click Update Services, and then click Stop.
ASKER
Your wild guess was a good one, just needed the addional step of stopping the update services. Thanks mate
I am afraid busbar takes this one ;p
I was just rummaging through post and thought i'd write up a one-liner hehe
I was just rummaging through post and thought i'd write up a one-liner hehe
then change the logs location then reattach the database :)