Tidying Up Ugly Tab Titles in SSMS

Ever get tired of the ugly looking tabs at the top of SSMS, full of impertinent information? I was recently told of a method to tidy up the info displayed on the tabs, making them more legible:

2015-02-11 03_57_55_uglyTabs

Go to Tools > Options

2015-02-11 03_56_35_tools

Then navigate to Text Editor > Editor Tab and Status Bar

2015-02-11 03_57_12_before

Those options underneath “Tab Text” determine what is displayed on the tab. Here you can set them to be on or off.

2015-02-11 03_55_52_options

Once you made your changes click “OK” and your tabs are now altered.

2015-02-11 04_04_10_tidyTab

SSIS 2012 Deployment Fail Part Two: SSIS Server Maintenance Job

Recently I post about a timeout issue I was getting when deploying SSIS projects.  It was mainly to do with too much log and previous versions being kept in the SSISDB on our test environments. My solution was to run the SSIS Server Maintenance Job prior to every deployment to mitigate the timeout. By and large this has been super effective.

However that job can take a considerable amount of time to run. And it is not just our test environment that has seen issues: we rely on SSIS heavily and our schedules are every 5 minutes or so, so lots and lots of logs are created.

Looking around the internet, I found an article that updates the stored proc that is run when deleting the log data by truncating it (link below). I’ve tested out the stored proc provided and it works well. It still takes a long time, like 45 minutes, but that is light speed compared to the 6 hours we were getting on a daily basis.

We don’t want this stored proc to replace the current one that is run as part of the Maintenance Job as we’d lose all ability to trace any issues.

In the comments section there is also a suggestion for indexes to create that have helped the purges of subsequent runs. It will be worth seeing if the daily job can now cope, and will keep the reports more snappy, which is no bad thing.


SSIS 2012 Deployment Fail

(Update: Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

No WiX Wednesday this week, owing to commitments in real life. Instead, here is something regarding SSIS 2012 Deployment. Enjoy!

As part of our CI and Test Builds we have automated the deployment of two SSIS Projects. One is fairly large and the other one contains only two dtsx packages. Recently we have been getting timeout issues with the deployment of the solutions. Continue reading “SSIS 2012 Deployment Fail”

Delete Old Tables on a Schedule

Update: turns out this job doesn’t actually execute (d’oh!) so I made a stored proc. Read the updated post by clicking this linky

One of our databases is exclusively used for staging tables of raw data, before it is extracted and transformed by a SSIS package and inserted into our data warehouse. These tables are large and pretty much useless after one run. The database has over 1.5TB of storage, but even after a few days of heavy month end data loading, it can fill up quickly. There’s no real warning for when the database does fill up either, as we have expanded the Files in the Filegroup to fill out the disks, so we don’t slow down the process from autogrow. Because these tables are so throwaway, we have a SQL Agent job that deletes anything older than three days at midnight. There’s pros and cons at running it at this time, but seeing as our data-loading window is 24/7 it seems as good a time as any.

As the DROP TABLE statement won’t accept a parameter, you can’t do DROP TABLE @tablename. You can however build a string of a complete T-SQL statement and EXECUTE it. I use a CURSOR, with READ_ONLY, to loop through the SELECT statement of the tables I want to delete. The READ_ONLY keyword allows for a faster and safer execution as no lock is required; we are only running a select statement. My select is pretty simple; using sys.sysobjects I get a list of the tables that were created more than 72 hours old.

USE <db_name>
DECLARE @tableName VARCHAR(100)
SELECT name AS tableName
FROM sys.objects
WHERE create_date > DATEADD (hh, -72,  CAST(GETDATE() AS datetime))

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName

SET @sql = 'DROP TABLE ' + @tableName
EXEC (@sql)
--PRINT @sql

FETCH NEXT FROM db_cursor INTO @tableName
CLOSE db_cursor
DEALLOCATE db_cursor

We need to be careful when just getting a bunch of names for tables and dropping them that way; if you put the hour at a sufficiently high number you will find all manner of sys tables listed there that you really don’t want to delete. There’s two ways I thought about dealing with this:


  • Add an “AND” clause to the “SELECT” to filter out anything you might not want to delete. As these are always staging tables for the same load, I was able to narrow it down by writing the following:

SELECT name AS tableName
FROM sys.objects
WHERE create_date > DATEADD (hh, -72,  CAST(GETDATE() AS datetime))

and name LIKE 'TempRun%'

  • Alternately, narrowing down the search to only tables that are defined as user type tables.

SELECT name AS tableName
FROM sys.objects
WHERE create_date > DATEADD (hh, -72,  CAST(GETDATE() AS datetime))

and type = 'U'

It’s surprising to see how different the Query Execution Plan is for either of these, and I was surprised to see which one was theoretically quicker (even though in the case of either of these queries, speed is not really an issue). There’s a print option commented out which you can switch in and comment out the EXECUTE @SQL line if you want to have a play around at first.

Once you’ve chosen which method you want to do it’s a case of creating a new SQL Agent Job. I’ve added the steps here in a gallery for completeness. Click on the first image to scroll through the gallery.

SQL Server 2012 Unknown Unknowns

Last year saw the release of both SQL Server 2012 and Windows Server 2012. Both bought significant updates, including changes to High Availability and Disaster Recovery for SQL Server 2012, and a focus on PowerShell and Server Core features for Windows Server 2012, not to mention improvements to Hyper V, which could lead my company to virtualize the only boxes in our company that are still physical, namely the database and SSAS boxes. So before we make this leap it’s important to hear from those who have experience and can pass it on. Apart from reading MSDN and trying out a few labs, there are a couple of user groups that I will be attending:

Continue reading “SQL Server 2012 Unknown Unknowns”

Object Explorer Details Window

Occasionally, there is a need for us to manually delete a large number of staging tables from some of our databases. This can also occur to our partitioned tables that sometimes leave the switched partition on the list of tables in the database. But through the Object Explorer window in SSMS, it’s not possible to select more than one table at anyone time. You could script it out using T-SQL, but if there is also the Object Explorer Details window in SSMS, which you can open by pressing F7 or going to View -> Object Explorer Details



object explorer

Now when you navigate over the “Tables” folder in the database that has all the tables you want to drop, you’ll see in the Object Explorer Details window all the tables.

object explorer 2

You can then select all the tables or a subset by pressing shift, and skip over others by pressing ctrl, just like you would with files through Windows Explorer.