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 […]
Tag archives: Database Administration
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]