SQL Server Consulting

Thursday, October 27, 2016

Just renamed a database for a customer, and I wanted to share the logic with all of you. When we rename a database, it only effects the database name itself. It does not change the data or log file names. I HATE it when the data/log file names don't match the db name! Consistently named objects are faaaar easier to manage, imo. :-) You can use this method to rename the database AND its data and log files. Easy peasy. In this example, the 'InformDVODV' database is renamed to 'InformDVOPD'.

-- get your db filenames

USEInformDVODV;-- current database name

EXECsp_helpfile

-- physical names and paths

-- only change the data/log
file names in the FILENAME value

ALTERDATABASEInformDVODV-- don't change

MODIFYFILE (

NAME='InformDVODV',-- don't change

FILENAME='E:\MSSQL\DATA\InformDVOPD.mdf'-- change this

);

ALTERDATABASEInformDVODV-- don't change

MODIFYFILE (

NAME='InformDVODV_log',-- don't change

FILENAME='O:\MSSQL\Log\InformDVOPD_log.ldf'-- change this

);

-- logical names

-- only change the NEWNAME value

ALTERDATABASEInformDVODVMODIFYFILE (

NAME=InformDVODV,

NEWNAME=InformDVOPD -- change this

);

ALTERDATABASEInformDVODVMODIFYFILE (

NAME=InformDVODV_log,

NEWNAME=InformDVOPD_log -- change this

);

-- now we rename the database

USEmaster;

GO

ALTERDATABASEInformDVODV

ModifyName=InformDVOPD;

GO

-- check your newly renamed files

USEInformDVOPD;-- new db name

EXECsp_helpfile

That's it! Important to know, you cannot do this if users are in the database. If there are active connections, you will need to kill them before attempting the rename. Run this to kill any connections by setting the database to single user mode:

USE[master];

GO

-- disconnect all existing sessions

ALTERDATABASEInformDVODVSETSINGLE_USERWITHROLLBACKIMMEDIATE

GO

And you'll run this after you've renamed the database:

-- change
to multi-user

ALTERDATABASEInformDVOPDSETMULTI_USER

GO

That's it! Follow the sequence and double-check your filenames, this will work fine. I would recommend taking a look at both of these for more details as well:

Monday, October 24, 2016

Yes. There will come a time when you need to confirm the domain name of your server. Today, for example, I was setting up a bunch of RDP connections, and the domain name was a required credential. This particular customer has several domains, so I just wanted to check quickly, to be sure I was targeting the right one. I have posted 3 ways below, beginning with the easiest... which is the one I prefer to use! Take a look and let me know if you have another way to do the same.

Friday, September 2, 2016

Just a quick script to check your database compatibility level... and change it, if you need to. This statement will bring back the compatibility levels for each database in the system:

/* query the db
compatibility level */

SELECT

name [database],

compatibility_level,

[version] =

CASEcompatibility_level

WHEN 65 THEN'SQL Server 6.5'

WHEN 70 THEN'SQL Server 7.0'

WHEN 80 THEN'SQL Server 2000'

WHEN 90 THEN'SQL Server 2005'

WHEN 100 THEN'SQL Server 2008/R2'

WHEN 110 THEN'SQL Server 2012'

WHEN 120 THEN'SQL Server 2014'

WHEN 130 THEN'SQL Server 2016'END

FROM

sys.databases;

And when you need to change it:

/* change db
compatibility level */

ALTERDATABASE YourDatbaseName

SETCOMPATIBILITY_LEVEL=
110;

Be wary, though, about changing the compatibility level. Often times by doing so, you will be unable to take advantage of certain features. Take a look at this MSDN reference for more details on the differences between higher/lower compatibility levels:

Friday, July 29, 2016

One of my customers has an instance where many people log in as sa. We're planning to change the password and rename the sa login, but we first need to know who is using it, and for what. To do this, I've put together an sa Logon Trigger. While I typically avoid triggers, I believe this one is very manageable because it is specific to only one login, and it is only collecting details from each login attempt.

First I create a LogonAudit table to collect the logon details.

USE DBA;

CREATETABLE LogonAudit

(

LogonTime datetime,

HostName varchar(50),

ProgramName varchar(500),

LoginName varchar(50),

OriginalLoginName varchar(50),

ClientHost varchar(50)

)ON [DBA_Data]

GO

Now for the trigger.

CREATETRIGGER
tr_LogonTrigger

ONALLSERVERWITHEXECUTEAS'sa'

FOR LOGON

AS

/*

Used with 'sa logon audit', to
determine who is logging in with sa, when and from where.

How to test it? Easy. Launch SSMS and open a query window. Look at the lower right, you should see you're logged with your domain login. Right click the query screen, choose the 'Connection' option, and then 'Change Connection...'. Here you will change the authentication to 'SQL Server Authentication', then input the sa login and password and hit 'Connect'. That's it! That single action was a logon attempt by the sa login, and it should have triggered an insert into your LogonAudit table.

This is what I captured in my local instance:

But wait. We logged in one time, and we have more than one entry recorded in our LogonAudit table. Why? This happens because there are multiple SQL Server Services running in parallel. If you were to go in and stop all SQL Server Services except for SQL Server (MSSQLSERVER), then you would only capture one entry in the table for each login attempt.

Maybe you want to disable it for some reason? Rather than deleting it, you can just DISABLE/ENABLE with these statements:

Friday, June 24, 2016

I was putting together a procedure earlier for a fairly common ETL need -- remove records from one table that no longer exist in another table. A simple WHERE NOT EXISTS, right? Well, I caught myself in a syntax error, so I thought I would post it here for all of you.In this example, we want to delete from CustomerSales where CustomerID no longer exists in the Customers table: DELETE dbo.CustomerSales
a

WHERENOTEXISTS(

SELECT 1 FROM dbo.Customers b

WHERE a.CustomerID = b.CustomerID)

Pretty close, but no cigar. Try that, and you'll receive this error:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'a'.

Sunday, June 19, 2016

I ran into this error a couple weeks ago when I was applying a service pack to a couple of mirrored SQL Servers. I had just patched the Mirror and resumed the mirroring session, but when I tried to perform a manual failover of the Mirror to the Principal, I received this message:

We'll see this if we try to failover the mirroring session when the databases are not in a SYNCHRONIZED state. Makes sense.. if you take a look at this post, you'll see the step before the manual failover was to resume the Mirror session. It just wasn't completely synchronized yet.

To confirm how far behind the mirror was, I ran this query on both the Principal and the Mirror.

SELECT

DB_NAME(database_id) [Database],

mirroring_failover_lsn

FROM

sys.database_mirroring

The 'mirroring_failover_lsn' is the log sequence number that the Mirror partners use as the point of reconciliation. I waited a couple minutes and tried again. The failover completed successfully because the LSN was now the same on both the Principal and Mirror instance.

Wednesday, June 15, 2016

On many occasions over the years, I've enabled a DATETIME attribute on a table, but allowed SQL to populate it for me, with a GETDATE() DEFAULT constraint. This can be very helpful for monitoring application data flow, and for troubleshooting or debugging. To know exactly when a record was written, is a very good thing, I can assure you.

You may already have a date field, but there is no constraint. Just use this to add the constraint to your table:

ALTERTABLE [dbo].[YourTable]

ADDCONSTRAINT
[df_YourTable_DateField]

DEFAULT (GETDATE())FOR [DateField];

Or, this one can be used to both add the new field, and enable the constraint:

ALTERTABLE [dbo].[YourTable]

ADD InsertTime DATETIMENOTNULL

CONSTRAINT [df_YourTable_InsertTime] DEFAULT{GETDATE());

* I usually use 'InsertTime' or 'DateCreated' to name attributes like this, because I think it very intuitively suggests what the value is used for.

I received this alert from one of my customer's servers today:

Msg 18054, Level 16, State 1, Line 1

SQL Server Alert System: 'Severity 016' occurred on \\SQLSERVER11

DESCRIPTION: Error 778441, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Odd. I didn't know who or what is trying to raise an error with a msg_id that doesn't exist yet, so I ran this to search all database objects for any reference to 778441:

SELECT o.name,o.id,c.text,o.type

FROM sysobjects o RIGHTJOINsyscomments c

ON o.id = c.id

WHERE c.textLIKE'%778441%'

It returned to me this trigger:

If we look at the trigger definition, you can see where the unknown msg_id's are being called: