Archive | SQLServerPedia Syndication RSS feed for this section

Visual SQL Joins

Came across a twitter post that made me remember how useful these things could be. For the visual learner it can go a long way in understanding how a join works. I’m linking to the one from the twitter post as well as.

For the visual learner:

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

http://www.codinghorror.com/blog/archives/000976.html

http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/

Comments { 1 } Posted on March 13, 2009 in Education, SQL General, SQLServerPedia Syndication

Execute SQL Task too big

I ran into an issue last night where copying selected text into the Execute SQL Task within SSIS would get truncated at a certain point. I really believe that I’ve successfully pasted larger text without an issue before, but I haven’t tested that theory as of yet.

One fix is to save the file as .sql and browse to it from within the Execute SQL task. Once I tried this I noticed that my last few lines were finally there. If you know of other ways around this please feel free to share.

Comments { 2 } Posted on March 10, 2009 in SQL Server 2005, SQLServerPedia Syndication, SSIS

SQL Server RSS Feeds

I came accross a blog entry by Jason Massie regarding SQL RSS feeds and I decided it would be a good time to check out the RSS Reader in Outlook 2007. Fortunately, Outlook will let you import a list of feeds from an xml or opml file. I took the list from Jason and plugged them into OPMLBuilder to get output I could save. I saved it as an .xml file imported it into the RSS Feeds for Outlook and it worked beautifully. I’ve included the file contents below in case you want to edit yours a bit and skip the OPMLBuilder.

Here are the contents of the xml file:

<opml version=”1.1″>

<head>

<title>
Generated by FeedShow OMPLBuilder: Wed, 25 Feb 2009 16:13:05 +0100
</title>
<dateCreated>Wed, 25 Feb 2009 16:13:05 +0100</dateCreated>
</head>

<body>

<outline text=”Main Folder”>
<outline title=”blog.sqlauthority.com/feed/” text=”blog.sqlauthority.com/feed/” type=”rss” xmlUrl=”http://blog.sqlauthority.com/feed/”/>
<outline title=”sqlblog.com/blogs/MainFeed.aspx” text=”sqlblog.com/blogs/MainFeed.aspx” type=”rss” xmlUrl=”http://sqlblog.com/blogs/MainFeed.aspx”/>
<outline title=”blogs.msdn.com/psssql/rss.xml” text=”blogs.msdn.com/psssql/rss.xml” type=”rss” xmlUrl=”http://blogs.msdn.com/psssql/rss.xml”/>
<outline title=”blogs.msdn.com/gertd/rss.xml” text=”blogs.msdn.com/gertd/rss.xml” type=”rss” xmlUrl=”http://blogs.msdn.com/gertd/rss.xml”/>
<outline title=”blogs.msdn.com/buckwoody/rss.xml” text=”blogs.msdn.com/buckwoody/rss.xml” type=”rss” xmlUrl=”http://blogs.msdn.com/buckwoody/rss.xml”/>
<outline title=”blogs.technet.com/dataplatforminsider/rss.xml” text=”blogs.technet.com/dataplatforminsider/rss.xml” type=”rss” xmlUrl=”http://blogs.technet.com/dataplatforminsider/rss.xml”/>
<outline title=”feeds.feedburner.com/SqlteamcomWeblogs” text=”feeds.feedburner.com/SqlteamcomWeblogs” type=”rss” xmlUrl=”http://feeds.feedburner.com/SqlteamcomWeblogs”/>
<outline title=”blogs.technet.com/andrew/rss.xml” text=”blogs.technet.com/andrew/rss.xml” type=”rss” xmlUrl=”http://blogs.technet.com/andrew/rss.xml”/>
<outline title=”www.microsoft.com/feeds/msdn/en-us/sql/sqldev_en_us.xml” text=”www.microsoft.com/feeds/msdn/en-us/sql/sqldev_en_us.xml” type=”rss” xmlUrl=”http://www.microsoft.com/feeds/msdn/en-us/sql/sqldev_en_us.xml”/>
<outline title=”feeds.feedburner.com/statisticsio” text=”feeds.feedburner.com/statisticsio” type=”rss” xmlUrl=”http://feeds.feedburner.com/statisticsio”/>
<outline title=”blogs.msdn.com/sqlsecurity/rss.xml” text=”blogs.msdn.com/sqlsecurity/rss.xml” type=”rss” xmlUrl=”http://blogs.msdn.com/sqlsecurity/rss.xml”/>
<outline title=”itknowledgeexchange.techtarget.com/sql-server/feed/” text=”itknowledgeexchange.techtarget.com/sql-server/feed/” type=”rss” xmlUrl=”http://itknowledgeexchange.techtarget.com/sql-server/feed/”/>
<outline title=”glennberrysqlperformance.spaces.live.com/feed.rss” text=”glennberrysqlperformance.spaces.live.com/feed.rss” type=”rss” xmlUrl=”http://glennberrysqlperformance.spaces.live.com/feed.rss”/>
<outline title=”blogs.msdn.com/sqlserverue/rss.xml” text=”blogs.msdn.com/sqlserverue/rss.xml” type=”rss” xmlUrl=”http://blogs.msdn.com/sqlserverue/rss.xml”/>
<outline title=”www.brentozar.com/feed/” text=”www.brentozar.com/feed/” type=”rss” xmlUrl=”http://www.brentozar.com/feed/”/>
<outline title=”feeds.feedburner.com/SqlInTheWild” text=”feeds.feedburner.com/SqlInTheWild” type=”rss” xmlUrl=”http://feeds.feedburner.com/SqlInTheWild”/>
<outline title=”blogs.lessthandot.com/index.php/All/?tempskin=_rss2″ text=”blogs.lessthandot.com/index.php/All/?tempskin=_rss2″ type=”rss” xmlUrl=”http://blogs.lessthandot.com/index.php/All/?tempskin=_rss2″/>
<outline title=”chrisshaw.wordpress.com/feed/” text=”chrisshaw.wordpress.com/feed/” type=”rss” xmlUrl=”http://chrisshaw.wordpress.com/feed/”/>
<outline title=”sqlbatman.com/feed/” text=”sqlbatman.com/feed/” type=”rss” xmlUrl=”http://sqlbatman.com/feed/”/>
<outline title=”sqlserverpedia.com/blog/?feed=rss2″ text=”sqlserverpedia.com/blog/?feed=rss2″ type=”rss” xmlUrl=”http://sqlserverpedia.com/blog/?feed=rss2″/>
<outline title=”www.sqlpass.org/DesktopModules/DnnForge%20-%20NewsArticles/Rss.aspx?TabID=75&ModuleID=494″ text=”www.sqlpass.org/DesktopModules/DnnForge%20-%20NewsArticles/Rss.aspx?TabID=75&ModuleID=494″ type=”rss” xmlUrl=”http://www.sqlpass.org/DesktopModules/DnnForge%20-%20NewsArticles/Rss.aspx?TabID=75&ModuleID=494″/>
<outline title=”blogs.msdn.com/search/Searchrss.aspx?q=sql&o=DateDescending” text=”blogs.msdn.com/search/Searchrss.aspx?q=sql&o=DateDescending” type=”rss” xmlUrl=”http://blogs.msdn.com/search/Searchrss.aspx?q=sql&o=DateDescending”/>
</outline>
</body>
</opml>

Comments { 0 } Posted on February 25, 2009 in Education, SQL General, SQLServerPedia Syndication

Tempdb needs a doctor

In the past, we’ve run into issues with contention and the -T1118 flag, hotfix 2040, and multiple tempdb files that led me to go ahead and put this info out there if nothing more than for my future reference.

I ran into a machine today that had the tempdb files in the wrong location. They were also the wrong size and there were not enough of them.

So that presents three problems that had to be solved. The first problem I decided to fix was to get rid of the current files that were sized inappropriately and in the wrong location. No need to double my work.

I’m sure it can just as easily be done by script, but I went into the GUI and deleted each tempdb data file that I didn’t want anymore for the time being. You will get an error message about deleting the file and restarting SQL Server. Once you’ve done that you are left with only one data file and one temp file, even though they may be in the wrong location and the wrong size.

Let’s go ahead and tackle the size issue first. After we’ve just restarted SQL Server lets do something like this:

dbcc shrinkfile (‘tempdev’,1024,TRUNCATEONLY)

Now it’s time to go ahead and move the files to the place we want them:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘I:\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘I:\templog.ldf’)
GO

Now it’s time to add the additional tempdb data files:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′,
FILENAME = N’I:\tempdb2.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′,
FILENAME = N’I:\tempdb3.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev4′,
FILENAME = N’I:\tempdb4.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev5′,
FILENAME = N’I:\tempdb5.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev6′,
FILENAME = N’I:\tempdb6.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev7′,
FILENAME = N’I:\tempdb7.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev8′,
FILENAME = N’I:\tempdb8.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO

This will create 8 files (for our # of processors) that are 1024MB in size and autogrow at 200MB. At this point it probably wouldn’t hurt to restart the SQL Server. We had a few small issues but I’m not sure they were related.

The links that helped me with this are as follows:
http://www.sqlteam.com/article/moving-the-tempdb-database
http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx
http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx
http://sql-server-performance.com/Community/forums/p/23813/135241.aspx
http://support.microsoft.com/kb/328551

Comments { 2 } Posted on February 18, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication