John H

SQL Saturday #92 @Portland

by

SQL Saturday 92 Portland Oct 8, 2011 I am going to present “Multi-threading TSQL” in Portland Oct 8th, 2011, http://www.sqlsaturday.com/92/eventhome.aspx. This session will tell you how to use pure T-SQL to implement parallel SQL processing with complex ordering, such as how to start 2 threads at beginning, synchrinoze them then start another n threads for … Read more

SQL Server Transaction Log Buffers

by

Transaction log is a very important component in SQL Server to keep transaction durable even in an event of system failure. It’s generated while a modification taking place in most of data pages in the memory that eventually being written into data files within a database. Inserting, deleting and updating DML operations will generate transaction logs. DDL operations, such as alter table, create procedure, which cause changes to the system tables, will generate transaction logs as well. Transaction logs are generated by transactions. Any data modification to persistent data in the data files will be at least implicitly involved in a transaction. As far as I know, table variables are not transactional even though SQL Server actually writes the data in table variable into the data files of tempdb. (this is an reason why table variable is faster)

Read more

A Procedure for Creating Database Snapshot

by

Database snapshot is a great SQL Server built-in feature serving many situations for programmers and DBAs to quickly create a read only copy of a operational database. Sometime, creating a database snapshot is a daunting work especially when you have hundreds database files. Here I give you the code to generate database snapshot automatically with … Read more

Cold and Hot Backup

by

There are always a chance databases suffering from severe system failure and hence causes database corruption, for instance, memory failure, hard drive failure, power failure and so on. Once the database gets corrupted, it’s almost hopeless to recover it without backup. So it’s very important to backup the databases for any database systems. There are two big categories in backup world – cold backup and hot backup.

Read more

Deadlock in Pooled Connections

by

When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.

Read more