-
Recent Posts
Personal Links
SQL Server Links
Windows OS
Meta
Author Archives: Bennett
PowerShell String Reverse Function
Strangely, PowerShell does not include a string reverse function. After some googling, I wrote a simple reverse function based on regular expressions: 1 2 3 4 function Reverse-String ($inputstring) { -join[regex]::Matches($inputstring,".",’RightToLeft’) }function Reverse-String ($inputstring) { -join[regex]::Matches($inputstring,".",’RightToLeft’) }
Posted in Powershell
Comments Off on PowerShell String Reverse Function
WAN Emulator for Availability Group Testing
I had an unmet need to be able to test my AlwaysOn Availability Groups under less than ideal network conditions. More specifically I needed a way to add network latency in order to create more realistic monitoring and troubleshooting scenarios. … Continue reading
Posted in Open Source, SQL Server
Tagged AlwaysOn, lab, performance testing, testing, WAN
Comments Off on WAN Emulator for Availability Group Testing
AlwaysOn Availability Group Aware SQL Agent Job Step
The SQL Agent job step that I present here is intended to make a job execute only if the node currently owns a specified replica. The T-SQL for this job step leverages a function that was published in SQL Server … Continue reading
Posted in SQL Server, T-SQL, Uncategorized
Tagged AlwaysOn
Comments Off on AlwaysOn Availability Group Aware SQL Agent Job Step
Collecting Database File Stats with PowerShell
Here is a fairly simple PowerShell script that can be used to collect database file statistics across multiple servers. My version of the script is incorporated into a scheduled SQL Agent job that runs daily. It is very useful for … Continue reading
Posted in Powershell, SQL Server
Comments Off on Collecting Database File Stats with PowerShell
Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads
Wait stats analysis is often my tool of first resort for triaging performance problems. Adam Machanic’s SP_Whoisactive utility is my tool of choice for getting insight into what is happening on a server. Data Warehouse ETL processes often occur overnight. … Continue reading
Posted in SQL Server, T-SQL
Comments Off on Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads
Missing Index Stats Enhancements
Here is my script to find missing indexes using the missing index DMVs. It’s pretty much like others with the exception that I first get the instance uptime and then use that to calculate the number of seeks and scans … Continue reading
Posted in Performance, SQL, SQL Server
Comments Off on Missing Index Stats Enhancements
Columnstore Index Performance Improvements
We have dimensional data mart running on SQL Server 2012. The larger fact tables vary between 7 and 17 billion rows. No fancy data types, no LOB data types, and all the join keys are ints. There were already well-tuned … Continue reading
Posted in Uncategorized
Comments Off on Columnstore Index Performance Improvements
Partition-Level Lock Escalation
I ran into a situation at work where partition lock escalation solved a particular problem. A table, with about 300 million rows, has a primary key that contains something called a ProcID or Process ID. The process ID is tied … Continue reading
Posted in SQL Server
Comments Off on Partition-Level Lock Escalation
Powershell file mover
Pretty simple script I wrote a while back. Moves files between two paths. For example, to sweep files from a DMZ server. Optionally renames files on the target system by appending a date code. This is to avoid name collisions. … Continue reading
Posted in Powershell, Windows OS
1 Comment
The final voyage of the USNS H. H. Hess
My first ship, the USNS H. H. Hess, is currently enroute to Brownsville, TX where it will be cut up into scrap metal and recycled. The Hess was an oceanographic survey vessel that was operated by the Military Sealift Command … Continue reading
Posted in Personal
2 Comments
Alert for long-running SQL database backups
One of my daily tasks is to do a quick check of each SQL Server instance using Activity Monitor, sp_who2, or a DMV-based script. Sometimes I get busy and forget to do this task. Today I broke down and wrote … Continue reading
Posted in SQL Server
1 Comment
Learning SMO & Powershell
I created a small script to collect SQL Server file utilization data into a repository. I’m collecting the space used by the files as well as the space used internally by SQL Server. I’m doing this across all production databases … Continue reading
Posted in Powershell, SQL Server
1 Comment
SQL Generators for moving database files
Here are several trivial scripts for updating the system catalog, moving files via robocopy, and adjusting filegrowth values. I hope someone finds them useful. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 … Continue reading
Index to Filegroup mapping
Here is a trivial script to show where a particular index resides. It saves clicking around the SSMS GUI. 1 2 3 4 5 6 7 SELECT i.name, i.type_desc, i. is_primary_key, i.is_unique, s.name AS [Filegroup] FROM sys.indexes i INNER JOIN … Continue reading
Posted in SQL Server, T-SQL
Comments Off on Index to Filegroup mapping
Trigger Mass Enable / Disable
Pretty trivial, really: SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME] FROM sys.triggers …add a where clause if needed. Paste the output back into the query window and modify as needed. You can also slap … Continue reading
Posted in T-SQL
Comments Off on Trigger Mass Enable / Disable
Moving Master and Resource databases
You may encounter the error message SQL SERVER is in SINGLE USER MODE and only one administrator can connect when moving the Master and Resource databases when following the procedure in BOL. It happens right after you bring up SQL … Continue reading
Posted in Uncategorized
Comments Off on Moving Master and Resource databases
Quick and Dirty CSV import to SQL Server
You can use LogParser (a free, unsupported utility from Microsoft) to quickly import a CSV file into a SQL Server table using syntax like this: logparser “SELECT * INTO MyTable FROM d:\MyFile.csv” -i:csv -o:SQL -server:MyServer -database:MyDatabase -createTable:ON There are many … Continue reading
Posted in SQL Server, Windows OS
Comments Off on Quick and Dirty CSV import to SQL Server
Useful Multipliers
Multiply SQL database pages by 0.0078125 to get space in Megabytes Multiply SQL database pages by 0.00000762939453125 to get space in Gigabytes Here are a couple of examples of where this is useful: This query uses a DMV to return … Continue reading
Posted in SQL Server, T-SQL
Comments Off on Useful Multipliers
One-off backups in SQL Server 2005
I am frequently asked to refresh development databases with production data. The usual way to do this is to back up the production database and then restore over the development database. SQL Server 2005 has a new backup option, “WITH … Continue reading
Posted in SQL Server
1 Comment
Converting an existing Perfmon .BLG file to CSV
You may run across the situation where you need to import an existing Perfmon (or System Monitor) binary log file into and Excel spreadsheet or SQL database. The obvious first step is to convert it to CSV format. It took … Continue reading
Posted in Uncategorized, Utilities, Windows OS
Comments Off on Converting an existing Perfmon .BLG file to CSV
Dynamic SQL – must declare scalar variable
I ran into a situation today where I was generating dynamic SQL that appeared to be valid. I could take the generated SQL strings and execute them with no issues, but when executed dynamically, I got the error message “Must … Continue reading
Posted in T-SQL
Comments Off on Dynamic SQL – must declare scalar variable
SQL Server “Max Server Memory” Config Value
I learned an interesting tidbit while reading Inside Microsoft SQL Server 2005: Query Tuning and Optimization. The Max Server Memory configuration value applies to the cache buffer pool and not SQL Server as a whole. Here is a link to … Continue reading
Posted in SQL Server
Comments Off on SQL Server “Max Server Memory” Config Value
Shrinking all log files
I’m not a big proponent of shrinking files. There are a lot of postings on DBA forums citing why this practice should be avoided. Tibor Karzai in particular has an article on why this is a bad idea. Shrinking log … Continue reading
Posted in SQL Server, T-SQL
Comments Off on Shrinking all log files
How to determine if TCP Chimmney Offloading is working
The TCP Chimmney Offload feature was introduced in Windows Server 2003 SP2. It can offload certain functions from the OS to the network adapter card. A lot of our newer HP Servers have a Broadcomm Ethernet chipset that supports this … Continue reading
Posted in Windows OS
Comments Off on How to determine if TCP Chimmney Offloading is working
Oracle Sysdate formatting
You can use the PL-SQL to_char function to format the output of SYSDATE like this: select to_char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;
Posted in SQL
Comments Off on Oracle Sysdate formatting
The Good Books
I used several books to study for the ICCP DBA exam including: Modern Database Management, 8th Edition by Hoffer, Prescott and McFadden Database Systems: Design, Implementation & Management 6th Edition by Rob and Coronel Database Administration: The complete Guide to … Continue reading
Posted in T-SQL
Comments Off on The Good Books
Remote Reboot
Today while applying patches to a couple of mission-critical servers, I was reminded of something: reboots issued through the terminal services client are not always successful. The server can get stuck in an odd state where it is running but … Continue reading
Posted in Windows OS
Comments Off on Remote Reboot
Ongoing Lessons from the Cluster Lab
It’s been a while since I worked on my cluster lab. The first thing that I did was to tear down the old cluster and start over from scratch. The first lesson from this round of testing came when the … Continue reading
Posted in Windows OS
Comments Off on Ongoing Lessons from the Cluster Lab
Stripping the time component from a datetime value
Here is a very simple and elegant way to strip the time component from a datetime value: CAST(FLOOR(CAST(@date AS float)) AS datetime) Written as a function: CREATE FUNCTION BareDate ( — Add the parameters for the function here @date datetime … Continue reading
Posted in SQL Server
Comments Off on Stripping the time component from a datetime value
The IIS Metabase Explorer Rocks
Today I was reminded of something that I knew but forgot. The IIS Metabase Explorer for IIS6 (or MetaEdit for IIS 4 and 5) is a very useful tool. I needed to blow away an inheritable attribute of the HttpRedirect … Continue reading
Posted in IIS
Comments Off on The IIS Metabase Explorer Rocks
Partition Misalignment can Cause Poor Performance
Here is an interesting Microsoft Knowledgebase article titled Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000. It explains how a partition misalignment can cause significant … Continue reading
Posted in Performance, SQL Server
Comments Off on Partition Misalignment can Cause Poor Performance
Using Diskpart to Align a Windows Disk Partition
There is an EMC whitepaper titled Using diskpar and diskpart to align partitions on Windows Basic and Dynamic Disks. This paper describes how legacy Windows issues cause a single disk I/O to span multiple tracks. This puts an unnecessary load … Continue reading
Posted in Performance, Windows OS
6 Comments
MD5 Hash Utilities
FCIV.EXE is a handy little utility to snap a MD5 hash of a single file or multiple files. Optionally it can recurse directories and produce XML output. http://support.microsoft.com/kb/841290 MD5SUM.EXE is a more commonly used utility that runs on a number … Continue reading
Posted in Utilities
Comments Off on MD5 Hash Utilities
Simple SQL Server Log Filter / Review Script
Here’s a very basic log filtering /review script that you can run on a daily basis to review your SQL Server logs. It shows the last two days of log entries filtering out logon and backup entries. — daily log … Continue reading
Posted in T-SQL
Comments Off on Simple SQL Server Log Filter / Review Script
Starting Windows Performance Logging at boot time
Performance logging will not start on its own at boot time. Here’s how to get around that problem. Applies to: W2k3, XP Using the Performance MMC, create a set of counter logs. Use a specific name to save the … Continue reading
Posted in Performance, Windows OS
Comments Off on Starting Windows Performance Logging at boot time
SQL Server Best Practices Analyzer (BPA)
A new version of the SQL Server 2005 Best Practices Analyzer (BPA) is here. On a related note, the BPA reported several operating system issues that I was unaware of, including an issue where SQL Server’s working set gets paged … Continue reading
Posted in SQL Server, Windows OS
Comments Off on SQL Server Best Practices Analyzer (BPA)
First stab at a database inventory script
Compatible with sql server 2k5: USE master CREATE TABLE #tempresults ( [name] sysname, db_size NVARCHAR(13), [owner] sysname, [dbid] smallint, created NVARCHAR(11), [status] NVARCHAR(600), compatibility_level tinyint ) INSERT INTO #tempresults EXEC sp_helpdb SELECT db.name, tr.db_size, db.compatibility_level,is_auto_shrink_on, state_desc, recovery_model_desc, page_verify_option_desc FROM sys.databases … Continue reading
Posted in SQL Server, T-SQL
Comments Off on First stab at a database inventory script
Code Prettifier
I found an easy way to render code snippets in HTML. It is called the Simple Talk Code Prettifier. In addition to rendering, it also converts SQL keywords to uppercase and gives you proper indentation. Also supports VB. http://www.simple-talk.com/prettifier/default.php By … Continue reading
Posted in SQL Server
Comments Off on Code Prettifier
Searching for a column name in a SQL database
Here are a couple of different ways to search for a column name within a SQL Server database: — using information_schema views (preferred method) SELECT sc.table_name FROM information_schema.columns sc INNER JOIN information_schema.tables st ON sc.table_name = st.table_name … Continue reading
Posted in SQL Server, T-SQL
Comments Off on Searching for a column name in a SQL database
Graduated
I am happy to report that I graduated from Excelsior College with a BS in Computer Information Systems and a GPA of 4.0!
Posted in Personal
Comments Off on Graduated
ICCP DBA Exam
On a personal note, I am happy to report that I passed the ICCP Database Administration Exam at the mastery level. Passing this exam completes my degree requirements.
Posted in Personal
Comments Off on ICCP DBA Exam
Open Source Diff Tool
Interesting tool: http://winmerge.org/ To quote the authors: WinMerge is an Open Source visual text file differencing and merging tool for Win32 platforms. It is highly useful for determing what has changed between project versions, and then merging changes between versions. … Continue reading
Posted in Open Source, Utilities
Comments Off on Open Source Diff Tool
Script to Return Space Used for All Tables in a Database
Here’s a handy script to return space used by table: spaceused.html
Posted in SQL Server, T-SQL
Comments Off on Script to Return Space Used for All Tables in a Database
Clustering Lab
I recently built an Windows Server 2003 cluster lab using a couple of PCs, two Adaptec AHA2940UW controllers and an old SCSI drive. When configuring the SCSI HBAs, it is important to remember to disable SCSI resets. Note that when … Continue reading
Posted in Windows OS
Comments Off on Clustering Lab
Tarpits
While working on a client’s Exchange server, I stumbled across an interesting knowledgebase article: SMTP tar pit feature for Microsoft Windows Server 2003. Tarpits insert a delay into certain SMTP communications that are associated with spam or with other unwanted … Continue reading
Posted in Windows OS
Comments Off on Tarpits
Automated, Scheduled Domain Controller Diagnostics
Wouldn’t it be nice to have the time once a week to run the dcdiag against all of your domain controllers and review the results? (yeah, right) Well, I wrote a script to automate that process. It works like this: … Continue reading
Posted in Windows OS
Comments Off on Automated, Scheduled Domain Controller Diagnostics
Retina WiFi Scanner
I audited my Wireless Access Point using the free Retina WiFi Scanner from eEye Digital Security. According to it, my WPA PSK key length was too short, so I increased it to around 25 characters. If you look at the … Continue reading
Posted in Uncategorized
Comments Off on Retina WiFi Scanner
Troubleshooting SQL Server with Filemon
Today I came across an interesting problem while changing the SQL server service to run on a non-administrative domain account. Previously the service was configured to log in as localsystem, which has full rights locally, but no domain rights. With … Continue reading
Posted in SQL Server
Comments Off on Troubleshooting SQL Server with Filemon