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.

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.

 

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.

</p>
<p>CREATE DATABASE AdventureWorks2012</p>
<p>ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf')</p>
<p>FOR ATTACH_REBUILD_LOG ;</p>
<p>

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.

 

 

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

 

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

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.

 

SQL Server DMVs and DMFs categorised to make it easier to find information.

Have you wanted to find a DMV which would help give information about AlwaysOn Availability Group ,           Change Data Capture , Change Tracking ,Common Language Runtime , Database , Database Mirroring , Execution , Extended Events , Filestream, Full-Text Search and Semantic Search ,        I/O , Indexes , Object s, Query Notifications , Replication , Resource Governor , Security , Service Broker , SQL Server Operating System and Transactions.

Well I have tried to categorise them and have put them into their relevant category.

AlwaysOn Availability Group sys.dm_hadr_auto_page_repair
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_cluster_nodes
sys.dm_hadr_availability_replica_cluster_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_cluster
sys.dm_hadr_cluster_members
sys.dm_hadr_cluster_networks
sys.dm_hadr_database_replica_cluster_states
sys.dm_hadr_database_replica_states
sys.dm_hadr_instance_node_map
sys.dm_hadr_name_id_map
sys.dm_tcp_listener_states
Change Data Capture sys.dm_cdc_errors
sys.dm_cdc_log_scan_sessions
sys.dm_repl_traninfo
Change Tracking sys.dm_tran_commit_table .
Common Language Runtime sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring sys.dm_db_mirroring_auto_page_repair
sys.dm_db_mirroring_connections
Database sys.dm_db_file_space_usage
sys.dm_db_fts_index_physical_stats
sys.dm_db_partition_stats
sys.dm_db_persisted_sku_features
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
sys.dm_db_uncontained_entities
Execution sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plan_dependent_objects
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_describe_first_result_set
sys.dm_exec_describe_first_result_set_for_object
sys.dm_exec_plan_attributes
sys.dm_exec_procedure_stats
sys.dm_exec_query_memory_grants
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_resource_semaphores
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_exec_text_query_plan
sys.dm_exec_trigger_stats
sys.dm_exec_xml_handles
Extended Events sys.dm_xe_map_values
sys.dm_xe_object_columns
sys.dm_xe_objects
sys.dm_xe_packages
sys.dm_xe_session_event_actions
sys.dm_xe_session_events
sys.dm_xe_session_object_columns
sys.dm_xe_session_targets
sys.dm_xe_sessions
Filestream and FileTable sys.dm_filestream_file_io_handles
sys.dm_filestream_file_io_requests
Full-Text Search and Semantic Search sys.dm_fts_active_catalogs
sys.dm_fts_fdhosts
sys.dm_fts_index_keywords
sys.dm_fts_index_keywords_by_document
sys.dm_fts_index_keywords_by_property
sys.dm_fts_index_population
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
sys.dm_fts_outstanding_batches
sys.dm_fts_parser
sys.dm_fts_population_ranges
Index sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
I/O sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Object sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
sys.dm_db_stats_properties
Query Notifications sys.dm_qn_subscriptions
Replication sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Resource Governor sys.dm_resource_governor_configuration
sys.dm_resource_governor_resource_pools
sys.dm_resource_governor_workload_groups
Security sys.dm_audit_actions
sys.dm_audit_class_type_map
sys.dm_cryptographic_provider_algorithms
sys.dm_cryptographic_provider_keys
sys.dm_cryptographic_provider_properties
sys.dm_cryptographic_provider_sessions
sys.dm_database_encryption_keys
sys.dm_server_audit_status
Service Broker sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Server Operating System sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_dispatcher_pools
sys.dm_os_hosts
sys.dm_os_latch_stats
sys.dm_os_loaded_modules
sys.dm_os_memory_brokers
sys.dm_os_memory_cache_clock_hands
sys.dm_os_memory_cache_counters
sys.dm_os_memory_cache_entries
sys.dm_os_memory_cache_hash_tables
sys.dm_os_memory_clerks
sys.dm_os_memory_nodes
sys.dm_os_memory_objects
sys.dm_os_memory_pools
sys.dm_os_nodes
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_stacks
sys.dm_os_sys_info
sys.dm_os_sys_memory
sys.dm_os_tasks
sys.dm_os_threads
sys.dm_os_virtual_address_dump
sys.dm_os_volume_stats
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_os_windows_info
sys.dm_os_workers
Transaction sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

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.

</p>
<p>SELECT * FROM SYS.ALL_OBJECTS</p>
<p>WHERE [name] LIKE '%dm_%'</p>
<p>AND [type] IN ('V', 'TF', 'IF')</p>
<p>ORDER BY [name]</p>
<p>

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.

Easily Check Database Integrity (DBCC CHECKDB) For All Databases On A Server

Today I was doing my daily checks on one of my biggest SQL instances which has over 1,000 databases and noticed that SQL Agent was stopped. I couldn’t see any errors and was able to simply start it without any problems but I was concerned that the daily integrity check jobs had not run and I am way too cautious to get caught by not doing it manually.

Initially I wasn’t  too sure how to do it very quickly without creating a script using DBCC CHECKDB, querying sys.databases  with some dynamic T-SQL but decided to give the below script a try and luckily it worked.

 EXEC sp_MSforeachDB ‘DBCC CHECKDB (?)’

What are the SQL Server System databases?

SQL Server System databases

SQL Server has 5 very default system databases which are

  1. Master
  2. MSDB
  3. Model
  4. TempDB
  5. Resource

Master

As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.

MSDB

The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.

Model

This is basically a template database for the creation of new databases.

TempDB

TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..

Resource

This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.