Database Consultant

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


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


Script to examine SQL Server 2000 logins

A customer had a third-party application (‘qlikviev’) that needed a database-backup downloading from the third-party’s ftp site every night and restoring over the ‘old’ one.

Already supplied was a vbs script that would do the downloading and produce a log-file.

Actually, I improved the vbs a bit to add leading zero’s to the date field’s if needed. Here’s the old then new lines …

‘ strFile = strDayOfMonth & strMonth & strYear & ” ” & strHour & strMinute

strFile = Right(“0” & strDayofMonth, 2) & Right(“0″ & strMonth, 2) & strYear & ” ” & strHour & strMinute

My task then was a) to read the log-file and b) if the download had been successful to restore the backup overwriting the ‘old’ database.

I looked at Bulk-Insert & SSIS, but they could only import the log-file not read it, before settling on the Operating-System command …

‘findstr “success” E:somefoldersomefile.txt’

… as job-step-1. I ensured that if the step failed (IE: the string was not found), the whole job would end.

Then in step-2 pasted some transact I’d bashed-out to run the restore …

RESTORE DATABASE adventureworks
FROM DISK=’E:somefolderadventureworks.bak’
WITH FILE=1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

BTW: To quickly create the right ‘restore’ command I went through the configuration-screens of the restore-wizard (not forgetting to set the ‘overwrite’ option) then hit ‘script’ to generate typo-free code.

Job step-3 was to grant db_reader access to a user. Here’s the code …

create user [domainQlikviewDevelopers] for login [domainQlikviewDevelopers]
exec sp_addrolemember ‘db_datareader’, ‘domainQlikviewDevelopers’

Finally, job-step-4 was to email the results …

declare @datetime as varchar(20)
declare @email as varchar(100)

set @datetime = convert(varchar(17),(select top 1 restore_date
from msdb.dbo.restorehistory
where (destination_database_name = ‘adventureworks’)
order by restore_date desc),113)

set @email = ‘Database ”Adventureworks” Refreshed ”’+@datetime+”’.’

exec msdb.dbo.sp_send_dbmail
@recipients = ‘someone@somewhere.co.uk’,
@subject = @email ;

As the vbs script was to run every 2am I created a sql-job to execute every 3am.

This query will show queued tasks in SQL 2005. Normally the last column should be all zero’s.

select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255

— autocheck.sql, RbS, to automate daily checks.

— create (temp) table (during development) to store results
SET NOCOUNT ON
drop table #ac
go
create table #ac(
cdate smalldatetime default getdate(),
c_free_gb int,
f_free_gb int,
up_since smalldatetime,
last_err smalldatetime,
last_jobf int)

— start a new row with todays date
insert into #ac default values

— temp-table to store raw drive-space
drop table #fs
go
create table #fs(
drive char(1),
mb_free int)
insert #fs exec xp_fixeddrives

— save drive-spaces
update #ac set c_free_gb = mb_free/1024 from #fs where drive = ‘C’
update #ac set f_free_gb = mb_free/1024 from #fs where drive = ‘F’

— save up_since
update #ac set up_since = crdate from sys.sysdatabases where name = ‘tempdb’

— latest error in sql-error-log
drop table #er
go
create table #er(
logdate datetime,
processinfo varchar(20),
text varchar(500))

insert #er exec xp_ReadErrorLog 0, 1, ‘error’ — errors in current sql-error-log
update #ac set last_err = (select max(logdate) from #er)

— date of last job failure
update #ac set last_jobf = (select max(run_date) from msdb.dbo.sysjobhistory)

select * from #ac

–ShowErrorLogs.sql
set nocount on

exec xp_ReadErrorLog 0, 1, ‘error’ — current sql error log
exec xp_ReadErrorLog 0, 1, ‘fail’
exec xp_ReadErrorLog 0, 2, ‘error’ — current sql agent log
exec xp_ReadErrorLog 0, 2, ‘fail’

from source article
http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005

I cobbled together this script from bits on the internet. It works fine, but needs OLE Automation to be enabled on the server. Should you wish to do this, use the Surface-Area configuration tool.

— “DiskSpaceUsed.sql” eg: C-Drive 65.3GB used
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint

SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,’GetDrive’, @odrive OUT, @drive
EXEC @hr = sp_OAGetProperty @odrive,’TotalSize’, @TotalSize OUT
UPDATE #drives

SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END

CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso

SELECT Drive,
cast((TotalSize – FreeSpace)/1024.0 as decimal(4,2)) as ‘Used(GB)’
FROM #drives
ORDER BY drive
DROP TABLE #drives

Having completed building a new application a customer wanted to upgrade there back-end database from SQL Express to full SQL Server 2005 for licencing issues.

Here’s my walk through …

Having already installed IIS and navigated within a command-prompt to the media (cd “D:AppsSQL 2005Disk 1”). I ran “setup.exe SKUUPGRADE=1”.

I accepted the licencing conditions, and continued installing components

1) SQL Server Database Services

2) Integration services

(resisting the temptation to re-assign the datafiles from the small C-Drive to the larger D-Drive), until Getting to the ‘Instance Name’ screen where I clicked the ‘Installed Instances’ button.

The next screen confirms the SQL Express instance name, I clicked ‘OK’, then with the instance-name autocompleted clicked Next.

In the ‘existing components’ screen I ticked the one box to request an upgrade and clicked ‘next’.

–NOTES

The resulting upgrade is not obvious, however it can be confirmed my right-clicking on the server in enterprize manager and examining the version.

Following on from this SP3 should be applied.