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

Archives: December 2011

SQLServerDBA News - 2011 Review for SQLServerDBA

2011 was a great year on a personal and professional level

1)       Completed the Brighton Marathon in 4hrs 36 secs

2)       Blogged on SQLServerDBA.com and DBA-DB2.com frequently

3)       More time spent on Database Architecture projects

4)       Read more books for recreation

5)       My favourite SQLServer-DBA.com posts this year were:

JOIN… Read more

0 comments, 1,383 reads

Posted in SQLServer-DBA on 31 December 2011

Routing OSPF and Network Team

After a server move to the failover Data Centre – a file copy between the two sites slowed down by 6x.  I ran the copy a few times to confirm the slow down.

The file is part of a SQL Server ETL process. The ETL process transforms the data and… Read more

0 comments, 621 reads

Posted in SQLServer-DBA on 21 December 2011

Nic Teaming

Network card teaming or NIC teaming are networking terms describing methods of combining multiple parallel network connections . The main purposes are 1) increasing throughput beyond a single connection and 2)  redundancy.

 The three main teaming configurations

 Network Fault Tolerance (NFT)

1)  Two – Eight Physical NICS are teamed… Read more

0 comments, 710 reads

Posted in SQLServer-DBA on 14 December 2011

CPU Count

I document the CPU Count for SQL Server inventory management.

Using a powershell script , iterate through all the SQL Servers and capture the CPU count with the following t-sql.

----Returns the logical cpu count on server 
SELECT cpu_count FROM  sys.dm_os_sys_info

----Ratio of the number of logical or physical cores…

Read more

0 comments, 880 reads

Posted in SQLServer-DBA on 13 December 2011

SQL Traces and c2 auditing are different

Sometimes end users interchange the terms : C2 auditing and Traces.
They server different purposes.

c2 auditing records all attempts (successfull and failed) on objects and statements. Typically a SQL Server DBA will use a c2 audit to monitor security compliance .

A trace will will monitor an event or… Read more

0 comments, 774 reads

Posted in SQLServer-DBA on 12 December 2011

SQL Database Status with sys.databases

I was creating a Powershell script for a SQL Server checkout procedure after a reboot , and used  the state_desc column in sys.databases view to report on the database status.

The descriptions , courtesy of BOL are:

ONLINE  - Database is available for access

RESTORING – One or more… Read more

0 comments, 771 reads

Posted in SQLServer-DBA on 9 December 2011

SQL Server Latency

Latency is the time delay measure in the system.  SQL Server  can only be as fast as the slowest component in the system.

Database servers are particularly sensitive to disk IO speeds. For example, introducing an SVC system may speed up writes , but will add a cost to Reads. Read more

0 comments, 1,690 reads

Posted in SQLServer-DBA on 7 December 2011

DATEADD and variables in SQL

To make the T-SQL  function DATEADD more dynamic use variables. The example below , accepts an input parameter , in this case “4”.

All events older than 4 months from now will be deleted.


DECLARE @months int
SET @months = 4

DELETE FROM [Events] WHERE [EventDate] < DATEADD (month,-1*@months,…

Read more

0 comments, 1,336 reads

Posted in SQLServer-DBA on 6 December 2011

Enable XA transactions for SQL Server

An XA transaction is a  global transaction usually covering multiple resources.

Use  the  Microsoft SQL Server JDBC Driver to support the   Java Platform based distributed transactions

Steps to enable XA transactions

1)  Enable MSDTC

On Windows 2003 > Start > Run > “dcomconfg” > Expand Component Services > Expand Computers… Read more

0 comments, 202,422 reads

Posted in SQLServer-DBA on 5 December 2011

System Reserved Partition and monitoring

On Windows 2008 R2  , a System Reserved Partition is created. It is 100 MB and acts as a preparatory step for implementing Bitlocker. The installation doesn’t install Bitlocker.  BitLocker is a disk encryption method  for full disk encryption.

The System Reserved Partition  can cause problems in some monitoring tools.… Read more

0 comments, 923 reads

Posted in SQLServer-DBA on 2 December 2011

SQL Table variable and temporary table

Table variables and Temporary tables  are used interchangeably – but they were designed for different purposes.

Temporary tables were designed for storage and  manipulation of temporal data.

Table variables were designed to return User Defined Function (UDF) datasets.

 Which type should be used ?

If I estimate the data sets… Read more

1 comments, 1,635 reads

Posted in SQLServer-DBA on 1 December 2011