July 9, 2008 at 9:44 am
Okay, I am having a brain dead moment. I am currently upgrading a SQL Server 2000 installation to SQL Server 2005. I know that I need to run a couple of SQL commands when I finish on the user databases, but for the life of me, I can't remember what and I am even having a hard time finding the info here on SSC. (I knew I should have called in sick this morning!)
😎
July 9, 2008 at 10:23 am
What's the issue?
sp_change_users_login?
July 9, 2008 at 10:25 am
No, something about statistics, and something else. Just having one of those "blonde" moments (or in my case, old-timer's moments).
July 9, 2008 at 10:32 am
After upgrading the Databases:
1. Change the Compatiility Level to 90
2. DBCC UPDATEUSAGE
3. DBCC CHECKDB (DatabaseName)
4. Copy all the logins associated with the Database OR ALL using this Scripts from MICROSOFT. http://support.microsoft.com/kb/246133
5. as Steve suggested
use databasename
sp_change_users_login 'Report'
which will give you the list of all orphaned users in the database. if you find any...
sp_change_users_login 'update_one','loginname','loginname' to correct the Login mapping.
Be Extra Cautious to check your SP's and Functions do not contain any deprecated syntax.
Maninder
www.dbanation.com
July 9, 2008 at 10:36 am
Mani Singh (7/9/2008)
After upgrading the Databases:1. Change the Compatiility Level to 90
2. DBCC UPDATEUSAGE
3. DBCC CHECKDB (DatabaseName)
4. Copy all the logins associated with the Database OR ALL using this Scripts from MICROSOFT. http://support.microsoft.com/kb/246133
5. as Steve suggested
use databasename
sp_change_users_login 'Report'
which will give you the list of all orphaned users in the database. if you find any...
sp_change_users_login 'update_one','loginname','loginname' to correct the Login mapping.
Be Extra Cautious to check your SP's and Functions do not contain any deprecated syntax.
YES!! Thank you!! DBCC UPDATEUSAGE is the one I was trying to remember!!!! :w00t:
Logins should be okay (I hope, will check) as I was doing an in-place upgrade. First one I've done. All the other SQL Server 2005 installs here have been new installs.
😎
July 9, 2008 at 10:40 am
Have you done an UPDATE STATISTICS?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 10:47 am
Oh, should I do that before or after DBCC UPDATEUSAGE? Couldn't remember the DBCC command so I started with updating the statistics.
😎
July 9, 2008 at 10:52 am
Lynn Pettis (7/9/2008)
Oh, should I do that before or after DBCC UPDATEUSAGE? Couldn't remember the DBCC command so I started with updating the statistics.😎
Doesn't matter. All updateusage does is correct possible errors in the space used metadata. It's not even essential to an upgrade (although people don't like seeing that their db file has -42% free)
It's the update stats that's the most essential to run. Old stats usually cause poor performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 10:56 am
Okay. Good to know. And yes, I still want to run the UPDATEUSAGE. It just really bothered me that I couldn't remember it.
Thanks!
😎
July 9, 2008 at 11:53 am
Here's a script I use for this:
use [database] -- Please remember to fill this in!!
go
dbcc UPDATEUSAGE (0) WITH NO_INFOMSGS;
go
DBCC CHECKDB (0) with no_infomsgs;
go
-- Update all table stats
declare Tables cursor local fast_forward
for
select name
from sys.all_objects
where type = 'u'
declare @Table varchar(100), @sql varchar(1000)
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
select @sql = 'update statistics dbo.' + @table + ' with fullscan'
exec (@sql)
--raiserror(@table, 10, 1) with nowait
fetch next from tables into @table
end
close tables
deallocate tables;
go
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(max),
@ProcName1 VARCHAR(max)
DECLARE @T TABLE
(ProcName VARCHAR(max),
sql VARCHAR(max),
ErrorMessage VARCHAR(max))
DECLARE c Cursor FOR
select name, definition
from sys.all_objects
inner join sys.sql_modules
on all_objects.object_id = sql_modules.object_id
where type in ('p','tf','if')
and name not like 'dt_%'
and name not like 'sys_%'
Open C
FETCH NEXT FROM c INTO @ProcName, @Text
WHILE @@FETCH_STATUS = 0 BEGIN
SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name
BEGIN TRY
EXEC(@text) -- try to create the proc
INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that could be created
END TRY
BEGIN CATCH
INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
if exists
(select *
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type = 'p')
begin
set @sql = 'drop procedure ' +
(select name
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type = 'p')
exec(@sql)
end
if exists
(select *
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type in ('if','tf'))
begin
set @sql = 'drop function ' +
(select name
from sys.all_objects
where name like '%' + @procname + 'createtest'
and type in ('if','tf'))
exec(@sql)
end
FETCH NEXT FROM c INTO @ProcName, @Text
END
CLOSE c
DEALLOCATE c
SELECT * FROM @T where errormessage is not null
order by procname
go
This updates the database, and tests for procs and functions that can't compile. There are a few things in 2005 that will keep a 2000 proc from compiling, this tests for them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply