SQL Server Connection Leak Simulation Tool

Sometimes it’s necessary to test application performance in low available resource situations…  I’ve seen tools that simulate low available memory, do network throttling, etc., but I have not come across one that simulates a lot of SQL Server connections being in use…   Attached is such a tool.  I’m providing both, source code and the…

0

SYSK 360: What’s taking up the space in my database? Also, a word about SQL reports…

So, you’ve updated usage stats on your database by running   EXEC sp_spaceused @updateusage = N’TRUE’   But, you still can’t figure out why it’s reporting much more used space then the data that appears in your tables… Try running the following query to get number of rows and detailed space usage information for each…

3

SYSK 359: How to Purge Workflow Tracking Data Inserted By SqlTrackingService

To my great surprise, after doing a search for a script that would delete the events logged by the almighty SqlTrackingService service, I didn’t find one…  So, I quickly created it (see below), and it seems to do the job just fine given the current table schema implementation used by the service: IF OBJECT_ID(‘TrackingPartitionSetName’) IS…

1

SYSK 357: SQL Server Login Problem on Fresh Installation in Vista

I just installed SQL Server 2008 CTP (Katmai) and all the patches on my Vista laptop…  However, trying to login using SQL Management Studio or osql using integrated security (and, yes, I’m an admin on my box) results in the “login failed” error. To resolve the problem, I had to turn off the “User Control Account”…

2

SYSK 349: What’s New in SQL 2008 (code named Katmai)?

Here are some of the new and improved features (extracted from David Campbell’s presentation) that will be available in SQL 2008: –      Transparent data encryption –      External key management –      Data auditing –      Pluggable CPU –      Transparent failover for data mirroring –      Declarative management framework –      Server group management –      Streamlined installation –      Enterprise system…

0

SYSK 347: How-To Keep Historical/Audit Data Accurate

I’m sure you’ve heard this saying many times before – “the hard disk space is cheap now – save everything”… With that, many companies create paired (or buddy) tables for all transactional data, i.e. for every transactional table (i.e. not lookups, a.k.a. domain values) you’d create another one with same columns plus (usually) four more…

2

SYSK 345: How To Assure Correct LastUpdatedDateTime Value in Your SQL Tables

Often, developers add LastUpdatedDataTime (or LastUpdatedTimestamp) and LastUpdatedByUser columns to tables in relational databases.    And, as one might guess, these fields are often created to be not nullable with default values set by using SQL functions – getdate() and suser_name() – correspondingly.   The problem is that the default values only work on inserts,…

4

SYSK 338: What SQL Queries Are Currently Running?

That’s easy — just run the SQL statement below…   select session_id, request_id, start_time, status, cpu_time, er.total_elapsed_time,       substring(st.text,       (er.statement_start_offset/2) + 1,       ((case er.statement_end_offset when -1 then datalength(st.text) else er.statement_end_offset end       – er.statement_start_offset)/2) + 1) as query   from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(er.sql_handle) st    

1

SYSK 333: What Query Plans Are Cached in SQL Server?

It’s well known that, to improve performance, SQL Server caches query execution plans in memory.  But would you like to know what query execution plans are currently in the cache?    The following query will yield that information:   select substring(st.text,       (qs.statement_start_offset/2) + 1,       ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end…

0

SYSK 328: The “Hidden” System Database in SQL 2005

If you just open SQL Server Management Studio and expand the System Databases node, you’ll see four databases – master, model, msdb and tempdb (just like in SQL 2000).   However, SQL Server 2005 has a new system database called the ‘Resource’ database, which contains all the system objects (see select * from sys.objects), which…

0