How to find time it took to failover SQL Server cluster from SQL Server error log

First run following query to read current error log. Please change date range to fit the time when failover happened

USE Master

GO

Exec xp_ReadErrorLog 0, 1, 'The NETBIOS name of the local node that is running the server is', NULL, '2012-03-21', '2012-03-29', 'desc'

After the failover new error logs are created. So go to the prevous error log and run the same script. You might want to change start and end time according to your error log retention setting

Following are parameters details for xp_readerrorlog

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time  
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending

SQL Server - How to Grant Read Access to ALL databases to a Login?

Results of this script should be run on destination server to grant read access to a login to all databases on a server. System databases are skipped

select 'USE ['+ name +  ']' + char(10) + 'GO' + char(10) +
'CREATE USER [ssrsuser] FOR LOGIN [ssrsuser]' + char(10) +
'GO' + char(10) +
'USE [' + name + ']' + char(10) +
'GO' + char(10) +
'EXEC sp_addrolemember N''db_datareader'', N''ssrsuser''' + char(10) + 'GO'
from sys.databases
where database_id > 4
order by name

Handling ad hoc workload using optimize for ad hoc workloads

How to find out if you need to enable “optimize for ad hoc workloads”

1. Plan cache size:

SQL Server 2008 is calculated as following

% Memory of target (GB)	Total GB	SERVER1
75% visible target memory from 0-4GB	4	3
10% visible target memory from 4-64GB	56	5.6
5% visible target memory > 64GB	0	0
		8.6

2. SERVER1 stats
   
   

Run following query on the server to get the stats

-- Do not run this TSQL until SQL Server has been running for at least 3 hours

SET NOCOUNT ON

SELECT objtype AS [Cache Store Type],

        COUNT_BIG(*) AS [Total Num Of Plans],

        SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576 AS [Total Size In MB],

        AVG(usecounts) AS [All Plans - Ave Use Count],

        SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/ 1048576

        AS [Size in MB of plans with a Use count = 1],

        SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Number of of plans with a Use count = 1]

       

        FROM sys.dm_exec_cached_plans

        GROUP BY objtype

        ORDER BY [Size in MB of plans with a Use count = 1] DESC

DECLARE @AdHocSizeInMB decimal (14,2), @TotalSizeInMB decimal (14,2)

SELECT @AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc'

THEN size_in_bytes ELSE 0 END) as decimal(14,2))) / 1048576,

        @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576

        FROM sys.dm_exec_cached_plans

SELECT @AdHocSizeInMB as [Current memory occupied by adhoc plans only used once (MB)],

         @TotalSizeInMB as [Total cache plan size (MB)],

         CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))

         as [% of total cache plan occupied by adhoc plans only used once]

IF  @AdHocSizeInMB > 200 or ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25  -- 200MB or > 25%

        SELECT 'Switch on Optimize for ad hoc workloads as it will make a significant difference' as [Recommendation]

ELSE

        SELECT 'Setting Optimize for ad hoc workloads will make little difference' as [Recommendation]

GO

On an average 24% of cache is used by ad-hoc plans which are used once

CacheType	Total Plans	Total MBs	Avg Use Count	Total MBs - USE Count 1	Total Plans - USE Count 1
Adhoc	14004	4711.320312	37	1658.414062	2902
Proc	3812	2138.3125	306590	970.523437	1184
Prepared	570	52.882812	3755	14.898437	84
Trigger	7	1.101562	57	0.085937	1
UsrTab	2	0.4375	16	0	0
View	796	74.242187	50	0	0
Check	21	0.585937	75	0	0

Current memory occupied by adhoc plans only used once (MB)	Total cache plan size (MB)	% of total cache plan occupied by adhoc plans only used once
1666.85	6994.29	23.83

So on SERVER1 8.6 GB of cache is available to SQL and about 2GB is being used by ad-hoc workload.

3. Advantage:

It is recommended that if more than 25% of procedure cache is used by ad-hoc queries then it is advisable to turn on. When this option is turned on, once the database engine has compiled a batch for the first time, instead of saving the full compiled plan (potentially of several tens of kilobytes) it instead saves just a tiny 18 byte “stub”. This saves considerable space in the procedure cache.(Ref http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/). In our case we will save about 2 GB memory if we turn on this option

4. Disadvantages:

Even though batch is ad-hoc there is possibility that it will need to run again. In such case plan will be compiled twice. Hence we run into risk of over compilation.

5. Conclusion:

We are at the borderline of threshold (25%) so at this time it will not make much difference if we turn on this option. Since we are in process of adding more memory to the server we should revisit this at that time. At this time I would focus more on reducing the number of ad-hoc plans by reviewing SPs for parameterization.

In case you decide to enable this option; here is the script to do so

sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'optimize for ad hoc workloads', 1

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO

How to move SQL Server database files to different location

How to move SQL Server database files to different location

(Works for SQL 2005/2008)

In case you need to move database files (data or log) to new location one way to do it is to detach the database, move the files to location and attach it back

1)      Detach database: Detaching a database removes it from the instance of the Microsoft SQL Server Database Engine but leaves intact the database, with its data files and transaction log files. Following script can be used

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

GO

2)      Move files to new location: You can manually copy the files but I prefer to use XCOPY. Here is how

xcopy "C:\DATA\MyDatabase_Data.mdf" "D:\DATA\MyDatabase_Data.mdf" /P

3)      Attach the database: Attaching a database places it in exactly the same state that it was in when it was detached. Here is how

USE master;

GO

CREATE DATABASE MyAdventureWorks

    ON (FILENAME = 'D:\DATA\MyDatabase_Data.mdf')

    FOR ATTACH;

GO

4)    Final step is to verify that files are where you expect them to be

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'MyDatabase');

Adding and removing TempDB files

Add/Remove data file to avoid TempDB Running out of space

Today I ran into a situation where there was heavy use of TempDB which caused data files to grow beyond the capacity of the drive.

So as an immediate solution I decided to add one more file to TempDB on a separate drive where there was space available. As you know this is going to be a NDF file.

To avoid new drive running out of space this new file needs to be restricted in maximum growth.

Use following script to add the file

USE [master]

GO

ALTER DATABASE [tempdb]

ADD FILE ( NAME = N'tempdev01',

FILENAME = N'D:\DATA\tempdev01.ndf' ,

SIZE = 10240000KB ,

MAXSIZE = 76800000KB ,

FILEGROWTH = 102400KB )

GO

Now once you know use of TempDB has receded and it is safe to remove the NDF file following script should be used. This is done in 2 parts.

1)      File can only be removed if it is empty so first we empty the file

use tempdb

go

DBCC SHRINKFILE ('tempdev01' , EMPTYFILE) ;

go

2)      After the file is empty then file can be removed

USE Master

GO

alter database tempdb REMOVE file [tempdev01] ;

So in short it is possible to temporarily allocate disk space to TempDB to avoid stoppage.

PS: No need to restart SQL Service for removing TempDB files

SQL Server 2008 R2 Installation error

While installing SQL Server 2008 R2 enterprise edition on WIndows Server 2008 R2 the SQL install fails with following error

TITLE: Microsoft SQL Server 2008 R2 Setup
------------------------------
The following error has occurred:
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0xD15B4EB2%25400x4BDAF9BA%25401306%254024

Cause:
This happens because account under which SQL is running is not able access protected folders on C:

To resolve this issue

1. Uninstall SQL
2. Remove all folders created during install (C:\program files, C:\Program Files (X86)
3. Add SQL servier service account (Domain account in my case) to local administrators group on the server
4. Run the setup again

Some useful DMVs

/*--Gives portion of buffer used by an object. Includes index ID (if 0 then heap)
--Breaks down buffers by object (table, index) in the buffer pool
--This should be run for individual database*/

USE TESTDB
GO
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [Buffer size(MB)], COUNT(*) AS [Buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

/*--Provides login name and session counts for that login.
--This is useful when there are multiple app users connecting*/
USE Master
GO
SELECT login_name as [Login Name] , COUNT(session_id) AS [Session count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC;

/*--Gives logical/physical CPU count, hyper threading and total memory*/
USE Master
GO
--SQL 2005
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;
--SQL 2008
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info;