-
Recent Posts
Personal Links
SQL Server Links
Windows OS
Meta
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 AlwaysOn, lab, testing
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 AlwaysOn, lab, testing
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
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