Search This Blog

Tuesday, September 27, 2011

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)


Monday, August 29, 2011

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

Thursday, August 25, 2011

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)

Tuesday, August 16, 2011

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

Friday, May 27, 2011

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.