SQL 2005 - How to move System DB in SQL 2005

Moving System Database in SQL Server 2005

1. Ensure the Destination Drive is having enough space then the Current one.

2. For Example :-

       Current Sys DB path : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

       To Move                   : D:\System DB

3    Moving Model DB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'model')             

                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.

                        use master

            go

            sp_detach_db 'model'

            go

      k. Move the Model DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

                       

            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            l.  Run this Query

                        use master

            go

            sp_attach_db 'model','D:\System DB\model.mdf','D:\System DB\modellog.ldf'

            go

            p. Rerun this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'model')             

                        This will provide the current location of your Model DB.

                        ( I am Sure now the Model DB is moved to Destination Place)

4    Moving MSDB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

            SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'msdb')               

                        This will provide the current location of your Model DB.

            j. Use this Query to Detach the Model db.

                        use master

            go

            sp_detach_db 'msdb'

            go

      k. Move the MSDB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

                       

            l. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            m.  Press Y . then the SQL Server will Shut down

            n.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            o.  Run this Query

                        use master

            go

            sp_attach_db 'msdb','D:\System DB\msdbdata.mdf','D:\System DB\msdblog.ldf'

            go

     

            p. Rerun this Query

                        SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'msdb')               

                        This will provide the current location of your MSDB.

                        ( I am Sure now the MSDB is moved to Destination Place)

            q . Run this Query

                        USE MSDB

            Go

            EXEC sp_changedbowner 'sa'

            Go

4    Moving Master DB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d. Move the Master DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

           

            e. Goto SQL Server Configuration Manager and open.

            f.  Select the SQL Sever 2005 Services.

            g.  Right click the SQL Server Service --> Properties --> Advanced (tab) --> Select the Startup Parameters.

            h.  Old Entry will be

                        -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;

                        -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ERRORLOG;

                        -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf

                        Replace to

                        -dD:\System DB\master.mdf;

                        -eD:\System DB\ERRORLOG;

                        -lD:\System DB\mastlog.ldf

            i. goto cmd prompt and Type Sqlservr -f -T3608 and run          

            j.Run this Query

                        SELECT name, physical_name AS CurrentLocation, state_desc

            FROM sys.master_files

            WHERE database_id = DB_ID(N'master')                       

                        This will provide the current location of your master.

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

5    Moving mssqlsystemresource DB

           

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

           

            d. Move the mssqlsystemresource DB files (.mdf & .ldf) file the Destination Folder

                        Ex : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data  ---> D:\System DB

            e.  Open a Command prompt ( Run -> cmd)

            f.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            g.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            h.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            i.  Select the New Query Option  ( Now you give the Username and password)   .

            j.  Run this Query

                       

                        use master

            ALTER DATABASE mssqlsystemresource

            MODIFY FILE (NAME=data, FILENAME= 'D:\System DB\mssqlsystemresource.mdf');

            GO

            ALTER DATABASE mssqlsystemresource

            MODIFY FILE (NAME=log, FILENAME= 'D:\System DB\mssqlsystemresource.ldf');    

            GO

            k. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            l.  Press Y . then the SQL Server will Shut down

            m.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

6    Moving TEMPDB

            a.  Goto SQL Server Configuration Manager and open.

            b.  Select the SQL Sever 2005 Services.

            c.  Right click the SQL Server Service and Stop.

            d.  Open a Command prompt ( Run -> cmd)

            e.  Go to the SQLserver installation path ie C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

            f.  Type Sqlservr -c -m -T3608 and run.( This will Start the SQL Server in Single user mode).

            g.  Open the Microsoft SQLServer Management Studio ( While opening it will ask the username and pwd..

                 Dont give any details, Just open SSMS)

            h.  Select the New Query Option ( Now you give the Username and password)   .

            i.  Run this Query

                       

                        USE master;

            GO

            ALTER DATABASE tempdb

            MODIFY FILE (NAME = tempdev, FILENAME = 'D:\System DB\tempdb.mdf');

            GO

            ALTER DATABASE tempdb

            MODIFY FILE (NAME = templog, FILENAME = 'D:\System DB\templog.ldf');

            GO

            j. Press Ctrl + c in your opened cmd prompt . it will ask Do you wish to Shutdown the SQL Server <Y/N>

            k.  Press Y . then the SQL Server will Shut down

            l.  Goto the SQL Server Configuration Manager  and Restart the MSSQL server.

            m. Run this Query

                          SELECT name, physical_name AS CurrentLocation, state_desc

             FROM sys.master_files

             WHERE database_id = DB_ID(N'tempdb')

            ( I am Sure now the TEMPDB is moved to Destination Place)

SQL DBA - How to change the SQL Server in Single user mode & How to restore the Master DB

1. MASTER database is a different beast from all the other databases.
2. The MASTER database stores the logins and the lists of all of your databases.
3. Without the MASTER database, you can't start SQL Server.
4. It must be done in single user mode.

Changing into single user mode.
1. In SQL Server Configuration Manager, click SQL Server Services.
2. In the right pane, right-click SQL Server (), and then click Properties.
3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;).
4. For example, to start in single-user mode, insert -m; in front of the existing startup options, and then restart the database. (When you start SQL Server in single-user mode, first stop SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.)
Note :
After you are finished using single-user mode, you must remove the -m; from the Startup Parameters box before you can restart the server instance in the normal multi-user mode.
5. Click OK.
6. Restart the Database Engine.
Restoring Master DB.
C:\> sqlcmd
1> restore database master from DISK = 'C:\temp\master-20070822-02.bak';
2> go

SQL DBA - Finding Last action on a Database using Script

Select the DB and run the Script

WITH LastActivity (ObjectID, LastAction)
AS
(
SELECT object_id AS TableName, Last_User_Seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id)AS TableName,
MAX(la.LastAction)as LastSelect
FROM
sys.objects so
LEFT JOIN LastActivity la
ON so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

SQL DBA : Killing All the Session in a DB

USE MASTER

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_KillDatabaseProcesses]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_KillDatabaseProcesses]

GO

CREATE PROCEDURE dbo.sp_KillDatabaseProcesses

( @databaseName varchar(100))

AS

DECLARE @databaseId int,

        @sysProcessId int,

        @cmd varchar(1000)

EXEC ('USE MASTER')

SELECT @databaseId = dbid FROM master..sysdatabases

  WHERE [name] = @databaseName

DECLARE sysProcessIdCursor CURSOR FOR

   SELECT spid FROM [master]..[sysprocesses] WHERE [dbid] = @databaseId

OPEN sysProcessIdCursor

FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId

WHILE @@fetch_status = 0

  BEGIN

    SET @cmd = 'KILL '+ convert(nvarchar(30),@sysProcessId)

    PRINT @cmd

    EXEC(@cmd)

    FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId

END

DEALLOCATE sysProcessIdCursor            

GO

SQL DBA : Msg 7303, Level 16, State 1, Line 1 oracle linked server error

1- install the Oracle OLEDB,
2- create the linked server
3- run the openquery at the server (it worked!!)
4- run the openquery at a client Management Studio (it failed!!)
In our case it failed and returned the generic error:
Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".Msg

5- so we had to do this:
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
6- run the openquery at a client Management Studio (IT WORKS!!)

SQL DBA : How to Update Statistics

How to Update Statistics

There are two ways to update statistics. The first way is the easy way. It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.

EXEC sp_updatestats

The other way, is to use the UPDATE STATISTICS command. This command gives much better granularity of control:

-- Update all statistics on a table

UPDATE STATISTICS Sales.SalesOrderDetail

-- Update a specific index on a table

UPDATE STATISTICS Sales.SalesOrderDetail IX_SalesOrderDetail

-- Update one column on a table specifying sample size

UPDATE STATISTICS Production.Product(Products) WITH SAMPLE 50 PERCENT

Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system.

The following script updates all out of date statistics. Set the @MaxDaysOld variable to the number of days you will allow the statistics to be out of date by. Setting the @SamplePercent variable to null will use the SQL Server default value of 20,000 rows. You can also change the sample type to specify rows or percent.

DECLARE @MaxDaysOld int

DECLARE @SamplePercent int

DECLARE @SampleType nvarchar(50)

SET @MaxDaysOld = 0

SET @SamplePercent = NULL --25

SET @SampleType = 'PERCENT' --'ROWS'

BEGIN TRY

DROP TABLE #OldStats

END TRY

BEGIN CATCH SELECT 1 END CATCH

SELECT

RowNum = ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))

,TableName = OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id)

,StatName = st.name

,StatDate = ISNULL(STATS_DATE(object_id, st.stats_id),1)

INTO #OldStats

FROM sys.stats st WITH (nolock)

WHERE DATEDIFF(day, ISNULL(STATS_DATE(object_id, st.stats_id),1), GETDATE()) > @MaxDaysOld

ORDER BY ROW_NUMBER() OVER (ORDER BY ISNULL(STATS_DATE(object_id, st.stats_id),1))

DECLARE @MaxRecord int

DECLARE @CurrentRecord int

DECLARE @TableName nvarchar(255)

DECLARE @StatName nvarchar(255)

DECLARE @SQL nvarchar(max)

DECLARE @SampleSize nvarchar(100)

SET @MaxRecord = (SELECT MAX(RowNum) FROM #OldStats)

SET @CurrentRecord = 1

SET @SQL = ''

SET @SampleSize = ISNULL(' WITH SAMPLE ' + CAST(@SamplePercent AS nvarchar(20)) + ' ' + @SampleType,N'')

WHILE @CurrentRecord <= @MaxRecord

BEGIN

SELECT

@TableName = os.TableName

,@StatName = os.StatName

FROM #OldStats os

WHERE RowNum = @CurrentRecord

SET @SQL = N'UPDATE STATISTICS ' + @TableName + ' ' + @StatName + @SampleSize

PRINT @SQL

EXEC sp_executesql @SQL

SET @CurrentRecord = @CurrentRecord + 1

END

After updating the statistics, the execution plans that use these statistics may become invalid. Ideally SQL Server should then create a new execution plan. Personally, I prefer to help SQL Server out by flushing the cache. I would recommend you do the same. Note, this clears the entire procedure cache for the server, not just the database.

-- Clears the procedure cache for the entire server

DBCC FREEPROCCACHE

You should then also update the usage stats. Usage stats are the row counts stored for each index:

-- Update all usage in the database

DBCC UPDATEUSAGE (0);

If you are not already doing so, it is highly recommended to leave the default settings of “Auto Update Statistics” and “Auto Create Statistics” ON.