How to change database collation?

Sometimes your server collation may not support for some application specific collation so you may need to change the database collation.  You can use  sys.databases   system cat log to check the database collations.  The below alter command show how to change database collation. One important thing is the database should be in single user mode so that during this time no other transaction can occur against this database. This method is applicable only for user database. For master database you need rebuild system database by using setup which will be demonstrated in other blog with more details.

SELECT name, collation_name

FROM sys.databases

USE master;

GO

ALTER DATABASE [DBA_Admin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE [DBA_Admin]

COLLATE Latin1_General_CI_AS ;

GO

ALTER DATABASE [DBA_Admin] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

SELECT name, collation_name

FROM sys.databases

Backup failure due to insufficient disk space

While running backup of 4 TB database on Local C drive where available space is around 1.5 TB on SQL server 2008 R2 EE backup command failed with some error.  But the error did not depict the insufficient disk space.  After modifying the command with backup compression option  completed successfully.

BACKUP DATABASE [GoogleAnalytics]

TO DISK  = 'C:\GA-DB-Backup\GoogleAnalytics_06122012.BAK'

WITH INIT,STATS = 10

Error message

Msg 3271, Level 16, State 1, Line 1

A nonrecoverable I/O error occurred on file "C:\GA-DB-Backup\GoogleAnalytics_06122012.BAK:" 112(failed to retrieve text for this error. Reason: 15105).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

So you should not panic, first need to check disk space whether is sufficient to take backup of database based on the size.