Uncategorized

How to remove a search term/url from Edge

pauliomLeave a comment

The other day I wanted to lookup the price of a PlayStation in France. I entered amazon.fr into Edge and found the details – all was good. However, now whenever I start to type ‘am..’ into Edge it keeps trying to ‘help’ me auto-filling in amazon.fr again. Grrr. The way I managed to stop Edge doing this is to hack the values out of the Registry. As with any Registry changes you must be careful and do this at your own risk;

    Close Edge
    Open Registry Editor: Windows->Run. Regedit
    Select the first node
    Edit->Find…
    Type in the core part of the url you want to remove, e.g. amazon.fr
    Find
    Look at the path (in the status bar at the bottom). Delete each instance your find that has any path related to Edge
    Repeat by pressing F3 once after each delete

Monitoring the progress of CreateIndex

pauliomLeave a comment

One of the frustrating aspects of SQL Server is the lack of progress indicators. One such command falling into this pit is CreateIndex. However, this is a great link for a solution; http://sqlblog.com/blogs/michael_zilberstein/archive/2013/10/21/51415.aspx

Step 1 – Before you run the following query make sure you have ‘include actual execution plan’ option set in query analyser. If you don’t have this, or similar, then the DMV won’t produce any results.

Step 2 – Select @@SPID

Step 3 – run your Create Index in the same query window as step 2

Step 4 – Create a new query with the following SQL, replace the SPID in the first line with that from step (2)

DECLARE @SPID INT = 51;
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
SUM(qp.[estimate_row_count]) AS [TotalRows],
MAX(qp.last_active_time) – MIN(qp.first_active_time) AS [ElapsedMS],
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N'<Transition>’)) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN (N’Table Scan’, N’Clustered Index Scan’, N’Sort’)
AND   qp.[session_id] = @SPID
), comp AS
(
SELECT *,
([TotalRows] – [RowsProcessed]) AS [RowsLeft],
([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM   agg
)
SELECT [CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
[ElapsedSeconds],
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
DATEADD(SECOND,
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

Problem shrinking tempdb – could not be moved because it is a work table page

pauliomLeave a comment

SQL holds a lot of cached information in tempdb. Until it’s cleared it won’t allow tempdb to be shrunk. So here’s a quick script you might have some joy with (obviously be aware that you will suffer the cost of losing this cached information) (thanks to MSDN);

USE [tempdb]

GO

DBCC DROPCLEANBUFFERS

GO

DBCC FREEPROCCACHE

GO

DBCC FREESESSIONCACHE

GO

DBCC FREESYSTEMCACHE ( ‘ALL’)

GO

DBCC SHRINKDATABASE (tempdb,10)

GO

 

SQL Server – The log cannot be rebuilt because

pauliomLeave a comment
I had the blood-drawn-from-face moments when a database stated is was ‘pending recovery’. Ultimately the transaction log file was broken. Actually I didn’t care about that because I knew it was pretty much all read-only data. So how to recover it? I tried various recovery techniques but the server was adamant, ‘restore from backup’. Well, I couldn’t do that either. So finally I found this little nugget of DDL, create a new database using the existing data files but with a new transaction log;
CREATE DATABASE [<new dbname>]

ON  PRIMARY
( NAME = N'<filename>’, FILENAME = N'<path to>.mdf’  ),
( NAME = N'<filename2>’, FILENAME = N'<path to 2nd>.mdf’ )

FOR ATTACH_REBUILD_LOG

Azure Table Insert, one of the request inputs is out of range

pauliomLeave a comment

Whilst trying to insert a new row via a TableEntity derived class I hit a strange error;

‘one of the request inputs is out of range’

Looking at my object it all seemed fine to me. The problem was that I had a DateTime property that I had not set. Although it was defaulting to the minimum date time Azure did not enjoy that. So I simply ensure that date property has a reasonable value in it and problem solved.

 

 

Surface Phone…what if

pauliomLeave a comment

I’ve no insight into what is in or out of the Microsoft Surface Phone or if it even exists. However, I was thinking what could it realistically do to be game changing. Well how about this. When undocked you can use UWP apps. Fine, that 1% of the apps the world wants but typically enough to read your emails and surf the odd web page. When you the dock the phone is able to run win32 apps. So you really would get a “full”, if maybe slightly underpowered, laptop. That really would be a legitimate reason to own one.

SQL Server with external drives – pending recovery

pauliomLeave a comment

When you are developing a SQL Server database you might want to use External Storage devices. Beware of a couple of slight gotchas. When you restart your machine SQL Server may start before the drives become available. If this happens SQL Server will mark the database as ‘pending recovery’. To get out of this situation just restart the SQL Server instance and select ‘refresh’ the database node in SQL Management Studio. A second, related problem, is that the drives may not be assigned the same drive letter, this will again result in ‘pending recovery’. If you cannot remember the drive letter you assigned you can run the following against the system database;

select [name], physical_name from sys.master_files

If you now have an incorrectly assigned drive letter, the best solution is to open the disk management tool where you can right-click and assign the original drive letter back.