Another example of MS making it hard to quickly check what version of a software you are running.

Use the SQL script below to find your SMS sites service pack version level for SMS 2.0 or SMS 2003 sites.

SQL Query:

Select
SiteCode,
SiteServer,
SiteName,

'Version' = Case
When Version between '2.00.1239.0000' And '2.00.1239.0999'
Then 'SMS Version 2.0 RTM'
When Version between '2.00.1380.1000' And '2.00.1380.1999'
Then 'SMS Version 2.0 Service Pack 1'
When Version between '2.00.1493.2000' And '2.00.1493.2999'
Then 'SMS Version 2.0 Service Pack 2'
When Version between '2.00.1493.3000' And '2.00.1493.3999'
Then 'SMS Version 2.0 Service Pack 3'
When Version between '2.00.1493.4000' And '2.00.1493.4999'
Then 'SMS Version 2.0 Service Pack 4'
When Version between '2.00.1493.5000' And '2.00.1493.5999'
Then 'SMS Version 2.0 Service Pack 5'
When Version = '2.50.2726.0018'
Then 'SMS 2003 RTM'
When Version = '2.50.3174.1018'
Then 'SMS 2003 SP 1'
When Version = '2.50.4160.2000'
Then 'SMS 2003 SP 2'
Else 'Unable To Determine Service Pack!'
End

From Sites

Often software will request a certain service pack level or version of SQL Server. (IE: MOSS 2007 requires SQL 2005 SP2). There is no simple Help > About to see the version of SQL you are running.

For SQL Server 2000 and up, run the below query (2000 - open Query Analyzer, 2005 - right-click a DB and select New Query):

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

This will return your version. You can see the build charts for more detailed information:

SQL Server 2000 Build Chart
SQL Server 2005 Build Chart

When dealing with some large ldf log files I started investigating the best methods to backup/compact/just generally deal with them.

Since the "shrink database" command within SQL 2000 Enterprise Manager doesn't always seem to do what you'd expect, here is the command to run in Query Analyzer.

backup log db_name_here with truncate_only
dbcc shrinkfile(db_log_filename_here,truncateonly)

You could script this to run on a regular basis or just as a one-off while doing manual maintenance on your dbs.