2007-04-27

Gaps in Identity Columns.

The recent tip at MSSQLtips.com covers just the topic in the heading.
But the problem solved in the body of the article is not actually the one stated. The resulting output from the script produces the list of all missing IDs. While being a decent academic exercise the result has not practical value not to mention is uses SQL 2005 features, which limits its application. With the same success somebody could use simply

...WHERE some_table.pkid NOT EXISTS IN(SELECT pkid FROM t1)

If we take a slightly different approach with temp tables, we can simplify things a lot and get all the information DBA may need for the analysis.
First, add all the ids to a temp table with IDENTITY column

CREATE TABLE #gaps
SELECT a.pkid, GapSize = pkid - case
FROM t1 a
WHERE NOT EXISTS(
SELECT * FROM t1 b
WHERE b.pkid = a.pkid + 1 )
AND a.pkid < (select max(pkid) from t1);

2007-02-20

SQL 2000 transaction log shrinking; crosswalk to SQL 2005.

Managing SQL Server 2000 Transaction Log Growth – detailed description what to do and what scripts to run to shrink transaction logs and ensure predictable growth in the future.
Also two articles on switching to MS SQL 2005 platform: The SQL Server Import and Export Wizard and DTS Package to SSIS Package Execution.
From MSSQLTips.com.

2007-02-14

SQL 2005 tablediff command line utility.

This utility allows you to compare the contents of one table against another to find any differences and get T-SQL script to sync them up, i.e. does what WinMerge does for your files. Tablediff article in books online provides all the details. In order to run this command you need to have primary keys setup on both tables, which makes sense.

2007-02-13

Sysinternals at Microsoft TechNet

The names Sysinternals and Mark Russinovich don't need any introduction, they are known and respected in the industry for over 10 years. What I did not know, is that Microsoft acquired the company back in July, 2006. Now the company's web content is hosted under Microsoft TechNet logo.
Benefits for Microsoft are obvious, I just hope it will work other way around as well.
While being under impression that tools from Sysinternals are targeted exclusively to Windows administrators and developers' community, I was pleasantly surprised by discovering ZoomIt , the tool I've been missing for all my technical presentations.
The introduction for the tool says it all:
ZoomIt is screen zoom and annotation tool for technical presentations that include application demonstrations. ZoomIt runs unobtrusively in the tray and activates with customizable hotkeys to zoom in on an area of the screen, move around while zoomed, and draw on the zoomed image. I wrote ZoomIt to fit my specific needs and use it in all my presentations.
ZoomIt works on all versions of Windows.
Another one may be an excellent start for some practical jokes on your co-workers: BlueScreen.
Enjoy!

2007-02-12

Determine size of MS SQL databases.

A couple scripts from MSSQLTips.com made of original scripts used by Enterprise Manager and Management Studio to determine space used for each table in the database. Under the hood sp_spaceused is used.