Database Engine, T-SQL

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… Continue reading How to change the collation of a database in SQL Server?

Database Engine, T-SQL

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… Continue reading SQL Server Collations Find current collation and all supported collations

T-SQL

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. Make sure… Continue reading SQL Cursor to Kill all connections to a database

Database Engine

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… Continue reading How to attach AdventureWorks 2012 to SQL Server

Database Engine, T-SQL

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… Continue reading Change a database state in SQL

T-SQL

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… Continue reading List SQL databases in SQL Server

Database Engine

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… Continue reading List of DBCC commands in SQL Server

Database Engine, Replication

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… Continue reading Length of LOB data (95563) to be replicated exceeds configured maximum 65536

T-SQL

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.… Continue reading Find a column in a database with INFORMATION_SCHEMA.COLUMNS

Database Engine, T-SQL

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… Continue reading How to list all SQL DMVs (Dynamic management views) s and DMFs (Dynamic management functions ) in SQL Server?