NTFS Compression and SQL Server Do Not Play Well Together
accidentally copying a database to a ntfs compressed volume can result in some unpleasant aftertaste. If you get the error The requested operation could not ...
sql-server
SQL .NET Requirements
SQL Server requirements vary based on the version.
Exploring SQL Server With Powershell And SMO Basics
SqlServer Powershell Cmdlets 2017 - Initialize Look
Capturing Perfmon Counters With Telegraf
Other Posts in Series
InfluxDB and Annotations
Other Posts in Series
Dataedo - First Look
Diagraming and Documentation
ANTS Performance Profiler for the SQL Server Dev
There are a few .NET tools that until recently I haven’t had the chance to work with as much, specifically ANTS Memory Profiler and ANTS Performance Profiler...
Update SSMS With PS1
Installing and Updating SSMS with a simple PowerShell script can be a nice little timesaver…
External Tool VSCODE called from SQL Management Studio
Previous Related Post:
Best Practices: Defining Explicit Length for Varchar/Nvarchar
SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length. Level: Warning
Running InfluxDB as a service in Windows
Other Posts in Series
Setting Up InfluxDb, Chronograf, and Grafana for the SqlServer Dev
Other Posts in Series
Automate Windows Updates for Development
I’ve run into the case where I wanted updates continually applied, while the machine still was part of the GPO that didn’t automatically install updates. For...
Red Gate SQL Clone (#1) - Initial Setup
Note this was during earlier beta usage, so some of the UI and other features will have been updated more. I plan on writing more on this promising tool as ...
Track Creation of Databases
Sys.Databases has some create information, but I was looking for a way to track aging, last access, and if databases got dropped. In a development environmen...
SSMS 2016 - Object Explorer Read Uncommitted
I ran through some directions from others, including the very helpful post from SqlVariant, but I had issues locating the correct keys. For my Windows 10 mac...
Redgate SQL Data Compare & Devart DBForge Data Compare
I’m a big fan of Redgate, as I’m in the Friend of Redgate program. However, I do also utilize some other toolkits. One competitor that I find has some , but ...
Quick Way to Run Powershell Tasks in Parallel
Running tasks in parallel can be a bit difficult in powershell. However, there are a few projects out there that optimize the performance and provide a bette...
Bad Idea Jeans: Query Optimization Through Minification
SQL is pretty verbose compared to some languages. It’s a pretty big disappointment that I have to type out select customer from dbo.customers where id = 2 in...
Dynamically Set Powershell Variables from json
I created this small snippet to allow a list of values from a json file be turned into variables to work with. For working with a fixed list of configuration...
Attaching Database Using SMO & Powershell
Steve Jones wrote a great article on using this automation here titled The Demo Setup-Attaching Databases with Powershell. I threw together a completed scrip...
Parallel Powershell for Running SQL
This is just a quick look. I plan on diving into this in the future more, as I’m still working through some of the changes being made in the main parallel mo...
Get Backup History for All Databases in Server
Here’s a quick snippet to get a listing of the database backups that last occurred on a server. Most solutions provided a single backup listing, but not the ...
Data Compare on Temporal Tables
I hadn’t seen much talk on doing data comparisons on temporal tables, as they are a new feature. I went through the exercise to compare current to historical...
Fixing Untrusted Foreign Key or Check Constraint
Untrusted constraints can be found when you alter/drop foreign key relationships and then add them back without the proper syntax.If you are deploying data t...
Easy SQL Maintenance with Minionware
Review of using Minionware sql maintenance solution
Cannot Generate SSPI Context
Troubleshooting
Centralized Management Server 101
I’ve used Central Management Server registered servers in SSMS for primarily one purpose, saving connections. :-) This is definitely not the intended usage....
Diff All Files - Reviewing Changesets Quickly
Diff all files
Migrating Database Collation - The Red Gate Way
I had some cross database comparisons that I wanted to simplify, but ensuring the collation matched. The amount of objects that I would have had to drop and ...
Fixing non-deterministic error when creating indexed view
I discovered a bit of info on working with float values, while creating a hash value that contained a float value, and a date value.
SQL 2016 - Configuration Manager Not Showing in Start Menu
Didn’t see SQL 2016 Configuration manager in the start menu. Ran a quick search to see if this was a common issue and found an article: Quick Trick Where is ...
Setting DBCC 1222 on startup
The following command is run to gain details on deadlocks.
Remote Desktop Workflow Improvements
Remote server management is a fact of life for folks involved with sql server. Since we work so often with remote machines I looked into a few tools that pro...
SSMS - Connection Color with SQL Prompt & SSMSBoost
If you haven’t explored the visual color coding of tabs based on pattern matches with SQL Prompt, I’d suggest you check this out. Earlier iterations of Red G...
SQL 2016 - Brief Overview on some new features
These are notes taken from the Houston SQL Pass User group from July. This presentation was given by John Cook, (Data Platform Solution Architect Microsoft) ...
Install ReadyRoll via Command Line
command line install options
Does sp_rename on a column preserve the ms_description?
Did some checking as couldn’t find help in the MSDN documentation. My test on SQL 2016 shows that since the column_id isn’t changing, the existing mapping of...
Regex With SQL Server - SQLSharp
In the context of my developer machine, I had log files I wanted to parse through. I setup a log library to output the results on a test server to a sql tabl...
Improvements with SSMS 2016
Improves to SSMS (Sql Server Management Studio) continue. Use the latest version instead of using the version bundled in older versions of SQL Server Install...
Glasswire: (Giveaway Included) Networking Monitoring even a caveman could use
Giveaway details at the bottom for those interestedDealing with development & sql servers, I like to know what type of network traffic is happening on my...
SQL Compare 12: Initial Look
I know there have been a few other folks going into more detail on SQL Compare 12 (beta), but I thought I’d share just a few looks at the new design. Looks p...
SSMS Tools Pack - A Handy Tool for generating CRUD
So I’ve had this tool around for a while, but never found much usage out of it to be honest. I didn’t end up writing a review as I had other tools that did t...
OmniCompare: A free tool to compare SQL Instances
comparing instances
Continual Deployment of Visual Studio SqlProj
Unveil the inner workings of the esoteric build system…
The Mysterious Black Box of R - For the SQL Server Guy
Took a class from Jamey Johnston @ SQLSaturday #516 in Houston. Lots of great information covered. Follow him for a much more detailed perspective on R. Jame...
Red Gate Dependency Tracker - Making Databases Into Moving Art
data structures are art
Automating SSMS 2016 Updates & Install
simplify your ssms install
Cool Tools: Powershell ISE-Steroids
Disclaimer: I have been provided with a free license because I am reviewing. This doesn’t impact my assessment of the tool. I have a passion for finding tool...
Failed to Initialize SQL Agent Log… not worthy
Moving system databases in SQL Server takes a bit of practice. I got that again, along with a dose of SQL humility (so tasty!), today after messing up some c...
TFS Work-Item Fancy Filtering
If you want to create a TFS query that would identify work items that have changed, but were not changed by the person working it, there is a nifty way to do...
SQL 2012 SP3 and entity framework conflict
the problem
Previewing the new SSRS 2016 portal
Ran into an issue with the “Preview New Reporting Portal” link on a fresh install of 2016 giving me a not found error.
Failover all databases to other server
Quick snippet I threw together to help with failing over synchronized databases to the other server in bulk. No way I want to click that darn Fail-over butto...
Calculating Some Max Mirror Stats
This turned out to be quite a challenge. I couldn’t find anything that made this very clean and straight forward to calculate, and in my case I was trying to...
Easy way to test log shipping or availability groups setup
Have been working through the fantastic training resources from Brent Ozar’s Everything Bundle and on the recommended resources they mention after all the tr...
Transaction Logging & Recovery (part 3)
Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log.
Verifying Instant File Initialization
Ran into a few issues verifying instant file initialization. I was trying to ensure that file initialization was enabled, but found out that running the xp_c...
Transaction Logging & Recovery (part 2)
… Continuation of some notes regarding the excellent content by Paul Randal in Pluralsight: SQL Server: Logging, Recovery, and the Transaction Log. Please co...
Transaction Logging & Recovery (101)
Logging & Recovery Notes from SQL Server Logging, Recovery, and the Transaction Log (pluralsight) Paul Randal
Model needs exclusive lock
Ran into an issue where a developer was trying to create a database and was denied due to no ability to obtain exclusive lock on model. After verifying with ...
Documenting Your Database with Diagrams
Don’t get too excited. I know you love documentation, and just can’t wait to spend some time digging in to document your database thoroughly. I imagine you p...
Dynamic SQL & Quotename
Not quite fineprint, but sure feels like it!
XML Attribute VS Element Assignment when working with SQL
XML handling with aliases
Split personality text editing in SSMS with Sublime Text 3
My preview post showed a demonstration of the multi-cursor editing power of Sublime Text 3 when speeding up your coding with SQL server.There is a pretty str...
Multi-Cursor Editing SQL feels like the movie Inception just became real
Yes… multicursor editing is epic
Brentozar’s Training: Chocolate & Cowboy Hats Included
It was entertaining to listen to a technical wizard fighting the obsession with waiting to eat chocolate with 3k viewers watching. Kendra wore about 4-5 cowb...
Monitoring SQL Server on a budget
Cheap ain’t easy
Stranger Danger… The need for trust with constraints
I ran into an issue with errors with an database upgrade running into a violation of a foreign key constraint. Don’t know how it happened. Figured that while...
Red Gate SQL Source Control v4 offers schema locks
Red Gate Documentation Update
Using Qure Profiler To Benchmark Tuning Progress
Qure Analyzer Benchmark Testing
SET NOEXEC is my new friend
Have never really played around with the option: SET NOEXEC ON
Running very large scripts is not a strong area for SSMS
out of memory, argggh!
Database Stuck in Single-User Mode Due to Botched Restore
Working in a development environment, I botched up a restore. After this restore attempt to overwrite my database with the previous version, I had it stuck i...
What was I thinking? Deleting myself from localdb?
Was testing a batch file to add a user to a localdb instance. Assumed that my user as admin on the machine wouldn’t have an issue inserting myself back…. did...
Some simple examples of querying xml with sql
XML is a beast if you’ve never tackled it. Here are some simple examples of what I discovered as I experimented and walked through obtaining values out of a ...
Enabling Instant File Initialization
Found a couple good walkthroughs on enabling instant file initialization. However, I’m becoming more familar with the nuances of various setups and found it ...
Upgrade from SQL 2014 Evaluation to Developer Edition
Couldn’t find documentation showing that upgrade from SQL 2014 evaluation version was possible to developer edition. I just successfully converted an evaluat...
Utilizing the power of table parameters to reduce IO, improve performance, decrease pollution, and achieve world peace…
I was dealing with a challenging dynamic sql procedure that allowed a .NET app to pass in a list of columns and a view name, and it would generate a select s...
Restoring a database that doesn’t exist
When restoring a database that doesn’t exist, say for instance when a client sends a database to you, you can’t use the option to restore database, because t...
Statistics Parsing
Never really enjoyed reading through the statistics IO results, as it makes it hard to easily guage total impact when you have a long list of tables. A frien...
Dev Tools: The File Searcher/Launcher to rule them all
Why does this not have more recognition? In the experimentation of various file management and launching apps, I’ve tried several (Launchy, Listary, etc), bu...
Case of the Mondays… causing me to randomly redefine the Scope of Global temp tables
Today, I was reminded that global temp tables scope lasts for the session, and doesn’t last beyond that. The difference is the scope of the global temp allow...
Get Information on Current Traces Running
This is just a quick informational query to save as a snippet to get some quick information on running traces, and provide the stop and close snippet quickly...
Generate Random Date With Starting Point
If you want to create sample random samples when dealing with date calculations to test your results, you can easily create a start and end point of randomly...
Snippet Alert: Useful dates (eom, bom, etc)
Common date values you may need to reference that you may not want to write from scratch each time.
Finding Groups - Consecutive Months
A step by step explanation on one way to get a consecutive period of months, which could easily be adapted to days, years, or other values. I’ll continue on ...
Eliminate Overlapping Dates
I was looking for an efficient way to eliminate overlapping days when provided with a historical table that provided events that could overlap. In my case, I...
Scalar functions can be the hidden boogie man
Ran across a comment the other day that scalar functions prohibit parallelism for a query when included. I thought it would be worth taking a look, but didn’...
Renaming all references inside stored procedures and functions can be migraine worthy without a little help…
assistance to convert database references when using synonyms
Shortcut to reference examples, syntax, and definitions straight from SSMS
I’ve never really used the F1 key for help files with most applications. I was surprised at the usefulness in SSMS I discovered today that uses scripting to ...
Check Constraints can help enforce the all or nothing approach when it comes to column updates
If you have a set of columns inside your table that you want to allow nulls in, however if one of the columns is updated force all columns in the set to be u...
SSMS 2012 Extender for the times you want some organization to the random pile of objects gathering in the dusty confines of that database
When dealing with large amounts of objects in a database, navigation can be tedious with SSMS object explorer. This extender organizes the objects into group...
TSQL Snippet for viewing basic info on database principals and their permissions
Quick snippet I put together for reviewing basic info on database users/principals, permissions, and members if the principal is a role.:
A moment of void in the cranium reveals a recursive computed column with an esoteric message
Msg 402, Level 16, State 1, Line 67 The data types varchar and void type are incompatible in the add operator.
View computed columns in database
Snippet to quickly view computed column information. You can also view this by doing a “create table” script. This however, was a little cleaner to read and ...
On how to Googlify your SQL statements for future searching
For sake of future generations, let’s begin to reduce typing and reuse code we’ve built. I think we can all agree that TSQL statements are often repeated.
Get synonym definitions for all databases in server
If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databa...
Calculating the next beginning of month and the current end of month
Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to fin...
Installing SSMS 2012 all by it’s lonesome
SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation p...
snippet designate a certain time of the day in getdate()
Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, str...
Native SSMS a second class citizen no longer…
Intellisense can be a boon to adding quick development. Quick hints on scope specific variables, syntax suggestions, function descriptions and more provide a...
dynamic sql and a char crash
Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn’t working. Fou...
How to default SSRS date parameters to the first and last day of the the previous month
Populating default dates in SSRS can be helpful to save the user from having to constantly input the date range they normally would use. When a report is pul...