Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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, 3,606 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.

 Highlights

 

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, 594 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'

/*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…

Read more

0 comments, 715 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, 361 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, 486 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, 2,200 reads

Posted in SQLServer-DBA on 15 November 2011

DBCC CHECKDB

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, 553 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, 1,067 reads

Posted in SQLServer-DBA on 10 November 2011