Home » 2012 » October

Monthly Archives: October 2012

SQL Server 2012 CU4 Is Now Available !


The 4thcumulative update release for SQL Server 2012 is now available for download at the Microsoft Support site. Cumulative Update 4 contains all the SQL Server 2012 hotfixes which have been available since the initial release of SQL Server 2012. This update contains hotfixes for issues that were fixed after the release of SQL Server 2012.

Those who are facing severe issues with their environment, they can plan to test CU4 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

Cumulative update package 4 for SQL Server 2012 is available:http://support.microsoft.com/kb/2758687

Multilanguage Table, How to insert different language text in table ?


Issue :

Today, I received one request from my old friend.

He wants to insert text in different languages in table. He is looking to create Multilanguage table.

Solution :

The simplest solution is to insert data in table as NVARCHAR data type, like below.

–Create Table

CREATE TABLE TBL_LANG

(

LNAME VARCHAR(50),

LTXT NVARCHAR(100)

)

–Insert “Hello World” in diffrent language

INSERT INTO TBL_LANG

VALUES (‘English’,N’Hello World’)

INSERT INTO TBL_LANG

VALUES (‘Hindi’,N’हैलो दुनिया’)

INSERT INTO TBL_LANG

VALUES (‘Chines’,N’你好世界’)

INSERT INTO TBL_LANG

VALUES (‘Urdu’,N’ہیلو دنیا’)

–View Table data

SELECT * FROM TBL_LANG

IMPORTANT MESSAGE || http://MSSQLFUN.WORDPRESS.COM is now http://MSSQLFUN.COM


Dear Friends

Firstly, I want to say thanks for your support & liking my blogs that make it possible.

I like to share this good news that http://MSSQLFUN.WORDPRESS.COM is now http://MSSQLFUN.COM.

Website Link – http://MSSQLFUN.COM

RSS Feed – http://mssqlfun.com/feed

Note : Old links will keep working, no change required.

Thanks

Rohit Garg

How to move the FullText catalog with SQL Server 2005 ?


4 simple steps to move FullText catalog with SQL Server 2005 :-

1. Detach the database which contains the FullText Catalog. While detaching from Management Studio, there is “check box” called “Keep FullText Catalogs”. Make sure that this is selected.

2. Move the catalog files to the location where you want them.

3. Attach the database from Management Studio and SSMS will try to find the FTS catalogs

a. This step will fail and it will prompt you to find the location of FTS catalog. Click “Yes”

b. Choose the FTS Catalog path and Click “OK” (You may have more than 1 catalog file that you need to select one by one)

4. You have done, check database is working fine or not

SQL Server 2012 SP1 CTP4 Is Now Available !


Service packs are very critical and important. It is very important from product upgrade & bug fixing point of view.

Microsoft has recently released SQL Server 2012 SP1 CTP4. CTP stands for Community Technology Preview and it is not the final version yet. The SQL Server 2012 SP1 CTP release is available for testing purposes and use on non-production environments.

Please wait for SP1 final release to deploy on your production environment.

The following highlights a few key enhancements in CTP4:

  • Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade
  • Selective XML Index
  • DBCC SHOW_STATISTICS works with SELECT permission
  • New function returns statistics properties
  • SSMS Complete in Express
  • Slipstream Full Installation
  • New Management Object Support Added for Resource Governor DDL

SQL Server 2012 SP1 CTP4: http://go.microsoft.com/fwlink/?LinkID=257796

SQL Server 2012 SP1 CTP4 Express: http://go.microsoft.com/fwlink/?LinkID=257783

SQL Server 2012 SP1 CTP4 Feature Pack: http://go.microsoft.com/fwlink/?LinkID=263965

Detailed list of improvements and fixes : http://msdn.microsoft.com/en-us/library/bb500435

SQL Server 2012 CU3 Is Now Available !


The 3rd cumulative update release for SQL Server 2012 is now available for download at the Microsoft Support site. Cumulative Update 3 contains all the SQL Server 2012 hotfixes which have been available since the initial release of SQL Server 2012. This update contains hotfixes for issues that were fixed after the release of SQL Server 2012. This build number of this cumulative update package is 11.0.2332.0

Those who are facing severe issues with their environment, they can plan to test CU3 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

Cumulative update package 3 for SQL Server 2012 is available:http://support.microsoft.com/kb/2723749

How to avoid windows REBOOT after any operation required restart ?


Issue :

Today, I decided to clear my desktop. I am currently running with around 10 SQL server instances with different names & versions, that I have created for different purpose & not required now.

I start uninstallation of SQL server but after one uninstallation, second uninstallation gives errors of “Restart Computer”.

It’s very difficult to restart computer again & again when you are doing several things in parallel.

Resolution :

Here, I go for shortcut. I have changed the registry value of current session manager to avoid reboot.

Go to Registry Location : HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSessionManager & Clear the entry of string PendingFileRenameOperations.

After change, I click on rerun to check status again for uninstallation & it works.

Note :

1) Its recommended to reboot system, whenever required.

2) Please be careful, while doing any changes to registry.

Is Instance name SQLEXPRESS is fixed for Express edition only ?


A Question “SQLEXPRESS instance name is fixed for Express edition only ?” is running in my mind. SO I have decide to test it.

First I have tried to install SQL server Evolution Enterprise edition with instance name “SQLEXPRESS” & it worked.

Now I tried to install SQL Server Express edition & It allows me install Express edition with different edition name.

You can see that I am having SQLEXPRESS instance name with Enterprise Evolution edition & SQLEXPRESS2008 (different name) instance with Express edition.

But it’s good to use default things & install Express edition with SQLEXPRESS instance name. It’s easy to understand.

ReFS || next generation file system for Windows


Q) Why is it named ReFS?

ReFS stands for Resilient File System. Although it is designed to be better in many dimensions, resiliency stands out as one of its most prominent features. It will come with Windows 8.

Q) What are the capacity limits of ReFS?

The table below shows the capacity limits of the on-disk format. Other concerns may determine some practical limits, such as the system configuration (for example, the amount of memory), limits set by various system components, as well as time taken to populate data sets, backup times, etc.

Attribute Limit based on the on-disk format
Maximum size of a single file 2^64-1 bytes
Maximum size of a single volume Format supports 2^78 bytes with 16KB cluster size (2^64 * 16 * 2^10). Windows stack addressing allows 2^64 bytes
Maximum number of files in a directory 2^64
Maximum number of directories in a volume 2^64
Maximum file name length 32K 255 unicode characters (for compatibility this was made consistent with NTFS for the RTM product)
Maximum path length 32K
Maximum size of any storage pool 4 PB
Maximum number of storage pools in a system No limit
Maximum number of spaces in a storage pool No limit


Q) Can I convert data between NTFS and ReFS?

In Windows 8 there is no way to convert data in place. Data can be copied. This was an intentional design decision given the size of data sets that we see today and how impractical it would be to do this conversion in place, in addition to the likely change in architected approach before and after conversion.

Q) Can I boot from ReFS in Windows Server 8?

No, this is not implemented or supported.

Q) Can ReFS be used on removable media or drives?

No, this is not implemented or supported.

Q) What semantics or features of NTFS are no longer supported on ReFS?

The NTFS features we have chosen to not support in ReFS are: named streams, object IDs, short names, compression, file level encryption (EFS), user data transactions, sparse, hard-links, extended attributes, and quotas.

Q) What about parity spaces and ReFS?

ReFS is supported on the fault resiliency options provided by Storage Spaces. In Windows Server 8, automatic data correction is implemented for mirrored spaces only.

Q) Is clustering supported?

Failover clustering is supported, whereby individual volumes can failover across machines. In addition, shared storage pools in a cluster are supported.

Q) What about RAID? How do I use ReFS capabilities of striping, mirroring, or other forms of RAID? Does ReFS deliver the read performance needed for video, for example?

ReFS leverages the data redundancy capabilities of Storage Spaces, which include striped mirrors and parity. The read performance of ReFS is expected to be similar to that of NTFS, with which it shares a lot of the relevant code. It will be great at streaming data.

Q) How come ReFS does not have deduplication, second level caching between DRAM & storage, and writable snapshots?

ReFS does not itself offer deduplication. One side effect of its familiar, pluggable, file system architecture is that other deduplication products will be able to plug into ReFS the same way they do with NTFS.

ReFS does not explicitly implement a second-level cache, but customers can use third-party solutions for this.

ReFS and VSS work together to provide snapshots in a manner consistent with NTFS in Windows environments. For now, they don’t support writable snapshots or snapshots larger than 64TB.

Complete Post : http://blogs.msdn.com/b/b8/archive/2012/01/16/building-the-next-generation-file-system-for-windows-refs.aspx

SQL Server 2000 || DBCC CHECKDB logs entried in T-log file


Issue : When running DBCC CHECKDB on SQL server 2000, We are facing t-log growth & blocking.

Cause : DBCC CHECKDB does block in 2000 and earlier. The code for DBCC in 2000 was complex since it was working against a moving target (the database being modified), so to have a consistent database to check, DBCC used the transaction log to keep track of what modifications was performed while DBCC was running. Due to this blocking cause.

DBCC CHECKDB was completely re-written in 2005. It now uses a database snapshot, hence no need to block. Thanks to Pual & Team.

Follow

Get every new post delivered to your Inbox.

Join 130 other followers