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
       SELECT
              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
       SET AUTO_CLOSE OFF


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
SELECT
       'Count' = COUNT(*),
       s.Type,
       '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
FROM
       sys.objects s
GROUP BY
       s.type
ORDER BY

       s.type

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 a.name FROM sys.objects a
WHERE a.type = 'U'
AND NOT EXISTS(
       SELECT 1 FROM ServerName.DatabaseName.sys.objects b
       WHERE b.type = 'U'
       AND a.name = b.name
       )


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
  WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1


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
       SELECT
              SERVERPROPERTY('productversion') ProductVersion,
              SERVERPROPERTY ('productlevel') ProductLevel,
              SERVERPROPERTY ('edition') Edition,
              SERVERPROPERTY ('MachineName') MachineName,
              create_date 'SQL Server Installation Date'
       FROM
              sys.server_principals
       WHERE
              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
       ALTER TABLE TableName NOCHECK CONSTRAINT ALL

       ------ do your table insert here

       -- Enable all table constraints, whole database
       ALTER TABLE TableName CHECK CONSTRAINT ALL 


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
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE; 

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
SET MULTI_USER;


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.