BACKUP DATABASE [DAILY LEARNING] TO BLOG [QIUSHIYANG.COM]
It’s common nowaday that taking long time to run DBCC CHECK for TB level database. Now there is a way to consume more IO resource to run DBCC CHECK faster, by using flag 2562 and 2549. This is only availble for SQL 2008 R2 CU11 now.
My 2 cents, pay attention to tempdb usage when enabling these flags as the trade-off is tempdb size and io. And dont set them as startup parameter, just enable them in the session level when you are ready for the trade-off.
One of my customers was migrating their databases from one SQL 2005 instance to another one last night, more powerful hardware with same SQL Server version. They didn’t recover system database, just moved the login/user/job/linked servers and user databases to new instance. Then they tried to create transactional publication in the new instance via GUI wizard but failed. The same transactional publication was created in the previous instance without error.
The error message from GUI read: "Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 159 This database is not enabled for publication." And we tried to restore the database again with KEEP_REPLICATION option like what the error message help link told us to do. It failed again. Uhh, what I did next was to use GUI wizard to generate the creation scripts first and executed the script one by one to see which step failed. The error message I got then was different from GUI one, it told me that connecting to linked server repl_distributor time out. After confirming with customer, they created this repl_distributor linked server when moving the system objects. Unluckily this one is reserved from replication and it would be created by replication initialize process. I then used sp_dropdistributor @no_checks=1, @ignore_distributor=1 to remove it. After that we could create the new publication.
What it impresses me again is how to do the t-shoot to SQL Server problem. Error message from application is not enough, (yes, GUI wizard here is application which sending T-SQL to SQL Server), we should capture what sent to back end and run them it one by one to narrow down the problem.
There are 2 ways to do the conversion: in-place and side-by-side. In-place means alter the column type directly; side-by-side means create a new database with new schema, and then use bcp to export/import data. It’s hard to say which one is better, it depends. If most of the data in database will be converted, I will use side-by-side. If not, I will use in-place.
This is a post from SQLCAT discuss side-by-side conversion http://blogs.msdn.com/b/mssqlisv/archive/2006/07/07/659374.aspx, you can take it as good start of this topic. In my post, I will discuss how to convert char/nvarch/ntext columns to unicode type, which store Chinese charater using in-place method. To narrow the scope, I assume collation of the database is already Unicode ready like Chinese_Hong_Kong_Stroke_90, and the SQL Server is SQL 2005/2008 which have datatype nvarchar(max). And I will cover real-world scenarios: find out columns which store Chinese character, planning of extra storage, validate the converted result and handle constraint.
Last month I hit this issue on customer site: I was using Aggreagation Design Wizard to do the usage based optimization design, but the "Next" button in the "Specify Object Counts" page was grey even I pressed "Count" button.
What you need to do if you hit the same problem is: Take a look at the dimension list to see any dimension has red line under it, open it to see which attribute has red line under. Input whatever number to update the count of this attribute, then the under red line would disappear.. And the "Next" button can be pressed to continue now!
Hope this can save your time.