PowerShell Script to find SQL Agent Job details on a SQL server instance

The below script helps to find SQL Agent Job details on a SQL server instance
 

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

SQL Server Cluster Installation AD Permission error SQL Server 2012 and SQL Server 2008R2

Recently when I was Installing SQL Server 2012 and SQL Server 2008 R2 Cluster, the installation completed successfully but the SQL Resources failed with the below error,

Cluster network name resource 'SQL Network Name (<SQLNetworkName>)' failed to create its associated computer object in domain 'DomainName' during: Resource online.

The text for the associated error code is: Access is denied.

Please work with your domain administrator to ensure that:

- The cluster identity '<Clustername>$' has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘<Clustername>$'.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity ‘<Clustername>$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

This error is due to the permission issue within the OU of the Cluster identity in the AD Server.The install creates computer objects in a staging area of AD and tries to move it to the Cluster OU when the resources are brought online. 

To resolve this either you might have to ask the Windows Admin to delegate access within Cluster OU or ask them to move the Objects from Staging Area to Cluster OU manually. Remember the SQL Server install was successful so once the permission is grated all you need to do is bring the SQL Services online.

The below MSDN article has an detailed explanation of the architecture based on the above problem(http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_installer)

MS Access DB to SQL Server Migration Post Migration Issues

Last week I had to migrate a Access database to SQL Server database with Access Front end, Though the actual migration of data was pretty much straight forward using SQL Server Migration Assistant I had to do a lot of post migration corrections and taught might me a good idea to share, I won’t go much into how the data need to be migrated as SSMA does everything for you, the only thing you need to remember is if are going to use MS Access as frontend Application after the data is migrated to SQL server don’t forget to check the Link table option to link the tables in SQL server to the Application data source.

Right, now once the data is migrated the below are the post migration tasks you might need to take care.

1.       Most of the issues comes around the datetime datatype as the date formats are different in SQL server and MS Access, I usually run the following query to find all the datetime columns and verify the format.

select a.* from sys.columns a

Join sys.objects b ON a.object_id = b.object_id

where a.user_type_id = 42 and b.type = 'U'

2.       Some of the date columns might be recognized as nvarchar due to US/UK date formats and use of date() functions within MS Access tables, Remember dates are saved in #<date># format in MS Access; Eg:- #01-01/2013#

I use the following TSQL to find obvious date/Time columns, You can add few other strings based on your application. Remember this just obvious date columns assuming the naming conventions will have date or time string on all datetime columns there may some date columns with different column name which should be dealt case by case basis.

select a.* from sys.columns a

Join sys.objects b ON a.object_id = b.object_id

where a.user_type_id <> 42 and b.type = 'U' and (a.name like '%date%' OR     a.name like'%time%' OR a.name like '%dob%')

3.       In the Front End MS Access Application change all Date input box format property to yyyy-mm-dd, and delete any format given in Input Mask property. This is because when you have the date column for the text box input in SQL Server the input mask and default Access format will send the date ad Varchar to SQL server and the Application will error out with string conversion failure.

4.       After the Access database was migrated to SQL there may be issues with some of the Access reports if it’s using Date() function in Access query due to the date format, we need to modify all date() function to format(date(),”yyyy-mm-dd”). If any reports uses hard coded date in their query change it using format function Eg:-Format(#01/10/2012#,"yyyy-mm-dd") 

5.       It is always a best practice to disable the Navigation buttons in all the forms and subforms after migration, Any search issued using the Navigation button will kill the front-end as it issues a very very bad designed query to SQL server using In-built Find command in MS Access, The search created 100 MB Trace file for one of my search for a record in 3000 rows SQL server table that because MS Access does not do row based search instead does a search for every element in the table, say there are 3000 rows and 10 columns the search runs for all 3000 * 10 rows to find a single record. Instead create search customized search functionality in the form using Filter function. Eg:- I used a button for customized search and created a filed in Click event with the following code,

Option Compare Database

Private Sub Command26_Click()

      Dim strIntBoxname As String

Dim strFilter As String

strIntBoxname = InputBox("Enter Input", _

      "Input Filter", "")

      If strIntBoxname = "" Then

      Me.FilterOn = False

      Else

      strFilter = BuildCriteria("[Col Name]", dbText, strIntBoxname)

      Me.Filter = strFilter

      Me.FilterOn = True

      End If

End Sub

6.       If you have subform in MS Access, use dataset table object instead of SQL query as some queries may not be recognized in SQL server, so it’s better to use object based approach. In my case my sub form did not populate or allow edit functionality to the user.

Hope the above step helps, There were not a lot of article in the web specific to these post migration issues.

PowerShell Script to Find last Backup and Restore of Databases

The below script helps to find Database last backup and restore time on a given set of SQL server instances
 

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Find the reason for recompilation using SQL Server Profiler

Often as a DBA you encounter performance issues on SQL due to frequent recompilation of the SQL Statement or a Store procedure, We can detect frequent SQL Recompilation by,

1.       SQL Profiler SP:Recompile and SQL:StmtRecomple Event classes

2.       Perfmon SQL Counter SQL:Statistics(Re-Compilations/sec)

3.       DMV sys.dm_exec_query_stats; plan_generation_num Column.

As the scope of our article is not detecting SQL Recompilation but find the reason for recompilation I am not going to detail on the above more.

The reason for recompile can be detected using SQL Server Profiler in EventSubClass Column corresponding to SP:Recompile and SQL:StmtRecomple Event classes. Before We go into an example

I am listing Type of event subclass. Indicates the reason for recompilation.

1 = Schema Changed

2 = Statistics Changed

3 = Recompile DNR

4 = Set Option Changed

5 = Temp Table Changed

6 = Remote Rowset Changed

7 = For Browse Perms Changed

8 = Query Notification Environment Changed

9 = MPI View Changed

10 = Cursor Options Changed

11 = With Recompile Option

Ref:- http://msdn.microsoft.com/en-us/library/ms187105.aspx

Let’s see an Example, I am using tempdb and a #table for this example, I have Started SQL Server profiler and have added Event Class, TextData, SPID and Event Sub Class as my columns and have added

SP:Recompile, RPC:Completed,SQL:StmtRecompile and SQL:BatchCompled eventsto the trace.

DROP TABLE #Temptbl

Create Table #Temptbl (col1 int)

INSERT INTO #Temptbl Values(1)

select * from #Temptbl

select * from #Temptbl

Note:- The event subclass shows Deferred compile because the object #Temptbl is not available during compilation.

ALTER TABLE #Temptbl

ADD Rnum int IDENTITY (1,1)

select * from #Temptbl

declare @i int;

set @i = 0;

while (@i < 1000)

begin

insert into #Temptbl values(@i);

set @i = @i + 1;

end

select * from #Temptbl

CREATE CLUSTERED INDEX idx1 ON #Temptbl(Rnum)

ALTER INDEX idx1 ON #Temptbl REBUILD

select * from #Temptbl

DELETE FROM #Temptbl where Rnum > 100

select * from #Temptbl where Rnum > 25300 and Rnum < 35300 OPTION(RECOMPILE)

But the most common way an SP gets recompiles is for EventSubClass:Statistics Change which I have not dealt in the above example. I will be writing a separate article on Temporary table and permanent table Cardinality and Recompilation where I will explain in detail. This article is intended to show how to find why SP or SQL batch are recompiled.

Check if a Temporary table exists

To check if a temporary table exists,The usual method of querying sys.objects table on tempdb will not store #tables in the same naming convention as we create, So one of the best way to check if a #table exists is as below,

IF object_id('tempdb..#MyTempTable') IS NOT NULL

BEGIN

<TSQL Batch>

END

Find last statistics update date

Find below 2 ways to find the last statistics update on a SQL table,

1. Using STATS_DATE Function,

SELECT name AS index_name,

STATS_DATE(object_id, index_id) AS statistics_update_date

FROM sys.indexes

WHERE object_id = OBJECT_ID('tblname');

Ref:- http://msdn.microsoft.com/en-us/library/ms190330.aspx

2. Use SHOW_STATISTICS consistency checker.

 DBCC SHOW_STATISTICS ('tblname', indexname);

Ref:- http://msdn.microsoft.com/en-us/library/ms174384.aspx