Month: October 2011

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 restoring database Liath using sql-script.

If database exists make it single-user-mode

Alter database liath set single_user with rollback immediate
Go

If possible backup the tail IE: unbacked up data

Backup log liath to disk = N’\192.168.0.32volume_1SQLBackups_D2liathliathTail.bak’ with init, norecovery
go

Restore the last full backup EG:-

Restore database liath from disk = N’\192.168.0.32volume_1SQLBackups_D2liathliath_backup_2011_10_13_174000_5625000.bac’ with norecovery
go

Restore the latest differential backup EG:-

Restore database liath from disk = N’\192.168.0.32volume_1SQLBackups_D2liathliath_backup_2011_10_14_000001_1250000.dif’ with norecovery
Go

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

Restore log liath from disk = N’\192.168.0.32volume_1SQLBackups_D2liathliath_backup_2011_10_14_003001_1718750.trn’ with norecovery
Go

Restore log liath from disk = N’\192.168.0.32volume_1SQLBackups_D2liathliath_backup_2011_10_14_013001_3437500.trn’ with norecovery
Go

Restore the final tail backup

Restore log liath from disk  = N’\192.168.0.32volume_1SQLBackups_D2liathliathTail.bak’ with recovery
Go

Change database to mult-user mode

Alter database liath set multi-user
Go

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, i.name 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]

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