SQL Server Storage: Reading Pages with T-SQL

This is a continuation post from last weeks’ SQL Server Storage: Pages and Extents explanation. Since the description was a bit longer than I originally expected it to be, I decided to split the scripts into more posts.

First, I’m going to quickly cover how you can see information about a specific page using T-SQL. Now, normally this won’t be of much use to you, but it’s fun to play around with a bit just to see how things are actually stored. You might have need to read a page during some heavy troubleshooting at some point in the future too.  This procedure is undocumented though, so information is scarce and the feature could disappear without notice in future versions.

To read a page, you’ll need to utilize DBCC PAGE which I’ve listed the basic layout for below.

  'DbName' OR DbId -- Database name or Database ID, either one!
  ,FileNumber -- File Number of the Page
  ,PageNumber -- Page Number in the File
  ,PrintOption -- Display option ranging from 0-3 with different info from each

Now we need to know what FileNumber and PageNumber to supply to DBCC PAGE though. Random numbers might work, but if you are actually trying to do anything halfway useful, they won’t get you far. To solve this problem, we have to utilize another procedure…DBCC IND

  'DbName' OR DbId -- Database name or Database ID, either one!
  ,TableName -- Table Name...I don't think this really needs a comment
  ,IndexId -- index_id from sys.indexes; -1 = indexes and IAMs, -2 = IAMs

Using DBCC IND we can get some relevant data to pass into DBCC PAGE. The problem is, you still need a relevant Index_ID  for the selected table. The following query can help with that. Just supply the desired TableName in the WHERE clause.

--Get the index_id relating to your desired TableName to pass into DBCC IND
SELECT * FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'TableName'


A sample result of sys.indexes. Grab the index_id and plug that into DBCC IND


With an index_id and a TableName in mind, we can get some results from DBCC IND.


If you are just testing, index_id = 1 is not a bad idea to check.

Awesome, now we have meaningful ids to use with DBCC PAGE. You’ll need to do one more thing before you run it though. Trace flag 3604 has to be set for SQL to provide output. Without it, you won’t get any results at all.


No Trace Flag means no results


With the Trace Flag on, you’ll get more data than you probably know what to do with.


DBCC Page provides LOTS of information, I only captured a tiny snippet for your visual delight

Now you know how to get page information about your tables, so long as these undocumented procedures are available. Since this post again got longer than I originally expected, I’ll cover reading and setting block sizes on your disks next week. That will involve some fun PowerShell too!


SQL Server Forced Mirror Failover

Failing a SQL Server Mirror over is normally an incredibly simple process. If you use the GUI, you can accomplish the failover in moments. However, it is not always a planned event. Yesterday we had just such an issue.

If your principal server goes down and you do not have a witness setup for automatic failovers, you will need to perform a forced manual failover on the mirror server. There is no way to do this via the GUI, scripts only. Opening the database properties on a mirror results in a greyed out pane of information and zero options.


Not very helpful when this is all you have to work with.

Forced Failover Method 1 (PREFERRED)

In a perfect world, you will be able to connect to the mirror database and quickly run the following script, substituting in your database name. This will force the mirror to become the principal, and voila, you are up and running again. If you use an alias in Active Directory, just make sure the alias is changed to point to the new server.


However, in the less perfect world we live in, you may receive the following error:

Cannot alter database because database is not in the correct state to become the principal

This error results for one of two reasons:

a. Full Safety Mode

If the database is not in Full Safety Mode, otherwise known as Synchronous commit, the failover will error. If your mirror database is in a remote location, it’s probably not in Full Safety. Asynchronous mirrors are High Performance for data transfers, but Low Performance when it comes to forced failovers. Try running the following to force the database into Full Safety mode, which will then allow the failover to occur.


However, this script will fail too, if the principal database is still available to SQL. It might still fail regardless. It did for me, so I had to resort to Method 2 below.

b. Principal Database can still be reached by Mirror

If your principal database is still accessible by the SQL mirror, but having too many connectivity issues for you or users to actually reach it, you may have to shut down the principal in order to run commands from the mirror database. This is a bit of a strange scenario, but if your principal cannot be connected to, you’ll need to failover somehow. In this situation, someone will have to make the call of what to do.

Note: If you failover using the forced manual failover script, you will be able to manually resume mirroring after the principal server becomes available again. All your mirroring settings will be saved for the database, making failback relatively painless.

Break Mirror Method 2 (LAST RESORT)

If Method 1 of forcing the failover does not work, even after trying to set the database to Full Safety Mode, you can run the following script and break the mirroring session and restore the mirror with recovery. This is the quick and dirty method.


If you destroy the mirroring session in this manner, you will not be able to resume mirroring. All the settings will be erased and you will have to recreate mirroring from scratch. You’ll have a usable database though, and if the principal died suddenly, you shouldn’t actually have much, if any, data loss.

String Manipulation in T-SQL or PowerShell

I don’t often have to do string manipulation in T-SQL, but when I do, it’s rare enough that I don’t remember the syntax. I’ve done it in the past, so I keep a script handy with a working example though. I recently added to that example after wanting to know not only the file names, but also the folder location.  This article will cover getting a string after a specific character. This can be used to get a folder path or just a file name.

The scripts are cumbersome, to say the least, in SQL. Some people use this problem as an interview question, but I feel like the only way you’d be able to answer this off the top of your head is if you did it on a daily basis. While I remember the general idea of switching the order and checking the text after a specific character, I couldn’t write this from scratch if my life depended on it. Even with intellisense helping me out, it’s hard for me to remember how CHARINDEX can be manipulated to get the correct answer here, since I use it so rarely. I can pull it from my script library quickly though, and now I’ll be able to refer to my own blog as well. (I do that every time I set up Transparent Data Encryption).

I’ve commented into the code a quick explanation of what each command does in order to find the correct answer. Then I found out WordPress refused to color code the comments correctly. So I removed them. Now I’ll just explain it instead.

SQL can do it

The below script will find the File Name of each master file by reversing the direction of the text and using CHARINDEX to get the length of the string until it reaches a specific character, in this case, the \. The RIGHT command then returns the number of characters at the end of the original string based on the inner index command. The -1 removes the \ from the returned results.

To get just the folder path, perform the same command again, but this time issue a REPLACE command for the FileName, replacing it with an empty string.

In order to get a single folder, you combine the steps above to get just the FolderPath, and then once again get the last value after the \. This returns the final folder in the list. This may not be the optimal way to find the results, but it uses the same methods in order to keep it relatively simple.

,[FileName] = RIGHT(physical_name,CHARINDEX( '\', REVERSE(physical_name)) -1)
,[FolderPath] = REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), '')
,[FileFolder] = RIGHT(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1),''),CHARINDEX( '\' ,REVERSE(REPLACE(physical_name, '\' +RIGHT(physical_name,CHARINDEX( '\' ,REVERSE(physical_name)) -1), ''))) -1)
FROM sys.master_files mf

PowerShell just does it better

On the other hand, I feel that the syntax to perform the same string manipulation is much more intuitive in PowerShell. It’s definitely less code, although I will concede that I assigned variables here, while with the SQL approach, I chose to do it all in a single statement. No variable assignments makes it much harder to read.

Leveraging the SPLIT command and LENGTH is about all you need for PowerShell. SPLIT creates an array from the string, with each new value separated by the character supplied in the parenthesis, that \ again. Returning the array value based on the length of the newly split variable returns any folder level or the file name alone, it just depends on the number you subtract from the length of the split.

$FullPath = "C:\Users\Test\Desktop\Text.txt"

$Split = $FullPath.Split('\')
$File = $Split[$Split.Length-1]
$LastFolder = $Split[$Split.Length-2]
$FolderPath = $FullPath.Replace($File,'')



Troubleshooting Policys and xp_dirtree

Today we had a policy failure on a newly built SQL server. The policy verifies that every SQL server has a backup file stored on a remote directory. Simple enough, but in the past, this policy has been prone to permissions errors.

In case you would like to implement something like this on your system, the dumbed-down condition of the policy is below:

ExecuteSql('NUMERIC', '
--Get DBs on server
SELECT  @DBCount = COUNT(*) FROM sys.databases WHERE database_id <> 2
--Insert DirTree results into a table variable to return a numeric result
DECLARE @Check TABLE (SubDirectory VARCHAR(250), Depth BIT, [File] BIT)
INSERT  @Check
EXEC master.dbo.xp_dirtree @File,1,1
--Numeric result for easy policy evaluation 1=Pass if( # files = # dbs )
IF((SELECT COUNT(*) FROM @Check) = @DBCount)

Policy Explanation

The dumbed-down policy checks a backup server, which has a folder named for each SQL server, to see if it has the correct count of backups.  Each folder should have a backup file for each database, except for TempDB. This isn’t exactly how we do it, but it gets my point across and it provides a good example of how a moderately complex policy works using ExecuteSQL. There aren’t a lot of examples on the web, so I try to provide as many as I can.

The Troubleshooting

The new server wasn’t processing the policy correctly. My first idea was, “Well, there’s no files!” Easy! Problem solved!  Or not…the files were in the folder, and the number was even correct. So it wasn’t the backups or copy failing. That meant that there was something wrong with the permissions, the problem was, what?

I verified the logins were all present in SQL by reviewing Security -> Logins through Management Studio. This was a new server, so I assumed it would be something simple like that. The account in question was the SQL Server service account.  Policy Based Management and xp_dirtree use this account. The account was in SQL and it had all the proper permissions. OK, next thing.

The folder where all the files are stored is a shared folder, so I went into the Properties of the folder and verified the account that was shared. Everything looked right there. Every time I’ve had an issue with the permissions of xp_dirtree reading servers, it’s been because the service account in the shared folder was wrong, so my next stop was checking out Active Directory.


Sharing a folder makes the file path easier. Just make sure your service account has permission here.

Drilling down through Active Directory, I found the account used for sharing the folder. This account had every SQL service account as a member. It looked fine in AD, it looked fine on the Shared folder. Everything worked perfectly except on this new server. What was wrong?!?

At this point, I just decided to throw a hammer at it and throw in extra service accounts until hopefully one worked. Eventually I added the SQL Agent account. Suddenly, the policy passed. The new server could read the backup directory! So I took the normal account out of the permissions. Now everything failed except this new server. Wait, what…?

I logged into the new server and loaded up SQL Configuration Manager…


When the server was setup, someone switched the accounts. SQL Server was running using the SQL Agent service account. SQL Agent was running using the SQL Server service account. I restarted each with the correctly corresponding service and account. I then fixed the permissions for the shared backup directory back to the original setup.

Voila! Everything worked perfectly!

The Moral of the Story

Make sure new servers and settings are configured properly before trying to fix things that weren’t broken before the new stuff existed. I’m sad to say it took me so long to understand the problem. Even with other DBAs chiming in. None of us thought about checking the Configuration Manager…that or they assumed I had already checked that.

Sometimes, it’s the little things that get you…OK, normally its the little things that get you.

Get Failed Jobs with T-SQL

It’s been quite some time since I did a post relating to SQL. I’ve just been sharing a lot of PowerShell scripts lately because almost all my views are related to PowerShell.

Monitoring SQL Jobs can be done in many ways, and I highly suggest your jobs send an email notification to your DBA group on failure. No not just you. What happens when you are on vacation or you leave permanently and your account gets disabled? No notifications. Don’t do that. You can also set up fancy reports to display information about your job status using SSRS, but I always like to have something that is easily portable and can be ran in SSMS quickly.


In the middle of the night when email isn’t loading and a call comes in that something important failed, but the helpdesk can’t articulate what exactly it is over the phone…this script can help a lot. Suddenly confusion and trying to piece together a string of letters and numbers over the phone turns into a simple, “Oh, yes, I see Server X has failed Job Y 3 times. It ran successfully last time, I’ll go ahead and connect to that server to look into it but I suspect someone might have been doing Z.”

I developed this failed job script to run on a single SQL server or through a multi-server query to see all failed jobs across all servers. I commonly run this script in the mornings as a summary when there are multiple failed job emails that came in over night. It’s a much easier way for me to see the history this way. If a job is continuing to fail and it’s high priority, I start there.


The job provides the name, run date, and run time for each failed job, but it also provides some extra information for sorting priority. I’ve used ROW_NUMBER to sort job history and retrieve the latest run results of each failed job. That’s the only purpose of using a CTE here, getting Row 1, or the most recent run result for each job. Using the latest result helps determine how problematic the reported job failure is. Did the latest trans log backup run successfully even though one of them failed in the past? OK, that’s probably a minor issue then. Are they still failing? Well, then we have something a bit more worrisome to consider.

I always limit the results to a 24 hour period to limit the result set. I’ve also included a clause in the past to exclude a specific job that was prone to failing due to network issues or a colleague testing something.

  ,[PriorStatus] = --'NA' --2008
    LAG(jh.run_status,1) OVER (ORDER BY jh.job_id,jh.run_date,jh.run_time)  --2012
  ,[NextStatus] = --'NA' --2008
    LEAD(jh.run_status,1) OVER (ORDER BY jh.job_id,jh.run_date,jh.run_time) --2012
 ORDER BY jh.job_id DESC, jh.run_date DESC, jh.run_time DESC)
 FROM msdb.dbo.sysjobs j
  INNER JOIN msdb.dbo.sysjobhistory jh ON jh.job_id = j.job_id
  AND jh.step_id = 0
  ,[RunDate] = CONVERT(DATE,CONVERT(VARCHAR,j.run_date,112))
  ,[RunTime] = CONVERT(TIME(0),STUFF(STUFF(REPLACE(STR(RTRIM(j.run_time),6,0),' ','0'),3,0,':'),6,0,':'))
  ,[PriorStatus] = CASE j.PriorStatus WHEN '1' THEN 'SUCCESS' WHEN '0' THEN 'FAIL' ELSE 'N/A' END
  ,[NextStatus] = CASE j.NextStatus WHEN '1' THEN 'SUCCESS' WHEN '0' THEN 'FAIL' ELSE 'N/A' END
  ,[LatestStatus] = CASE ji.run_status WHEN '1' THEN 'SUCCESS' ELSE 'FAIL' END
 FROM JobInfo j
   INNER JOIN JobInfo ji ON ji.job_id = j.job_id AND ji.RN = 1
 WHERE j.run_status = 0
 ORDER BY j.name,[RunDate],[RunTime]


If you are running the script on SQL 2012, you can get a bit more information using LEAD/LAG in the form of the completion status of the job before and after the reported failure. I use this to pattern failures and to further prioritize multiple job failures. All other things being equal, I’m going to be more concerned about the job that has consecutive failure than a job that fails intermittently.

Sadly, IF statements, LEAD/LAG, and SQL 2008R2 or older do not play well with each other. I’ve tried making IF statements that run the LEAD/LAG version of the job only on SQL 2012 while running the NA version of the job otherwise, but SQL always wants to parse the 2012 functions, resulting in errors. I’ve resigned to just commenting out the LEAD/LAG functions when running this script on SQL 2008R2 and older. The benefits of the PriorStatus and NextStatus is fairly minimal, so it’s not a huge loss. I find the LatestStatus to be the most important of the three by far.

Database Mirror Monitoring with Scripts

The database mirror monitor is a nice tool, but registering all your databases and clicking through each one can be time consuming when you have dozens. I want quick, easy to read results when an error comes in. If things look bad, I’ll open the monitor for a specific database to start an in depth look.

And yes, I do have reasonable alerts set up, but scripts are for proactive searching, dealing with new servers, and, most importantly, a jumping off point when an alert is generated. My normal process is:

  1. Read alert email
  2. Open multi-server query and run all the mirroring scripts below
  3. Investigate specific databases that are still reporting errors from the scripts

Sometimes I get to stop at step 2 because the issue has already been resolved due to a network glitch or someone else responding faster than me. I keep all the scripts saved in a single file which is always open and I run periodically.

The scripts are not as refined as I’d like, so please tweak them. They do the job when errors are coming in though. The only time I can think of any changes are when they are showing a slew of horrific errors, and at that time I just don’t have time to think about changing a script to make it prettier.

Find un-Synchronized Mirrors
Running this as a multi-server query will provide a list of all mirrored databases not currently synchronized. This is my most used method for verifying that a mirror is still in trouble.

FROM sys.database_mirroring
WHERE mirroring_state_desc IS NOT NULL
    AND mirroring_state_desc <> 'SYNCHRONIZED'

Find Databases that are not Online or are Restoring
This script is a bit more specialized. I rarely get results (which is good), but it has indicated anomalies before. This will return any databases that are offline or in a restoring state, but not part of a mirror. Sometimes this indicates a test database or one left in the process of being mirrored, but at least in my environment, I never want to let a database be left in that mode for very long.

The worst scenario I’ve had with this script was a time that it found a database that had mirroring removed, so the partner had just been left in restoring state. Somehow that had been overlooked until I found it with this.

FROM sys.databases d
   LEFT JOIN sys.database_mirroring m ON m.database_id = d.database_id
WHERE (d.state_desc <> 'ONLINE'
OR d.state_desc IS NULL)
   AND (m.mirroring_role_desc IS NULL
OR m.mirroring_role_desc <> 'MIRROR')

DB Mirror Monitor Script
This mimics the database mirroring monitor. Main benefits here are you do not have to preregister every single database to the monitor and then wait for each one to refresh and click through each database. Just run the script, and apply any filters that you want. When run as a multi-server query it will automatically increment through each mirrored database.

If you want to see your entire mirroring monitor in one moment, this is for you.

The reason for this script is because sometimes I get mirrors that are still considered synchronized, but are not pushing data fast enough. They throw strange alerts, if any, when this happens. Resolving this normally involves finding out why the network is moving slowly. Ultimately there is little I can do while I wait for a fix, but it’s good to know what is going on in the meantime.

 (database_name SYSNAME,[role] INT,mirroring_state INT
,witness_status INT,log_generation_rate INT,unsent_log INT
,send_rate INT,unrestored_log INT,recovery_rate INT
,transaction_delay INT,transactions_per_sec INT,average_delay INT
,time_recorded DATETIME,time_behind DATETIME,local_time DATETIME)

-- @id will increment and pull each mirrored database
DECLARE  @id        INT
 ,@db        VARCHAR(256)
 ,@command    VARCHAR(2000)
SELECT @id = MIN(database_id) FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL

-- Loop through each database to pull mirror monitor information
  SELECT @db = d.name FROM sys.databases d WHERE d.database_id = @id
  SELECT @command = 'EXEC msdb.dbo.sp_dbmmonitorresults @database_name='+QUOTENAME(@db)+''
  PRINT @command
    EXEC (@command)
--Increment @id
  SELECT @id = MIN(database_id) FROM sys.database_mirroring
  WHERE mirroring_guid IS NOT NULL AND database_id > @id
-- Your WHERE clause/values will vary
WHERE unsent_log > 10

Get Drive Sizes using SQL or PowerShell

There are countless methods to obtain drive size information, each with its own set of benefits and limitations. I use a few different methods depending on if I have to type out the solution quickly or if I need a comprehensive view.

Method 1 – xp_fixeddrives
The most basic command to get drive sizes is easy to remember, so I use it when I need a fast result. This script leaves a lot to be desired though. It returns all drive letters on the queried SQL server, but only displays one column, MB free.

EXEC master.dbo.xp_fixeddrives


You could get extra information by inserting the results to a temp table and formatting it, but that defeats the purpose of this quick command. If I want more information, I move onto a more robust script entirely.

Method 2 – dm_os_volume_stats
In newer versions of SQL Server (2008R2+) you have the opportunity to use the dynamic management function dm_os_volume_stats. This function returns data on all drive letters with SQL files (Data, Log, Temp) and you can easily modify the select statement to tailor the results to your desires.

   [Drive] = volume_mount_point
  ,[FreeSpaceMB] = available_bytes/1024/1024 --/1024
  ,[SizeMB] = total_bytes/1024/1024 --/1024
  ,[PercentFree] = CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
FROM sys.master_files mf
  CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id)
--Optional where clause filters drives with more than 20% free space
WHERE CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100) < 20
  ,total_bytes/1024/1024 --/1024
  ,available_bytes/1024/1024 --/1024
  ,CONVERT(INT,CONVERT(DECIMAL(15,2),available_bytes) / total_bytes * 100)
ORDER BY [Drive]

I’ve returned the results in MB to match the result set in xp_FixedDrives, but feel free to divide by another 1024 bytes to convert to gigabytes. (That’s normally what I do). My query includes Free Space, Total Size, and Percent Free space. I also included an optional WHERE clause to filter out any drives that have over 20% Free space. Simply open up a multi-server query, copy and paste the script, and you have current data for drives with low space.


This is great for most situations…but this method fails to return all drives I’m concerned about. I do not get feedback on the C drive nor D Drive, which is where I have SQL and other applications installed. Not everyone will worry about system drives, but I want to know if someone is throwing data where it doesn’t belong.  I bet you care about that too. In that case, I use another resource.

Method 3 – PowerShell (preferred)
When I need data on every drive, I turn to PowerShell. PowerShell allows me to perform a full drive query against servers with versions prior to 2008R2 as well. The script isn’t overly difficult, but you will need to supply a list of computers to run against. I suggest saving your SQL Server computer names into a notepad file and having PowerShell read from that.

If you want a quick and dirty way to get your computer names, open a multi-server query using your CMS and run:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBios')
SELECT NodeName FROM sys.dm_os_cluster_nodes

That will return all computers running SQL and all cluster nodes – active or inactive. If you combine these as a distinct list, you should have all your computers. I didn’t say it was clean, I said it was quick.

The PowerShell script returns all drive letters, but you can filter a specific drive or name if you so desire. You can manipulate the WHERE clause by following the format of the one I provided.

For example, change $_.PercentFree to $_.VolumeName and -lt (which means less than) to -eq (equals).  Replace 20 with the drive name in quotes, for example, 'Data'. If you need multiple clauses, the format is WHERE { $_.Value1 -eq '1' -AND $_.Value2 -eq '2' }

$Computers = ( Get-Content C:\Users\$Env:UserName\Desktop\Computers.txt )
Get-WmiObject -class Win32_LogicalDisk -ComputerName $Computers -Filter "DriveType=3" |
 Select-Object SystemName,DeviceID,VolumeName,
  @{n='FreeSpace(MB)';e={$_.FreeSpace / 1MB -as [int]}},
  @{n='Size(MB)';e={$_.Size / 1MB -as [int]}},
  @{n='PercentFree';e={$_.FreeSpace / $_.Size * 100 -as [int]}} |
   Where { $_.PercentFree -lt 20 } |
    Sort SystemName,DeviceId | FT -AutoSize


Enterpise Policy Management Framework – Skip the Report

Policy-Based Management in SQL Server is a wonderful and underused feature. The Enterprise Policy Management Framework available through Codeplex further enhances this with reports and history tables, but if you have ever used the main report, you know how distressingly slow it can be. Not only that, but drilling through the report to find failed policies on instances is an extremely painful process. Visual reports are pretty, and the aggregation is a really cool feature, but expanding nodes are not practical for a large environment.


It depressed me just pulling up the slow report to screen shot this. Expanding nodes is even worse.

So while I use EPMF, I do not check the reports daily. Instead, I script the daily failures and errors to quickly review them. Below are three scripts that can provide policy results, use whichever you prefer.

Script 1: Short and sweet. This provides all failures and errors for the current day, including the unshredded XML evaluation results. This is my go to script each morning. Find failures and investigate.

FROM EESqlReports.policy.PolicyHistoryDetail phd
INNER JOIN EESqlReports.policy.PolicyHistory ph ON ph.PolicyHistoryID = phd.PolicyHistoryID
PolicyResult <> 'PASS'
ORDER BY PolicyResult DESC,Policy_id,phd.EvaluatedServer;

Script 2: This script is essentially the same as above, but it includes the evaluated object or database along with a cleaner XML result. While still XML, it is easier to read, and the best solution I’ve found to read the evaluation results reliably for any policy. Sometimes the XML will not provide an actual value on a failed result. I’ve yet to discover what causes this. Theories include network issues or unexpected values.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
,[CheckParam1] = res.Expr.value('Function[1]/Constant[2]/Value[1]','nvarchar(100)')
,[ExpectedValue1] = res.Expr.value('Constant[1]/Value[1]','nvarchar(100)')
,[ActualValue1] = res.Expr.value('Function[1]/ResultValue[1]','nvarchar(100)')
FROM policy.PolicyHistoryDetail phd
INNER JOIN policy.PolicyHistory ph on ph.PolicyHistoryID = phd.PolicyHistoryID
CROSS APPLY ResultDetail.nodes('Operator') AS Res(Expr)
PolicyResult <> 'PASS'
ORDER BY PolicyResult DESC,EvaluatedServer;

Script 3: This script attempts to shred the XML results, showing you the check parameter, expected value, and actual value. This sample script is very short because shredding a policy result gets complicated fast. I’m only shredding the first line of a policy here. Since most policies have more than one parameter and thus values, this script gets excessive fast.  Further, if the policy doesn’t fit the XML format scripted perfectly, you’ll get NULL results. Before you know it, you have to tailor-make a script for almost every policy in your environment. It’s just not effective, and why I suggest just reading the XML from the above script if you really need to view results. As terrible as XML is to read, it’s better than crafting a script for dozens or even hundreds of policies.

<span style="color: #808080; font-family: Consolas; font-size: small;">Consolas; font-size: small;"><span style="color: #808080; font-family: Consolas; font-size: small;">WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
,Res.Expr.value('(../DMF:TargetQueryExpression)[1]', 'nvarchar(150)') AS EvaluatedObject
,(CASE  WHEN Res.Expr.value('(../DMF:Result)[1]','nvarchar(150)') = 'FALSE'
AND Expr.value('(../DMF:Exception)[1]','nvarchar(max)') = '' THEN 'FAIL'
WHEN Res.Expr.value('(../DMF:Result)[1]','nvarchar(150)') = 'FALSE'
AND Expr.value('(../DMF:Exception)[1]','nvarchar(max)') <> '' THEN 'ERROR'
END) AS PolicyResult
,CAST(Expr.value('(../DMF:ResultDetail)[1]','nvarchar(max)') AS XML) AS ResultDetail
FROM policy.PolicyHistory PH
INNER JOIN policy.PolicyHistoryDetail phd ON ph.PolicyHistoryID = phd.PolicyHistoryID
INNER JOIN msdb.dbo.syspolicy_policies p ON p.name = PH.EvaluatedPolicy
CROSS APPLY EvaluationResults.nodes('
declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
//TargetQueryExpression') AS Res(Expr)
WHERE phd.PolicyResult = 'FAIL'
ORDER BY PolicyResult DESC,EvaluatedServer;</span>


Automatic Tempdb Configuration Script

The default configuration for TempDb leaves a lot to be desired, and although good changes are on the way in 2016, it will be quite some time before any of us get to use that in production.

I manage many SQL servers, and the TempDb files have long been ignored on them, mostly due to overpowered server hardware. Recently I’ve been working to correct their file allocations and, most importantly, set up some standards for future server builds.

Microsoft best practices suggest assigning one data file per logical processor, up to eight. If contention remains, you can then assign additional data files, in groups of four, until contention lessens. I decided an automatic script could handle the initial setup.

If you hate reading you can skip to the code. I’ve commented everything you need to change and explained each step in detail. Even better, if you smash F5 and run the code, it will just show you the debug mode by default, no changes will be made.

Script Explanation

This script is meant to run against a new server that has the default settings in place for Tempdb, with a single data and log file. The script will rename those files per your input and then create additional data files up to the calculated limit based on logical CPUs or eight, whichever is lower.

The script sets the location for the data and log files, but if you are one of the lucky few who have a single drive per data file, this script cannot help you in its present state. You can modify the script to deal with multiple file locations, or manually make the changes.

One of my biggest pet peeves is percentage based autogrowth. It’s a very dangerous setting as your files grow. It isn’t even an option in this script. Generally it’s a good idea to presize your files anyhow, so the autogrowth shouldn’t matter regardless most of the time. You also may be asking why the size variables are all VARCHARs. That is only so they do not have to be converted when used later in the script.

My second favorite feature, after the number of data files calculation, is that you supply the total data file size. The script will calculate the size of each file for you. If you want an eight gig data file and have eight files, each file will be set to one gig. Simple. No math required.

The @Debug variable is supplied by default as 1. This allows you to run the script to see the actual ALTER DATABASE commands without executing them. Once you are happy with the results, you can rerun the script with @Debug=0 and the files will be modified and created immediately.

USE [master]

--- Set these values
 @Debug BIT = 1 --1=Information Only; 0=Execute Command
,@DataName VARCHAR(25) = 'Tempdb_Data' --Desired file name for data files
,@DataTotal VARCHAR(10) = '1024' --Total size of all Tempdb files; to be divided per file (in mb)
,@DataGrowth VARCHAR(10) = '100' --Data Autogrowth size (in mb)
,@DataLocation VARCHAR(250) = 'D:\DATA' --Data file location
,@LogName VARCHAR(25) = 'Tempdb_Log' --Desired file name for log file
,@LogSize VARCHAR(10) = '512' --Size of Log (in mb)
,@LogGrowth VARCHAR(10) = '50' --Log Autogrowth size (in mb)
,@LogLocation VARCHAR(250) = 'E:\LOG' --Log file location

--- Everything else is calculated for you!
,@Count INT = 2
,@LogicalCPU INT
,@DataSize VARCHAR(10)
,@OldData VARCHAR(25)
,@OldLog VARCHAR(25)

--- This will set # of Data files = Logical CPUs or 8, whichever is smaller
--- Afterwards, manually increase past 8 files, in groups of 4, if contention remains.
SELECT @LogicalCPU = CASE WHEN cpu_count>8 THEN 8 ELSE cpu_count END FROM sys.dm_os_sys_info
SET @DataSize = @DataTotal / @LogicalCPU
SELECT @OldData = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 1
SELECT @OldLog = Name FROM sys.master_files WHERE database_id = 2 AND file_id = 2

--- Modify original single log and data files to follow desired inputs
MODIFY FILE (NAME= N'''+@OldLog+''', NEWNAME= N'''+@LogName+''', FILENAME= N'''+@LogLocation+'\Tempdb_log.ldf'', SIZE= '+@LogSize+'MB, FILEGROWTH= '+@LogGrowth+'MB);

MODIFY FILE (NAME= N'''+@OldData+''', NEWNAME= N'''+@DataName+'1'', FILENAME= N'''+@DataLocation+'\Tempdb1.mdf'', SIZE= '+@DataSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);

--- Generates additional data files up to calculated limit
SET @SQL+= '
ADD FILE (NAME= N'''+@DataName+''+CONVERT(VARCHAR,@Count)+''', FILENAME= N'''+@DataLocation+'\Tempdb'+CONVERT(VARCHAR,@Count)+'.ndf'', SIZE= '+@DataSize+'MB, FILEGROWTH= '+@DataGrowth+'MB);
SET @Count+=1

--- Debug=1 Prints SQL commands to execute for testing; Debug=0 will process the commands

Comments and suggestions are welcome. I’ve tested this on over a dozen servers so far with no issues (other than forgetting to change the drive path on a server with different drive letters).

Querying Memory Settings

SQL settings should be verified on a regular basis, and I’m occasionally asked to produce a list of memory configuration settings on all servers for management. It’s a simple process to check this information using Management Studio, but scripting is a much better solution, especially if you need to verify the settings on a large number of servers.

You can verify memory settings quickly for a single system by right-clicking on the server in Object Explorer then selecting Memory on the left hand side.


SQL defaults to have access to all the memory on your server (2,147,483,647MB). Best practices suggest a variety of configurations, from 10-20% reserved for the OS, to a minimum of 4GB reserved, to 1GB reserved for every 4-8GB of RAM available. The most important thing to do is leave some space reserved for the OS. Leaving the default memory setting will allow SQL to eat all the available RAM, potentially leaving the server unresponsive as windows may eventually be completely memory starved.

I prefer to verify memory settings against all my servers at once. Not only is it faster, it’s easier to provide a report this way. Open a New Query from your Central Management Server targeting all of your servers for fastest results. Querying sys.configurations and sys.dm_os_sys_memory will provide the information you need, but the results leave a lot to be desired. By pivoting the data and performing a bit of formatting, you can create a report that is much cleaner and appealing.

[TotalRamGB] = Ram/1024
,[MinRamGB] = [Minimum size of server memory (MB)]/1024
,[MaxRamGB] = [Maximum size of server memory (MB)]/1024
,[RamLeftForWindowsGB] =
CASE WHEN [Maximum size of server memory (MB)]/1024 = 2097151 THEN 0
ELSE  Ram/1024 - [Maximum size of server memory (MB)]/1024
[Descr] = [Description]
,[Val] = CONVERT(BIGINT,value)
,[Ram] = total_physical_memory_kb/1024
FROM sys.configurations c
,sys.dm_os_sys_memory m
WHERE c.name IN ('min server memory (MB)','max server memory (MB)')
) a
MIN(Val) FOR Descr IN ([Minimum size of server memory (MB)],[Maximum size of server memory (MB)])
) b

This script converts all the memory settings to GB, but if you need to work in MB, just remove the GB conversions. The last column indicates how much memory is reserved for the OS. A value of 0 here indicates that SQL’s max memory setting has been left at default, and thus has free reign to steal all the memory. These servers should be addressed as soon as possible. Again, the amount of space you leave available will vary, but I suggest at least 4GB. If your server has such a small amount of RAM that this is a substantial portion of your total RAM, it’s probably time to ask for more RAM too. It’s one of the cheapest and best upgrades possible.