July 8, 2010 at 1:27 am
Hi,
Small clarification needed.
I believe in SQL 2005, the new system database RESOURCE is added so that we cannot make any
direct changes to the system catalog's.
But recently, i was experimenting the server migration..meaning, we are trying to replace existing server with new server with more hardware resources (RAM,CPu's disk space).
If everything works well on the new server, we will be renaming and assigning the same IP for the new server as existing server and bring the old server offline.
For do this, i tried with allow updates to disable all the jobs as follows
sp_configure 'show advanced options',1
reconfigure with override;
go
sp_Configure 'allow updates',1
reconfigure with override;
go
sp_configure 'allow updates'
go
Then to disable the jobs , i have used the following script as we do it in normal SQL 2000 scenario.
Here is the script
use msdb
go
update sysjobs
set enabled = 0
go
This would disable all the SQL Agent jobs.
But my question is, Why the updations of system catalogs are still supported even "Resource " database is supported.
Correct me if am wrong !!!! Am confused....
Thanks in Advance,
July 8, 2010 at 1:50 am
In SQL Server 2005, the 2000 system tables changed to catalog views, and were not writeable.
This has nothing to do with the mssqlsystemresource database - this is a read only database that contains mostly functions and stored procedures.
The msdb.dbo.sysjobs table is NOT a system table. It's a Microsoft Shipped table, but it is not a table like sysindexes was, so you're free to update it. That said, I'd prefer to use the sp_update_job procedure to disable each job individually.
EXEC msdb.dbo.sp_update_job @job_id=N'957d0500-1c5a-46ab-86ef-ef76a2175731', @enabled=1
The "allow updates" option does nothing in 2005+.
July 8, 2010 at 3:13 am
Okay.
Thanks for the tip.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply