Bulk Backup and Restore at the tip of your finger

(Code can be downloaded here) (A newer version of the code can be downloaded here) The Backup part “Can you please give me a recent backup of all user databases on that computer please ?” That is the sentence that will delay your planned work for the afternoon and will keep you busy for numerous…

14

Get the NULL percentage of values in a specific table dynamically

  Nothing really fancy, but a small script I was now asked twice for. So I thought I publish it for the public and for the reference for others. It simply calculates the percentage of NULL values for a specific table in all columns. The last request was originated from the post here. Be aware…

4

Did your SQL Agent doze off and you don´t know which jobs to run afterwards ?

  In some cases you might face the problem that SQL Server Agent Service didn’t come up or was stopped for reason. Jobs scheduled in the downtime will not run automatically when the Agent service comes up again, and that is a good thing. I often hear people saying, why isn’t that supposed to be…

9

Database internal file versions–Where is the Undo button for a database upgrade ?

  A common question is how to move back to the older version of the database once upgraded. The answer is, you can´t. By attaching the database to the new SQL Server version an upgrade is done internally which can be seen if you run the attach commands in e.g. the script window of SSMS….

3

Usage of @@Trancount in DML statements

  Some time ago I got an interesting questions why the following applies: –drop table a create table a(m varchar(max), i int) go declare @cnt int –Explicit transaction begin tran set @cnt=@@trancount insert into a select ‘inside’, @@trancount insert into a select ‘inside cnt’, @cnt commit tran set @cnt=@@trancount –Implicit transcation insert into a select…

5

(Log) resistance is futile – how to drop log files

At the beginning everyone is trying to tune the database by creating additional database files, spreading the data across file groups, adding additional log files for the database… But wait a minute, is there a performance benefit from having more than one log file in the database ? Not really. This is due to the…

0

The “magic” about trustable relationships with NULL and NOT IN

  As a follow-up to my former post “Why you shouldn’t´trust the friendship of NULL and the (NOT) IN predicate” I asked Paul Randal during our SQL Server Master training about the possible internal reason that the results can vary if you have NULL in the IN-list. The explanation is that easy that I did…

1

Getting feedback / progress from batches and stored procedures

Ever wanted to get feedback and interim results like a progress from a stored procedure ? Well, not that easy as the results such as PRINT information is send after the batch has been completed. If you want to get information back from your batches you can use the property FireInfoMessageEventOnUserErrors in conjunction with the…

5

A long (but not missed) friend revisited, prefixing stored procedures with SP_

Coming as a simple sample with PBM (creating a policy with a condition that procedure names shouldn’t´t start with SP_) and getting an interesting question in one of my classes, I wanted to revisit the question about the yet in some places existing naming convention of prefixing the procedure with SP_. To keep a long…

2