Archives: November 2011
Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk. The purpose of asynchronous IO is to release resources and for other tasks to progress.
i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can… Read more
Bulk loading with large amounts of data requires strategy. The Data Loading Performance Guide for SQL Server 2008 and SQL Server 2005 is a very useful document.
Understanding Minimally Logged Operations
Trace Flag 610 (sql server 2008)
Summarizing Minimal Logging Conditions
Bulk Load Methods
Integration Services Data Destinations Read more
A sql reindex command was being blocked by two active transactions in MS DTC. Use
sys.dm_tran_active_transactions to report more detail on transactions
select * from sys.dm_tran_active_transactions where transaction_uow = '922D5B60-A5CA-4C96-8891-703021B40AD8' /*Returns: Transaction_id = “1770489134” Name = “DTCXact” Transaction_begin_time =”2011-10-29 11:01:39.517” Transaction_type = “4” Transaction_uow=”922D5B60-A5CA-4C96-8891-703021B40AD8” Transaction_state=”2” Transaction_status=”12” Transaction_status2…
Developers like to have some control over the development process. An example is index building. Asking a DBA repeatedly for changes to a Development Database might not be the best use of time – for either parties.
For example , a developer may want to work over the weekend to… Read more
The dynamic management view (DMV) sys.dm_exec_requests returns information about each request that is executing within SQL Server.
Instead of using Activity Monitor to view the status of a Rollback , use this T-SQL code.
Activity monitor is based on window panes and is cumbersome to export any… Read more
Quotename() is a SQL Server String function.
It accepts an input string of of up to 128 characters. Which is a limitation , although you could easily create a workaround with a UDF
Returns a Unicode string of a valid delimited identifier.
Why would you use Quotename()?
1) … Read more
Torn Pages \ Corrupt pages – all in a days work for a DBA. But can anyone tell me how to use DBCC CHECKDB and the steps to take if a corrupt page is discovered?
This is a typical question I’ll ask at an interview for a DBA. In most… Read more
DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist will stimulate some ideas you could use
For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist
1) Same… Read more