Author Archives: admin

Just when you thought the worst of 2020 was past

Today I got an e-mail from the PASS board indicating that the organization is dissolving.  It reads: Dear Bennett,   PASS could not have anticipated the impact COVID-19 would have on the world, and on our organization. We are all … Continue reading

Posted in Uncategorized | Comments Off on Just when you thought the worst of 2020 was past

Photography Hobby Stuff

A few weeks ago I bought a Pentax Spotmatic at thrift store. It ran around 15 bucks.  I bought it because it appeared to have a nearly pristine Super Takumar 50mm F1.4 lens.  Post-purchase and on closer inspection, I found … Continue reading

Posted in Personal, Uncategorized | Comments Off on Photography Hobby Stuff

Synchronizing Logins, Jobs, and Dependent Objects between Replicas with PowerShell

Here’s a simple PowerShell Script that I developed to synchronize logins, credentials, proxies, operators, and jobs between AlwaysOn replicas using PowerShell, dbatools, and a little bit of T-SQL.   1 2 3 4 5 6 7 8 9 10 11 … Continue reading

Posted in AlwaysOn, Powershell | Comments Off on Synchronizing Logins, Jobs, and Dependent Objects between Replicas with PowerShell

High Availability Lab: Adding dual Ethernet NIC yields big improvements

I have a lab that includes: Two standalone SQL Server 2014 instances One standalone SQL Server 2017 instance running on Linux Thee 2016 instances that are part of an availability group A 2016 failover cluster instance with two nodes A … Continue reading

Posted in AlwaysOn, SQL Server | Tagged , , | Comments Off on High Availability Lab: Adding dual Ethernet NIC yields big improvements

How to quickly and easily simulate a low disk space scenario

It turns out that the built in fsutil OS utility can quickly create an empty file of any size.  For example: fsutil file createnew test.txt 1300000000 Will create a file 100 billion bytes in size. For my scenario I needed to … Continue reading

Posted in Performance, SQL Server | Tagged , , | Comments Off on How to quickly and easily simulate a low disk space scenario

Azure Cert

Today I finished and passed the Microsoft/edX  DAT219x: Provisioning SQL Server and Azure SQL Databases certification course.  It covered provisioning and configuring Azure SQL databases, i.e., PAAS, as well as building SQL Server instances on both Azure Windows and Linux … Continue reading

Posted in Uncategorized | Tagged , , | Comments Off on Azure Cert

SQL Server 2017 on Linux POC

Fun, new stuff today. I installed an instance of Ubuntu on my Oracle VirtualBox hypervisor and then installed SQL Server 2017 using the directions found here. Once installation was complete I used systemctl status mssql-server to validate that the services … Continue reading

Posted in Uncategorized | Comments Off on SQL Server 2017 on Linux POC

24 Hours of PASS Spring 2017

The Spring 2017 24HOP focused on data security, a subject near and dear to me. One of favorite presentations was Bob Pusateri’s Passive Security for Hostile Environments. He showed what measures you can take when you have a situation where … Continue reading

Posted in Uncategorized | Comments Off on 24 Hours of PASS Spring 2017

Controlling Large Deletions and Transaction Log Growth

Deleting from very large tables can cause uncontrolled transaction log growth.  The way around this is to perform an iterative TOP(n) delete where you limit the number of rows to be deleted in each batch.  I got this idea from … Continue reading

Posted in Uncategorized | Comments Off on Controlling Large Deletions and Transaction Log Growth

Troubleshooting beyond the database

Users were seeing transient performance slowdowns every day between 15:00 and 16:00.  The slowdowns would typically last for 2-3 minutes and occur one or more times during this time window.  After looking at things on the SQL Server and database … Continue reading

Posted in Performance | Comments Off on Troubleshooting beyond the database

Way too Many Virtual Log Files

Holy cow! Today I encountered a database with nearly 10,000 VLFs! I should have taken a screenshot. I used Kimberly Tripp’s guidance to get things under control

Posted in Uncategorized | Comments Off on Way too Many Virtual Log Files

Deduplicating files with LogParser and SQL Server

Perhaps this should be categorized as a stupid people trick. On the other hand, it’s a good, cheap way to deduplicate files assuming you’re running SQL Server and LogParser. This post assumes at least a basic knowledge of T-SQL and … Continue reading

Posted in T-SQL, Utilities, Windows OS | Comments Off on Deduplicating files with LogParser and SQL Server

Free SQL Server training videos

The people over at SQLSkills.com have made their MCM training videos available online for free.  They’re pretty incredible.  Subject areas include database internals and performance, high availability and disaster recovery, performance tuning,  security and development support.  Link here:  http://www.sqlskills.com/T_MCMVideos.asp   … Continue reading

Posted in Performance, SQL Server | 1 Comment

PowerShell Script to Clean Up Old Files Based on Age

Here’s an extremely simple PowerShell script to remove old files based on age. You could use it to enforce a policy that keeps 90 days of ETL input files on a server. Files older than that would purged with this … Continue reading

Posted in Utilities, Windows OS | 3 Comments

Getting data file space usage

Here is a simple query to get space usage by data file:  SELECT     a.FILEID,     [FILE_SIZE_MB] = CONVERT(decimal(12,2),ROUND(a.size/128.000,2)),     [SPACE_USED_MB] = CONVERT(decimal(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),     [FREE_SPACE_MB] = CONVERT(decimal(12,2),ROUND((a.size–FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,     NAME = LEFT(a.NAME,24),     FILENAME = LEFT(a.FILENAME,55)     FROM dbo.sysfiles a 

Posted in SQL Server, T-SQL | Comments Off on Getting data file space usage

Getting HH:MM:SS value from Seconds

Here is another handy date manipulation function. This one converts the number of seconds, expressed as an int, into the HH:MM:SSS format SELECT CASE WHEN @seconds/3600<10 THEN '0' ELSE ” END + RTRIM(@seconds/3600) + ':' + RIGHT('0'+RTRIM((@seconds % 3600) / … Continue reading

Posted in T-SQL | Comments Off on Getting HH:MM:SS value from Seconds

Moving

I originally blogged on Yahoo 360, but it is a lame duck, so I’m moving my postings over to WordPress.

Posted in Uncategorized | Comments Off on Moving

Basic Database Inventory Script

CREATE TABLE #tempresults2 ( database_name NVARCHAR(128), backup_start_date DATETIME ) INSERT INTO #tempresults2 SELECT database_name, backup_start_date FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND TYPE IN (‘D’, ‘I’) ); WITH … Continue reading

Posted in SQL Server | Comments Off on Basic Database Inventory Script

Query to get last database backup dates

Here’s a simple query to find the last backup dates:   SELECT backup_start_date, database_name FROM msdb.dbo.backupset AS b1 WHERE backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb.dbo.backupset AS b2 WHERE b1.database_name = b2.database_name AND type = ‘D’ )

Posted in SQL Server | Comments Off on Query to get last database backup dates

Windows shell script: putting date & time into a file

SET mydate=%DATE:~4,2%_%DATE:~7,2%_%DATE:~10,4% echo mydate >> somefile.txt

Posted in Windows OS | Comments Off on Windows shell script: putting date & time into a file

Earlier Postings

…stuff imported from an earlier journal 2/3/07  I stumbled across a number of useful SQL security tools at SQLSecurity.com: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx 1/23/07  I ran into a problem configuring SQL XML support in IIS 6.0. When I went to do a query … Continue reading

Posted in Uncategorized | Comments Off on Earlier Postings