Browse by Tags

Tagged Content List
  • Blog Post: Check Script

    Someone contacted me yesterday and said they were getting blocked when they tried to create a FileStream data column type. On investigation, I found they were Mirroring that database – and the two aren’t compatible. Which got me to thinking – it’s probably a good idea to make a “check script” as you...
  • Blog Post: Backup Meta-Data

    I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this...
  • Blog Post: PowerShell: Read Excel to Create Inserts

    I’m writing a series of articles on how to migrate “departmental” data into SQL Server . I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach)...
  • Blog Post: System Variables, Stored Procedures or Functions for Meta Data

    Whenever you want to know something about SQL Server’s configuration, whether that’s the Instance itself or a database, you have a few options. If you want to know “dynamic” data, such as how much memory or CPU is consumed or what a particular query is doing, you should be using the Dynamic Management...
  • Blog Post: Code that Writes Code

    I have scripts that re-create my databases for testing and development purposes. But sometimes I want to take the data from a set of tables and move it as well – I could use SSIS, or a SELECT INTO statement, but what if I want to “re-set” the data to a point in time? In other words, load it with some...
  • Blog Post: Using Perfmon with SQL Server – Part Two

    Yesterday I posted a header from a script that shows you some links for how to use “Logman”, a Windows command-line utility that can automate your collection of Windows Performance Monitor counters. Towards the bottom, you can see the logman command I use, and you can see that it points to a counter...
  • Blog Post: Color Me Corrected – Will the REAL sysobjects please stand up?

    A few days ago I posted a way to look at the Dynamic Management Views (DMV’s) using a query, which I then copy and paste into the Help viewer’s Index tab to learn more. Well, my good friend and Colleague, Cliff, pointed out that I was using the older sysobjects system table. Well, of course this is a...
  • Blog Post: T-SQL Prettifiers

    OK, I don't think that's even a word, but the synonyms (can a non-word have a synonym?) are code beautifiers, sql formatters, etc. The basic idea is that it's a program that re-formats the text in a Transact-SQL statement using a standard set of rules. They do things like capitalizing keywords or breaking...
  • Blog Post: Commenting your Code

    You should always add descriptive comments to any code that will live longer than a single event. It's useful not only for the other unfortunate souls blessed with your code, but for yourself, years later, when you say "what was I thinking here?" I've recently had to debug some code and it...
  • Blog Post: A Template for Setting and Reading Extended Properties

    In the SQLCMS project, I show an example of some "base tables" that you can create for the system. Whenever I create a database, or database object, I make use of a feature that's been around for quite awhile - Extended Properties. You can set and read Extended Properties on almost any database object...
  • Blog Post: T-SQL to Find Physical and Logical Processors

    I ran into a licensing question the other day on Physical versus Logical Processors. Happily, Microsoft only charges you for Physical sockets - which you can find with this query: SELECT cpu_count / hyperthread_ratio AS PhysicalCPUs FROM sys.dm_os_sys_info And then this query will show you how...
  • Blog Post: Listing the Central Management Servers Programmatically

    I don't normally advocate hitting the system tables in any database with direct queries, preferring instead to use views or functions that the vendor provides. But so far I haven't been able to find the view or function that would return the list of servers in the Central Management Servers group on...
  • Blog Post: Back to basics - Using another Default Filegroup

    SQL Server uses the file system on a computer, just like all database platforms. Whenever you create a database, there are always at least two files that are created - one for the data (MDF files) and another for the logging function (LDF files). But you can create more files if you want, and it's a...
  • Blog Post: Using WITH MIRROR to Copy Your Backups

    The other day I chatted with a gentleman who needed a second copy of his backup for a remote reporting system. Most of us need a copy or two of the backups, sometimes for a reporting or development refresh, or sometimes we want a second copy for safety reasons. He would take the backup, and then copy...
  • Blog Post: Scripts, Blogs, and Learning

    I received an e-mail from a reader asking if I could post a link to a script he's documented in an article. I'm always happy to do that, but there are some things you should know about ANY script you find on the web, and while I'm at it, for how you read blog entries. 1. Read each blog entry carefully...
  • Blog Post: Versioning the Database

    If you write apps that hit a database that you own, then you probably want version control on the database itself. I always script out the initial database, and version that script. I also script the changes, and version those, and then script the entire DB again and version that as well. That allows...
  • Blog Post: Free T-SQL Beautifier

    I do love me some good-looking code. Sure, the syntax has to be there, and the code has to be correct and all that, but I'm old-school, and I think pretty code is easier to read. I'd love to have a "code beautifier" built right in to SQL Server Management Studio, but I suppose there's other...
  • Blog Post: Top Queries DMV

    I spoke at the Pacific Northwest SQL Server User Group last night, and we covered Performance tuning. I promised to blog a script that uses the "query_stats" DMV and a CROSS APPLY to find the top queries - there are lots of these on the web, so here's one I like for I/O: 1: SELECT TOP 10 2: total_logical_reads...
  • Blog Post: Mandelbrots and SQL Server

    I was watching Nova last night and they talked to Benoit Mandelbrot, the man who codified some of the math that bears his name. This math branch deals with "rough" or repeating surfaces. You probably already know that Transact-SQL can be used to type almost any math formula. I've included statistical...
  • Blog Post: Getting Bit by Forgetting

    I was working on a SQL Server 2005 system yesterday and I had really awful performance on one of my queries. I just couldn't figure out why it was running slow. I decided to use the new SQL Server 2008 T-SQL Debugger on the code, and sure enough - I found the problem. I had a User-Defined Function (UDF...
  • Blog Post: Scripts and more scripts

    A few posts back I mentioned that I used the "Templates" feature in SQL Server Management Studio to track all of my script librarires. I wanted to expand on that a little bit, and make a correction. Like many DBA's, I have a series of scripts I have collected over the years. I created some as I learned...
  • Blog Post: Templates

    I’ve run into quite a few DBA’s who aren’t using the Templates feature we provide with SQL Server Management Studio. If you spend a little time becoming familiar with them, I think you’ll find they are a powerful tool. Templates are really just a series of Transact-SQL statements that live on your...
  • Blog Post: Out Out, Darn Database!

    Have you ever really needed to drop a database and you couldn’t? You’ve tried using the management tools, but you forgot you can’t drop a database when you’re in it, or you’re just fighting a process that just won’t seem to die? Fear not – the following script will do the job. Just replace DatabaseName...
  • Blog Post: It’s two helps in one!

    Did you know that you can show help in two different ways from SQL Server Management Studio? Most of us like the full client of Books Online, which is actually a program called the “DExplorer”. This tool has multiple windows, a URL bar, a search feature and more. But those of you who are more familiar...
  • Blog Post: Script of the day: Count all objects in a database

    This script counts all of your objects in the database, at least the ones stored in the sysobjects table. Some objects (such as jobs) don't show up there - one day I'll add those: /* usc_DBA_Count_Objects.sql Author: Buck Woody Purpose: Shows a quick count of database objects found in sysobjects ...
Page 1 of 2 (37 items) 12