Saturday, 16 February 2013

Database Files Physical Location

declare @db_list table( row_no smallint identity (1,1), db varchar(200))

INSERT into @db_list
select name from master..sysdatabases

declare @first smallint
declare @last smallint
declare @db varchar(200)
declare @sql varchar(500)

select @first = min(row_no) from @db_list
select @last = max(row_no) from @db_list

create table #db_file_list ( db_name varchar(100), Filename varchar(200), file_location varchar(500))

while @first <= @last

select @db = db from @db_list where row_no = @first

SET @sql = 'INSERT INTO #db_file_list select '+ CHAR(39) + @db + CHAR(39)+' ,name,filename from '+ @db+'..sysfiles '

--print (@sql)
exec (@sql)

SET @first = @first + 1


--select * from #db_file_list where file_location like '%N:%'

select * from #db_file_list

drop table #db_file_list


Ping Linked servers

I created a monitoring server and one of the things I want to know if all the servers that I look after are up and running. So I created this proc to do just that. I even have a linked server that is an Oracle box and I pinged that one to ensure I can still get a connection to it.
I wanted to only know when any of the linked servers where having issues so this is how this was created. I added parameters and logic to handle the situation where you want to know the the job was run and what it's findings were.
I will proably add the the sql to write this out to a table so we can record system uptime over time and automatically calculate database availability % and report on that SLA .
To execute from a SSMS
 DECLARE @return_value int
 EXEC @return_value = [dbo].[usp_lsping]
 @nf = N'Y',
 @wtn = N'E'
 SELECT 'Return Value' = @return_value
I set this up as a job to run at regular intervals ie dev/test servers every 30 min every 6 minutes for production boxes I want it to let know me if it finds an error. I added an in clause to the where clause that builds the list of the servers so that one version check the dev and test versions and another checks the production databases.
 Please feel free to update and modify as you see fit please contribute it back so we can get a good script to use and share with everyone.

Create procedure usp_lsping(@nf nchar = 'N', @wtn nchar = 'E')
-- ===========================================================================
-- Description:Description to ping the linked server and see if there alive
-- ===========================================================================
-- Parameters
-- @nf - did you want to be notified Y - yes N - no 
-- @wtn - when to notofiy E - on error S - on Succes B - on Both
--DECLARE@return_value int
--EXEC@return_value = [dbo].[usp_lsping]
--@nf = N'Y',
--@wtn = N'E'
--SELECT'Return Value' = @return_value
-- ===========================================================================
set nocount on
SrvName nvarchar(128)

insert into @LSrvrs
select srvname from sys.sysservers
where srvname != CONVERT(nvarchar(128), SERVERPROPERTY('servername'));
declare @maxloocnt int;
declare @loopcnt int;
declare @srvr nvarchar(128);
declare @retval int;
declare @Clr nvarchar(4)
declare @msg nvarchar(MAX)
declare @errchk int;
set @errchk = 0;
set @Clr = char(13)+char(10);
select @maxloocnt = count(*) from @LSrvrs;
set @loopcnt = 1;
set @msg = '';
set @msg = @msg + '<Start>'+@Clr
while @loopcnt <= @maxloocnt
select @srvr = srvname from @LSrvrs where SrvrID = @loopcnt;
--select @srvr
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
set @errchk = 99;
end catch;
--if @retval =qwqw 0 
set @msg = @msg + @Clr+@srvr + ':' + cast(@retval as nvarchar(100));
set @loopcnt = @loopcnt + 1;
set @msg = @msg + @Clr+'<end>'+@Clr
if @nf = 'Y'
--if @wtn = 'E' or @wtn = 'S' or @wtn = 'B'
--print 'WTN:'+@wtn
--print '@errchk:'+ CONVERT(nvarchar(128),@errchk)
if (@wtn = 'S' and @errchk = 0) or (@wtn = 'E' and @errchk > 0) or(@wtn = 'B' )
--select @msg;
EXEC msdb.dbo.sp_send_dbmail
@subject ='Linked Server Ping'--,@query ='select @msg;', 
--@attach_query_result_as_file = 1,@query_attachment_filename ='PingResults.txt'
-- END
-- '';

set nocount off

Restore database from a device containing multiple backups

If you have multiple backup database backups on a single SQL Server 2008 R2 backup device, this script will help you restore a database from the LATEST full and differential backups residing on that backup device.

If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the CREATE temporary table statement.

Script to restore the LATEST full and differential backups from a SQL Server 2008 R2 backup device.
If this script is to be used for restoring a SQL Server 2005 database, please remove "CompressedBackupSize" from the temporary table
--1. Create a temporary table for holding backup header information
IF OBJECT_ID('TempDB..#RestoreHeaderOnlyData') IS NOT NULL
DROP TABLE #RestoreHeaderOnlyData
CREATE TABLE #RestoreHeaderOnlyData( 
BackupName NVARCHAR(128) 
,BackupDescription NVARCHAR(255) 
,BackupType smallint 
,ExpirationDate datetime 
,Compressed tinyint 
,Position smallint 
,DeviceType tinyint 
,UserName NVARCHAR(128) 
,ServerName NVARCHAR(128) 
,DatabaseName NVARCHAR(128) 
,DatabaseVersion INT 
,DatabaseCreationDate datetime 
,BackupSize numeric(20,0) 
,FirstLSN numeric(25,0) 
,LastLSN numeric(25,0) 
,CheckpointLSN numeric(25,0) 
,DatabaseBackupLSN numeric(25,0) 
,BackupStartDate datetime 
,BackupFinishDate datetime 
,SortOrder smallint 
,CodePage smallint 
,UnicodeLocaleId INT 
,UnicodeComparisonStyle INT 
,CompatibilityLevel tinyint 
,SoftwareVendorId INT 
,SoftwareVersionMajor INT 
,SoftwareVersionMinor INT 
,SoftwareVersionBuild INT 
,MachineName NVARCHAR(128) 
,Flags INT 
,BindingID uniqueidentifier 
,RecoveryForkID uniqueidentifier 
,Collation NVARCHAR(128) 
,FamilyGUID uniqueidentifier 
,HasBulkLoggedData INT 
,IsSnapshot INT 
,IsReadOnly INT 
,IsSingleUser INT 
,HasBackupChecksums INT 
,IsDamaged INT 
,BeginsLogChain INT 
,HasIncompleteMetaData INT 
,IsForceOffline INT 
,IsCopyOnly INT 
,FirstRecoveryForkID uniqueidentifier 
,ForkPointLSN numeric(25,0) 
,RecoveryModel NVARCHAR(128) 
,DifferentialBaseLSN numeric(25,0) 
,DifferentialBaseGUID uniqueidentifier 
,BackupTypeDescription NVARCHAR(128) 
,BackupSetGUID uniqueidentifier 
,CompressedBackupSize BIGINT 

--2. Collect header information FROM the backup device into a temporary table
INSERT INTO #RestoreHeaderOnlyData 
EXEC('RESTORE HEADERONLY FROM DISK = ''\\servername\foldername\BackupDeviceName.bak''') 

--3. Complete database restore from the latest FULL backup; 
--NORECOVERY is specified so that roll back not occur. This allows additional backups to be restored. 
DECLARE @File smallint
SELECT @File = MAX(Position) 
FROM #RestoreHeaderOnlyData 
WHERE DatabaseName = 'SomeDatabase' 
    AND BackupTypeDescription = 'Database'

FROM DISK = N'\\servername\foldername\BackupDeviceName.bak' 
WITH FILE = @File, 
    MOVE N'SomeDatabase' TO N'E:\Data_NI1\SomeDatabase.mdf', 
    MOVE N'SomeDatabase_log' TO N'E:\Log_NI1\SomeDatabase.ldf', NOUNLOAD, REPLACE, STATS = 10, NORECOVERY

--4. Next: Restore the latest differential database backup
--If log backups are to be restored, specify NORECOVERY in this step also
--Then use RESTORE LOG to restore logs in the correct sequence 
--Specify RECOVERY in the last RESTORE LOG statement

DECLARE @File smallint
SELECT @File = MAX(Position) 
FROM #RestoreHeaderOnlyData 
WHERE DatabaseName = 'SomeDatabase' 
    AND BackupTypeDescription = 'Database Differential'
RESTORE DATABASE [SomeDatabase] FROM DISK = N'\\servername\foldername\BackupDeviceName.bak'
    WITH FILE = @File, 
    MOVE N'SomeDatabase' TO N'E:\Data_NI1\SomeDatabase.mdf', 
    MOVE N'SomeDatabase_log' TO N'E:\Log_NI1\SomeDatabase.ldf', 

Disk space alert per drive

When executing this script, it will create the stored procedure: usp_DiskSpaceAlert
Now you can create little jobs to check the available diskspace for 1 drive (so by adding more steps in the job, you can check all disks depending other parameters).
- MinMBFree = triggeramount of free disk space
- Drive = diskdrive to check
- RCPT = emailaddresses of recipients
For example:
EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 3072, @Drive='C', @RCPT=''

USE [master]GO/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @Drive char(1), @RCPT VARCHAR(500) AS/*****************************************************    Object: dbo.usp_DiskSpaceAlert (DiskSpaceAlert per diskdrive)*    Dependent Objects: master.sys.xp_fixeddrives*    Version: 1.0*    Script Date: 3/10/2011*    Author: Sven Goossens*    Purpose: Validate sufficient disk space per drive*    Detailed Description: Validate sufficient disk space based on based on the @MinMBFree and @Drive parameters*        Mails when defined amount is reached to parameter @RCPT*    EXECUTE AS:*        EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 30000, @Drive='C', @RCPT=''*    Updates:*     v1.0    - Drive will be checked and sends mail when diskspace is less then given amount****************************************************/SET NOCOUNT ON-- 1 - Declare variablesDECLARE @MBfree int-- 2 - Initialize variablesSET @MBfree = 0-- 3 - Create temp tablesCREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)-- 4 - Populate #tbl_xp_fixeddrivesINSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])EXEC master.sys.xp_fixeddrives-- 5 - Initialize the @MBfree valueSELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @Drive-- 6 - Determine if sufficient free space is availableIF @MBfree > @MinMBFreeBEGIN RETURNENDELSEBEGIN IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL    BEGIN        DECLARE @MSG VARCHAR(400)        SET @MSG = @Drive + ' drive has only ' + CONVERT(VARCHAR,@MBfree) --PUT THE VARS INTO A MSG            + 'MB (' +CONVERT(VARCHAR,@MBfree/1024)+ 'GB) left on ' + @@SERVERNAME + CHAR(13) + CHAR(10)        DECLARE @EMAIL VARCHAR(600)        SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail            @recipients = ''' + @RCPT + ''',            @body = ''' + @MSG + ''',            @subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @Drive + ' @ ' + @@SERVERNAME + ' !!'''        EXEC (@EMAIL)    ENDEND-- 7 - DROP TABLE #tbl_xp_fixeddrivesDROP TABLE #tbl_xp_fixeddrivesSET NOCOUNT OFFGO

Database Migration Automated

Part 1:-
  • Configure the Distributor, Publisher and enable the database to synchronize from backup ( to make sure I would not need to generate a full snapshot to initialize, as the business requirement was to do this task with least overhead on the live systems)
  • Create an empty publication with no articles. (again I could add all the articles but I choose not to, so I can have control over which article I would like to replicate across)
  • Backup database to be migrated across
  • Verify the backup taken above is valid
  • Copy the backup across the network to the destination server ( scripted Robocopy and used multi threading (MT: 100), flows like a breeze on a dedicated network )
  • Restore the database on the destination server.
  • Create a push subscription
The script creates a log file for each task organizing it in the folder structure with ServerName and a subfolder with DatabaseName then creates a txt log file with results of each step.
The script then prompts the user to check the log file to make sure there are no errors, and press any key to continue migration.

Part 2:-
  • Copy across logins with SID to destination server (I did this using SSIS package, I could use the sp_help_revlogin)
  • Copy across agent Jobs and SSIS packages ( again did this using SSIS package)
  • Copy across Linked Servers
  • Automated LogShipping setup.
Issues faced:
Faced no unusual issues other than things you would face normally when setting up transactional replication using backup to initialize. We could have avoided these issues if we had enough time to analyze the databases prior to actually working on the migration. I was put on the task with no lead time to investigate.
A subset of the issues I faced:
  • Errors with TimeStamp Column when initializing using backup; The business requirement was not to change any database schema, so converting TimeStamp column to varbinary was ruled out. I got lucky as the databases with this issue were all small databases ranging upto 8 GB and I could easily initialize delivering a full snapshot for these without any concerns.
  • Tables without Primary Keys, obviously replication would only work for tables with primary keys defined on them. I prepared a script that would provide me a row count of each table of every user database on each of the source and destination servers I was migrating. I pulled this data into an excel, prepared a pivot table and there it was , very easy to compare which tables were out of sync. Thanks to redgate data compare and SSIS package I could easily sync these tables without any issues. the trick is to compare the tables selecting all the columns for comparison.
  • Ever changing business requirements
  • Application teams were busy working towards their release and had BLOB data imports into the database I was replicating across in preparation of the Migration CutOver. Had to increase the max text repl size to let the application import BLOB data.
  • Had a couple of Logshipping databases on the source included in the list to perform this premigration replication activity. Later identified as to setup Log shipping separately for these two databases. Now the replication would not allow you remove the subscription and publication as the subscriber was a logshipped read only database. Had to manually remove replication on both publisher and subscriber individually.
  • SQL Servers evaluation Editions were installed on the destination servers. Had to upgrade SQL Server Evaluation Edition to Enterprise Edition. Had to Manage SQL Server Active – Passive Windows Cluster.
  • I had to move the distribution database to another drive at some point in the pre migration process due to lack of disk space on the servers.

Find storage including mount points Sql and windows utility


DECLARE @v_cmd nvarchar(255)

        ,@v_drive char(99)

        ,@v_sql nvarchar(255)

        ,@i int

SELECT @v_cmd = 'fsutil volume diskfree %d%'

SET @i = 1

CREATE TABLE #drives(iddrive smallint ,drive char(99))

CREATE TABLE #t(drive char(99),shellCmd nvarchar(500));

CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));

-- Use mountvol command to

INSERT #drives (drive)
EXEC master..xp_cmdshell 'mountvol'
DELETE #drives WHERE drive not like '%:\%' or drive is null
WHILE (@i <= (SELECT count(drive) FROM #drives))
     UPDATE #drives
 SET iddrive=@i
 WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)
 SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i
 INSERT #t(shellCmd)
 EXEC master..xp_cmdshell @v_sql
 SET =
 FROM #drives d
 WHERE IS NULL and iddrive=@i
            SET @i = @i + 1

            ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace
        ,tt.titi as total
FROM #t bb
                ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi
        FROM #t
        WHERE drive IS NOT NULL
                AND shellCmd NOT LIKE '%free bytes%') tt
    ON =
        AND bb.shellCmd NOT LIKE '%avail free bytes%'
        AND bb.shellCmd LIKE '%free bytes%';

-- SET FreespaceTimestamp = (GETDATE())

SELECT RTRIM(LTRIM(drive)) as drive
 ,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]
FROM #total
WHERE (freespace/totalspace * 100) < 5
ORDER BY drive
DROP TABLE #drives

Self Deleting Job

What do you need to do?
Give the Job a name, a schedule, and the TSQL command that you want to run. That simple!
After the job runs, it will self-destruct.
Error Handling:
Since the Job is deleting it self before it finish executing, there will be an entry on your SQL Agent log that would say:
[180] Job SelfDeletingJob was deleted while it was executing: the outcome was (Unknown)

USE [msdb]

DECLARE @JOBNAME nvarchar(10) 
DECLARE @SQLCMD nvarchar(100)
DECLARE @SQLCMD2 nvarchar(100)

SET @JOBNAME= 'SelfDeletingJob' -- Name your job appropiately
SET @STARTDATE= '201112311' -- Date format:  YYYYMMDD
SET @STARTTIME= '120000' -- Time format:  hhmmss (military time)

-- Below Enter the command that you want to schedule. In this case, it is removing a login from the sysadmin role

SET @SQLCMD='EXEC master..sp_dropsrvrolemember @loginame = N''temp_sysadmin'', @rolename = N''sysadmin'''

-- Do not change below this line

SET @SQLCMD2 ='EXEC msdb.dbo.sp_delete_job @job_name='+@JOBNAME+', @delete_unused_schedule=1'

SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/29/2011 09:50:28 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@JOBNAME, 
@description=N'Self Deleting Job', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step', 
@os_run_priority=0, @subsystem=N'TSQL', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [step2]  ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step2', 
@os_run_priority=0, @subsystem=N'TSQL', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'test', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave