Befriending Dragons

Intersectional Coaching Journeys: Reframe, Unblock, Move Forward

Leave a comment

How to Find the Amount of Fragmentation on Your SQL Server Instance

Need to find the amount of fragmentation in all your indexes in all databases for a given instance of SQL Server 2005 or later? You can use the undocumented/unsupported sp_MSforeachdb to loop through all the databases. For more information on fragmentation see my previous blog


— Cindy Gross 2009

— find fragmentation on all indexes in all databases on this instances

— to find fragmentation on just one db comment out the exec master… line and the last quote

— you must use the db_id() rather than NULL as the first parameter of the DMV or it will try to do a join across all dbs


exec master.sys.sp_MSforeachdb ‘ USE [?];


DECLARE @starttime datetime, @endtime datetime

SELECT @starttime = GETDATE()

SELECT db_name() as CurrentDB, @starttime as DBStartTime

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id, DB_ID()) as ObjectName,

a.index_id, as IndexName,

avg_fragmentation_in_percent, page_count, index_depth, index_type_desc, alloc_unit_type_desc

— , record_count, avg_page_space_used_in_percent –(null in limited)

FROM sys.dm_db_index_physical_stats (db_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

–WHERE index_id > 0 — exclude heaps

ORDER BY DatabaseName asc, avg_fragmentation_in_percent desc, ObjectName asc, IndexName asc

SELECT @endtime = GETDATE()

SELECT @starttime as StartTime, @endtime as EndTime, DATEDIFF(MINUTE,@starttime,@endtime) as TotalMinutes

Leave a comment

How People Abuse Their SQL Server Transaction Log – Things NOT to Do

Care and Feeding of the Transaction Log


Want to learn more about how the transaction log works? Kalen Delaney, celebrated author of SQL Server 2008 Internals and the Inside SQL Server series as well as a world class trainer and performance tuner,  is giving a pre-conference talk at SQL PASS next week. The pre and post conference sessions are full day sessions you can purchase as an add-on to the regular conference (which runs Tuesday-Thursday). Kalen’s pre-conference session “Care and Feeding of the Transaction Log” is scheduled for Monday, November 2, 2009. She will be raffling off copies of her book and will have copies of her DVD to give away. If you are guilty of any of the transaction log practices below, or have colleagues who are and you need ammunition in your fight for change, Kalen’s session will be a great help to you! Note that she will not necessarily be covering all the items below, they are my own list and not hers. 🙂

How People Abuse Their SQL Server Transaction Log – Things NOT to Do

·         Rely on autogrow – this causes fragmentation, poorly allocated VLFs, a performance hit for duration of grow, etc.

·         Don’t take backups at all or do them on a schedule that doesn’t meet SLAs or doesn’t keep the log “small enough”.

·         Use simple recovery mode in the belief it will improve performance when there are no bulk inserts or something else that actually benefits from minimal logging.

·         Want to “turn off logging”.

·         “Shrink” the log by deleting the LDF file – can result in corruption and an unusable database.

·         Shrink the log file when it will just grow again.

·         Add multiple LDF files due to the mistaken impression that this will improve performance (multiple threads myth or thinking it will split IO over multiple files when logs are mostly sequential). This is particularly a problem with TempDB where we recommend 1/4 to 1 file per core and don’t always make it clear we mean only the data files and not the log file.

·         Only backing up the log files when they reach a certain percentage full to reduce the number of backups occurring at any one time – this can result in not being able to meet your recovery SLAs.

·         Enable instant file initialization and expect it to help with log growth.

·         Create one extremely large log file without regard to # of VLFs created.

·         Sharepoint documents have recommended simple recovery mode to “keep the log from filling” but don’t explain the tradeoffs for recoverability such as losing Point in Time recovery options.

·         Don’t understand the difference between truncating and shrinking the log.

·         Don’t realize that in simple mode you still have to do a full backup first.

·         Don’t realize they need to do a log backup after switching from simple or bulk logged to full.

·         Think DBCC CHECKDB checks for inconsistencies in the log

·         Put data and log on same drive (mix random and mostly sequential), put lots of logs on one drive (defeats purpose of having a log/sequential writes on own IO path), put output of profiler or other activity on same drive as a log

·         Don’t put log on fast enough/properly configured drive (RAID 10, disk partition alignment, separate from file server/other SQL/db data, disk allocation unit = 64k, HBA queue depth 64+, anything that gets writes to 3ms or less, etc.)

·         Ignore corruption messages/event log messages about bad IO

·         Don’t secure the directories where the MDF/LDF and backup files reside

·         Ignore the fact that Tran replication affects log size (can’t truncate until log reader has read data from log)

·         Do many transaction log backups between full backups (with few or no differentials) and don’t consider what happens if one of the files is corrupted/missing or how long it will take to do the restores

·         Don’t test the DR strategy, or at least have it documented and planned/thought out

·         Don’t exclude the MDF/LDF files from virus scanning software


Instead of abusing your transaction log, pay attention to Pond’s Twelfth Law: Don’t practice in front of the CIO.  A professional prepares ahead of time. To help you prepare, attend Kalen’s pre-conference session!


Checklist for installing SQL Server 2005 as a clustered instance

Checklist for installing SQL Server 2005 as a clustered instance



1)      Verify the Windows cluster is set up per basic best practices and that basic failover works.

2)      Verify you have the latest patches, especially security patches, for Windows.

3)      For Windows 2008: Validate the configuration using “Validate a Configuration” in Failover Cluster Management.

4)      For Windows 2008: Make sure your quorum choice is appropriate for the number of nodes and other factors in your environment.

5)      Unless you have verified that your network cards support it, disable TCP Chimney and the other SNP settings.

6)      Verify none of the cluster nodes are domain controllers.

7)      Request a disk subsystem configured per IO and recoverability best practices that meets your minimum performance requirements (often you will require that Avg disk sec/read < 10-20ms and Avg disk sec/write < 3-5ms for a given load on the system).

8)      Have one or more “shared disks” that are not otherwise used by anything else (not even another instance of SQL) available to SQL Server. Create a new group and move the disk(s) from “Available Storage” (new in Windows 2008) to the group you created for this SQL Server instance. The disks should be configured per database best practices. Generally no other resources should be in this group.



9)      Download whatever service packs (SPs) and cumulative updates (CUs) you will be installing. Make sure the files you download are the proper architecture (x86 vs. x64).

10)   Download Visual Studio 2005 SP1.

11)   Find a new, (preferably) static IP address that is not currently used by anything. You will enter this during setup and the setup process will take care of adding it to DNS and other locations as appropriate.

12)   Find or create one or more domain groups that will be used by setup. The best practice is to use three unique groups for each instance of SQL Server, one each for SQL Server, SQL Agent, and Full Text. If the account you are using for setup does not have the permission to add the startup account(s) to the groups, add them manually ahead of time.

13)   Determine your SQL virtual name (cannot be used for a physical or virtual machine anywhere in the domain).

14)   Determine an instance name which will be unique in the cluster. Note that the name of a default instance is implicitly MSSQLSERVER so you can only have one default instance per cluster.

15)   Determine which domain user account(s) you will assign to run SQL Server, SQL Agent, and Full Text. The account(s) you choose will be added to the domain groups by the setup process if your setup account has permissions to do so. As a security best practice each service should have a unique account.

16)   Look through the items in my blog to find possible setup blockers



17)   From the node which currently owns the group with the disks to be used by SQL Server, log in with an account that is a local admin on all nodes.

18)   Make sure no one is logged on through terminal services to any of the remote nodes.

19)   Make sure the Remote Registry service, Cryptography services, and Task Scheduler service are started on all nodes.

20)   Verify all available disks in the cluster are online, even those that SQL Server will not use.

21)   Stop non essential services that may slow down file copies (like virus scanners) or try to connect to SQL Server (like IIS or monitoring tools).

22)   Install the RTM version of SQL Server, for SQL Server 2005 you run setup once per instance. It will install the cluster aware components on all nodes. This includes SQL Server and Analysis Services if you choose them.

1.       Install the SQL SP.

2.       Install the SQL CU.

3.       Install the VS05 SP1.

23)   On every other node in the cluster, if you want the non-cluster aware components to be available, install those components on each node.

1.       RTM (for example, you may want to install the client tools and SSIS on the other node(s))

2.       SQL SP.

3.       SQL CU.

4.       VS05 SP1.



24)   Make SQL depend on any drives it will use for data and log files. Those drives must be in the SQL Server group.

25)   If you will be using DTC, you may want to cluster it.

26)   Update MSDTSSrvr.ini.xml on each node to point to the SQL Server virtual nameinstance. If you have multiple instances of SQL Server that will store SSIS packages you can add multiple instance names to the file.

27)   Consider setting “Max server memory” for each instance of SQL Server.

28)   Make sure the SQL Server service is set to “affect the group”.

29)   Follow your normal SQL Server best practices and standard configuration such as removing builtinadministrators and configuration maintenance operations.



·         The 3 Things you Need to Know to Install SQL 2005 on Windows 2008 Cluster

·         List of known issues when you install SQL Server 2005 on Windows Server 2008;EN-US;936302

·         SQL Server 2005 Failover Clustering White Paper

·         System Configuration Check (SCC)

·         Hardware and Software Requirements for Installing SQL Server 2005

·         How to: Create a New SQL Server 2005 Failover Cluster (Setup)

·         SQL Server 2005 Readme

·         Changes to the readme file for SQL Server 2005;EN-US;907284

·         (my blog) SQL Server 2005 Clustering Tips/References

·         (my blog) How to configure DTC for SQL Server in a Windows 2008 cluster

1 Comment

SQL Server Security Granularity

I have had some questions recently about how to grant developers certain permissions without giving them sysadmin rights. Hopefully this summary will help you determine how to grant the least possible privileges. The summary is based on SQL Server 2005 but will also apply to SQL Server 2008.

·         I would hesitate to grant any more permissions in development than they get in production. This means avoid not only sysadmin but also db_owner where possible.

o   This avoids problems where they spend a long time developing something only to find out at the last minute that it won’t work with production permissions.

o   If there is any production data on the development system it may be more vulnerable to attack when more people have elevated permissions.

o   As an alternative you may want to create an application that lets them submit requests to do things that require elevated permissions. It can log on to SQL Server with the appropriate permissions and perform whatever action they need. It can optionally log this activity, create a change request ticket, email the DBAs, or whatever you like. This should reduce the chance that some elevated permission need makes it into the application because it is much more obvious when they are performing an activity that they or the application will not be able to do in production.

·         Generally you will not want to grant CREATE DATABASE permissions to non-DBAs. Creating databases involves OS level permissions and space management, performance considerations, best practice implementations, backups, maintenance, etc. Also, the creator of a database can make themselves a db_owner which is usually more than a developer needs. If you do decide to grant permissions to create databases, the permission is GRANT CREATE DATABASE TO … and/or GRANT ALTER ANY DATABASE TO ….

·         The KILL command to kill an existing SPID requires either PROCESSADMIN or SYSADMIN role membership. The PROCESSADMIN role includes both ALTER ANY CONNECTION and ALTER SERVER STATE and the combination of the two are required to use the KILL command.

·         To run SHOWPLAN or use the GUI actual/estimated execution plans to see execution plans, you can GRANT SHOWPLAN TO… in the database(s) that contain the objects referenced in the queries. They also need permission to execute the query itself. There is no need to grant anything more than the ability to execute the query if you just want to SET STATISTICS TIME or SET STATISTICS IO. The danger in granting this permission is that the plan could theoretically contain information about data or the schema that would help a hacker.

·         To run SQL Profiler you can GRANT ALTER TRACE TO…. The danger is that the user can see information about the schema and sometimes the data that could be used to hack into the system.

·         To use Job Activity Monitor, first add the login or group as a user in the MSDB database. Then add them to the operator role:

sp_addrolemember ‘SQLAgentReaderRole’, ‘test1’

·         Using the Activity Monitor requires VIEW SERVER STATE and SELECT on sysprocesses and syslocks. The SELECT on sysprocesses and syslocks is granted by default to PUBLIC and therefore everyone, but VIEW SERVER STATE has to be explicitly granted.

·         To run the Database Tuning Advisor (DTA) you need SHOWPLAN permissions and the ability to execute the queries in all the databases in the workload. However, if the trace file used as input includes the LoginName data column DTA will try to impersonate the users and therefore permission needs to be granted to each user OR you can avoid collecting the LoginName data column. Right after a new instance of SQL Server is installed, a sysadmin must run DTA once before anyone else can use it initialize some settings.



This query will show you the list of available privileges:
select * from sys.fn_builtin_permissions (DEFAULT)

Leave a comment

SQL Server 2005 Clustering Tips/References

I have copied this over from an older blog. I have cleaned it up a bit to clarify a few areas and added some links.

SQL Server 2005 Clustering Tips/References

This information will supplement the clustering information I wrote in chapter 10 of SQL Server 2005 Practical Troubleshooting: The Database Engine  

— Handy cluster related info

select SERVERPROPERTY(‘IsClustered’) as _1_Means_Clustered

, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as CurrentNode

, SERVERPROPERTY(‘Edition’) as Edition

, SERVERPROPERTY(‘MachineName’) as VirtualName

, SERVERPROPERTY(‘InstanceName’) as InstanceName

, SERVERPROPERTY(‘ServerName’) as Virtual_and_InstanceNames

, SERVERPROPERTY(‘ProductVersion’) as Version

, SERVERPROPERTY(‘ProductLevel’) as VersionNameWithoutHotfixes

select * from sys.dm_io_cluster_shared_drives

select * from sys.dm_os_cluster_nodes

Best practices

Setup (RTM, Service Pack, Cumulative Update, or hotfix)

·         The account you use to launch setup must be a local admin on all nodes. However, it is not required that the account you choose to assign as the service account for each service be a local admin. Only setup requires local admin permissions.

·         Install and cluster DTC before installing any SQL instance. For Windows 2003, DTC should ideally go in its own group with its own disk and IP address. Second best is to place it in the cluster group and make it depend on the quorum disk and IP address. For Windows 2008 see this blog.

·        For SQL 2005, the client tools, SSIS, NS, and RS are only installed on the node where setup was run because they are not cluster aware. If you are installing as many or more instances than nodes, you can run the install program for each instance from a different node so the non cluster aware components are installed on each node. Otherwise install the non cluster aware components later on the other node(s). This means that the service packs and hotfixes must be installed on each node as well so that the non cluster aware components are updated. Setup is run once per instance and that will update all the cluster aware and components for that instance. The first time setup (RTM, SP, hotfix) is run from each node it will also update any non cluster aware components on the box such as the tools, SSIS, NS, and RS.

·         All nodes should be configured identically.

·         Windows level policies should be the same on all nodes.

·         Remote Registry, Cryptography services, and Task scheduler must be started on all nodes during the setup process.

·         No Terminal Services users can be logged in on any remote nodes during setup.

·         Do not use quotes in the password for SQL service account.

·         Do not allow <, >, ‘, “, & in the cluster group names.

·         Virtual Server name should be 14 characters or less.

·         NIC name cannot have trailing spaces.

·         Stop any non-essential applications or services as they may hold open files that setup needs to modify or may otherwise interfere with setup.

·         All nodes must have access to setup files without prompting for credentials.

·         All disks in all groups must be online during setup.

·         All other, existing instances in the cluster must have valid, non-UNC paths for the SQL Server registry keys

·         If there are many trusts for the domain the nodes/service account reside in, see KB 910070 before running setup

·         For Windows 2003, the complete node must be on HCL, for Windows 2008 each node in the cluster must pass a validation.

·         Copy the setup files to the “primary” node or at least make sure all nodes can access the setup files without being prompted for credentials.

·         Any mounted drives must have an associated drive letter and be clustered, even if they will not be used by SQL Server. Do not use mounted drives anywhere on a cluster if SQL Server 2000 will exist in the cluster. A mounted drive must be in SQL resource group and SQL Server must depend on them if you want that instance of SQL Server to use them for data or log files.

·         Verify you have not installed terminal services, there are no compressed drives, and no node is a domain controller.

·         Disable netbios on private NICs.

·         Pre-create the domain groups needed by setup.

·         Make sure the SQL Server resource is set to “affect the group”. Often it’s best to leave SQL Agent, FTS, and DTC to not “affect the group” but that depends on your business needs.

·         Test failover of all groups before any SQL setup, test again before any service packs or hotfixes. If any group gets an error during failover, address that problem before running SQL setup.

After setup

·         Go back to the cluster administrator and take the SQL Server resource offline. Make SQL Server dependent on the disk(s) and mount points in their group (any disk or mount point where you need to create data files, log files, or full text catalogs) then bring SQL Server, SQL Agent, and FTS back online.

·         For the SSIS service, %ProgramFiles%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml must have the SQL virtual server name instead of “.”

·         Configure memory to handle instances moving due to failover.

·         If you installed NS, you now have to configure it separately.

·         Leave SQL services set to manual in the services applet.


·         Use add/remove programs to add/remove nodes.

·         Add/remove non-cluster aware components must be done from cmd line.

·         Can rename the virtual server name but not the instance name.

·         Make IP changes in cluster admin instead of setup.

·         Cluster service account must have a login in SQL, must be sysadmin only for FTS.

·         Clustered SQL Server startup account can only be a local admin if SQL 2000/7.0 is not installed side-by-side with 2005. Even if a lower version is not installed side-by-side it is best not to make the SQL Server startup account a local administrator or otherwise give it excessive permissions.

·         After adding new node, fail to that node to  apply SPs/hotfixes.

·         For Kerberos on a cluster, you need two SPNs per instance, one with and one without the port. They must belong to the current SQL Server startup account and not to any other accounts.

·         If you use SSL encryption, install certificates on all nodes before turning on the SSL encryption.

·         If antivirus is installed, see


·         Updated Books Online:

·         915846  Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster;EN-US;915846

·         819546  SQL Server 2000 and SQL Server 2005 support for mounted volumes;EN-US;819546

·         913815  Error message when you install a SQL Server 2005 failover cluster on a node: “The drive specified cannot be used for program location”;EN-US;913815

·         922670  How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005;EN-US;922670

·         910230  How to install SQL Server 2005 Analysis Services on a failover cluster;EN-US;910230

·         910233  Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster;EN-US;910233

·         912397  The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid;EN-US;912397

·         910851  You receive error messages when you try to set up a clustered instance of SQL Server 2005;EN-US;910851

·         926621  Error message when you try to install SQL Server 2005 in a cluster environment: “SQL Server Setup could not validate the service accounts”;EN-US;926621

·         327518  The Microsoft SQL Server support policy for Microsoft Clustering;EN-US;327518

·         254321  Clustered SQL Server do’s, don’ts, and basic warnings;EN-US;254321

·         942176  Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service;EN-US;942176

·         922209  The SQL Server 2005 Setup program does not remove all IP address cluster resources when you uninstall SQL Server 2005;EN-US;922209

·         295732  How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed;EN-US;295732

·         263712  How to impede Windows NT administrators from administering a clustered instance of SQL Server;EN-US;263712

·         932881  How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult;EN-US;932881

·         934749  BUG: Error message when you try to install SQL Server 2005 Service Pack 1 or SQL Server 2005 Service Pack 2 from the existing active node: “The product instance <InstanceName> been patched with more recent updates”;EN-US;934749

·         283811  How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005;EN-US;283811

·         910070  FIX: The SQL Server 2005 Setup program may take much longer than expected to finish running;EN-US;910070

·         909967  How to uninstall an instance of SQL Server 2005 manually;EN-US;909967

Windows 2003 SP2 is better than SP1 because:

·         918483  How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005;EN-US;918483

·         922658  SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer;EN-US;922658

·         904160 Network performance is slower than expected in Windows Server 2003 SP1

Cluster specific:

·         923830  Recommended hotfixes for Windows Server 2003 Service Pack 1- based server clusters;EN-US;923830