Category: MS SQL 2000

Set every users default schema to DBO where its blank

In this quick script I am assuming the Windows domain is called ‘DOM’ …

-- ChangeDefaultSchemaToDboWhereNull.sql

DECLARE @cmd varchar(1000) 

SET @cmd = 
'USE ? IF DB_ID(''?'') > 4 SELECT ''USE ?; ALTER USER ['' + name + ''] WITH DEFAULT_SCHEMA = [dbo]''
 FROM sys.database_principals
 WHERE default_schema_name IS NULL
 AND [name] LIKE ''DOM\%'''

IF OBJECT_ID('tempdb..#output') IS NOT NULL DROP TABLE #output
CREATE TABLE #output
(command varchar(1000))

INSERT INTO #output
EXEC sp_MSforeachdb @cmd

SELECT * 
FROM #output

Searching every Procedures for a string.

To search every stored procedure in every database on every server (ranging from SQL Server 2005 to SQL Server 2016) for the string ‘QueryTraceOn’, I first registered every server within SSMS.

Right-clicking on the registered server folder, I chose ‘new query’ and ran ‘select 1’ to exclude from my list any server with issues.

Once I had an error free list, I ran this code (which took around 40 minutes) …

-- SearchProcs4String.sql

EXEC sp_MSforeachdb 'use ?
SELECT db_name() [Database], ROUTINE_SCHEMA + ''.'' 
+ ROUTINE_NAME [Proc]
FROM INFORMATION_SCHEMA.ROUTINES WITH (NOLOCK)
WHERE ROUTINE_DEFINITION LIKE ''%QUERYTRACEON%'';'

More thoughts against Triggers

For Rob & Karl – Triggers run outside of transactions. An insert that fires a trigger may be rolled back, but the trigger rolls on.

Triggers introduce a long-term maintenance headache. You can read a stored-procedure from top to bottom and imagine you understand what it does. But unless you examine every tables it touches – you don’t. Little bits of code may be running silently which augment or even reverse some of the logic within the stored-procedure.

Triggers are used by lazy developers to ‘bolt on’ new features to applications, rather than track-down all the code that could insert/update/delete from a table and add the code (or a link to it) there.

This would be forgivable if the application code was closed or propitiatory, but never when the application is open to the application developer, who just cannot be bothered to integrate code changes properly, and cares not-a-jot about long-term maintenance headaches (slow breaths, slow breaths :))

Set Default Autogrowth

Here’s another one of my global changes. This one sets-up ‘Model’, which is the template for all databases created in the future.

-- AutogrowthDefault.sql

-- first examine the current settings
select Name, size*8/1024 [SizeMB], case is_percent_growth 
	when 1 then convert(varchar(50), growth) + ' %' 
	when 0 then convert(varchar(50), growth*8/1024) + ' MB' end AutoGrowth
from master.sys.master_files
where db_name(database_id) = 'Model'
order by [type];

----set Initial-Size to best-practice
ALTER DATABASE [model] MODIFY FILE (NAME = N'modeldev', SIZE = 256MB);
ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', SIZE = 128MB);

---- set Autogrowth to best-practice
ALTER DATABASE [model] MODIFY FILE (NAME = N'modeldev', FILEGROWTH = 256MB);
ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', FILEGROWTH = 128MB);

Migrate SQL 2000 to SQL 2012

Luckily there was a shiney new (virtual) server waiting to be the backend for this customers myriad in-house applications.

The live production server was Standard edition SQL 2000, so I installed SQL Server 2008r2 on my laptop and SQL2012 on the new box – both ‘Standard editions’.

My plan …

– Tidy-up by removing dev and unused databases.
– Run SQL2008r2 upgrade-advisor against the SQL2k box & correct issues
– Backup all SQL2k user databases & drag to SQL2008 box
– Restore them (triggering an auto-upgrade)
– Change compatability levels from 80 to 100

– Run SQL2012 upgrade-advisor against SQL2008r2 box & correct issues
– Backup all SQL2008 user databases & drag to SQL2012 box
– Restore them (triggering an auto-upgrade)
– Change compatability level from 100 to 110
– DBCC CHECKDB WITH DATA_PURITY;
– EXEC sp_MSForEachDB ‘DBCC UPDATEUSAGE(?);’;

– Capture and apply SQL2k logins to SQL2012
– Rewrite 2x DTS packages as SSIS
– Apply SQL 2012 (SP2)
– Backup all SQL2012 databases

I was able to get one application (and Reports) re-pointed to the new backend immediately, however the others look ages.

Some of the ‘fun’ issues moving individual applications were … Some had links to a remote Oracle database necessitating Oracle 64bit client installation. Many had old reporting issues.

To keep the SQL2012 data up to date during the ‘fun’, I ran the SSIS wizard from SQL2012 and pulled data directly from SQL2k.

Notification Test

Notification emails telling a DBA that something failed are all-well-an-good, but if you don’t get any emails, how can you be sure the notification system is working?

The simplest solutions are often the best. I’ve taken to scheduling an 8am job (on every SQL Server that runs jobs) called ‘Notification Heartbeat’ that contains just an empty step. It’s own notification configuration is set to email the ‘DBA’ operator every time it succeeds.

Of course, I then have an Outlook rule that moves them (all with ‘Heartbeat’ in the subject), to the ‘DBA’ folder so I dont have to read them every day.

Now when my morning inbox is suspiciously empty – I have a way to verify that the Notification system was tested end-to-end at 8am.

NOTE: I used to try combining the notification-test with actual working Jobs like Backups, but this is simpler and the message value is clearer.

Database Compare

Here’s a neat script I wrote to list every database with the total number of rows in it.

The idea was to do a quick-n-dirty post-migration checksum between the old sql2000 and new sql2012 databases – to see which ones needed updating.

--database_compare.sql

EXECUTE sp_MSforeachdb 'use ?; DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;'

create table #temp (DBName varchar(100), TotalRows int)
exec sp_msforeachdb 'insert into #temp select ''?'', sum(rows) 
from [?].[dbo].sysindexes i join [?].[dbo].sysobjects o on o.id=i.id
where indid < 2 and type=''U'''

select * 
from #temp
where DBName not in ('master','model','msdb','tempdb')
order by 1--2

drop table #temp

Is that database used?

Before an upgrade I wanted to remove any unused databases. To help find them I created a generic sql-job that every 10 mins would log connections.

I say ‘generic’ as the job needed to work on SQL2000, SQL2005, and SQL2012. So I created it using SQL-2000 enterprise-manager and scripted it out as ‘createJob_DbaAudit.sql’.

The script in job-step-1 (‘Build Table – if needed’) was …

select	@@ServerName AS Server, 
	db.[name] [Database],
	getdate() [TimeStamp], 
	Hostname, 
	Program_name, 
	Net_library, 
	Loginame
into master.dbo.tbl_connections
from master.dbo.sysdatabases db
join master.dbo.sysprocesses pr on db.dbid = pr.dbid
where db.dbid > 4

… this step was set to continue on to the next-step regardless of whether it succeded or failed.

Step-2 (‘Record Connections’) was similar …

insert into master.dbo.tbl_connections
select	@@ServerName AS Server, 
	db.[name] [Database],
	getdate() [TimeStamp], 
	Hostname, 
	Program_name, 
	Net_library, 
	Loginame
from master.dbo.sysdatabases db
join master.dbo.sysprocesses pr on db.dbid = pr.dbid
where db.dbid > 4

Final notes: To be robust and generic, I did not set-up Notifications. Also, I added a few lines to the top of the ‘CreateJob_DbaAudit.sql’ script to delete the tbl_connections table from master if it already existed.

How to Start SQL Server Agent via Management Studio

I could not remote onto this customers database-server so looked at my local machines and found the ‘short name’ for the SQLAgent service was ‘SQLAgent$sql2014’.

I recognized the last bit (‘~sql2014’) as my local instance so was able to surmise this code would work for the customer (if there instance was called ‘SomeInstanceName’).

exec xp_cmdshell ‘net start SQLAgent$SomeInstanceName’

** UPDATE **
When a colleague locked up a dev server I was able to free it by restarting the service like this …

xp_cmdshell ‘net start’ — to get list of services

xp_cmdshell ‘net stop “SQL Server Agent (DEV2008r”)”‘ — to stop the agent service in preparation for the next step
xp_cmdshell ‘net stop “SQL Server (DEV2008r2)”‘ — to stop the SQL Server Service NOTE: double-quotes

xp_cmdshell ‘net start “SQL Server (DEV2008r2)”‘
xp_cmdshell ‘net start “SQL Server Agent (DEV2008r2)”‘

Notepad++ and hidden characters

Notepad++ is great for finding patterns of text spread over several lines as you can include hidden characters in the search-string (like tab, new-line, etc).

But WATCH OUT!! if you leave a single new-line without a partner carriage-return SQL scripts will miss-behave intermittently.

The FIX – When you’ve completed whatever you’re doing – click Format / Convert to UNIX, followed by Format / Convert to Windows. Then save 🙂

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.

ss11

** 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*.

Simple Log-shipping of ‘Actions’ database from London to Paris using SQL2000 Std

SOURCE

All the code below is freely available on the internet. I first saw it in the SQL2000 SDK from where it has been improved many-many-times-over. Rather than rehash the implementation here is my handover notes.

OVERVIEW OF THIS PROJECT

Transaction-logs (Tlogs) from the London database ‘Actions’ are backed up every hour to a warm standby in Paris and restored there.

DETAILS

Hourly a SQL Job in London (‘Log ship Incidents to Paris’) executes a stored procedure (‘sp_ShipLogOnce’) that backs up the Tlog directly to Paris (to E:Backups shared as ‘Backup$’) then runs the Paris SQL Job (‘sp_ApplyStandByLog’) that restores it.

MAINTENANCE & LIMITATIONS

There is a Windows batch file on Paris (‘PurgeFilesExceptRecent.bat’) that will delete old Tlogs every 2am. It has been initially set to ignore the newest 720 (being one per hour for 30 days).

Logins on Paris need to be regularly updated from London (at least monthly). The stored procedure ‘sp_help_revlogins’ will script out the London logins, from which any new, sql, non-svc, entries should be added to Paris.

The Tlogs need to be restored in strict order. After a temporary network failure all outstanding Tlogs can be restored by running this command at Paris …

C:ApplyStandbyLogs.bat E:Data S:Backups

Alternatively individual Tlog can be restored using the stored procedure …

exec Sp_ApplyStandByLog
@DBName=’Actions’,
@BackupFileName=’S:BackupsActions[name.trn]’,
@UndoFile=’T:DATAActions_undo.ldf’,
@MoveFileTo=’S:BackupsActionsRestored’

SWITCHING ROLES

  1. Disable the London LogShip job
  2. Apply final Tlog (sp_ShipLogOnce with @LocalUndoFile)
  3. Change Paris ‘Actions’ database to read/write, multi-user
  4. Enable the Paris LogShip job

DISASTER RECOVERY

If London is unavailable, only step-3 above is needed. IE:-

exec sp_dboption 'actions', 'dbo use only', 'false'
go
exec sp_dboption 'actions', 'read only', 'false'
go
restore database actions with recovery
go

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 …

--lastbackups.sql
select sdb.name,
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 = sdb.name
where sdb.name != 'tempdb'
group by sdb.name, sdb.[status]
--order by sdb.name
order by max(bst.backup_finish_date) -- nulls at top
--order by sdb.[status]

Script to return the SQL Server service-account

--service_accounts.sql

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

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

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

Search All Tables – script

--dba_SearchAllTables.sql
--Searches all columns of all tables for a given search string (4.47 mins)
--EG: exec dba_searchalltables 'patriqu%'

alter proc dba_SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
begin

	create table #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	set nocount on

	declare @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	set  @TableName = ''
--	set @SearchStr2 = quotename('%' + @SearchStr + '%','''')
	SET @SearchStr2 = quotename('' + @SearchStr + '','''')

	while @TableName is not null
	begin
		set @ColumnName = ''
		set @TableName =
		(
			select min(quotename(table_schema) + '.' + quotename(table_name))
			from information_schema.tables
			where table_type = 'BASE TABLE'
				and	quotename(table_schema) + '.' + quotename(table_name) > @TableName
				and	objectproperty(
						object_id(
							quotename(table_schema) + '.' + quotename(table_name)
							 ), 'IsMSShipped'
						       ) = 0
		)

		while (@TableName is not null) and (@ColumnName is not null)
		begin
			set @ColumnName =
			(
				select min(quotename(column_name))
				from information_schema.columns
				where table_schema = parsename(@TableName, 2)
					and	table_name = parsename(@TableName, 1)
					and	data_type in ('char', 'varchar', 'nchar', 'nvarchar')
					and	quotename(column_name) > @ColumnName
			)

			if @ColumnName is not null
			begin
				insert into #Results
				exec
				(
					'select ''' + @TableName + '.' + @ColumnName + ''', left(' + @ColumnName + ', 3630)
					from ' + @TableName + ' (nolock) ' +
					' where ' + @ColumnName + ' like ' + @SearchStr2
				)
			end
		end
	end

	select ColumnName, ColumnValue from #Results
end

GO

Kill all users

In a technical sense of course 🙂

Here’s the code – very handy before a restore …

DECLARE processes CURSOR FOR
  select spid from sysprocesses where dbid=(select dbid from sysdatabases where name='AdventureWorks') and status in ('runnable','sleeping')
DECLARE @processid int   -- spid of active processes

OPEN processes --open the cursor
 FETCH FROM processes INTO @processid
 WHILE (@@FETCH_STATUS = 0)
  --cycle through the list and kill each spid
 BEGIN 
  Print @processid
  Print 'killed'
  Exec ('
  kill '+@processid+'
  ')
 FETCH FROM processes INTO @processid
 END -- END OF WHILE (@@FETCH_STATUS = 0) 
CLOSE processes
DEALLOCATE processes

Find stored-procedure on current server

A SQL-Job was failing with Could not find stored procedure ‘RemovePushMsgs’.

I noticed the drop-down-value ‘database’ was missing from the job-step – which was odd. To confirm my suspicion that the database had been removed but the job forgotton I wrote the following script to search each and every database for the missing SP.

--find_sp.sql
-- to find stored-procedure names like '%RemoveM%' in all databases

create table #temp (name varchar(50), dbname varchar(50), xtype varchar(50))

exec sp_Msforeachdb "use [?];insert #temp select name, '?', xtype from sysobjects where name like '%RemoveM%'"

select name, dbname, case xtype
when 'P' then 'SP'
when 'S' then 'System Table'
when 'X' then 'XP'
when 'U' then 'Table'
when 'TF' then 'FunctionT'
when 'FN' then 'FunctionS'
when 'V' then 'View'
when 'IF' then 'FunctionI'
when 'D' then 'Default'
else xtype end [type]
from #temp
order by 1

drop table #temp

Script to list Simple-recovery-mode databases.

— simple_mode.sql
— script to list Simple-recovery-mode databases

declare @ver varchar(50)
set @ver = (select @@version)

— SQL 2000
if @ver like ‘%2000%’
select name from sysdatabases where status = ’24’ — simple, excluding tempdb

— SQL 2005/8
else
select name simple_mode from sys.databases where recovery_model = 3 — (3=Simple 1=Full)

Manually Resizing SQL files

The easy way to calculate what size of datafile would have 40% free …
– using the GUI choose Tasks / Shrink / Files
– if the percentage free is less than 40%
– find the last numeric value on the page. EG: “MB (Minimum is 998 MB)”
– multiply this value by 1.6. EG: 1593
– resize the datafile to 1593 or better, IE: 1600 MB

(btw, on this subject – my best-practice is to adjust the increment-size so there is about one ‘autogrowth’ each week).

What physical box am I on?

To find out the Windows Server Name that is currently running the live node use

begin try
exec xp_cmdshell 'ping -a localhost'
end try

begin catch
exec sp_configure 'show advanced options', 1 reconfigure -- to show the advanced options
exec sp_configure xp_cmdshell, 1 reconfigure -- to enable the command-shell
exec xp_cmdshell 'ping -a localhost' -- run a dos-command
exec sp_configure 'xp_cmdshell', 0 reconfigure -- to disable the command-shell
exec sp_configure 'show advanced options', 0 reconfigure  -- to hide the advanced options
end catch

SQL Server Auditing

After running the logins-report script (below) before an external audit, I wanted to ‘lock’ or ‘disable’ unused accounts for a month – just to confirm that they really were unused before deleting them 🙂

Unfortunatly SQL 2000 doesn’t have a facility to lock/disable sql accounts, so I changed the passwords to a known value. This looses the original p/w forever, but gives us more options than mearly deleting the accounts.

option-a inform the user of the new password.
option-b create a new account with the configuration of the ‘old’ account.

Windows logins (on the other hand) ARE lockable in SQL 2000. You use

exec sp_denylogin ‘domainuser’

and

exec sp_grantlogin ‘domainuser’

to unlock it (if required).

BTW: You need to use SQL 2000 Enterprise Manager to see locked Windows accounts, not SQL 2005/8 Management Studio.

UPDATE: watch-out for the situation where you disable a users individual account (as above), then make him a member of a group for access. Remember ‘Deny’ overrules ‘grant’.

Script to list dates of last Full backups

Script to list dates of last Full backups


--last_full_backup.sql
select sdb.name,
convert(varchar, max(bst.backup_finish_date), 113) last_full_backup
from master..sysdatabases sdb
left join msdb..backupset bst
on bst.database_name = sdb.name
left join msdb..backupmediafamily bmf
on bmf.media_set_id = bst.media_set_id
where sdb.name 'tempdb'
and bst.type is null -- never been backed up
or bst.type = 'D' -- or full
group by sdb.name
order by last_full_backup -- oldest dates at top
--order by sdb.name -- alphabetical by name