Last execution date/time of DBCC CHECKDB

Execute the below query to check when was the last time DBCC CHECKDB has ran on all the Databases.

CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
)

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
)
SELECT CHECKDB1.Value AS DatabaseName
, CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp

Installing 32 bit SQL server 2008 on Wow64 (64 bit O.S).

Generally we inclined to navigate X86 folder and use setup100.exe executable file to install the 32 bit SQL server, which leads to below error.

SQL Server 2008 setup100.exe and setuppatch.exe cannot be run in this mode. If you are running setup100.exe, start setup.exe instead. If you are running setuppatch.exe, run it from the command prompt, as the user interface is not supported for this action. For more information about Setup, see SQL Server Books Online.
Error code 0x84B20001.

In SQL 2008, Installer window is little bit different compare to SQL 2005. Instead of navigating to X86 folder, click on setup.exe and go to Option tab and select the architecture as shown below.

DBCC FLUSHPROCINDB(db_id);

Have you guys heard of this ?
To flush the cached procedure plans of a single DB
DBCC FlushProcINDB(db_id);
Complete Server:
DBCC FREEPROCCACHE

Handy Dynamic Management Views:

1. SPID’s with their text.

select a.session_id,a.percent_complete,a.blocking_session_id,SUBSTRING(b.text,(a.statement_start_offset)/2+1,
(Case a.statement_end_offset
when -1 then DATALENGTH(b.text)
else a.statement_end_offset
end - a.statement_Start_offset)/2)
From sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(a.sql_handle) b

2. Index Fragementation:

SELECT object_name(b.object_id),a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('dbName'), OBJECT_ID(Null),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id


3. Stats last updated specific to an object:

SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats
WHERE object_id = OBJECT_ID('Person.Address');
GO

4. Stats last updated specific to an Index:

SELECT name AS index_name,
STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID('Person.Address');
GO

5. Top 20 Queries by Total CPU Time

select top 20 SUBSTRING(b.text,(a.statement_start_offset)/2+1,
(Case a.statement_end_offset
when -1 then DATALENGTH(b.text)
else a.statement_end_offset
end - a.statement_Start_offset)/2) as SQLText,
a.execution_count as Executes, a.plan_generation_num as [Plan Generation], a.creation_time as [Plan Cached], a.last_execution_time as [Last Executed],
a.total_worker_time/1000 as [CPU(ms)],a.total_elapsed_time/1000 as [Duration(ms)],a.total_physical_reads as [Physical Reads],
a.total_logical_reads as [Logical Reads], a.total_logical_writes as [Logical Writes], a.total_clr_time/1000 as [CLR Time(ms)]

from sys.dm_exec_query_stats as a cross apply sys.dm_exec_sql_text(a.sql_handle) b order by [CPU(ms)] desc

6. History of Wait Types:

select wait_type, case
when wait_type like N'LCK_M_%' then N'Lock'
when wait_type like N'LATCH_%' then N'Latch'
when wait_type like N'PAGELATCH_%' then N'Buffer Latch'
when wait_type like N'PAGEIOLATCH_%' then N'Buffer IO'
when wait_type like N'RESOURCE_SEMAPHORE_%' then N'Compilation'
when wait_type = N'SOS_SCHEDULER_YIELD' then N'Scheduler Yield'
when wait_type in (N'LOGMGR', N'LOGBUFFER', N'LOGMGR_RESERVE_APPEND', N'LOGMGR_FLUSH', N'WRITELOG') then N'Logging'
when wait_type in (N'ASYNC_NETWORK_IO', N'NET_WAITFOR_PACKET') then N'Network IO'
when wait_type in (N'CXPACKET', N'EXCHANGE') then N'Parallelism'
when wait_type in (N'RESOURCE_SEMAPHORE', N'CMEMTHREAD', N'SOS_RESERVEDMEMBLOCKLIST') then N'Memory'
when wait_type like N'CLR_%' or wait_type like N'SQLCLR%' then N'CLR'
when wait_type like N'DBMIRROR%' or wait_type = N'MIRROR_SEND_MESSAGE' then N'Mirroring'
when wait_type like N'XACT%' or wait_type like N'DTC_%' or wait_type like N'TRAN_MARKLATCH_%' or wait_type like N'MSQL_XACT_%' or wait_type = N'TRANSACTION_MUTEX' then N'Transaction'
when wait_type like N'SLEEP_%' or wait_type in(N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'WAIT_FOR_RESULTS') then N'Sleep'
else N'Other'
end as [Category],waiting_tasks_count as [Num Waits], wait_time_ms as [Wait Time], max_wait_time_ms as [Max Wait Time(ms)]
from sys.dm_os_wait_stats where waiting_tasks_count > 0 order by 3 desc,4 desc,5 desc

Email Configuration for SSRS:

Below is the procedure to configure an email account for SSRS.

1. Open reporting service configuration manager and connect to the report server.
2. In the Email setting tab, provide SMTP Server name and an email account that can send emails from the provided SMTP server.


Report Server subscriptions might fail to send an email if the remote SMTP server is used with the below error message:

The Error message: Failure sending mail: The server rejected the sender address. The server response was: 530 5.7.1 Client was not authenticated) (PFA – SMTP configuration & Error message)

This is because, if the SMTP server provided in report server configuration is other than local, there is small configuration change that needs to be updated in “RSreportserver.config” file which is located at “C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer”.

1. Open RSreportserver.config file and look for “SendUsing”. This value must set to 2 if we use remote SMTP server.
2. Look for SMTPAuthenticate and set its value to 2. This is to send emails to restricted distribution list that accept mails only through authenticated accounts.
3. After all it should be something like below .

"SMTPServer"smtp.adminworks.com"/SMTPServer"
"SMTPServerPort"25 "/SMTPServerPort"
"SMTPAccountName" "/SMTPAccountName"
"SMTPConnectionTimeout" "/SMTPConnectionTimeout"
"SMTPServerPickupDirectory" "/SMTPServerPickupDirectory"
"SMTPUseSSL" "/SMTPUseSSL"
"SendUsing"2"/SendUsing"
"SMTPAuthenticate"2"/SMTPAuthenticate"
"From"admin@local.com "/From"

Generic SQL Server Configuration Manager Issues

1. Error:
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

Above error will appear while opening SQL server configuration manager, if some of the Managed Object Format (.mof) files are not installed properly and registered during the SQL Server installation.

The solution is to register the missing .mof files using mofcomp.
Run the below command in cmd prompt.

For 64bit SQL

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files
(x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

For 32bit SQL

C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Output would be something like below.

Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180
Copyright (c) Microsoft Corp. 1997-2001. All rights reserved.
Parsing MOF file: C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmprovider
xpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!

If you run into any compilation error like - An error occurred while processing item 1 defined on lines 4 - 7 in file C:\Prog
ram Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof:
0X80041002 Class, instance, or property 'Name' was not found.
Compiler returned error 0x80041002

follow the below steps.

1. Stop WMI service
2. Navigate to the folder “C:\Windows\System32\wbem” and rename the repository folder to repository_old
3. Rerun the same cmd again
C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
-------------------------------------------------------------------------------------------------
2. Error Message:
WMI Provider Error: Access is denied. [0x80070005]
The best practice to update a service account password is through SQL Server Configuration Manager. But on Windows server 2008 you will see this error if UAC is enabled.

There is no solution that I am aware of, but here is the work around.

All we have to do is, click on the browse button to reselect the service account though it’s already listed, and then provide the pwd and click apply.