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.

SQL DBA - Corrupted DB ( logical consistency-based I/O error: torn page \ incorrect pageid \ incorrect checksum)

Errors :-

When u Run a Query you will get a Error like this :-

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xd9706042). It occurred during a read of page (1:91308) in database ID 5 at offset 0x0000002c958000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Adb.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

From Error Log you will find the Following Errors :-

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:91422; actual 20559:675107653). It occurred during a read of page (1:91422) in database ID 8 at offset 0x0000002ca3c000 in file 'H:\ITPS\PATDatabases\Adf.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

2011-01-29 12:14:07.56 spid117 Error: 824, Severity: 24, State: 2.

2011-01-29 12:14:07.56 spid117 SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x474447a6). It occurred during a read of page (1:91410) in database ID 8 at offset 0x0000002ca24000 in file 'H:\ITPS\PATDatabases\Adf.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

2011-01-30 00:00:22.34 spid19s This instance of SQL Server has been using a process ID of 6132 since 1/29/2011 1:32:31 AM (local) 1/28/2011 8:02:31 PM (UTC). This is an informational message only; no user action is required.

2011-01-30 08:27:27.60 spid115 Error: 824, Severity: 24, State: 2.

2011-01-30 08:27:27.60 spid115 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x67e10a73; actual: 0x5b34192a). It occurred during a read of page (1:91407) in database ID 8 at offset 0x0000002ca1e000 in file 'H:\ITPS\PATDatabases\Adf.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

2011-01-31 00:00:52.72 spid15s This instance of SQL Server has been using a process ID of 6132 since 1/29/2011 1:32:31 AM (local) 1/28/2011 8:02:31 PM (UTC). This is an informational message only; no user action is required.

Solution For this Error :-

Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First.

Follow the Steps to Recover the Tables.

1. Check which Table is Corrupted using

DBCC Checktable ('Your_Table_Name’).

2. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select as NONE.

3. Run this Query to Change DB into Single User Mode

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Run this Query to Recover the Table/DB.

DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)

(OR)

DBCC CheckDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

5. Once this Execution Completed Sql returns “Errors are Corrected”

6. Run this Query to Change the DB into MultiUser Mode

ALTER DATABASE ApplicationManager SET MULTI_USER

7. 7. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select the Option CHECKSUM.

8. Now Run DBCC CHECKDB('Your DB')

No Error’s Will Show

SQL DBA -SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed

To workaround the problem:

1. Download MSMXL fix from the following Microsoft website:

Download Here

2. After Downloading the .Exe file, Extract in to a Folder.

3. Run the msxml6.msi file.

4. After completing the setup. Run the msxml_fix file. (This will Fix all the errors).

5. Try to Reinstall the SQL 2005 Setup.