Moving the System Databases

This document applies to moving the following databases in SQL Server 2005:

Model
MSDB
Master
Tempdb

Moving these databases requires downtime and must not be done while there are connections to the SQL Server

Move the Model and MSDB Databases

To move the model database, you must start SQL Server together with the -c option, the –m option, and trace flag 3608. Trace flag 3608 prevents SQL Server from recovering any database except the master database.

Note You will not be able to access any user databases after you do this. You must not perform any operations, other than the following steps, while you use this trace flag. To add the startup parameters follow the steps below:

1. Use SQL Server Configuration Manager to change the startup parameters of the SQL Server service

a. Open SQL Server Configuration manager
b. Right Click the SQL Server Service and select properties

clip_image002

c. Select the Advanced Tab

The Startup parameters variable should look something like this:

-dC:\SYSTEMDBMove\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG;-lC:\SYSTEMDBMove\mastlog.ldf

clip_image002[4]

Edit the Startup parameters row by adding the following to the end of the variable:

;-c;-m;–T3608
(the first semi-colon is to close off the previous startup parameter – no spaces)

Or run via command prompt…

Net Stop MSSQL$SQL1

Net Start MSSQL$SQL1 /c /m /T3608

Startup Parameter

Details

-c

Shortens startup time when starting SQL Server from the command prompt. Typically, the SQL Server Database Engine starts as a service by calling the Service Control Manager. Because the SQL Server Database Engine does not start as a service when starting from the command prompt, use -c to skip this step.

-m

Starts an instance of SQL Server in single-user mode. When you start an instance of SQL Server in single-user mode, only a single user can connect, and the CHECKPOINT process is not started.

T trace#

Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior

3608

Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed.

2. Stop and Restart the SQL Server instance. Ensure that the SQL Server Agent is not running

3. Detach the Model database

use master
go
sp_detach_db ‘model’
go

4. Move the Model.mdf and Modellog.ldf files from the <Current Location> folder to the <New Location> folder.

5. Reattach the Model database using the new location details

use master
go
sp_attach_db
‘model’,<New Location>\model.mdf’,<New Location>\modellog.ldf’
go

6. Detach the MSDB Database

use master
go
sp_detach_db
‘msdb’
go

7. Move the Msdbdata.mdf and Msdblog.ldf files from the <Current Location> folder to the <New Location> folder.

8. Remove -c -m -T3608 from the startup parameters box by using the SQL Server Configuration manager

9. Stop and Restart SQL Server

10. Reattach the MSDB database using the new location details

use master
go
sp_attach_db
‘msdb’,<New Location>\msdbdata.mdf’,<New Location>\msdblog.ldf’
go

Check that databases have successfully moved to the new location

use model
go
sp_helpfile
go

use msdb
go
sp_helpfile
go

Move the Master and Resource Databases

The Resource database depends on the location of the master database. The Resource data and log files must reside together and must be in the same location as the master data file (master.mdf). Therefore, if you move the master database, you must also move theResource database to the same location as the master data file. Do not put the Resource database in either compressed or encrypted NTFS file system folders. Doing so will hinder performance and prevent upgrades.

1. Open The SQL Server Configuration Manager and go to the startup parameters variable for the SQL Server Service

2. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf; 
-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; 
-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

If the planned relocation for the master data and log files is E:\SQLData, the parameter values would be changed as follows:

-dE:\SQLData\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG; -lE:\SQLData\mastlog.ldf

3. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop

4. Move the master.mdf and mastlog.ldf physical files to the new location.

5. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

  • For the default (MSSQLSERVER) instance, run the following command.
NET START MSSQLSERVER /f /T3608
  • For a named instance, run the following command.
NET START MSSQL$instancename /f /T3608

6. Using SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

ALTER DATABASE mssqlsystemresource
   MODIFY FILE (NAME=data, FILENAME= ‘new_path_of_master\mssqlsystemresource.mdf’);
GO

ALTER DATABASE mssqlsystemresource
   MODIFY FILE (NAME=log, FILENAME= ‘new_path_of_master\mssqlsystemresource.ldf’);
GO

7. Move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to the new location.

8. Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

9. Stop and Restart the instance of SQL Server

10. Verify the file change for the master database by running the following query. The Resource database metadata cannot be viewed by using the system catalog views or system tables.

SELECT name, physical_name AS CurrentLocation, state_desc

Move the TempDB Database

Note: Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

1.      Determine the logical file names of the tempdb database and their current location on the disk. 


SELECT
name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N‘tempdb’);
GO

2.       Change the location of each file by using ALTER DATABASE.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);
GO

3.       Stop and restart the instance of SQL Server. 

4.       Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N‘tempdb’);
GO

5.       Delete the tempdb.mdf and templog.ldf files from the original location.


Useful Links

Main KB
Article – http://support.microsoft.com/kb/224071

Move the
Master and Resource
databases – http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

Startup
parameters – http://msdn.microsoft.com/en-us/library/ms190737.aspx

Advertisements

Fixing Orphaned Logins

Find Orphaned Logins:

sp_change_users_login @Action=‘Report’
GO

Fix an Individual Login:

sp_change_users_login ‘auto_fix’, login_name
go

Fix All Logins for a database:

— fix Users
set nocount on
declare @v_dbuser varchar(255)
declare @sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
      open c1
      fetch c1 into @v_dbuser
      while (@@FETCH_STATUS <> 1)
      BEGIN
      print ‘Fixing User ‘ + @v_dbuser
      set @sql = ‘sp_change_users_login ”auto_fix”,’ + @v_dbuser
      exec sp_executesql @sql
      fetch c1 into @v_dbuser
      END
CLOSE C1
DEALLOCATE C1

Migrating Logins between Servers

Before Logins can be generated, 2 procedures have to be added to the master database. These are Microsoft built but come unsupported.

===================================================================


USE master

GO

IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL

  DROP PROCEDURE sp_hexadecimal

GO

CREATE PROCEDURE sp_hexadecimal

    @binvalue varbinary(256),

    @hexvalue varchar (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstring char(16)

SELECT @charvalue = ‘0x’

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring = ‘0123456789ABCDEF’

WHILE (@i <= @length)

BEGIN

  DECLARE @tempint int

  DECLARE @firstint int

  DECLARE @secondint int

  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

  SELECT @firstint = FLOOR(@tempint/16)

  SELECT @secondint = @tempint  (@firstint*16)

  SELECT @charvalue = @charvalue +

    SUBSTRING(@hexstring, @firstint+1, 1) +

    SUBSTRING(@hexstring, @secondint+1, 1)

  SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

GO

===================================================================


IF Object_id(‘sp_help_revlogin’) IS NOT NULL

  DROP PROCEDURE sp_help_revlogin

GO

CREATE PROCEDURE Sp_help_revlogin

                @login_name SYSNAME  = NULL

AS

  DECLARE  @name SYSNAME
   
  DECLARE  @xstatus INT
   
  DECLARE  @binpwd VARBINARY(256)
   
  DECLARE  @txtpwd SYSNAME
   
  DECLARE  @tmpstr VARCHAR(256)
   
  DECLARE  @SID_varbinary VARBINARY(85)   
  DECLARE  @SID_string VARCHAR(256)

  

  IF (@login_name IS NULL)

    DECLARE login_curs CURSOR  FOR

    SELECT sid,

           name,

           xstatus,

           password

    FROM   MASTER..sysxlogins

    WHERE  srvid IS NULL

           AND name <> ‘sa’

  ELSE

    DECLARE login_curs CURSOR  FOR

    SELECT sid,

           name,

           xstatus,

           password

    FROM   MASTER..sysxlogins

    WHERE  srvid IS NULL

           AND name = @login_name

  

  OPEN login_curs

  

  FETCH NEXT FROM login_curs

  INTO @SID_varbinary,

       @name,

       @xstatus,

       @binpwd

  

  IF (@@FETCH_STATUS = 1)

    BEGIN

      PRINT ‘No login(s) found.’

      

      CLOSE login_curs

      

      DEALLOCATE login_curs

      

      RETURN 1

    END

  

  SET @tmpstr = ‘/* sp_help_revlogin script ‘
   
  PRINT @tmpstr
   
  SET @tmpstr = ‘** Generated ‘ + Convert(VARCHAR,Getdate()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
   
  PRINT @tmpstr
   
  PRINT 
   
  PRINT ‘DECLARE @pwd sysname’

  

  WHILE (@@FETCH_STATUS <> 1)

    BEGIN

      IF (@@FETCH_STATUS <> 2)

        BEGIN

          PRINT 

          

          SET @tmpstr = ‘– Login: ‘ + @name

          

          PRINT @tmpstr

          

          IF (@xstatus & 4) = 4

            BEGIN — NT authenticated account/group

              IF (@xstatus & 1) = 1

                BEGIN — NT login is denied access

                  SET @tmpstr = ‘EXEC master..sp_denylogin ”’ + @name + ””

                  

                  PRINT @tmpstr

                END

              ELSE

                BEGIN — NT login has access

                  SET @tmpstr = ‘EXEC master..sp_grantlogin ”’ + @name + ””

                  

                  PRINT @tmpstr

                END

            END

          ELSE

            BEGIN — SQL Server authentication

              IF (@binpwd IS NOT NULL)

                BEGIN — Non-null password

                  EXEC Sp_hexadecimal

                    @binpwd ,

                    @txtpwd OUT

                  

                  IF (@xstatus & 2048) = 2048

                    SET @tmpstr = ‘SET @pwd = CONVERT (varchar(256), ‘ + @txtpwd + ‘)’

                  ELSE

                    SET @tmpstr = ‘SET @pwd = CONVERT (varbinary(256), ‘ + @txtpwd + ‘)’

                  

                  PRINT @tmpstr

                  

                  EXEC Sp_hexadecimal

                    @SID_varbinary ,

                    @SID_string OUT

                  

                  SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name + ”’, @pwd, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘

                END

              ELSE

                BEGIN

                  — Null password

                  EXEC Sp_hexadecimal

                    @SID_varbinary ,

                    @SID_string OUT

                  

                  SET @tmpstr = ‘EXEC master..sp_addlogin ”’ + @name + ”’, NULL, @sid = ‘ + @SID_string + ‘, @encryptopt = ‘

                END

              

              IF (@xstatus & 2048) = 2048

                — login upgraded from 6.5

                SET @tmpstr = @tmpstr + ”’skip_encryption_old”’

              ELSE

                SET @tmpstr = @tmpstr + ”’skip_encryption”’
               
              PRINT @tmpstr

            END

        END

      

      FETCH NEXT FROM login_curs

      INTO @SID_varbinary,

           @name,

           @xstatus,

           @binpwd

    END

  

  CLOSE login_curs
   
  DEALLOCATE login_curs

  RETURN 0

GO

===================================================================

To Extract logins just run

sp_help_revlogin

on the master database on the server. This extracts logins with encrypted passwords

Check http://support.microsoft.com/kb/246133 for full support details

Scheduler Jobs Queries

Query for running Scheduler Jobs:

select s.username, rj.job_name, rj.running_instance, s.sid, s.serial#, p.spid, s.lockwait, s.logon_time
from dba_scheduler_running_jobs rj,
     gv$session s,
     gv$process p
where rj.running_instance = s.inst_id
and   rj.session_id = s.sid
and   s.inst_id = p.inst_id
and   s.paddr = p.addr
–and   p.spid = <spid>
order by s.username, rj.job_name;

Query for seeing broken/failing jobs:

select ‘DB1’ as DB, Owner, Job_name, Enabled, Run_Count, failure_count Fails, max_failures  Max,  NVL(TO_CHAR(LAST_START_DATE,‘YYYY-MM-DD
HH24:MI:SS’
),‘NULL

) Last_start_Date

from dba_scheduler_jobs

where (max_failures failure_count) <= 2

order by failure_count desc;

Dynamically Disable All Scheduler Jobs:

BEGIN
    DBMS_OUTPUT.ENABLE(9000000
);
    FOR cur
IN
        (SELECT ‘BEGIN SYS.DBMS_SCHEDULER.STOP_JOB(JOB_NAME => ‘ || chr(39) || OWNER || ‘.’ || JOB_NAME || chr(39) || ‘, FORCE => TRUE); END;’ stmt
,
        OWNER || ‘.’
|| JOB_NAME job_name
        FROM
DBA_SCHEDULER_JOBS
        WHERE OWNER <> ‘SYS’)
   
LOOP
       
–DBMS_OUTPUT.PUT_LINE(cur.stmt);
       
BEGIN
            EXECUTE IMMEDIATE(cur.stmt
);
        EXCEPTION

        WHEN others THEN
            DBMS_OUTPUT.PUT_LINE(‘JOB NOT RUNNING ‘ || cur.job_name
);
           
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
       
END;
    END LOOP;

    FOR cur IN
        (SELECT ‘BEGIN SYS.DBMS_SCHEDULER.DISABLE(NAME => ‘ || ”” || OWNER || ‘.’ || JOB_NAME || ”” || ‘, FORCE => TRUE); END;’ stmt
,
        OWNER || ‘.’
|| JOB_NAME job_name
        FROM
DBA_SCHEDULER_JOBS
        WHERE OWNER <> ‘SYS’
)
    LOOP

        –DBMS_OUTPUT.PUT_LINE(cur.stmt);
       
BEGIN
            EXECUTE IMMEDIATE(cur.stmt
);
       
EXCEPTION
        WHEN others
THEN
            DBMS_OUTPUT.PUT_LINE(‘JOB NOT DISABLED ‘ || cur.job_name
);
           
–DBMS_OUTPUT.PUT_LINE(SQLERRM || ‘ CAUSE:’ || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
       
END;
    END LOOP
;
END;
/

How to Install OSWatcher

WinSCP the osw file to server in the $ORACLE_BASE directory

>cd $ORACLE_BASE
> tar xvf osw212.tar
> cd osw
> vi private.net

Comment out all the examples bar the linux example and replace with list of nodes in cluster with _priv at the end, so:

To get a list of nodes type: olsnodes

In private.net:

######################################################################
#Linux Example
######################################################################

echo "zzz ***"`date`
traceroute -r -F -m1 nodename_priv
traceroute -r -F -m1 nodename_priv

Finally:

nohup ./startOSW.sh 30 48 &

This will start OSW and run every 30 seconds and keep logs for 48 hours.

cd into archive and check all the logs to ensure it is working as expected, in particular oswprvtnet folder and see if all the nodes are getting traceroute over the interconnect:

zzz ***Thu Apr 30 12:54:13 BST 2009
traceroute to Inodename_priv (10.0.0.1), 1 hops max, 46 byte packets
1  * * *

traceroute to nodename_priv (10.0.0.3), 1 hops max, 46 byte packets
1  * * *

For reference, OS Watcher User Guide:
https://metalink2.oracle.com/metalink/plsql/docs/OSW_UG.htm

Attaching Database – Unable to Open Physical File (Access is Denied)

Error:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "\\servername\sharename\filename.mdf". Operating system error 5: (Access is denied.).

This is more often than not an issue with permissions and windows level security.

The permissions on the S:\ and T:\ seem a bit odd in that the server Admins overall have full control on the drives but not of some individual DB’s within the drives. However, when looking at individual files within the S:\ Drive I can see that some of the data files don’t have the correct permissions filtered down to them.

The reason additional permissions are needed is when a DB is reattached it inherits the Owner as the person attaching the file.

To fix, I did the following:

Added the Administrators Group to the file security permissions with full control for the Data file (S:\) and the Log File (T:\).

Attached the database and it works fine.

I did some more digging and think I may have resolved the security problem without manually editing each file

clip_image006

Check the tick box and then click ok.

clip_image007

Click Yes and this will propagate permissions on to each file.

After I did this, I was able to attach databases without any issues.