SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Archives: November 2011

SQL Dirty Pages

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

2 comments, 10,417 reads

Posted in SQLServer-DBA on 28 November 2011

Data Loading Performance Guide

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

0 comments, 1,797 reads

Posted in SQLServer-DBA on 26 November 2011

MS DTC and sys.dm_tran_active_transactions

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'


Transaction_id = “1770489134”	
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”

Read more

0 comments, 1,905 reads

Posted in SQLServer-DBA on 24 November 2011

ALTER INDEX permissions

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

0 comments, 928 reads

Posted in SQLServer-DBA on 23 November 2011

Monitoring a Rollback and sys.dm_exec_requests

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

0 comments, 1,259 reads

Posted in SQLServer-DBA on 22 November 2011

Quotename in SQL

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

1 comments, 6,705 reads

Posted in SQLServer-DBA on 15 November 2011


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

1 comments, 1,017 reads

Posted in SQLServer-DBA on 14 November 2011

Stored Procedure Checklist

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

9 comments, 2,667 reads

Posted in SQLServer-DBA on 10 November 2011