Posted on

How to change the collation of a database in SQL Server?

Have you ever wondered how to change the collation for a database in SQL Server?

Well the other day I received a database from a third party supplier and the first step was to change the Collation of the database. My questioning about why they would supply the database in the incorrect collation setting was met with a wall of silence. Therefore I had to put in my documentation how to change the Collation of the database.

I did it the quick way by right click on database > Select properties > options > Select appropriate collation from dropdown menu next to Collation.

Database properties
Database properties

That was a good enough solution at the time but for my documentation I would always add the T-SQL so that anyone could follow the guide without making mistakes.

Below is an example of how to change the Collation via T-SQL

ALTER DATABASE AdventureWorks2012 COLLATE SQL_Latin1_General_CP850_BIN

Please let me know whether you found this guide useful and leave a message in the comments.

Posted on

SQL Server Collations Find current collation and all supported collations

Have you ever needed to check your database or SQL instance to find out its collation? Have you ever wanted to check whether a collation is supported by Windows or SQL Server?

Well I am going to show you how to find out the information quickly and easily.

The below query will show the instance level collation.
SELECT SERVERPROPERTY(‘collation’)

1

The query below will show the collation setting at the database level

 

SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘Collation’) SQLCollation

2

Sometimes its important to know the collations which the OS supports and the following query will help list all the collations which are supported by the OS.

 

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;

3

When doing migrations it is very important to ensure that the Collation setting is maintained and to know whether it is supported by the SQL instance. The below query will help you easily and quickly ascertain that information.

 

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE ‘SQL%’;

4

 

Hopefully that covers all your collation gathering requirements but if you do have another technique which I have missed then please leave them in the comments below.

 

Posted on

SQL Cursor to Kill all connections to a database

Have you ever tried to restore over a database but found that all attempts are being blocked by an annoying SPID? Or had hundreds of orphaned SPIDs running crazy on your instance?

Well I have the solution for you with the below script which uses a cursor to kill all connections to a database.

DECLARE @spid varchar(10)</pre>
DECLARE kill_spid CURSOR fast_forward FOR
SELECT SPID FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'AdventureWorks2012' AND spid > 50
OPEN kill_spid
FETCH NEXT FROM kill_spid INTO @spid
while @@FETCH_STATUS = 0
BEGIN EXEC ('Kill ' + @Spid)
FETCH NEXT
FROM kill_spid INTO @spid
END
CLOSE kill_spid
DEALLOCATE kill_spid

Make sure that you change AdventureWorks2012 for your database name and double check that you have the correct database name as I have seen it when people put the wrong database name in and its never a pretty sight.

 

Posted on

How to attach AdventureWorks 2012 to SQL Server

Have you ever wanted to install the sample database (AdventureWorks )provided by Microsoft which a lot of the tutorials and books refer to? Well one of my colleagues has started to learn SQL Server so I thought I would knock up a quick guide for him.

The correct classical way is to use T-SQL would be to use the below script which you should adjust for wherever you have located the data/ MDF file.


CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG ;

Or you could use the simple GUI method and Right click on databases and select Attach.

1

Then go to the correct path and select the data / MDF file and press OK.

2

If you use the T-SQL method ensure that you do not have the LDF / Log file in the directory of the data / MDF file otherwise you may encounter an error. Also, when recreating the database from just the data / MDF file you will get the following error message.

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf” may be incorrect.

New log file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf’ was created.

 

 

Posted on

Change a database state in SQL

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

 

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

 

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

 

Make Database Read Only

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

 

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO

 

Posted on

List SQL databases in SQL Server

Have you ever wanted to list out all the databases you have on a server? Well I am going to show you how to do it quickly and easily.

The other day I had completed a migration from a creaky old dinosaur server to a brand new powerful server which always brings a smile on my face as I like new stuff. I stopped all jobs, put the databases into offline, updated my documentation and diarised a week in the future to finish my clean-up work.

The next day my colleague asked me where I had moved the databases as they did not appear to still be on the server. When he listed the databases using the two following procedures it showed that we were 8 databases down which was the same amount I had put into offline mode.

EXEC sp_databases
EXEC sp_helpdb

I took a step back and did a quick manual search on the server which when you have a few thousand databases can take a while. I found all eight databases and came to the realisation that those two stored procedures can’t find offline databases.

I used the below query to get an accurate list of the databases.

SELECT name
FROM sys.sysdatabases

Posted on

List of DBCC commands in SQL Server

Have you wanted a list of DBCC commands to enable you to do a deep dive? Well I am going to list them out for you.

Below is a list which complied after a discussion with a fellow DBA who said there were only about twenty whilst I was sure there were way more. We were both disappointed when I found only 39 commands but I will be using this to further my knowledge of the DBCC commands. Please note these are the documented functions as we always risk using undocumented functions on Production servers.

DBCC ALLOC CHECK

DBCC ALLOC REPAIR

DBCC CHECKALLOC

DBCC CHECKCATALOG

DBCC CHECKCONSTRAINTS

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKIDENT

DBCC CHECKTABLE

DBCC CLEANTABLE

DBCC DBREINDEX

DBCC dllname (FREE)

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE

DBCC HELP

DBCC INDEXDEFRAG

DBCC INPUTBUFFER

DBCC IVIEW CHECK

DBCC OPENTRAN

DBCC OUTPUTBUFFER

DBCC PROCCACHE

DBCC SHOW_STATISTICS

DBCC SHOWCONTIG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

DBCC SQLPERF

DBCC SSB CHECK

DBCC SYS CHECK

DBCC SYS REPAIR

DBCC TABLE CHECK

DBCC TABLE REPAIR

DBCC TRACEOFF

DBCC TRACEON

DBCC TRACESTATUS

DBCC UPDATEUSAGE

DBCC USEROPTIONS

Posted on

Length of LOB data (95563) to be replicated exceeds configured maximum 65536

Have you ever had the above error message? Wondered how to sort it out? Well I am going to show you how.

Today I was confronted by the above error when a test migration database which was setup with replication was loaded with simulated data. The migration source environment was not replicated so I did a check of the length of the data using  LEN but thought this was inappropriate as we could solve the problem for a short period before facing the same problem again in testing. I thought it would be best to note the current configuration and then put the max value and note that we would have to change this configuration when it was loaded with data from production.

I checked the current configuration using the below script and screen captured it for future reference.

USE InsertyourDBname;1

GO

EXEC sp_configure ‘show advanced options’, 1 ;

RECONFIGURE ;

GO

EXEC sp_configure ‘max text repl size’;

GO

 

I then decided that I would give the maximum value until later in the project when I could more fine tune this value.

USE InsertyourDBname;

GO

EXEC sp_configure ‘show advanced options’, 1 ;

RECONFIGURE ;

GO

EXEC sp_configure ‘max text repl size’, -1 ;

GO

RECONFIGURE;

GO

This can be done easily via SSMS by going on the server > Properties > Advanced > Max text replication Size > change value to -1.

 

Posted on

Find a column in a database with INFORMATION_SCHEMA.COLUMNS

Have you ever just been given a column name and not known which table it was in? Needed to check whether a column existed within a database?…

Well today I was just given a column name and told the information I needed was in there but alas they didn’t know which table it was in.

I could have gone through each table but that is very inefficient and I can more effectively harness the power of SQL to get the answer quickly and easily.

I knew that the INFORMATION_SCHEMA.COLUMNS would be a great place to start and did the below query.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

1

 

The next step was to add the COLUMN_NAME in with a WHERE clause.

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = ‘max_history_rows’

2

Posted on

How to list all SQL DMVs (Dynamic management views) s and DMFs (Dynamic management functions ) in SQL Server?

Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.

Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.


SELECT * FROM SYS.ALL_OBJECTS

WHERE [name] LIKE '%dm_%'

AND [type] IN ('V', 'TF', 'IF')

ORDER BY [name]

This script will only run in SQL2005 ( SQL 2000 does not include these features)  and above.

In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.