Multi Threading FTP client capable of resuming an interrupted download

July 13, 2013

I recently coded a .net console application that is aimed to automatically download backup files from one data center to another. I enjoyed this small project and since I did experience a few difficulties I thought pointing out some points would help others. I faced 2 major issues: 1. Even though my program was opening several threads I could only see 2 files being downloaded at a time It was important for me to have several files being simultaneous downloaded since we have a download limitation at the session level so in order...
tags: , ,
no comments

FOREIGN KEY CONSTRAINT defined with ON DELETE CASCADE introduce Key Range locks leading to deadlocks

November 7, 2012

I was troubleshooting a severe deadlock(s) case at the lab of a customer involving quite a few processes with lots of code… not one of the simple cases, and what caught my eye was the fact that the deadlock details printed at the ERRORLOG showed Range Key locks such as RangeS-U and RangeI-N while the isolation level in effect was read committed for all processes involved. Since the isolation level in effect was not Serializable (where you typically expect to see range locks) I immediately looked for triggers and indexed views that introduce range locks but none existed....
3 comments

Kill all open transactions using the DBCC OPENTRAN command

July 14, 2012

  Recently (last weekend ) I run into a situation where the transaction log of database ReportServerTempDB grew far beyond it’s regular size. When looking at the log_reuse_wait_desc column of sys.databases it indicated ACTIVE_TRANSACTION so I run a DBCC OPENTRAN command just to find out that the oldest open transaction dates 2 days back. I killed the spid returned from DBCC OPENTRAN and still there was an active transaction from the same time period. So after having to issue the DBCC OPENTRAN command several times followed by a kill command I took a few moments to...
tags: , ,
no comments

Get the machine uptime in T-SQL

April 29, 2012

  Here is how to get the operating system uptime by querying the sys.dm_os_sys_info DMV. To get the SQL Server service uptime checkout this post SELECT DATEADD(s,((-1)*(/1000)), CURRENT_TIMESTAMP) AS machine_start_time ,CAST(/1000/60/60.0 AS DECIMAL(15,2)) AS diff_hh ,CAST(/1000/60/60/24.0 AS DECIMAL(15,2)) ...
tags: , ,
one comment

Get SQL Server last restart time and uptime

It is sometimes needed to get the sql server uptime, for example you may want to deploy a policy that restarts the service or machine after a specific uptime or because you relay on DMVs for monitoring and reports and since the information they store is cleared when the sql server service is restarted you would like to be aware of such an event and calculate the diff etc.. There are a few ways to get the time that the sql server service was last started and I show here the ways that I am familiar with.  Needless...
no comments

Use the Missing Indexes Feature to optimize and tune sql queries

April 28, 2012

The the Missing Indexes Feature is a great tool that can and should be used by DBAs as it provides useful information out of the box at no additional cost. It has it’s cons but overall this is a great feature. See the bellow link about the feature’s limitations. Here is a simple query that uses 3 out of the 4 DMVs that make up the database representation of the Missing Indexes Feature to show up information about missing indexes in the current db and construct the CREATE INDEX command. Some points to...
one comment

Perform a count comparison on replicated tables between the publisher and subscriber– enhanced

April 23, 2012

Here is an improvement to a code posted in an older post Perform a count comparison on replicated tables between the publisher and subscriber. The code introduced in this post also generates (prints) the INSERT commands required to close the diff/gap between the subscriber and publisher. The script is designed to be executed on the subscriber and you will have to edit the 2 parameters: @LinkedServer and @database which represent the linked server to be used for accessing the publisher and the published database. ...
no comments

Get the server wide configuration options that were modified using sp_configure

When working with SQL Server you sometimes have the need to know what server configuration options were modified. The bellow simple script does just that returning 2 result set; the first shows the option that was modified including the default value and the current value being in use and the second result set is a print out of the constructed command. Download the code here -- Yaniv Etrogi 20100628 -- Get all the server configuration options that were modified from the default...
no comments

update to sp_helpindex2

I have added the following 2 useful columns to the output:  reserved_mb  and compression derived from sys.partitions and sys.allocation_units respectively showing the index size and if it is compressed (PAGE, ROW or NONE). Go a head and download the latest version.
one comment

Perform a count comparison on replicated tables between the publisher and subscriber

February 11, 2012

Here is useful script to be run on the subscriber and get the diff in row counts between the publisher and subscriber for tables participating in Transactional or P2P replication (Merge replication will require a quick modification for tables i.e.:  sysarticles --> sysmergearticles and syspublications --> sysmergepublications).       /* Run this script on a subscriber of transactional or p2p replication The script accesses...
no comments