Misc « Lohn Solutions

100 Posts

Since April 9, 2007, I’ve posted 100 times now. I’m a numbers guy, so here’s what it breaks down to…

Declare @startdate date
Set @startdate = '4/9/2007'

Select DATEDIFF(Day, @startdate, getdate())/100.0

11.38 days per post – much smaller number than I thought it would be. In the future, I’d like to get that number down to 2.5 or [...]

Sweet Sweet Bacon – Sunflower Seeds?

Is there anything bacon doesn’t make better?

Here’s the info on the seeds I’m currently eating – kind of tastes like McCormick’s BBQ seasoning.

The easy way to find deadlocks

A quick, simple and “easy” way to find deadlocks I’ve found is to do the following:

Turn on the trace flag 1222
Wait for a deadlock to happen
Open query analyzer and run the following script:

Create Table #ErrorLogTable
(
LogDate DATETIME NOT NULL,
ProcessInfo VARCHAR(75),
LogInfo VARCHAR(MAX)
)
SET NOCOUNT ON
INSERT INTO #ErrorLogTable
EXEC xp_readerrorlog
Select
*
– , Left(right(loginfo, Len(loginfo) – 20), charindex(' ',(right(loginfo, Len(loginfo) – 20))))
from [...]

What Do I Do?

I’ve got to give a presentation on my role at Medtronic, so here it is:

XML Data via Service Broker

I’ve been working on some development work. This involves service broker messages and XML data. We wanted the XML data in basically the same format as the table, but with an additional root tag on it. To automate testing, I started digging into how to return XML via TSQL. The FOR XML AUTO, ELEMENTS option [...]

Script for Generating Random Data

I often need to fill up tables with random data to test data conversion scripts, foreign keys, indexes, etc. Here’s my “Go To” script for coming up with random data. I usually base it off this and change the name of the table to insert data into, but this has the things I [...]

SQL and Twitter

One of my goals for 2010 is to get more involved in the SQL server community. A great way to do this is to use twitter. I’ve been using twitter since January of 2009 – you can follow me here. I’ve had my high usage times (where I check it and post to it all [...]

Table Size

–This will give you the amount of disk space that each table uses in a database.

SET NOCOUNT ON
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit

CREATE TABLE #TempTable
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
INSERT INTO #TempTable EXEC(@cmdstr)

–Use one of these depending what you are looking for.
–SELECT * FROM #TempTable ORDER BY Table_Name
SELECT * FROM [...]

The Real Challenge I’m Currently Working On

I’m currently training for the Des Moines Marathon on 10/18/09 and raising money for the American Cancer Society.

The details, along with some fun stuff can be found here:

www.andylohn.com

Challenges I’m Currently Facing

Seven base SQL Servers used for the web application:

3 – Clustered, SQL 2005 SP3 Enterprise edition
2 – Clustered, SQL 2005 SP3 Standard edition
2 – Stand Alone, SQL 2005 SP3 Standard edition

Three Log Shipping SQL servers:

1 – Clustered, SQL 2005 SP3 Enterprise edition
2 – Stand Alone, SQL 2005 SP3 Standard edition

One Utility Clustered Standard edition:

1 – [...]

Lohn Solutions

Categories

Archives

Blogroll