posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Utilities

SQL Server Configuration Scripting Utility Release 9

There’s another update to my little utility to script a SQL Server’s configuration.  I use this for two purposes.  First, I use it to keep my database mirroring servers up to date.  Second, I capture the output in a version control system and keep that for historical reference. In release 3.0.9 I made the following changes: Rewrote the encrypted trigger scripting.  It will now list the encrypted triggers in a comment in the table script but can’t actually script them. It now scripts any server event notifications. You can...

posted @ Tuesday, October 09, 2012 5:59 PM | Feedback (0) | Filed Under [ Utilities ]

Another Update to SQL Server Configuration Scripting Utility

I’ve been gradually adding features to my utility that scripts the configuration of a SQL Server.  Since my last post I’ve added the following features: Skip any encrypted object in a database Script alerts, alert notifications and operators Script audits Always script model, master and msdb to capture any user-defined objects in those databases Logins are now scripted so that everything for a login is grouped together. There’s a second section in the logins that handles default databases.  In many cases...

posted @ Tuesday, July 10, 2012 4:37 PM | Feedback (0) | Filed Under [ Utilities ]

TraceTune shows Reads graphically

TraceTune now shows a graphical view of logical reads for each SQL statement in a trace file.  The width of the colored bar in the screen capture below is the percentage of logical reads for that statement.  The absolute number of reads is shown to the right. Any statement that has a user entered comment is shown in bold.  If you hover over the statement it will show the most recent comment for that statement.

posted @ Monday, June 27, 2011 9:00 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

TraceTune supports uploading Zip files

I’ve been using the online version of ClearTrace more and more lately.  When I get to a new client it’s just much easier to upload a trace file rather than install ClearTrace. That means I’ve finally been adding more features to it.  The two latest features are around ease of use. You can now upload a ZIP file that contains a trace file.  Trace files are already somewhat compressed.  Putting it in a ZIP file further compresses it by a factor of 8X or 9X in my testing. That means you can start with a 100MB trace...

posted @ Thursday, June 23, 2011 7:46 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

Utility to Script SQL Server Configuration

I wrote a small utility to script some key SQL Server configuration information. I had two goals for this utility: Assist with disaster recovery preparation Identify configuration changes I’ve released the application as open source through CodePlex. You can download it from CodePlex at the Script SQL Server Configuration project page. The application is a .NET 2.0 console application that uses SMO. It writes its output to a directory that you specify.  Disaster Planning ScriptSqlConfig generates scripts for logins, jobs and linked servers.  It writes the properties...

posted @ Monday, April 04, 2011 7:34 PM | Feedback (4) | Filed Under [ Utilities ]

Tracking "To Do" Items

One of the challenges I struggle with is keeping a good "to do" list of things I need to do on the various SQL Servers I support. I have servers that I don't visit on a regular basis so my situation may be different than many of you. Though I'm sure you all have servers that you only touch every few months. (And it's usually the accounting server!) It's difficult for me to remember what changes I made and what changes I need to make. I've tried Outlook, OneNote and various other to do list managers and haven't been...

posted @ Tuesday, February 22, 2011 7:30 AM | Feedback (2) | Filed Under [ Utilities ]

Generate MERGE statements from a table

We have a requirement to build a test environment where certain tables get reset from production every night.  These are mainly lookup tables.  I played around with all kinds of fancy solutions and finally settled on a series of MERGE statements.  And being lazy I didn’t want to write them myself.  The stored procedure below will generate a MERGE statement for the table you pass it.  If you have identity values it populates those properly.  You need to have primary keys on the table for the joins to be generated properly.  The only thing hard coded is the...

posted @ Tuesday, February 15, 2011 7:47 AM | Feedback (1) | Filed Under [ SQL Server Stuff Utilities ]

TraceTune: Improved Comment View

I wanted an easier way to identify queries I’d already looked at so I could skip them.  I’ve been entering comments for each query as I review it.  These comments typically fall into three categories: a change I made, no easy fix available or something needs to be changed on the client.  TraceTune now highlights any statement with a comment in bold.  If you hover over the statement you’ll see the most recent comment for that statement. This gives me a quick way to see what’s new and identify those queries that still need work. ...

posted @ Monday, February 14, 2011 7:40 AM | Feedback (0) | Filed Under [ Utilities ]

SQL Server Scripts I Use

When I get to a new client I usually find myself using the same set of scripts for maintenance and troubleshooting.  These are all drop in solutions for various maintenance issues. Reindexing.  I use Michelle Ufford’s (SQLFool) re-indexing script.  I like that it has a throttle and only re-indexes when needed.  She also has a variety of other interesting scripts on her blog too. Server Activity.  Adam Machanic is up to version 10 of sp_WhoIsActive.  It’s a great replacement for the sp_who* stored procedures and does so much more.  If a server is acting funny this is one...

posted @ Tuesday, February 08, 2011 6:45 PM | Feedback (4) | Filed Under [ Utilities ]

Enter comments on queries in TraceTune

I’m trying to make TraceTune (and eventually ClearTrace) work the way I do.  My typical query tuning session goes like this: Run a trace and upload to TraceTune/ClearTrace Tune the slowest queries Goto 1 I might do this two or three times in one day and then not come back to it again for weeks or even months.  This is especially true for those clients that I only visit a few times per month.  In many cases I’ll look at a query, decide I can’t...

posted @ Monday, February 07, 2011 7:48 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

TraceTune: Larger Files and History

I updated TraceTune over the weekend.  I increased the trace file upload size to 20MB.  We’ve processed over half a million rows of trace data so far and I’m confident this won’t kill the server. I added average CPU and average disk reads to the screen that lists the SQL statements in a trace file. I only added these two.  I’m pretty sure average writes isn’t that import.  I’m still thinking about average duration.  I’m trying to balance showing you what you need with a clean, simple interface.  Plus I have a way to see the...

posted @ Monday, January 24, 2011 7:59 AM | Feedback (0) | Filed Under [ Utilities ]

Script to UPDATE STATISTICS with time window

I recently spent some time troubleshooting odd query plans and came to the conclusion that we needed better statistics.  We’ve been running sp_updatestats but apparently it wasn’t sampling enough of the table to get us what we needed.  I have a pretty limited window at night where I can hammer the disks while this runs.  The script below just calls UPDATE STATITICS on all tables that “need” updating.  It defines need as any table whose statistics are older than the number of days you specify (30 by default).  It also has a throttle so it breaks out of...

posted @ Thursday, December 02, 2010 5:21 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Will you share your SQL Server configuration?

I regularly visit client sites and review their SQL Server configurations.  I come across all kinds of strange settings.  I’ve been thinking about a way to aggregate people’s configurations and see what’s common and what’s unique.  I used to do that with polls on SQLTeam.com.  I think we can find out more interesting things if we look at combinations of settings in relation to size and volume. I’ve been working on an application for another project that is similar.  It will be fairly easy to use that code for this.  I can have something up and running in a...

posted @ Monday, October 25, 2010 8:06 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Scripting out SQL Server Logins

I regularly move logins between servers.  Mostly this is between production and our DR site.  I’ve used the code in KB246133 many, many times but it’s pretty limited.  I started with that and wrote the script below. There is a user-defined function that’s created in master.  You can create it in any database you want but you’ll need to update the script.  The function converts varbinary hashed passwords to a string representation. It keeps the password intact for SQL Server logins. It scripts both Windows logins and SQL Server logins. ...

posted @ Thursday, July 08, 2010 11:09 AM | Feedback (2) | Filed Under [ SQL Server Stuff Utilities KCTechBlog Syndication ]

Playing with Google Voice

I put my name down for the Google Voice trial a few months back.  I recently received my invitation and configured my account.  The entire process was painless and it has some interesting benefits: I’m in the process of starting a new business and needed a work number.  At free, this is an attractively priced alternative.  I would like some way to have a fax number though.  Right now I’m signed up for a second service that does fax. Choosing a phone number was easy.  You tell it where you are...

posted @ Friday, July 24, 2009 6:33 AM | Feedback (0) | Filed Under [ Miscellaneous Utilities ]

Storing My Files in the Cloud

(Written on my snappy new Windows 7 RC installation.  Operating systems are so much faster before they get those pesky applications installed.) I’ve been gradually transitioning all my file storage and backup to online services, aka “The Cloud”.  I’m nearly done and thought I’d share what I’ve done in hopes of getting even better ideas or helping someone else out. Live Mesh My main working computer is a laptop.  I have a small server here but it’s only used for testing.  I have a few folders in “My Documents” where I store the bulk of my non-development...

posted @ Monday, May 04, 2009 7:17 AM | Feedback (3) | Filed Under [ Miscellaneous Utilities ]

ClearTrace now supports SQL Server 2005 *AND* SQL Server 2008

Or my alternate SEO-friendly title, “Dynamically load the proper SQL Server SMO Trace library in C#”.  I just posted a version of ClearTrace that will determine whether you have SQL Server 2005 or SQL Server 2008 installed and dynamically load the proper SMO library.  Previously I had one version of ClearTrace for each version of SQL Server (YUCK!).  I’m pretty happy with how this works even though I’m not very happy with SMO.  The method names are the same between the two versions of SMO but they aren’t both derived from the same interface.  That...

posted @ Friday, April 03, 2009 7:35 AM | Feedback (0) | Filed Under [ Utilities ClearTrace ]

ClearTrace updated to support SQL Server 2008 trace files

It was a long time coming but I finally updated ClearTrace to read SQL Server 2008 trace files.  This requires SQL Server 2008 to be installed on the computer where ClearTrace is running.  ClearTrace will process traces created in SQL Server 2000, SQL Server 2005 and SQL Server 2008.  ClearTrace is the tool I wrote to make performance tuning easier.  Many of my client engagements are to make SQL Server faster and ClearTrace is the tool I use to do that.  ClearTrace will read a series of trace files and aggregate the results so you can tune the...

posted @ Sunday, March 29, 2009 12:07 PM | Feedback (5) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Scriptio available on CodePlex

Scriptio is a little utility I wrote when I was unhappy with the scripting features in SQL Server 2005.  The biggest problem I faced was that I couldn't script a DROP and CREATE in the same file.  I'm pretty sure that's finally fixed.  The utility gives you all kind of scripting options now. The source code was always available on the site but wasn't always easy to find.  And there wasn't a good way for people to submit improvements.  I finally migrated the code base over to CodePlex.  You can download the source code or just the compiled EXE. ...

posted @ Thursday, June 05, 2008 9:12 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

SQL Server 2005 Best Practices Analyzer Updated (Jan 2008)

Microsoft just updated the SQL Server 2005 Best Practices Analyzer.  There are over 60 new rules included in this release.  You can find more information on the SQLCAT blog.

posted @ Saturday, January 19, 2008 9:40 AM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ]

Terminals: A Great RDP Client

One of the joys of being a consultant is trying to keep track of remote desktop connection information for a dozen clients.  Each one has multiple servers so the combination of clients, machines, user names and passwords add up quickly.  Recently I've been using Terminals to manage all my RDP connections. This is a small open-source application that provides a wrapper for the RDP client.  It allows me to securely store all my connection information for multiple machines.  Connecting to any machine I work with is as easy as selecting it from the drop down list.  As you can...

posted @ Friday, January 04, 2008 8:23 AM | Feedback (3) | Filed Under [ Utilities ]

ReadTrace for SQL Server 2005

Microsoft just released an updated Read80Trace that works on SQL Server 2005.  The name is simplified to just ReadTrace.  ReadTrace is a command-line utility that analyzes a SQL Server trace file and summarizes performance.  The new version has a more graphical interface.  The original was one of the most helpful performance tuning utilities I've ever used.  I expect this one will be just as useful. I typically use the tool to summarize performance by SQL statement.  ReadTrace will "normalize" the SQL statements by replacing constants with placeholders.  This lets you identify which SQL statements are using...

posted @ Wednesday, December 19, 2007 7:59 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Read90Trace coming soon!

Bob Ward announced in his PASS pre-conference session that Microsoft will be releasing a SQL Server 2005 version of Read80Trace in roughly 30 days.  It's currently going through a security review.  They're planning to call it ReadTrace instead of Read90Trace though.  I don't know what this will do to my plans for more work on ClearTrace.  I'll have to wait and see exactly what they release.

posted @ Sunday, September 23, 2007 10:20 PM | Feedback (0) | Filed Under [ SQL Server Stuff Utilities ClearTrace ]

Scriptio 0.5.5 and Source Code

I've checked in some minor bug fixes and updated the ClickOnce install for Scriptio (only works with IE).  I also uploaded a fresh copy of the Scriptio source code.  I'm not planning to make any additional updates at this time.  I'm spending all my time working on ClearTrace.  I'm certainly open to hosting this on one of the open source sites but someone else will probably have to take over development. Update: The ClickOnce installation is out of date and the Scriptio source code and executable are now available on CodePlex.

posted @ Tuesday, June 13, 2006 11:02 PM | Feedback (9) | Filed Under [ Utilities ]

Using Read80Trace in a batch file

It's 4:40AM in Barcelona and I can't sleep. I never seem to figure out jet lag. I'm sure I'll be sleeping just fine by the time I leave to come home on Saturday. Every year at the PASS Conference I find one idea that pays for the cost of the conference. One tip or shortcut that saves me so much time it justifies attending the conference. I think this year that idea is going to be automating Read80Trace. It is a fantastic little tool that aggregates and summarizes performance data on your SQL Server. The...

posted @ Wednesday, February 22, 2006 9:45 PM | Feedback (3) | Filed Under [ Utilities ]

Scriptio Update - Actually uses the SQL Server account

Well that was a little embarressing. When you told Scriptio to use a SQL Server account it used it to get the list of objects but not to do the actual scripting. That's fixed (0.5.4). Scriptio Install Page And on a lighter note I'm writing this from Rome. I came over a few days early to visit a friend. Of course, he had travel issues and couldn't make it back from Milan. Grrrr. I very much dislike the weather some days. Update: Most recent install and source code:  http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

posted @ Friday, February 17, 2006 1:23 PM | Feedback (2) | Filed Under [ Utilities ]

Scriptio Updated - Assemblies and Permissions

Scriptio is updated to allow the scripting of assemblies and permissions on the objects it scripts. Scriptio Install Page Update: Most recent install and source code:  http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

posted @ Sunday, February 12, 2006 5:30 PM | Feedback (2) | Filed Under [ Utilities ]

Scriptio Updated - Better server and database support

Scriptio is updated to 0.5.2. This version offers better support when switching servers. It also provides a friendly error message if you choose a SQL Server 2000 server. Update: Most recent install and source code:  http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

posted @ Tuesday, January 03, 2006 2:39 PM | Feedback (2) | Filed Under [ Utilities ]

SQL Server 2005 object scripting tool source code

I posted the source code for my SQL Server 2005 object scripting tool. The code reads like something I threw together to see if I could really do this. Because that's what it is. You can read a prior post that describes a little of what it does. The ClickOnce deployment is available here: http://www.sqlteam.com/publish/scriptio/ It's at version 0.5.1 as I type this. New features include: Connect to SQL Server using SQL Server accounts Option to include collation in the script Can optionally qualify creates, drops foreign keys with the schema Update: Most recent install and source code:  http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

posted @ Saturday, December 24, 2005 12:05 PM | Feedback (10) | Filed Under [ SQL Server Stuff Software Development Utilities ]

Scripting Objects in SQL Server 2005

After working with SQL Server 2005 I've discovered that I can't script out one object per file and include both the DROP and CREATE in the same file.  Which is driving me absolutely crazy!  So I wrote a little utility and thought I'd share it.  If you'd like to see this functionality included in a future service pack you can vote for this feature through MSDN Feedback Center. This utility requires .NET 2.0 and the SQL Server 2005 client tools installed on the machine.  The application is deployed as a ClickOnce application.  It can be installed at: http://www.sqlteam.com/publish/scriptio/ (Location Updated) I haven't released the...

posted @ Tuesday, November 22, 2005 2:58 PM | Feedback (64) | Filed Under [ SQL Server Stuff Software Development Utilities ]

Powered by:
Powered By Subtext Powered By ASP.NET