Wednesday, February 17, 2016

before restarting that instance...

Check number of VLFs - Virtual log file counts in the 1000s will mean longer recovery time

Check size of database and transaction log files - the bigger the file sizes, the longer it will take to recover

Check size of tempdb file definitions - tempdb will be rebuilt, is it properly sized and in the right location?  Also, bigger the file sizes, the long it will take to allocate the space.

When was last Full / Incremental / Transaction log backup taken?  If possible, kick off another transaction log backup before restating.

Check SQL Server Agent jobs and make sure no big jobs are running, then stop the Agent

Update your resume and/or linked-in profile :)

After restart;
- If smaller databases and minimal impact run checkdb with physical only
- Check SQL Agent, start it if it has not started automatically
- Trace flags not defined in startup parameters were lost
- Statistics were reset

Wednesday, February 3, 2016

Fix Logins -> DB Users

When a database is restored from one server to another, the databases users are still mapped to the guid of the logins on the original server. To remap the database users to the logins on the gaining server, you can drop and recreate the user or run sp_change_users_login.  The script below will run this for all users.


USE [db_name]
GO


SET NOCOUNT ON
GO

declare @tab TABLE ( id int identity(1,1), sqlcmd nvarchar(4000) )
declare @id int, @sqlcmd nvarchar(4000)

insert into @tab ( sqlcmd )
select 'exec sp_change_users_login ''update_one'', ''' + A.name + ''',''' + B.name + ''''
from sys.sysusers A
inner join sys.syslogins B
on lower(A.name) collate SQL_Latin1_General_CP1_CI_AS = lower(B.name) collate SQL_Latin1_General_CP1_CI_AS
where issqluser = 1

select @id = max(id) from @tab

while @id > 0
begin
   select @sqlcmd = sqlcmd from @tab where id = @id
   print @sqlcmd
   exec sp_executesql @sqlcmd
   set @id = @id - 1
end

GO