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.

How to Find Port Number Of a SQL Server Instance?

1) Using the GUI:
For SQL Server 2000, we can use SQL Server Network Utility, as below screen shot show how to check. 

For SQL server 2005 and 2008, we can use SQL  Server Configuration Manager as below.

2)  Using SQL Server Error log:  We can see some entry in the startup time after creating tempdb, as like in the below screen.

3)  Registry Entry: 

HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

 As below screen, you need run  regedit  in command line (be careful  while running regedit if you modify anything in the registry that will cause system failure).  If you are running multiple named instance then you have to select the named instance in regedit.

4) Using Extended Stored Procedure “xp_regread”: we can  find out the value of the TcpPort by using SQL.  

Example:
DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT
select @tcp_port

5)  Using “netstat –an”:  we can see all the ports listening on that server, in the below screen it shows 1433 which is SQL server port and 2383 is SSAS port  80 is SSRS port.  We can also use other tools like Microsoft  net monitor, network sniffer or any other third part tools.