Wednesday, May 8, 2019

SQL Server Database - Is Auto_Close Enabled?

Should Auto_Close be ON for any of your databases?  In short, no.  Having the Auto_Close option ON = increased overhead.  Or, per MSFT, "...this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection."  

In my book that means turn it off.  Here's a quick query to show you which databases have Auto_Close ON:

       -- is auto_close enabled
              name [Database],
              CASE WHEN is_auto_close_on  = 0 THEN 'No'
                      WHEN is_auto_close_on = 1 THEN 'Yes' END AutoCloseEnabled
       FROM sys.databases

And this is what you use to set it OFF:

       -- set auto_close off

       ALTER DATABASE ReportServer

Take a look at both of these for more details:
     Set the AUTO_CLOSE Database Option to OFF

Tuesday, April 30, 2019

Quick count on all SQL Server Object Types

How many times have you copied objects from database A to B, and needed to compare object counts when complete?  Or, maybe you just want to see what's in one database that is not in another?  Here's a quick piece to give you object type counts in your database, and another to show you what's in one database that's not in the other.

-- object type counts
       'Count' = COUNT(*),
       'Description' = CASE [Type]
       WHEN 'AF' THEN 'Aggregate function (CLR)'
       WHEN 'C' THEN 'CHECK Constraint'
       WHEN 'D' THEN 'DEFAULT (constraint or stand-alone)'
       WHEN 'EC' THEN 'Edge Constraint'
       WHEN 'F' THEN 'FOREIGN KEY Constraint'
       WHEN 'FN' THEN 'Scalar functions'
       WHEN 'IF' THEN 'SQL Inline Table-valued Function'
       WHEN 'IT' THEN 'Internal table'
       WHEN 'P' THEN 'SQL Stored Procedure'
       WHEN 'PK' THEN 'Primary Key'
       WHEN 'R' THEN 'Rule (old-style, stand-alone)'
       WHEN 'RF' THEN 'Replication-filter procedure'
       WHEN 'S' THEN 'System base table'
       WHEN 'SN' THEN 'Synonym'
       WHEN 'SO' THEN 'Sequence  Object'
       WHEN 'SQ' then 'Service Queue'
       WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
       WHEN 'TF' THEN 'SQL table-valued-function'
       WHEN 'TR' THEN 'SQL DML trigger'
       WHEN 'TT' THEN 'Table type'
       WHEN 'UQ' THEN 'UNIQUE Constraint'
       WHEN 'U' THEN 'User Table'
       WHEN 'V' THEN 'View'
       WHEN 'X' THEN 'Extended stored procedure'
       ELSE type END
       sys.objects s


Here's the output from my AdventureWorks2012 database:           

And this piece will tell you what tables are in the database you are connected to, that is not in the 'DatabaeName' on the other 'ServerName' in the query:  

SELECT FROM sys.objects a
WHERE a.type = 'U'
       SELECT 1 FROM ServerName.DatabaseName.sys.objects b
       WHERE b.type = 'U'
       AND =

Of course, you can change it to any object type, or even all objects... and you'll need to have a linked server for 'ServerName' in order for it to work.

Hope it helps!

Sunday, April 28, 2019

How to find all startup stored procedures?

I upgraded an instance from v2008 to v2014 over the weekend.  Fortunately, everything went well, but this was one of the prerequisites:

  • Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time might block the upgrade process.

So I ask myself -- how do I find all startup procs?  Very simple.  Run this query to identify what procedures are configured to run at startup:

  -- find procs that run at startup
  SELECT name,create_date,modify_date
  FROM sys.procedures

And run this to tell the system to run it at startup, or not:

  -- edit to run at startup, or not
  EXEC sp_procoption
   @procname = 'usp_MssqlServiceStart',@optionname= 'startup',@optionvalue = 'off'

As I said, very simple.  Take a look at this for more details on sys.procedures:

And even this for more details on the SQL Server upgrade:

Thursday, April 25, 2019

How to find out when SQL Server was installed?

Good question.  How do you find out when SQL Server was installed?  I believe there may be a few different methods but the one that I use is pretty simple.  I just query the system for something that is created at the time of the install --- the NT AUTHORITY\SYSTEM login.  Pretty easy... as you can see here.

              SERVERPROPERTY('productversion') ProductVersion,
              SERVERPROPERTY ('productlevel') ProductLevel,
              SERVERPROPERTY ('edition') Edition,
              SERVERPROPERTY ('MachineName') MachineName,
              create_date 'SQL Server Installation Date'
              name='NT AUTHORITY\SYSTEM'

Here's the output from one of my instances:

And that's that!

Monday, April 22, 2019

How to disable or re-enable table constraints

Say you are copying data to one table from another, and you need to work around a  particular FOREIGN KEY constraint.  Or, maybe you you need to work around ALL FK constraints on that table.  We'll often need to work around FK constraints in situations like these.  This post is just a quick how-to, for a single table constraint, or all constraints for a table.

For all constraints on a table, replace 'TableName' with your table name:

       -- Disable all table constraints, whole database

       ------ do your table insert here

       -- Enable all table constraints, whole database

For a single constraint on a table, replace 'TableName' and 'ConstraintName' with your specific object names:

       -- Disable single constraint, single table
       ALTER TABLE TableName NOCHECK CONSTRAINT ConstraintName

       ----- do your table insert here

       -- Enable single constraint, single table
       ALTER TABLE TableName CHECK CONSTRAINT ConstraintName

There you have it.

Friday, March 22, 2019

Kill all connections to SQL Server database -- fast

What's the fastest way to kill all database connections?  Properly, I should say... without pulling the cord on the server.

I'm glad you asked! 😉  The easiest/fastest way that I am aware of is to set the database into SINGLE_USER.  This will sever all active connections to the database aside from your own.  Like this: 

-- kill all connections to a database
USE master;
ALTER DATABASE SSISDB -- change to your dbname

When you've done your thing and you're ready to let them back in, you'll set it back to MULTI_USER, like this:

-- set it back into multi user
USE master;
ALTER DATABASE SSISDB -- change to your dbname

Easy peasy.

You could also use a cursor to go through all active processes in the database, killing them sequentially... but, I don't often recommend the cursor method, if it can be avoided.