Rebuild all Indexes and Update Statistics

Here is a quick script to rebuild all indexes and update statistics for you database:

USE [database name]
GO
exec sp_MSForEachtable @command1=”ALTER INDEX ALL ON ? REBUILD”
GO
exec sp_MSForEachtable @command1=”UPDATE STATISTICS ? WITH FULLSCAN”
GO

Advertisements

Error 14274

So I was trying to modify some of my SQL Agent jobs and I got an Error: 14274. Wild.

Background:
We had to rename our production server after software upgrade this weekend. We did not want anyone hitting the old server. The problem started.

Solution:

1. Run a select @@SERVERNAME. Verify that you have the correct server name. The server name was incorrect due to the server rename so I had to run the following commands:
sp_dropserver ‘Old Server Name’

sp_addserver ‘New Server Name’

You then have to restart the SQL Service. Once you restart run the select @@SERVERNAME command again and verify the name is correct.

2. Select * from msdb..sysjobs. This will show you the Originating Server Column. In my case this was the original server name.

3. Run an update statement to fix the server name: update sysjobs set originating_server = ‘New Server Name’ where originating_server = ‘Old Server Name’

After that final update statement I was then able to update/edit/delete the jobs.