John McCormack DBA

SQL Server Databases and Cloud

Search and replace inside SSIS package

By 1 Comment

Search and replace inside SSIS package

replace all in notepadI had the situation recently where I needed to search and replace a string inside an SSIS package. The reason for this was about 25 Execute SQL tasks within the package all included some hard coded T-SQL that pointed at a decommissioned network share.  I needed to change the T-SQL in each task to point to a new network share. I know the package should have used a variable for this but I needed a quick fix.

If you realise that your SSIS package is simply just some XML which is made to look graphical by SSDT or BIDS, it becomes clear that this is a very simple process. It is just like doing a replace all in notepad which most people will have done many times. This is much easier and quicker than manually opening each and every Execute SQL Task and making each change manually.

Steps

  1. Make a copy of your SSIS package (just in case)
  2. Right click on the .dtsx file and choose Open with -> Notepad
  3. In notepad, press Ctrl-H
    1. Put the old value in Find What
    2. Put the new value in Replace with
    3. Click Replace All
  4. Save your package
  5. Open in SSDT or BIDS to confirm your changes have worked.

 

Disk space monitoring with SQL Server

By 2 Comments

In this post, I discuss 3 methods (of varying complexity) to carry out some disk space monitoring with SQL server. This can be ad-hoc or you can build it into SQL agent jobs to run on a schedule. Now the first thing I’d like to say is this isn’t a replacement for good monitoring software, it’s more of a compliment to monitoring software. It’s a double check in case something goes wrong with your monitoring. Having some handy scripts is useful as well, because it lets you find out info on an ad-hoc basis, rather than relying on alerts or checking manually. [Read more…]

Compressing backups with Litespeed for SQL Server

By Leave a Comment

I recently looked at an issue where free space on a backup drive was running low. One option was to increase the storage. Another was to look at the composition of the backups and see if I could try compressing backups with Litespeed for SQL Server.

2 particular backups stood out. Database1 was 93GB and Database2 was 297GB. Incidently, this was only half of the total size as we stripe across 2 drives so the total size was 186GB and 594GB.

Example backup script for Litespeed.

[sql]
exec master.dbo.xp_backup_database
@database = ‘Database1’,
@filename = ‘D:\Backup\Database1\Database1_1.BKP’,
@filename = ‘G:\Backup\Database1\Database1_2.BKP’,
@backupname = Database1 backup’,
@desc = ‘Backup of Database1’,
@encryptionkey = ‘LiteSp1234567’,
@cryptlevel = 8,
@init = 1,
@logging = 0,
@throttle = 99,
@comment = ”,
@with = ‘SKIP’,
@with = ‘STATS = 5’,
@compressionlevel = 1
[/sql]

For the purposes of this post, the line we are most interested in is @compressionlevel = 1. By increasing @compressionlevel on a scale from one to eight, we can reduce the size of our backups. One thing to note is that the higher the level of compression, the higher the CPU utilisation will be. More highly compressed backups will usually take more time. It is worth testing the backups using incremental increases in @compressionlevel to find the one that best suits your server.

LiteSpeed for SQL Server 7.5 allows a compression level of 1 – 8.
1 = Medium Compression
2 = Medium High Compression
3-6 = High Compression
7-8 = Extreme Compression.
(Previous versions allowed 9-11 but these are now classed as Level 8)

It is worth testing the backups using incremental increase in @compressionlevel to find the one that best suits your server.

My results:

Compressing backups with Litespeed for SQL Server

In the end, I opted for @compressionLevel = 5 for Database1 and @CompressionLevel = 3 for Database2. The increased backup time to use @compressionlevel=5 for Database1 is justifed, given the 64GB reduction in backup size. For Database 2 backup, there was too large a jump in processing time between levels 3 and 5. If we had no option, we could live with it but the original reduction in size by using @CompressionLevel = 3 was sufficient.

Side note: As a side benefit I wasn’t considering, our backup team advised the tape backup duration reduced by 1.5 hours so there was a positive knock on effect for another team too.

Source: http://documents.software.dell.com/litespeed-for-sql-server/7.5/netvault-litespeed-for-sql-server-user-guide/back-up-databases/compression-levels

Troubleshooting Transactional Replication in SQL Server

By Leave a Comment

SQL Server ReplicationThis might make me the odd one out but I actually really like replication. It took me a while to get comfortable with it but when I did and when I learned how to troubleshoot transactional replication confidently, I became a fan. Since I exclusively use transactional replication and not snapshot replication or merge replication, this post is only about transactional replication and in particular, how to troubleshoot transactional replication errors.

In the production system I work on, replication is highly reliable and rarely if ever causes the DBA’s headaches. It can be less so in our plethora of dev and qa boxes, probably down to rate of change in these environments with regular refreshes. Due to this, I’ve had to fix it many times. As I explain how I troubleshoot replication errors, I assume you know the basics of how replication works. If you don’t, a really good place to start is books online. It describes how replication uses a publishing metaphor and describes all the component parts in detail.

[Read more…]

How to write a PIVOT query in T-SQL

By Leave a Comment

sql server pivotIn this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

[sql]
CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[/sql]

[Read more…]