Thought I'd share something I threw together for a client today. They were looking for index a list of indexes in a database with their associated sizes and usage. Using this WHERE statement allowed them to identify 38GB of index space that was being allocated unnecessarily for indexes.
Category: SQL Server
AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration
I've been looking into the AUTO_UPDATE_STATISTICS_ASYNC option for SQL Server lately and found a couple decent articles on there on it. AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration Asynchronous Update Statistics Preventing the blocking of queries in OLTP environment from statastic updates seems like a good thing.
Last Time SQL Server Restarted
Not a really hard thing to figure out, but for some crazy reason I always forget. So, hopefully actually writing it out for the world to see will help me remember. Since tempdb is created every time that SQL Server restarts, the created date for that database can be used to determine the last time … Continue reading Last Time SQL Server Restarted
Database Mail Failed Items Log
Recently, I was asked why e-mails would mysteriously not be sent from one of the servers that I help maintain. One of the greatest things about working with SQL Server is most everything that happens in the server is data and all I had to figure out is where the data was stored. In msdb … Continue reading Database Mail Failed Items Log
Database Edition msdb Sample Database Projects
I've been working with Database Professional a little bit more these days. One of the databases that I've been working on for a while provides a number of diagnostic features that utilizes some msdb stored procedures. Fortunately, going forward I can stop ignoring the systax errors that occurs because of those cross-database references. The Visual … Continue reading Database Edition msdb Sample Database Projects
CLR_AUTO_EVENT Wait Stat
I've was reviewing wait stats the other day and kept finding this wait stat peaking out the other wait stats by a few hundred milliseconds. Oh my! Should I care or not... well long story short is that if all of the CLR assemblies are created in safe mode there is no problem. Or at … Continue reading CLR_AUTO_EVENT Wait Stat
Transfer Logins Between SQL Server 2005 Instances
I've never been a fan of the SQL Server 2005 Integration Services task for transferring logins between servers. It seems that I always misconfigure it or something gets missed. And when I just want to move a single login its more effort to setup the task than it is worth. What I'm really saying here … Continue reading Transfer Logins Between SQL Server 2005 Instances
Upgrade Issues When ‘sa’ Renamed
I've not done recommended changing the sa account name at a client for quite a while. Since the account can be disabled there isn't any good reason to rename it that I've been able to come up with. But if you do and plan to upgrade to SQL Server 2008, beware of the following... http://blogs.msdn.com/psssql/archive/2008/09/10/upgrade-for-sql-server-2008-can-fail-if-you-have-renamed-the-sa-account.aspx
Incrementing Values
Occasionally, I run across procedures that mimic the functionality of the IDENTITY property. There are always various reasons for these procedures to exist, some valid and some not quite so. Recently while trying to tune one of these procedures that someone had added interesting locking hints to a creative rewrite to the procedure was suggested. … Continue reading Incrementing Values
Change the Server Collation of SQL Server 2005
A nice little change in SQL Server 2005 has been the ability to change the collation of the server. Back in the day, circa SQL 2000, changing collation and reinstalling SQL Server were pretty much the same thing. By pretty much, I mean the exact same thing. In SQL Server 2005 you can now do … Continue reading Change the Server Collation of SQL Server 2005
Strate SQL 