Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.
- Termination of launcher session: Either the launcher session get cancelled or killed, running asynchronous workers should be cancelled.
- Different ways to shut down a batch: Waiting Workers should be abandoned. Executing workers should be either cancelled or waited to be completed.
- Effective monitoring: People want to see which session is running what.
- Adjustable maximum threads in the course of execution.
Continue reading “Run T-SQL in Parallel” →
I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that
sys.sql_expression_dependencies can tell. At the end of the day, I figured that depending on the complexity of the procedure, those 2 views couldn’t always give me accurate information as needed, dynamic SQLs for instance. Even worse, my customer also asked me if a table was accessed by anyone and what columns were referenced. I realized that I have to write something to get it done.
Continue reading “Get All Referenced Tables and Columns” →
Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time
declare @line nvarchar(max) = Replicate(cast('0123456789' as varchar(max)), 800)+'abc
select @line = replicate(@line, 3)
declare @pos int = 1
while @pos <= len(@line)
print substring(@line, @pos, 8000)
select @pos = @pos + 8000
Continue reading “Print Long String” →
Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256.
It does not seem very significant to our life. But it gives you surprise when this function is used for a calculated column where the value of it is persisted in the index. The indexes containing this column will be disabled automatically after restoring an SQL Server 2008 database to SQL Server 2012. Here is the result
RESTORE DATABASE successfully processed 0 pages in 2.094 seconds (0.000 MB/sec).
Warning: The index "PK_blablaView" on "blaSchema"."blablaView" was disabled because the implementation of "soundex" have changed.
Warning: Clustered index 'PK_blaView' on view 'blaView' referencing table 'blaTable' was disabled as a result of disabling the index 'PK__blaTable'.
Warning: The index "PK__blaTable" on "blaSchema"."blaTable" was disabled because the implementation of "soundex" have changed.
The solution is to rebuild indexes after restore.
It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which is restored 5 minutes. The user account the ETL is using has very limited right on Sybase Server. The Sybase DBA does not have SQL Server driver on the Unix system. This means that I can’t put log restore as part of my ETL and Sybase DBA does not have connectivity to SQL Server side. We need to coordinate those 2 processes that when one is running the other must not run.
Continue reading “Create Semaphore in Sybase” →
Both system functions, Len() and DataLength(), in SQL Server are used to measure the length of the data. The main difference of those 2 is that Len() gets the string length of the data in which DataLength measures the storage length of the data.
Continue reading “Len() and DataLength()” →
I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…
Continue reading “Protect Yourself from Using DBCC WritePage” →
In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back online. In either situation, you will need to modify/correct SQL Server metadata. This is allowed in SQL Server 2000 with “Allow ad hoc update” option turned on. However, it’s deprecated in SQL Server 2005 and removed in SQL Server 2008 and upper versions.
Continue reading “Bring Offline File Online — Modify SQL Server Metadata” →
I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They created a new file group with files to host some temporary data for data fixing or testing. New files were not created under the folder as usual and they thought they would remove them right after tasks complete. But the latter part got forgotten afterwards.
Continue reading “Attach Database with Missing NDF File” →
A checksum value is tagged with every data page in SQL Server. This is used to detect issues on the storage system to ensure what has been read from the disk is what has been written to the disk. When the checksum is detected which is not the one it’s supposed to be, the page will not be read into the buffer pool. SQL Server will return error. If you are sure the data on the page are all correct or the checksum is broken purposely, you do have the option to fix it even you don’t know how to calculate it.
Continue reading “Fix Page Checksum” →