SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Simple SQL Server

I started out working with Microsoft Access and SQL Server back in 2000 as the only employee doing IT full-time, and worked most of my career where “big fish in a little pond” was an overstatement. Learning is scarce when you do everything and don’t work with anyone who knows more than you. In 2010 I was plunged into the Ocean and grabbed onto anything I could find to stay afloat. I wasn’t going to simply run scripts I didn’t understand, so I learned the DMVs and system tables in the scripts I found and rewrote them all. Now, I know enough where I can start giving back to a community that saved me from drowning.

Archives: May 2013

Blocking – Capturing and Monitoring

If a query is taking longer to run than normal, there’s a good chance it’s being blocked by something else. This is especially true when you’re doing something rediculously simple and SQL Server just sits there thinking. Symptoms of blocking problems include a trace of SQL:BatchCompleted and RPC:Completed with durations… Read more

1 comments, 620 reads

Posted in Simple SQL Server on 22 May 2013

Wait Stats – Monitoring and Using

sys.dm_os_wait_stats is one of the most important DMVs out there, and one that you should know the historical values of for every server you care about. This DMV will tell you the cumulative amount of time you waited for each wait type since SQL Server services started, which is nice… Read more

6 comments, 1,668 reads

Posted in Simple SQL Server on 21 May 2013

Indexes – Understanding basic types and their components

The biggest problem developers and newer DBAs have with understanding indexes is that you don’t realize when you’re using the exact same thing away from your computer.  Pick up any reference style book and you have one clustered index and one nonclustered index. The clustered index is also split into… Read more

0 comments, 1,256 reads

Posted in Simple SQL Server on 18 May 2013

Indexes – Unused and Duplicates

Indexes aren’t free, and many databases end up with unused indexes. Every time you make any update to a table you will be updating the clustered index (I assume no heaps, because I hate heaps), and every index that has uses one of the columns that were updated. Inserts and… Read more

6 comments, 1,588 reads

Posted in Simple SQL Server on 16 May 2013

Job Schedules – SQL Agent

This info should be easier to get than it is. Keep in mind that for something to run the subscription and schedule both have to be enabled.

To be fair, the majority of this code was grabbed from the internet, and this is not something I modified to the extent… Read more

2 comments, 1,215 reads

Posted in Simple SQL Server on 15 May 2013

Roll Logs – Backup Recovery

Rolling log files for a day, especially with 15 or even 5 minute log backups is a pain at best. Here’s a slightly better way to do it. Set the two variables at the top to the directory where the log backups are and the database you’re looking to restore.… Read more

8 comments, 1,797 reads

Posted in Simple SQL Server on 15 May 2013

Table Sizes

There’s no simple way in SQL Server to see the sizes of all the tables and their indexes.  Even seeing a single table’s size through SSMS can take a while.  The solution below is grabbed from statistics, so there’s no guarantee you’re getting exact numbers.  However, exact numbers don’t tend… Read more

2 comments, 593 reads

Posted in Simple SQL Server on 15 May 2013

Index Columns and Names

Don’t trust the name of an index to tell you what it is. The name is wrong, or at least that’s what you have to assume.

I’m a huge fan of index naming conventions, with my favorite being starting out by saying it’s an index (IDX) followed by the table… Read more

0 comments, 1,196 reads

Posted in Simple SQL Server on 14 May 2013

Running Processes

Sp_who2 is not used by this DBA.  That info is amazingly good to have, but I want more.  Don’t get me wrong, sp_who2 is kinda hidden in this one as it’s really just a proc that does little more than “SELECT * FROM SysProcesses“, and I use SysProcesses… Read more

0 comments, 724 reads

Posted in Simple SQL Server on 14 May 2013

Backup History

Can you tell me the drive we were backing up server #58 to exactly 8 months ago, and, if possible, it would really help if you could give me the exact file name for me to pull the backup from tape for you.  Or, last night’s backup didn’t finish until… Read more

0 comments, 368 reads

Posted in Simple SQL Server on 14 May 2013

Cluster Info

This script is so simple you’ll start off by asking why you’d ever use it.  Then you’ll use it and wonder why no one gave this to you as part of your new-hire orientation at your first DBA job.  The truth is that in larger environments there will always be… Read more

2 comments, 515 reads

Posted in Simple SQL Server on 14 May 2013

File Sizes

There are two scripts I like to use to look at the file sizes on servers.  The first one is everything I could hope for on servers where there is only a single file per filegroup.  All of the vital information is all in one place.  I know, DBAs tend… Read more

0 comments, 404 reads

Posted in Simple SQL Server on 14 May 2013

Copy / Paste Code

Copy / Paste formatted text is a nightmare in WordPress.  I’m singling out WordPress because I use it, and, from what I can see, every blog site has this issue.  If you have a SQL Script (again, singled out because that’s what I use), and copy / paste from SSMS… Read more

3 comments, 510 reads

Posted in Simple SQL Server on 13 May 2013

Database Assessment

This script looks much more intimidating than the results it produces, so I recommend running it before reading through it.  I’ll run this when I first get on a unfamiliar server as a basic assessment of what’s there and find some hard to find issues with no effort.

The issues…

Read more

0 comments, 394 reads

Posted in Simple SQL Server on 11 May 2013


Like many SQL Server DBAs I run into a problem at work or get an idea in my head then scour the internet to see if someone else had the same thing so I don’t have to reinvent the wheel.  Some DBAs stop at that point, I don’t.  I grab… Read more

0 comments, 362 reads

Posted in Simple SQL Server on 10 May 2013