SQL dbo.Chris

The application is down! Blocking alerts are flooding your email inbox!  SQL Server is working as fast as it can but it's still your problem. Kill SPID, kill kill kill kill waiting for Roll Back.  We need to find a new way to do the same thing.... Now what just happened? The creation of a new NOT NULL column with a default value to an existing table operation occurred on a large table: where every row in the table is trying to updated to add the default value of the new column. Now why did this happen? You are running SQL Standard 2016 or an Enterprise version prior to SQL 2012.  Small tables this is fairly insignificant as it will be a fast operation, but for large tables this will be problematic as to completely prohibit the operation as it will be a long operation causing blocking which will lead to application and job timeouts. How can we reduce this and can we?  Starting with SQL Server 2012 and if you are on ENTERPRISE Edition the operation in mos...

I can't say enough good things about  sp_RestoreGene So much so that I used it to generate all my syntax for a nightly refresh process using Full and Diff backups from production to another server of nearly 250 databases consisting of 2tb of data.  I will cover this solution in another blog post.  The creator of sp_RestoreGene looks and takes in feedback from their website and b ased upon some of my feedback and suggestions for enhancement they were later implemented.  Today the variable @ExcludeDiffAndLogBackups was updated so that the value changed from d ata type bit to int    This allowed for the existing BIT data type functionality to be retained and extended it offering by allowing for new values 2 and 3 where 0 (default) = full, diff and logs and   1 = full only where previously the only  excepted values.  Now values  2 = full and diff,    3 = full and logs can be used. Prior to using this stored proce...

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...

Hopefully people will find this blog to be both helpful and offer up some decent advice with relevant content.  It will also serve as a placeholder for experiences, lessons learned, and some of the scripts I have used both currently and over the past 10 years.  My goal will be to update content at least weekly but we will see how that shapes out as I really enjoy all things SQL Server while juggle being a husband and crisis negotiator of 5 wonderful kids.  I have decided to follow what many others have done and start putting my SQL Server journey and thoughts out there for people to learn. We will kick things off super simple with the following script that I use after restoring anything from production to a dev/test environment where I do not care about a database being in FULL Recovery so we flip it to simple and shrink the log file: --xSimple --Sets the DB to simple recovery and truncates the log file. DECLARE @CURRENTDB VARCHAR(128); DECLARE @LOG VARCHAR(128...