Database Consultant

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

--orphaned_users.sql

-- 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)
Select
'Create Login ' + QUOTENAME(A.name) 
+ ' 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
From 
sys.sql_logins A
Where A.name Not like '##%##'  --remove those system generated sql logins
And A.sid != 0x01 --SA sid is always same
and QUOTENAME(A.name) = '[' + '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

Here’s my crib-sheet of commands for dealing with ‘backup devices’ …

--devices.sql
USE master;
GO

--list
select * from sys.backup_devices

--create
--EXEC sp_addumpdevice 'disk', 'somedevicename', '\somebackupserverSomebackupshareSomeservernameSomeinstancenameSomedatabasename.bak';

--delete
--EXEC sp_dropdevice 'somedevicename';

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

--database_sizes.sql
select db.name, 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 db.name
--order by db.name
order by [SizeGB] desc

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 …

--Checkdb_most.sql
DECLARE @dbname VARCHAR(100)
declare @return int
set @return = 0

DECLARE db_cursor CURSOR FOR
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)
BEGIN
       if getdate() > '01 feb 2012 18:20' set @return = 1
       DBCC CHECKDB (@dbname) with PHYSICAL_ONLY
       FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor

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

--backupAll.sql

DECLARE @dbname VARCHAR(50)
DECLARE @fileName VARCHAR(256)
DECLARE @path VARCHAR(256)

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

DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name  'tempdb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
       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
END

CLOSE db_cursor
DEALLOCATE db_cursor

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 …

--checkdb_history.sql
CREATE TABLE #DBInfo_LastKnownGoodCheckDB
	(
		ParentObject varchar(1000) NULL,
		Object varchar(1000) NULL,
		Field varchar(1000) NULL,
		Value varchar(1000) NULL,
		DatabaseName varchar(1000) NULL
	)

DECLARE csrDatabases CURSOR FAST_FORWARD LOCAL FOR
SELECT name FROM sys.databases WHERE name NOT IN ('tempdb')

OPEN csrDatabases

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

FETCH NEXT FROM csrDatabases INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
	--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
END

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

SELECT Distinct
	DatabaseName,
	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

I found that SQL 2008 SSMS cannot connect to SQL 2005 SSIS. I needed to remote onto the Server, or install SQL 2005 locally.

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 …

--cmdshell.sql

--	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
BEGIN
	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
END
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)

I didn’t know what to make of the error message “Date spin detected”. Until I noticed a SQL job scheduled every 10 seconds!

The overhead of executing a job so often is very high. Naughty developers!

A much better solution would be for the SQL job to call a stored-procedure, and the sp to include a While loop containing a “wait 10” instruction.

The best script I’ve found to Reorganize/Rebuild indexes and Update Stats can be found on this web-page …
http://ola.hallengren.com/

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)

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]

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.

--freespace.sql
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#DBSTATS') IS NOT NULL
BEGIN
   DROP TABLE #DBSTATS
END

CREATE TABLE #DBSTATS (
   [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
BEGIN
   DROP TABLE #temp_log
END

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

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

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
BEGIN
    DROP TABLE #temp_db
END

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)
BEGIN
   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
         EXECUTE(''DBCC SHOWFILESTATS'')

      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'

    EXEC(@sql)

    DELETE FROM #temp_db WHERE name = @dbname
END

_NEXT:

INSERT INTO #Temp_Log
   EXECUTE ('DBCC SQLPERF(LOGSPACE)')

--select * from #Temp_Log

UPDATE #DBSTATS
   SET SIZE = B.LogSize
   , USED = LogSize * LogSpaceUsed / 100
FROM #DBSTATS A
INNER JOIN #Temp_Log B
    ON (A.DBNAME = B.DBNAME)AND(A.Usage = 'LOG')

SELECT dbname AS [database name],
   lname AS [file name],
   usage,
   [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 %]
FROM #DBSTATS
ORDER BY [free space %] --dbname, usage

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

SET NOCOUNT OFF

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

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

When developing using temp tables here’s a good line to drop them if they exist.

IF OBJECT_ID('tempdb.dbo.#indexes','U') IS NOT NULL DROP TABLE dbo.[#indexes]

To download a backup from an FTP site then delete the original …

C:pathcorecmd.exe -s -O -site site -d /path/latestbackup.bak -p c:ftp -delsrc -log c:logfile.log

-s = silent

-O = overwrite

-site = pre configured connection details within coreftp gui

-d = path to ftp site

-p = local destination

-delsrc = delete source after successful download

-log = record a logfile

******** UPDATE Dec 2012 ********

I found that this utility was the only way I could find to script/schedule FTPS uploads & downloads. Here’s more working examples …

“C:Program Files (x86)CoreFTPcorecmd.exe” -s -OS -site thirdpartyname -u “E:DBAthirdpartynamesomefilename_2012*.txt” -p “/export files/” -log E:DBAthirdpartynamelogfile.log

“C:Program Files (x86)CoreFTPcorecmd.exe” -s -OS -site thirdpartyname -d “/Client imports/somefilename_*.txt” -p \mylocalsqlserverdbathirdpartyname -log \mylocalsqlserverdbathirdpartynamelogfile.log

Note:-
– The use of double quotes so the utility isn’t confused by spaces in the path.
– The -OS switch which means copy everything except what’s already copied.
– The use of wild-cards with -OS to simplify management of file-names containing dates.
– the use of -u for uploading, and -d for downloads.

To create or alter a stored procedure that contains references to data on remote servers you need to connect using an account that can access all the data.

That is, to successfully alter a sp that includes links to 2 remote servers, connect (in SSMS) using an account that is common in the ‘Linked Servers’ configuration. Test this by drilling down through ‘Linked Servers’ to see both lots of tables.

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