Author Archives: admin

Just when you thought the worst of 2020 was past

Posted on 17. December 2020 by admin

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

Posted on 31. August 2018 by admin

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

Posted on 19. June 2018 by admin

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

Posted on 8. November 2017 by admin

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

Posted on 14. October 2017 by admin

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

Posted on 27. June 2017 by admin

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

Posted on 24. June 2017 by admin

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

Posted on 4. May 2017 by admin

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

Posted on 1. March 2017 by admin

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

Posted on 30. May 2013 by admin

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

Posted on 15. October 2011 by admin

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

Posted on 14. April 2011 by admin

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

Posted on 16. February 2011 by admin

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

Posted on 20. January 2010 by admin

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

Posted on 2. February 2009 by admin

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

Posted on 7. April 2008 by admin

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

Posted on 3. April 2008 by admin

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

Posted on 4. March 2008 by admin

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

Posted on 19. February 2008 by admin

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

Posted on 15. September 2007 by admin

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

Posted on 11. February 2007 by admin

…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