Error while opening SQL Server configuration manager

I am using SQL Server 2008 ,and I got the below error while trying to open SQL Server configuration manager :

 

 

To work around this problem, open a command prompt, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof" After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation
Plz make sure to use " number" as Sql server version like 90,100,1110

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e25e284b-6c95-4205-9714-4aa355edfa2a/sql-server-configuration-manager-cannot-connect-to-wmi-provider-invalid-class-0x80041010?forum=sqlkjmanageability

What is Checkpoint in SQL Server?

What is Checkpoint in SQL Server?


Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.
It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.
Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

In SQL Server 2012 there are four types of Checkpoints:
Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.
Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.
Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.
Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:
 1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)
 2. If the recovery model gets changed from Full\Bulk-logged to Simple.
 3. While taking Backup of the Database.
 4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
 5. Alter Database command to add or remove a data\log file also initiates a checkpoint.
 6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
 7. DB Snapshot creation.

difference between Checkpoint and Lazy Writer

 difference between Checkpoint and Lazy Writer

Row	Checkpoint	Lazy writer
1	Checkpoint is used by sql engine to keep database recovery time in check	Lazy writer is used by SQL engine only to make sure there is enough memory left in sql buffer pool to accommodate new pages
2	Check point always mark entry in T-log before it executes either sql engine or manually	Lazy writer doesn’t mark any entry in T-log
3	To check occurrence of checkpoint , we can use below query select * from ::fn_dblog(null,null) WHERE [Operation] like ‘%CKPT’	To check occurrence of lazy writer we can use performance monitor SQL Server Buffer Manager Lazy writes/sec
4	Checkpoint only check if page is dirty or not	Lazy writer clears any page from memory when it satisfies all of 3 conditions. 1.      Memory is required by any object and available memory is full 2.      Cost factor of page is zero 3.      Page is not currently reference by any connection
5	Checkpoint is affected by two parameters 1.      Checkpoint duration: is how long the checkpoint can run for. 2.      Recovery interval: affects how often it runs.	Lazy writer is affected by 1.      Memory pressure 2.      Reference counter of page in memory
6	Check point should not be very low , it can cause increasing recovery time of database	No. of times lazy writer is executing per second should always be low else it will show memory pressure
7	Checkpoint will run as per defined frequency	No memory pressure, no lazy writer
8	Checkpoint tries to write as many pages as fast as possible	Lazy writer tries to write as few as necessary
9	checkpoint process does not put the buffer page back on the free list	Lazy writer scans the buffer cache and reclaim unused pages and put it n free list
10	We can find last run entry of checkpoint in Boot page	Lazy writer doesn’t update boot page
11	Checkpoint can be executed by user manually or by SQL engine	Lazy writer cant be controlled by user
12	It keeps no. of dirty pages in memory to minimum	It helps to reduce paging
13	Auto frequency can be controlled using recovery interval in sp_configure	Works only @ memory pressure , It uses clock algorithm for cleaning buffer cache
14	It will be automatically executed before every sql statement which requires consistent view of database to perform task like (Alter, backup, checkdb, snapshot …..)	It kicks pages out of memory when reference counter of page reaches to zero
15	Command : Checkpoint	No command available
16	It comes in picture to find min lsn whenever t-log truncates	No entry in T-log
17	Checkpoint is affected by Database recovery model	Lazy writer doesn’t get impacted with recovery model of database
18	To get checkpoint entry in error log DBCC TRACEON(3502, -1)	Not Applied
19	Members of the SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR can execute checkpoint maually	Not Applied

How to Check SQL Server Authentication Mode..?

DECLARE @AuthenticationMode INT

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServer',

N'LoginMode', @AuthenticationMode OUTPUT

SELECT CASE @AuthenticationMode

WHEN 1 THEN 'Windows Authentication'

WHEN 2 THEN 'Windows and SQL Server Authentication'

ELSE 'Unknown'

END as [Authentication Mode]

Finding the DB name and Table name based on coloumn from whole instance

Below script helps you to find the DB name, Table when you know only column name and not sure about database and table name.

exec sp_msforeachdb 'select TABLE_CATALOG as DBName,TABLE_NAME,COLUMN_NAME

from [?].INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME= ''Column Name'';'