Blog Archives

SQL Tip: Empty space release from large databases. Small chunks at a time

Many a time as DBAs and developers we come across trying to handle large or very large databases. Large is a relative term depending on experience, tools and infrastructure resources you are dealing with. But it can be a pain

Tagged with: ,
Posted in backup, SQL Server, SQL Tips, tsql

SQL Server intelligent Database compression script with error handling

In absence of a good script for Database compression that includes, Error handlings of all and any unforeseen kind Avoidance of “Sparse columns” Not attempting on already compressed objects Attempt Row compression if Page  compression fails Proceeding in case of errors

Tagged with:
Posted in SQL Tips, tsql

Description of support for SQL Server databases on compressed volumes

Summary Microsoft SQL Server databases are not supported on NTFS or FAT compressed volumes except under special circumstances for SQL Server 2005 and later versions. A compressed volume does not guarantee sector-aligned writes, and these are necessary to guarantee transactional

Posted in Uncategorized

SQL Tip: Pre and post installation checklists

SQL build standards Following are the standards recommended by me after considering best practices and configurations specific to SQL Server Infrastructure Collation: This setting cannot be changed after installation easily and without disruption to service therefore it is absolutely necessary

Posted in SQL Tips

SQL Tip: Change owner on all Agent Jobs – TSQL code

Quick note. After creation of maintenance plans and agent jobs to be careful always replace owner with SA or any other more permanent account. This will reduce the chances of jobs failing due to any issues with accounts. DECLARE @name_holder VARCHAR(1000) DECLARE

Tagged with:
Posted in SQL Tips, tsql

SQL Tip: Afraid of PowerShell? Use with Powershell ISE

dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included. Please note that documentation and command names may be out of date while we work furiously towards 1.0   User it with

Tagged with: , ,
Posted in powershell, SQL Server, SQL Tips

Upgrade from TFS 2010 to TFS 2017 (with SQL 2016). Fast!

Upgrade from TFS 2010 to TFS 2017 (with SQL 2016). Fast!

Old Live TFS 2010 needs to upgrade to latest TFS 2017.2. There doesn’t exist a direct for an upgrade from TFS 2010 to TFS 2017. It is too big of a gap it seems. To achieve this the 2 steps should be or the route I took was. First, upgrade to TFS 2015 and then to TFS 2017.
In this particular instance, we opted for a new server with new OS and latest SQL 2016 to make the most of it.

Tagged with: , ,
Posted in Visual Studio

SQL Tip: Identifying Column store index potential candidate tables in your databases for major query performance improvements

Column Store indexes appeared in SQL Server 2012 and by 2016 and beyond have progressed much further allowing their usage with fewer restrictions than before and this new storage mechanism for indexes and data provides significant gains for performance especially

Tagged with: ,
Posted in Column Store Indexes, SQL Server, sql server performance, Uncategorized

SQL Monitoring Solution Development – SQL Performance Dashboard Reports – Part 2 Customisation for centralised remote monitoring of SQL instances

In Part 1 of this series on SQL Server Performance Monitoring Dashboard I provided an intro. Now what we would like to do is to be able to run these reports remotely… without logging into a server or ever connecting

Tagged with: ,
Posted in Monitoring, SQL Server, SQL Server Administration, sql server performance

How to get GDPR compliant with Microsoft

When the EU’s GDPR (General Data Protection Regulation) is introduced next year, the bar for data privacy protection in the UK will be raised. For thousands of businesses GDPR highlights daunting issues of compliance. Organisations that fail to comply with

Tagged with: , ,
Posted in gdpr, security, SQL Server, SQL Server Administration, Uncategorized