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.
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.
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 * FROMsys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;
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 * FROMsys.fn_helpcollations() WHERE name LIKE ‘SQL%’;
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.
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
FETCH NEXT FROM kill_spid INTO @spid
while @@FETCH_STATUS = 0
BEGIN EXEC ('Kill ' + @Spid)
FROM kill_spid INTO @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.
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.
Then go to the correct path and select the data / MDF file and press OK.
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.
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
ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT
ALTER DATABASE [YourDBName] SET READ_ONLY
Make Database Read/Write
ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT
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.
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.
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.
EXEC sp_configure ‘show advanced options’, 1 ;
EXEC sp_configure ‘max text repl size’;
I then decided that I would give the maximum value until later in the project when I could more fine tune this value.
EXEC sp_configure ‘show advanced options’, 1 ;
EXEC sp_configure ‘max text repl size’, -1 ;
This can be done easily via SSMS by going on the server > Properties > Advanced > Max text replication Size > change value to -1.
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.