There could be a beast lurking in your log files, robbing you of performance, and you might not even know its name. But fear not - I'll give you a weapon to kill the beast.If you've never heard of "virtual log files" (VLFs), read these articles: they'll well-written, short, and extremely informative. You owe it to yourself to read the originals, but I'll sum them up briefly here. VLFs: A Quick

This is a special-purpose script, but it still may be useful to someone, someday.Imagine you have many tables with similar filenames and the same column names. This might happen if the tables are created automatically by a Job on a daily basis, for example. For each column you specify, this script displays the declared width, the average number of characters stored, and the minimum and maximum

Probably the first thing I learned about query tuning is that "clock-on-the-wall" time is definitely not the thing you want to measure. The elapsed time a query takes can be influenced by countless factors (other processes running, disk speed, number of processors, amount of RAM, phase of the moon, a few hundred database settings) that it's too rough a metric for serious performance tuning.What

Remember that Twilight Zone episode, where a man sells his soul to the Devil for immortality, only to be sentenced to life in prison the next day?I'm learning SSIS (2005, for now), and have discovered, like all those before me, that Microsoft implemented Annotations, but... they don't word-wrap! Your Annotation must fit on one really long one line! Bwa-ha-ha-ha-ha-ha!Of course, I'm no longer

Are you getting this error message, been Googling for an hour, and nothing helps?Error: 0xC0029186 at {Your FTP Task}, {Your FTP Task Description}: Directory is not specified in the file connection manager {Your Variable Name}.If you entered the value for the source and/or target filename variable and enclosed it with double-quotes, try removing them. Worked for me.(Before coming over to the SQL

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that

A colleague pointed out something I'd never noticed about SQL Server Management Studio's (SSMS) "Select Top 1000 Rows" feature: it doesn't display SPARSE columns. This is not a bug, but rather by design: SQL Server tables can have up to 30,000 SPARSE columns: imagine the issues with viewing thousands of columns at a time! The annoying thing, however, is that no matter how few columns a table has

Here's a script to tell you how big your table(s) are, subtotaled by indexes, with a grand total. Includes LOBs, and excludes dropped pages. SELECT CASE WHEN GROUPING(t.name) = 0 THEN t.name ELSE 'GRAND TOTAL' END AS 'Table Name' , CASE WHEN GROUPING(i.name) = 0 THEN i.name ELSE 'TABLE TOTAL' END AS 'Index Name' , SUM(au.total_pages) * 8 / 1024

I don't know if it's ever happened to you, but I always find myself wishing I had documented what the devil I was thinking when I added an index to a table. I mean, it's all well and good to create an index, but it's annoying when six months later you can't remember what it was for. Is anything actually using this index? Can I maybe drop it? Yes, yes, I suppose I could document my indexes,

Here's a quick script to help you quantify how useful your indexes are, in the sense of how much they're used to speed up queries, compared to how much work it is for SQL Server to maintain them during inserts, updates, and deletes. This only reflects the usage since the last SQL Server boot.-- Displays the read-to-update ratio of all indexes. Good for finding ones that may not be needed.