Database corruption false alarm

To check for corruption on a very large production database I created a database-snapshot to run CHECKDB against.

This found a corrupt-table (“CHECKDB found 0 allocation errors and 4191 consistance errors in table … repair_allow_data_loss is the minimum repair level for the erors found…”).

I tried running CHECKTABLE against the suspect table within the live database, but couldn’t due to lack of space.

Finally I used DBCC CHECKTABLE(sometablename) WITH TABLOCK so dbcc wouldn’t create a background copy on the database to check – and run out of space!

This confirmed the live table was fine (yay! ooo cake).

Arithmetic overflow error converting expression to data type int.

This error message is a sign of the times [sigh]. With databases getting bigger all the time ‘INT’ often needs to be replaced with ‘BIGINT’.

For example my ‘database_sizes’ script used to work just fine starting like this …

select round(sum(mf.size)*8/1024/1000,0) [SizeGB]

But now with Terabyte sized databases, when mf.size is multiplied by 8 its just too big! The (ugly) solution is to explicitly convert INT to BIGINT …

select round(sum(convert(bigint, mf.size))*8/1024/1000,0) [SizeGB]


To change a table column from INT to BIGINT paste any ‘create constraint’ code into a script, then above that create the update statement, then above that work-out the drop constraint code. Here’s an example on a primary-key …


USE [Production]

--1 remove constraint

ALTER TABLE [dbo].[sometablename] DROP CONSTRAINT [someconstraintname]

--2 change int to bigint

ALTER TABLE  [dbo].[sometablename] ALTER COLUMN [somecolumnname] bigint not null

--3 re-create constraint

ALTER TABLE [dbo].[sometablename] ADD  CONSTRAINT [someconstraintname] PRIMARY KEY CLUSTERED 

Resize Datafile

Here’s a simplified version of my previous datafile resizing helper script. I tend to resize most datafiles to 30%free on Friday afternoons for resilience over the weekend 😉


create table #filestats 
(fileid int, fg int, AllocatedSpace int, UsedSpace int, dfile varchar(200), DataFile varchar(200))

insert into #filestats exec('dbcc showfilestats')
update #filestats set AllocatedSpace = AllocatedSpace * 0.0625, UsedSpace = UsedSpace * 0.0625

select DataFile, AllocatedSpace, UsedSpace,
     cast(cast(AllocatedSpace - UsedSpace as numeric) / AllocatedSpace * 100 as int) [Free%],
     cast(UsedSpace * 1.449 as int) [Plus30%]
from #filestats 
-- where datafile like 'E%' -- e-drive only
order by [free%]

drop table #filestats

dbcc checkfilegroup – semi-automatic

On a very-large database I wanted to check each filegroup for corruption with real-time feedback. That is, I did not want to just set a loop going and wait indefinately for the results. Also I did not want info-messages.

The semi-automatic query I wrote (below) runs CheckFileGroup against the smallest filegroup and stops. Then when manually re-run (in the same SSMS session) checks the next smallest filegroup etc.

Then at the end of the day I would know where it was upto.

-- checkfilegroups_vldb.sql
-- use current (snapshot?)

-- create table of filegroup names and sizes
begin try
     select, sum(df.size/128) mb
     into #tables
     from sys.filegroups fg
     join sys.database_files df on fg.data_space_id = df.data_space_id
     group by
     order by mb, name
     select '[F5] to Start'
end try

--check each filegroup starting with smallest
begin catch
     select * from #tables
     declare @sql varchar(100)
     select top 1 @sql = 'dbcc checkfilegroup (''' + name + ''') with all_errormsgs, no_infomsgs;' from #tables order by mb, name
    delete from #tables where name = (select top 1 name from #tables order by mb, name)
    if (select count(*) from #tables) = 0
        drop table #tables
        select 'Complete'
end catch

cdc work-sheet

Here are the cdc commands I use …


--is cdc enabled on my current database?
select is_cdc_enabled, * from master.sys.databases

--is cdc enabled on this table?
use SomeDatabaseName
select is_tracked_by_cdc, * from sys.tables order by 1 desc

use SomeDatabaseName
exec sys.sp_cdc_enable_db          -- enable database
exec sys.sp_cdc_enable_table       -- enable table
   @source_schema = N'dbo',
   @source_name = N'SomeTableName' -- table to be monitored
   @role_name = NULL,              -- login used to restrict access
   @filegroup_name = N'cdc_fg'     -- used to set path for change-table to its own drive

select * from cdc.dbo_SomeTableName_CT -- changes plus 5x cdc columns
where __$start_lsn = 0xSomeLsnNumber

     $operation ... 1=delete
                    3=value before update
                    4=value after update
     $update_mask ... bit-map of column-names updated (IE: 3 & 4 only)

select * from cdc.captured_columns -- list of logged column-names
select * from cdc.change_tables    -- table(s) monitored by cdc
select * from cdc.ddl_history      -- ddl chages
select * from cdc.index_columns    -- indexes on monitored tables
select * from lsn_time_mapping     -- lsn numbers with time-stamps

select min(tran_begin_time), max(tran_begin_time)
from cdc.lsn_time_mapping          -- check its running

use Somedatabase
exec sys.sp_cdc_help_change_date_capture -- get values for @capture_instance etc

use SomeDatabase
-- exec sys.sp_cdc_disable_table  -- disable table
@source_schema = N'dbo',
@source_name = N'SomeTableName',      -- table name
@capture_instance = N'dbo_SomeTableName'
use somedatabase
--exec sys.sp_cdc_disable_db      -- disable database ... and delete all data and objects

--show latency in seconds & hours
select latency, latency/3600 latency_hrs, *
from SomeDatabase.sys.dm_cdc_log_scan_sessions

--show current sqljob settings
select * from msdb.dbo.cdc_jobs -- note: 4320 mins = 3 days

--chanage 'capture' job settings
exec sys.sp_cdc_change_job @job_type = 'capture',
  @maxtrans = 600,       --500 default
  @maxscans = 200,       --10
  @continuous = 1,       --1
  @pollinginterval = 2   --5
--dont forget to stop/start job to use these new settings

-- see if cdc (aka replication) is stopping the log-file shrinking
select name, log_reuse_wait_desc from sys.databases

-- see percentage log-file used
dbcc sqlperf(logspace)

-- show cdc error-log
select * from master.sys.dm_cdc_errors

-- emergency dump all pending transactions, allowing shrink
--sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0, @reset = 1

CDC Update mask

To decode the update_mask 0x00007C00 I wrote out the last 4 digits (‘7C00’, thats those with values plus those to the right) in binary 0111 1100 0000 0000 which (counting from the right) indicated columns 11, 12, 13, 14, and 15 had been updated.

simples 😉

Ok ok. Until I get round to convert the above logic into a big-dynamic-case-statement … here’s a script I adapted from here (

select (select cc.column_name + ', '
     from cdc.capture_columns cc
     join cdc.change_tables ct on cc.[object_id] = ct.[object_id]
     where ct.capture_instance = 'dbo_MYTABLE'
     for xml path('')) as changedcolumns, *
from cdc.dbo_MYTABLE_ct pd
where __$start_lst = SOME_LSN_NUMBER

Converting Delphi datetime to SQL Server

--1of2 -- convert delphi datetime to sql

-- input a delphi float for conversion
declare @input float = 41492.9039269676 -- <<<<<<<<<<< user input

-- seperate the date-part
declare @date int = @input

-- seperate the time-part
declare @time float = (@input - @date)

-- display results
select @input 'delphi', cast(dateadd(dd, @date, '1899-12-30 00:00:00') + @time as datetime) [sql]

--2of2 -- convert sql datetime to delphi

-- input the sql datetime for conversion
declare @input2 datetime = '2013-08-06 21:41:39.290' -- <<<<<<<<<<<< user input

-- display results
select @input2 [sql], cast(@input2 as float)+2 'delphi'

Large Page Extensions

After a re-boot I noticed the message “Cannot use Large Page Extensions: lock memory privilege was not granted.”

To fix this I remoted onto the SQL2008r2 Enterprise Edition server and ran “gpedit.msc”.

Within the tree I navigated to / Computer Configuration / Windows Settings / Security settings / Local Policies / Use Rights ~ / Lock Pages in Memory.

And added the account used to start the SQL Server Service.


The wait type CXPACKET is no biggie. Its the time spend re-assembling the results from parallel queries.

You can change the parameter ‘Max Degree of Parallelism’, to however many cores your big-queries should be shared among. Best practice is the number of core’s in a single numa (max 8).

You can also change the ‘Cost Threshold for Parallelism’ to set the trigger level for big-queries. The default (‘5’) is usually a bit low.

Removing unwanted datafiles

Many people are too precious about scripts. Here’s my simple GUI-based method for removing unwanted datafiles.

1a) fire up the shrink gui (tasks / shink / files)
1b) locate your target datafile
1c) run the 3 ‘shrink actions’ in order, one at a time (2nd one to ‘1’)

2a) Fire up the Database properies files tab
2b) highlight the unwanted datafile, click ‘remove’, and then ‘ok’

How to limit sql-job notifications by time

Rolling my own Notifications – I wanted a sql-job that ran every 5 minutes to Notify me when it failed – but not to “spam” me every 5 minutes until I fixed it!

Here’s the simplified last-job-step that would email me the first time the job failed, but then NOT again for half an hour.

declare @email varchar(500), @operator varchar(50)
set @operator = 'DBA'

if (select datediff(mi, max(send_request_date), getdate()) 
 from [msdb].[dbo].[sysmail_mailitems]
 where subject like 'ALERT%') > 30
 set @email = 'execute msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''ALERT: [XX] failed '',
 @body=N''The [XX] sql-job on server [YY] has failed!'''
 select 'email sent'

Backup Status

Here’s my script that estimates when a running backup will finish …

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle))) [CommandText]
FROM sys.dm_exec_requests r WHERE r.session_id > 50
--AND CONVERT(NUMERIC(6,2),r.percent_complete) > 0

Alert on low disk space

Here’s a script I wrote for SQL2008R2, to run hourly in a sql-job, and email me if the percentage of free disk-space is getting low.

-- space_alert.sql

     DECLARE @trig int = 40, -- percentage free
		@operator varchar(50) = 'DBA', -- operator with email address
		@drive varchar(50), @free varchar(50), @email varchar(300)

-- capture free-space to temp-table

	DROP TABLE #space ------------testing
     select volume_mount_point drive, cast(sum(available_bytes)*100 / sum(total_bytes) as int) Free
     into #space
     from sys.master_files f
     cross apply sys.dm_os_volume_stats(f.database_id, f.[file_id])
     group by volume_mount_point

-- loop through the table row-by-row

     while (select count(*) from #space) > 0
          set @drive = (select top 1 drive from #space order by drive)
          set @free = (select top 1 free from #space order by drive)

-- and send email if space low

          if @free < @trig
               set @email = 'EXECUTE msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''Space Alert (' + @@servername + @drive + @free + '%)'',@body=N''On '+ @@servername  + ' the Volume ' + @drive + ' has only ' + @free + '% free space.'''

-- then remove current line from table

          delete from #space where drive = @drive

NOTE: I am using @@servername as there are only default instances here.

Try/Catch and Transactions

Try/Catch-blocks and Transaction-Blocks really don’t play nice together. Within nests for example an outer @@ROLLBACK TRANSACTION will rollback both inner and outer transaction-blocks regardless of already executed COMMIT TRANSACTION statements.

This is the most robust template I could come up with for nested Try/Catch-blocks that contain Transactions.

(inner DDL statements)
begin transaction
(inner DML statements)
commit transaction

(inner error capture statements)
rollback transaction
(outer DDL statements)
begin transaction
(outer DML statements)
while @@trancount > 0 commit transaction

(outer error capture statements)
while @@trancount > 0 rollback transaction

Configuring DAC

There is another definition of DAC in SQL Server – but this one is about the emergency DBA connection method, used when the server it too busy to get onto any other way.

DAC can be a life saver but has to be configured in advance. Luckily its a two minute job and I do it on all Production servers. and here’s how …

– Right click on server-name (in SSMS Object explorer), and choose facets.
– when the Facets box pops up select Surface Area Configuration in the top box.
– in the lower box set RemoteDacEnabled to True, and save with OK.

** TO USE IT **

– startup SSMS and ensure nothing is connected.
– click on “New Query”
– in the “Connect to Server” box prefix the Server Name with “admin: ”
– complete Authentication and click “Connect”

You should now be connected within a minimal safe-mode type environment. That is, with a blank Object Explorer pane.

** UPDATE 05 Mar 2015 **

To enable DAC on multiple servers (2005 and above) programmatically (via Registered Servers) use this …

Use master
sp_configure 'remote admin connections', 1

Using OUTPUT to simplify the auditing of data changes.

Here’s an example of using OUTPUT to simplify the recording of updates etc …

-- create the audit log
create table audit_table (fname varchar(50), colour varchar(50), 
old_tasty int, changed smalldatetime, new_tasty int)

-- create and populate the fruit table
create table fruit (fname varchar(50),colour varchar(50),tasty int)
insert into fruit values ('banana','yellow',6),('kiwi','green',3),('gooseberry','green',2)

-- change score to 'yuk' for any green fruits
update fruit set tasty = 1
output deleted.*, getdate(), inserted.tasty into audit_table
where colour = 'green'

-- check audit log
select * from audit_table

… and another nail in the coffin of Triggers 🙂

Log Shipping is simply the best!

For robust High Availability and/or Disaster Recovery projects Log-Shipping is my ALL TIME FAVORITE option.

Log-Shipping is the idea that you take your -already there- backups and restore them on any number of other SQL Servers. This means it works with all versions of SQL Server, and all edition.

There are no overheads on the production server that is being protected and the standby server(s) are fully open to use between restores. For example you could schedule restores to a reporting-server out of hours.

Compared to the complexity, restrictions, over-head, and manageability of other more fashionable HA/DR options its a “no-brainer” for me.

For example Mirroring, is only one prod-server to one (read-only) standby server, Replication is high maintenance and flattens referential-integrity, and AlwaysOn Availability Groups is difficult to setup correctly and near-impossible to troubleshoot.


** UPDATE ** It is best to log ship between the same versions of SQL Server.

SQL 2005 to SQ 2008 for example will look like its worked, but all restores will be skipped *Unless you use no-recovery mode*.

Script to examine SQL Server 2005/08 logins

Adapting my previous “Script to examine SQL Server 2000 logins” for an upcoming SQL-2005 audit 🙂 …

-- sp_logins_report.sql
use dba

begin try drop table #rolemember, #dbnames, #report END TRY
BEGIN CATCH END CATCH -- ignore errors

declare @counter int
declare @dbname varchar(50)
declare @sqlstr nvarchar(4000)

--make table to hold database, user-define roles & user-names
create table #rolemember
rm_servername varchar(50) default @@servername,
rm_dbname varchar(1000),
rm_rolename varchar(1000),
rm_username varchar(1000),
rm_userid varchar(1000)

--make table to hold database names
create table #dbnames
(dbid int identity(1,1),
db_dbname varchar(50))

--make table to accumulate report
create table #report
re_servername varchar(50),
re_dbname varchar(1000),
re_rolename varchar(1000),
re_username varchar(1000),
re_userid varchar(1000)

--get members of each server role
insert into #rolemember (rm_rolename, rm_username, rm_userid)
exec dbo.sp_helpsrvrolemember

--get database names
insert into #dbnames (db_dbname)
select '[' + name + ']' from master.dbo.sysdatabases
where version > 0  -- online
set @counter = @@rowcount

--loop through databases to get members of database roles and user-defined roles
while @counter > 0

	--get database name from #dbnames table
	set @dbname = (select db_dbname from #dbnames where dbid = @counter)

	--get members of each database and user-defined role
	set @sqlstr = 'insert into #rolemember (rm_rolename, rm_username, rm_userid)
	exec ' + @dbname + '.dbo.sp_helprolemember'
	exec sp_executesql @sqlstr

	--update database name in rolemember table
	update #rolemember
	set rm_dbname = @dbname
	where rm_dbname is null

	set @counter = @counter - 1

--put data into report table
insert into #report
select rm.* from #rolemember rm
left join #report re
on rm.rm_username = re.re_username
and rm.rm_dbname = re.re_dbname
and rm.rm_rolename = re.re_rolename
and rm.rm_servername = re.re_servername
where re.re_servername is null

--display report
select re_username UserName, re_rolename RoleName, re_dbname DBName, re_servername ServerName
from #report
where re_username != 'dbo'
--order by re_username --display by user
order by re_rolename --display by role
--order by re_dbname --display by database

TransactionLog sizes

Here’s a nice script I like to run on all registered servers (at once) to show the sizes of the log-files …


create table #temp(
dbname varchar(100),
logsize_mb real,
spaceused_pct real,
dbstatus int)
insert #temp exec ('DBCC SQLPERF (LOGSPACE)')

select dbname, logsize_mb
from #temp
where logsize_mb > 1000
order by logsize_mb desc

drop table #temp

Moving Logins

After migrating a database to another server use the system-procedure ‘sp_help_revlogin’ (on the source) to create scripts of all logins. Including PASSWORDS of all sql-logins (then run it on the target) …

exec sp_help_revlogin --all
exec sp_help_revlogin 'somelogin' -- just for 'somelogin'

Orphaned users

Here’s my crib-sheet for finding and dealing with orphaned users …


-- SOURCE = 
-- TARGET = 

-- step-1: list orphaned users in current db
EXEC sp_change_users_login 'Report';

-- step-2: map user to login - if they both exist
EXEC sp_change_users_login 'update_one', 'some_user', 'some_login';

-- step-3: copy login, then step-2, then step-1
-- (NOTE: execute this on the source, then execute the output on the target)
'Create Login ' + QUOTENAME( 
+ ' With Password=' + CONVERT(varchar(max), A.password_hash, 1) + ' hashed'		--script out the passwrod
+ ', DEFAULT_DATABASE=' + quotename(A.default_database_Name) --if you apply the scrip to a mirroed server or log-shipped server user master database, as user database is not in useable state
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
+ ', CHECK_POLICY=' + Case A.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End 
+ ', CHECK_EXPIRATION=' + Case A.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1)		--script out the SIDs
 As SQLLogin
sys.sql_logins A
Where Not like '##%##'  --remove those system generated sql logins
And A.sid != 0x01 --SA sid is always same
and QUOTENAME( = '[' + 'some_login' + ']'

-- Extra

-- list USERS (current server/db)
SELECT * FROM sys.database_principals where type in ('S', 'U') order by 1

-- list LOGINS (current server/db)
SELECT * FROM sys.server_principals where type in ('S', 'U') order by 1

Database sizes

Although I already have one – here’s a neat script to show all database sizes …

select, round(sum(convert(bigint, mf.size)) * 8 /1024 /1000 ,0) [SizeGB]
from master.sys.master_files mf
inner join master.sys.databases db
on db.database_id = mf.database_id
where db.database_id > 4
group by
--order by
order by [SizeGB] desc

Scheduling CHECKDB

I modified my backup-all script to schedule a DBCC CHECKDB run on ‘most’ databases. Then I augmented it further – to quit if still running at 18:30 …

DECLARE @dbname VARCHAR(100)
declare @return int
set @return = 0

SELECT name FROM master.dbo.sysdatabases
where dbid > 4 -- exclude system databases
and name not in ('somedatabasename')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE (@@FETCH_STATUS = 0) AND (@return = 0)
       if getdate() > '01 feb 2012 18:20' set @return = 1
       DBCC CHECKDB (@dbname) with PHYSICAL_ONLY
       FETCH NEXT FROM db_cursor INTO @dbname

CLOSE db_cursor
DEALLOCATE db_cursor

Scripted full backup of all databases.

I had a requirement to script full backups and this was the simplest code I could find …


DECLARE @fileName VARCHAR(256)

SET @path = N'H:\UpgradeBackups\'

SELECT name FROM master.dbo.sysdatabases
WHERE name  'tempdb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

       SET @fileName = @path + @dbname + '_full.Bak'
       BACKUP DATABASE @dbname TO DISK = @fileName with init, noskip, noformat, nounload, stats=10, checksum
       FETCH NEXT FROM db_cursor INTO @dbname

CLOSE db_cursor
DEALLOCATE db_cursor

When was CheckDB last run?

There’s a whole bunch of scripts out there that show the history of when dbcc checkdb was last run. Many are inaccurate. Here’s the one I use …

CREATE TABLE #DBInfo_LastKnownGoodCheckDB
		ParentObject varchar(1000) NULL,
		Object varchar(1000) NULL,
		Field varchar(1000) NULL,
		Value varchar(1000) NULL,
		DatabaseName varchar(1000) NULL

SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')

OPEN csrDatabases

	@DatabaseName varchar(1000),
	@SQL varchar(8000)

FETCH NEXT FROM csrDatabases INTO @DatabaseName

	--Create dynamic SQL to be inserted into temp table
	SET @SQL = 'DBCC DBINFO (' + CHAR(39) + @DatabaseName + CHAR(39) + ') WITH TABLERESULTS'

	--Insert the results of the DBCC DBINFO command into the temp table
	INSERT INTO #DBInfo_LastKnownGoodCheckDB
	(ParentObject, Object, Field, Value) EXEC(@SQL)

	--Set the database name where it has yet to be set
	UPDATE #DBInfo_LastKnownGoodCheckDB
	SET DatabaseName = @DatabaseName
	WHERE DatabaseName IS NULL

FETCH NEXT FROM csrDatabases INTO @DatabaseName

--Get rid of the rows that I don't care about
DELETE FROM #DBInfo_LastKnownGoodCheckDB
WHERE Field != 'dbi_dbccLastKnownGood'

SELECT Distinct
	Value  as LastGoodCheckDB--,
	--DATEDIFF(dd, CAST(Value AS datetime), GetDate()) AS DaysSinceGoodCheckDB,
	--DATEDIFF(hh, CAST(Value AS datetime), GetDate()) AS HoursSinceGoodCheckDB
FROM #DBInfo_LastKnownGoodCheckDB
ORDER BY DatabaseName

DROP TABLE #DBInfo_LastKnownGoodCheckDB

Scripted Notification

Here’s an interesting (ActiveX) SQL Job-Step. It can be used to send an email should the usual methods not be available.

Function Main()
	Main = DTSTaskExecResult_Success

Set objEmail = CreateObject("CDO.Message")
objEmail.From = ""
objEmail.To = ""
objEmail.Subject = "FAILED JOB: name SERVER: name"
objEmail.Textbody = "FAILED JOB: name SERVER: name"
objEmail.Configuration.Fields.Item ("") = 2
objEmail.Configuration.Fields.Item ("") = ""
objEmail.Configuration.Fields.Item ("") = 25
set objEmail = nothing
End Function

Where the ip-address ( is the local email server.

Working with xp_cmdshell

Most production systems these-days have xp_cmdshell disabled as a security precaution, rightly so. If you NEED to execute an operating-system command best to use this sort-of routine …


--	DECLARE @cmd varchar(100) = 'shutdown /r'
--      DECLARE @cmd varchar(100) = 'shutdown -r -f -t 0'
	DECLARE @cmd varchar(100) = 'Dir'

		-- DEBUG ... is xp_cmdshell enabled?
		SELECT case when value_in_use = 1 then 'YES' else 'no' end [is CMDSHELL enabled]
		FROM sys.configurations
		where name = 'xp_cmdshell'

IF (SELECT value_in_use /* cmd shell is disabled */
	FROM sys.configurations
	WHERE name = 'xp_cmdshell') = 0
	exec sp_configure 'show advanced options', 1 reconfigure -- show advanced options
	exec sp_configure xp_cmdshell, 1 reconfigure -- enable command-shell
	exec xp_cmdshell @cmd -- run the command
	exec sp_configure 'xp_cmdshell', 0 reconfigure -- disable command-shell
	exec sp_configure 'show advanced options', 0 reconfigure  -- hide advanced options
ELSE /* cmd shell is enabled */
	exec xp_cmdshell @cmd -- just run the command

		-- DEBUG ... is xp_cmdshell enabled?
		SELECT case when value_in_use = 1 then 'YES' else 'no' end [is CMDSHELL enabled]
		FROM sys.configurations
		where name = 'xp_cmdshell'

(NOTE: executing sp_configure by itself will show the current settings)

Moving SSIS packages

To copy IIS packages that are stored in MSDB from PROD to UAT I connected to the ‘Integration Services’ object of both servers within SSMS.

To ‘pull’ the package from PROD into UAT I first drilled down both trees (EG: server stored packages msdb ) to see which folders were missing.

The first one was called ‘Press’ so I right-clicked on the UAT msdb icon  and created a new folder with that name.

I right-clicked on the new folder and chose ‘import package’. This opened a config-box, within which I just filled in the ‘server:’ and Package path:’ values. The first contained the name of the PROD server (including instance).

The ‘package path’ box contained one parent folder (‘SSIS Packages’) and subfolders for each package. I opened the ‘Press’ folder and selected the package within. Once I clicked ‘ok’ twice the package was copied over.

Mirroring of BESMgmt from London to Paris using SQL Server 2008 r1 Std


  • First I changed the recovery model of the BESMgmt database to use the Full Recovery Model.
  • To manage the BESMgmt Tlog files generated from this I created a Maintenance Plan to backup the Tlogs every 6 hours to the E:Backups folder. These will be automatically deleted after two weeks (NOTE: The size of the TLogs can be reduced by increasing the backup frequency).
  • I reinstalled SQL Server 2008+SP1 on Paris ensuring that …
  •           The SQL service account used was SVC_SQL
  •           The SQL agent account used was SVC_AGT_SQL
  •           The collation was set to LATIN1_GENERAL_CI_AS
  •           The default location for datafiles was set to S:DATA
  •           The default location for logfiles was set to T:LOGS
  •           TempDB was located on the U-Drive

– Minimised potential connectivity delays by …

o Setting up Paris as a linked-server on London
o Ensuring the SQL Browser Services was active and working on both servers


To initiate mirroring I …
– Backed up the BESMgmt database and restored it on Paris with no recovery
– Backed up the TLOG and restored it on Paris with no recovery
– Configured Mirroring of BESMgmt on London GUI without a witness.


There are three methods of failing over / swapping the Mirror-Live (Principal) and Mirror-Standby (Partner) roles.
1. There is a ‘Failover button’ on the Principal SSMS GUI mirror-configuration screen. This should be used for testing, and for planned maintenance of the Mirror-Live server.
2. I believe ‘BES Manager’ can be configured to automatically failover the database. This should be the normal way failover occurs.
3. A sql-command can be executed on the Mirror-Partner as a last-ditch option should the Mirror-Live server become unavailable ( IE: alter database besmgmt set partner force_service_allow_data_loss). Data loss may occur.


The Mirror Monitor (screen within SSMS) can be viewed from either server by right-clicking the BESMgmt database and choosing Tasks / Launch database Mirroring Monitor. I have initiated mirror warnings within this tool which will log an error to the application log if Mirroring becomes slow.

Removal of SQL Server 2008 R1

The day after I installed two instances of SQL Server 2008 R1 on my old Windows-2003 test-server I decided to remove them, here’s what I did …

– rebooted 😉
– start/ settings/ control panel/ add or remove programs/ Microsoft SQL 2008/ remove
– from the instance screen I chose the last instance installed
– from the component screen I ticked All Components
– rebooted and repeated the above steps for the other instance
– used the Add or Remove screen to remove the SQL 2008 browser then native client
– removed the base folders for the two instances from my c-drive
– used Start/Search to find any other folders modified yesterday (happily there were none)
– rebooted then fired up SQL Server 2005 to test it still worked – no problems 🙂

An example of a sql Restore

An example of restoring database Liath using sql-script.

If database exists make it single-user-mode

Alter database liath set single_user with rollback immediate

If possible backup the tail IE: unbacked up data

Backup log liath to disk = N’\’ with init, norecovery

Restore the last full backup EG:-

Restore database liath from disk = N’\’ with norecovery

Restore the latest differential backup EG:-

Restore database liath from disk = N’\’ with norecovery

Restore all the transactional logs in sequence from the last diff backup EG:-

Restore log liath from disk = N’\’ with norecovery

Restore log liath from disk = N’\’ with norecovery

Restore the final tail backup

Restore log liath from disk  = N’\’ with recovery

Change database to mult-user mode

Alter database liath set multi-user

Remove Completely Unused Indexes

Here’s a great little script I found that lists indexes in the current database with no stats at all. That is, indexes that have never been read or even written to (since the last reboot, note). I’m not sure how much overhead these will have on the system – but it’s probably worth clearing them out anyway.

From the results I wouldn’t delete the ‘system’ indexes (that is ones starting with ‘MS~’ or ‘sys~’ and the like), or Clustered Indexes 🙂

I was about to work-up a script to delete them, then (phew) remembered … “dba-rule #23: never automate deletes”

SELECT object_name(i.object_id) as tableName, as indexName
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = db_id()
WHERE objectproperty(i.object_id,'IsUserTable') = 1 and i.index_id> 0
AND s.object_id IS NULL
AND i.is_Primary_Key = 0
AND i.is_unique_constraint = 0
AND i.is_unique = 0
--DROP INDEX [index-name] ON [dbo].[table-name]

Record db Growth

To get my own metrics in place quickly at a new sql2008r1 site I created a ‘dba’ database with a table called ‘dbsizes. Then a job scheduled to run every 10pm with the first step called ‘record sizes’ containing this code …

set nocount on

create table #sizes (
name varchar(50),
db_size varchar(50),
[owner] varchar(50),
[dbid] char(1),
created smalldatetime,
[status] varchar(500),
compatability_level char(2))

insert #sizes exec sp_helpdb

insert into dba.dbo.dbsizes
select name, getdate() dbdate, db_size
from #sizes
order by name desc

drop table #sizes

Simple-Mode to Full-Recovery-Mode script

Leading on from my script to list databases set to Simple recovery-mode, I found changing them to Full was not as easy as just changing the sys.databases flag from ‘3’ to ‘1’ (not allowed).

I had a requirement to change a whole server full of databases, but due to our ‘smart’ backup software (that on discovering new Full recovery-mode databases would immediately want to take full backups), had to stagger the changes to one-an-hour – so the (production) box wouldn’t slow to a crawl.

Here’s the job-step …

--capture name of one simple database
declare @name varchar(50)
select @name = [name] from sys.databases where recovery_model = 3 and [name] not in ('master', 'msdb', 'tempdb')
-- 1 = Full, 3 = Simple

--change it to full
declare @cmd varchar(200)
set @cmd = 'alter database ' + @name + ' set recovery full'
exec (@cmd)

When were the last Full, Diff, & Log Backups?

My all-time favorite script is this cross-tab script showing the latest backups of all databases on the current box …

max(case bst.[type] when 'D' then convert(varchar(16), bst.backup_finish_date, 120) end) last_full_backup,
max(case bst.[type] when 'I' then convert(varchar(16), bst.backup_finish_date, 120) end) last_diff_backup,
max(case bst.[type] when 'L' then convert(varchar(16), bst.backup_finish_date, 120) end) last_log_backup,
case sdb.[status]
when '0' then 'Full'
when '4' then 'Bulk-logged'
when '8' then 'Simple'
when '16' then 'Full'
when '24' then 'Simple'
when '32' then 'Restoring'
when '528' then 'Off_line'
when '536' then 'Off_line'
when '1048' then 'Read_only'
when '2072' then 'Restricted User'
when '65536' then 'Full'
when '65544' then 'Simple'
when '2098176' then 'Standby / Read-Only'
when '4194328' then 'Simple'
when '4259848' then 'Simple'
when '1073741840' then 'Full'
when '1073807360' then 'ShutDown'
else '' end db_status, sdb.[status]
from master..sysdatabases sdb
left join msdb..backupset bst on bst.database_name =
where != 'tempdb'
group by, sdb.[status]
--order by
order by max(bst.backup_finish_date) -- nulls at top
--order by sdb.[status]

Script to help manually resize datafiles.

Here’s a great script I use when manually resizing datafiles. It lists the percentage of free space and suggest a new size that would have 60% free.



   [dbname]   sysname,
   lname    sysname,
   usage    varchar(20),
   [size]   decimal(9, 2) NULL ,
   [used]   decimal(9, 2) NULL

IF OBJECT_ID('tempdb..#temp_log') IS NOT NULL
   DROP TABLE #temp_log

CREATE TABLE #temp_log
   DBName          sysname,
   LogSize         real,
   LogSpaceUsed    real,
   Status          int

IF OBJECT_ID('tempdb..#temp_sfs') IS NOT NULL
   DROP TABLE #temp_sfs

CREATE TABLE #temp_sfs
   fileid          int,
   filegroup       int,
   totalextents    int,
   usedextents     int,
   name            varchar(1024),
   filename        varchar(1024)

DECLARE @dbname sysname
       ,@sql varchar(8000)

IF OBJECT_ID('tempdb..#temp_db') IS NOT NULL
    DROP TABLE #temp_db

SELECT name INTO #temp_db
   FROM master.dbo.sysdatabases
   WHERE DATABASEPROPERTY(name,'IsOffline') = 0
   AND has_dbaccess(name) = 1
   ORDER BY name

WHILE (1 = 1)
   SET @dbname = NULL

   SELECT TOP 1 @dbname = name
   FROM #temp_db
   ORDER BY name

   IF @dbname IS NULL
      GOTO _NEXT

   SET @sql = ' USE [' + @dbname + '] 

      TRUNCATE TABLE #temp_sfs

      INSERT INTO #temp_sfs

      INSERT INTO #DBSTATS (dbname, lname, usage, [size], [used])
         SELECT db_name(), name, ''Data''
         , totalextents * 64.0 / 1024.0
         , usedextents * 64.0 / 1024.0
         FROM #temp_sfs

      INSERT INTO #DBSTATS (dbname, lname, usage, [size], [used])
         SELECT db_name(), name, ''Log'', null, null
         FROM sysfiles
         WHERE status & 0x40 = 0x40'


    DELETE FROM #temp_db WHERE name = @dbname



--select * from #Temp_Log

   SET SIZE = B.LogSize
   , USED = LogSize * LogSpaceUsed / 100
    ON (A.DBNAME = B.DBNAME)AND(A.Usage = 'LOG')

SELECT dbname AS [database name],
   lname AS [file name],
   [size] AS [space allocated (MB)],
   [size]*1.6 as 'plus 60% (MB)',
-- used AS[space used (MB)],
-- [size] - used  AS [free space (MB)],
-- cast(used/[size]*100 AS numeric(9,2)) AS [space used %],
   cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %]
ORDER BY [free space %] --dbname, usage

DROP TABLE #temp_db
DROP TABLE #temp_sfs
DROP TABLE #temp_log


Script to return the SQL Server service-account


-- return sql-server-service-account used by the default-instance
DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
@ServiceAccount OUTPUT,
SELECT @Serviceaccount ServiceAccount_DefaultInstance

-- ditto for named-instance
DECLARE @serviceaccount2 varchar(100)
exec master.dbo.xp_instance_regread
N'SystemCurrentControlSetServicesMSSQL$sqlins01', -- edit
@ServiceAccount2 OUTPUT,
SELECT @Serviceaccount2 ServiceAccount_NamedInstance

-- and for named-agent
DECLARE @agentaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'SYSTEMCurrentControlSetServicessqlagent$sqlins01', --edit
@agentaccount OUTPUT,
select @agentaccount ServiceAccount_NamedAagent

Orphaned Indexes

Our ‘check indexes’ job was failing occasionally with an error like …

Msg 7999, Level 16, State 40, Line 1 Could not find any index named ‘index_name’ for table ‘table_name’.

I tried to just exclude the named index, but a few days later another one appeared. The fix was to dig into the SP and add a condition to the statement …

insert into #indexlist
            select name
	    from sysindexes
	    where name not like '_WA%'
	    and indid not in (0,255)
	    and [root] is not null
	    and id = @tab_id