Search This Blog

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)
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())
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())
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
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

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]
CREATE PROCEDURE dbo.sp_KillDatabaseProcesses
( @databaseName varchar(100))
DECLARE @databaseId int,
        @sysProcessId int,
        @cmd varchar(1000)
SELECT @databaseId = dbid FROM master..sysdatabases
  WHERE [name] = @databaseName
   SELECT spid FROM [master]..[sysprocesses] WHERE [dbid] = @databaseId
OPEN sysProcessIdCursor
FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId
WHILE @@fetch_status = 0
    SET @cmd = 'KILL '+ convert(nvarchar(30),@sysProcessId)
    PRINT @cmd
    FETCH NEXT FROM sysProcessIdCursor INTO @sysProcessId
DEALLOCATE sysProcessIdCursor