News & Events

Writing a SQL Script for “all versions” of SQL

One of our Microsoft SQL Server consultants gives us a handy tip for writing a SQL script for many different versions of SQL Server.

“One of the daily challenges you may have as a Microsoft DBA is supporting many different versions of SQL Server. One thing that can be particularly painful is writing a script that can be run on all versions of SQL Server past and present. Newer versions of SQL server have new ways on which to find out information or run tasks (that are a lot easier), things which were never there in previous versions.

For example, I notice that a lot of my SQL Severs have been hungry hippos overnight and the Log files on most of my servers are suddenly very large. If I have a 100 SQL severs, I don’t want to be checking each individually, I want to deploy a script across all of them and I need to do it quickly.

I start writing this on my new SQL Server 2012 box and quickly decide to use the Dynamic Management Views (DMV’s). I start with something like:

— using DMV os Performance stats
SELECT instance_name
,cntr_value ‘Log File(s) Used Size (KB)’
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Log File(s) Used Size (KB)’

This runs well on my SQL Server 2012, 2008r2 and SQL 2005 servers. But the pesky SQL Server 2000 server simply comes back with “object not found”. Of course, SQL Server 2000 didn’t have DMV’s, man we should really upgrade these. However in the meantime, I need that data. Ok so in 2000 days, I have to use the DBCC command SQLPERF to get the log size.

— using DBCC SQLPERF(LOGSPACE);

— Create a temp table
create table #dbccresults
(
DBName [varchar](255),
LogSize_MB decimal(18, 8),
LogSpaceUsed decimal(18, 8),
status int)

— Get the Log file sizes
insert into #dbccresults
exec (‘dbcc sqlperf(logspace)’)

— Output the results
select * from #dbccresults

But is there some way I can have 1 script that does both of these – now that I have both scripts ready?
One trick that we at dsp use is to write our scripts to ensure they check the version of SQL Server first, and then run the relevant piece of SQL based on the version of SQL that part of the script supports.

To do this we use SERVERPROPERTY this is supported in all version of SQL and likely to be ever more.

SELECT CAST(LEFT(SERVERPROPERTY (‘productversion’), 2) as int) as ‘ProductVersion’

This will output the following based on the current version of SQL Server:

8 – SQL Server 2000
9 – SQL Server 2005
10 – SQL Server 2008
11 – SQL Server 2012

If we use this in our script with an IF statement then we can allow our DMV script to work on SQL Server 2005 onwards and our DBCC script to run on SQL Server 2000 servers.

— Run if SQL Server 2000
IF SELECT CAST(LEFT(SERVERPROPERTY (‘productversion’), 2) as int) = 8
BEGIN
— using DBCC SQLPERF(LOGSPACE);

— Create a temp table
create table #dbccresults
(
DBName [varchar](255),
LogSize_MB decimal(18, 8),
LogSpaceUsed decimal(18, 8),
status int)

— Get the Log file sizes
insert into #dbccresults
exec (‘dbcc sqlperf(logspace)’)

— Output the results
select * from #dbccresults

END

— Run if SQL Server 2005 or above
IF SELECT CAST(LEFT(SERVERPROPERTY (‘productversion’), 2) as int) = 8
BEGIN
— using DMV os Performance stats
SELECT instance_name
,cntr_value ‘Log File(s) Used Size (KB)’
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Log File(s) Used Size (KB)’
END

Great, now I have one script I can use anywhere on my system and it’s going to give me the results I need no matter what version of SQL Server I am using.
You can use this method to do any number of checks, and it’s always there in the kitbag for when needed.”

Get in touch

For more information or to schedule a demo please contact us

Contact us

News

dsp drives up its share of the Database MSP Market with
record contract growth and 2nd acquisition

DSP firmly established itself as one of the UK’s fastest growing proactive Database MSPs during 2016, signing £2.1m of new contracts and making its second acquisition, the Oracle DBA support division of IT Services provider ITSB.

Read more
View All

Event

Applications on Oracle Database Appliance (ODA)

A main goal... how to future proof your applications environment through Oracle while becoming more efficient in terms of costs and productivity.

As well as the option of developing the fundamental steps you need to take to get ...

Read more
View All

Blog

How can you regularly benchmark your database and application infrastructure against real-life? Why would you want to?

In the age of big data, public cloud, private cloud it’s easy to be pushed into constantly thinking about the future... What should you optimise though and how can you be sure it’ll make a difference? Would knowing it could give you a competitive edge be worth considering?

Read more
View All