Pages

Tuesday, October 26, 2010

Find Index’s Table Name

Hello!!..

At times i came across situations when i need to search for particular index name and on which table it is and for which database. To help me out i came across with this small piece of code yet very useful. So posting it out thought might help out.

Query:

EXEC sp_MSforeachdb 'USE ?
select ''?'' as DatabaseName, st.name as TableName,si.name as IndexName from sys.indexes si
inner join sys.tables st
on
si.object_id = st.object_id
where si.name like ''%INDEXNAME%'''

Saturday, October 16, 2010

Change Data Capture Known as CDC

Change Data Capture popularly known as CDC is a new feature in SQL Server 2008 that records insert, update and delete activity in SQL Server tables.  A good example of how this feature can be used is in performing periodic updates to a data warehouse.  CDC records (or captures) DML activity on designated tables. CDC works by scanning the transaction log for a designated table's 'captured columns' whose content has changed and then making those changes available for data synchronizing purposes in a relational format. As you can see this feature in entrenched in transaction log architecture and thus a lot of the metadata in CDC is related around the concept of a Log Sequence Number (LSN). After performing some setup and configuration steps (which we will cover below), CDC will begin scanning the database transaction log for changes to certain tables that you specify, and will insert these changes into change tables.  These change tables are created during the setup and configuration process.  The setup and configuration process will also create table-valued functions which can be used to query for the changes.  You use the table-valued functions in lieu of querying the underlying change tables directly.  Based on this high level description, let's proceed to the demo.

Use the below query to check which databases is enabled for CDC.

select name,is_cdc_enabled from sys.databases where is_cdc_enabled = 1

Enabling CDC for database.

use <DatabaseName>

GO

Exec sys.sp_cdc_enable_db

GO

Replace <DatabaseName> with the database name on which the you want to enable CDC.

The above query enables CDC on database level.

Note: Just enabling CDC on database level does not capture any data change to any table.

Enabling CDC for Database Table.

These are parameter which we need to set for enabling CDC for new investran database tables for our environment.

exec sys.sp_cdc_enable_table

@source_schema = '<schemaname>',

@source_name = '<TableName>',

@role_name = '<CDCRoleName>',

@supports_net_changes = 1 or 0,

@captured_column_list = '[Column1],[ Column1],...,[ ColumnN]',

@filegroup_name = '<FileGroupName>';

Replace <schemaname>,<TableName>,<CDCRoleName>,[Column1],[ Column1],...,[ ColumnN] and <FileGroupName> as per your database details where

@source_schema is the name of the schema in which the source table belongs.

@source_name is the name of the source table on which to enable change data capture

@role_name is the name of the database role used to gate access to change data

@supports_net_changes indicates whether support for querying for net changes is to be enabled for this capture instance

@captured_column_list identifies the source table columns that are to be included in the change table

@filegroup_name is the filegroup to be used for the change table created for the capture instance

After running the above query two jobs <Databasename>_Capture and <Databasename>_Change get created. Once these jobs are running all the changes made to CDC enabled table gets captured in CDC tables.

Will cover more in next articles: Follow below link for Enabling CDC while restoring a Database

Enabling CDC while Restoring database

READING WINDOWS EVENTS AND INSERTING IN SQL SERVER

As a DBA its not only we need to check for SQL Server Error Logs but often we also need to check for Windows Event Log for any Critical Error or Warning as well. Incase a daily check list needs to be followed at that time reading events if in thousands and filtering is a big pain. Here is where WMI and Script comes handy.

So i have written below VBScript which reads Windows Application and System events for Errors and Warnings for 1 day back till date and inserts into a table into SQL Server. This can later be used for querying and multiple purposes etc. This can we turned to Weekly as well by Selecting number of days back you want the logs to to be Read.

This can further be used in SSIS Which i will be posting shortly.

Sub Main()
Const CONVERT_TO_LOCAL_TIME = False
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")
DateToCheck = CDate(Date)
'SET Number of Days back u want the logs to be read
dtmStartDate.SetVarDate DateToCheck - 1, CONVERT_TO_LOCAL_TIME
dtmEndDate.SetVarDate DateToCheck, CONVERT_TO_LOCAL_TIME
dim sServer, sConn, oConn,oRS
'Connection to SQL Server
'Provide Server Name instead of "." if any other machine
'Replace <DBServerName>,<USERID>,<PASSWORD>,<DATABASENAME> with your desired details
sServer="."
sConn= _
    "Provider=SQLOLEDB;Data Source=<DBServerName>;" & _
        "Trusted_Connection=Yes;Initial Catalog=<DATABASENAME>;" & _
             "User ID=<USERID>;Password=<PASSWORD>;"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn
Set oRS =CreateObject("ADODB.Recordset")
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & "." & "\root\cimv2")
'USE THIS FOR READING SYSTEM EVENTS AND TYPE ERROR
                                             Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate &

"'  and Logfile = 'System' and Type = 'Error'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)   
'REPLACE <TABLENAME> with your table Name
sSQL = "Insert into <TABLENAME> (LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next

'USE THIS FOR READING SYSTEM EVENTS AND TYPE WARNING
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate & "' and Logfile = 'System' and  Type =

'Warning'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name   
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next

'USE THIS FOR READING APPLICATION EVENTS AND TYPE ERROR
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & "." & "\root\cimv2")
                                             Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate &

"'  and Logfile = 'Application' and Type = 'Error'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name   
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next

'USE THIS FOR READING APPLICATION EVENTS AND TYPE WARNING
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate & "' and Logfile = 'Application' and  Type =

'Warning'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name   
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next
'ors.close

End Sub
'FUNCTION FOR PARSING DATE
Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
            & " " & Mid (dtmInstallDate, 9, 2) & ":" & Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))
End Function

Hope this helps you. Shortly will incorporate it with SSIS for Automation.

Thanks,

Manjunath C Bhat