Rebuild SQL database indexes

In this article I will not be going through the matter of indexes.The purpose of the post is to provide a quick reference to a manually triggered re-indexing process. What is important to be understood is the fill-factor for every particular index. The fill-factor value defines the percentage of space in every index page to be filled with data and respectively defines the space left as free space for future growth.

Here is the recommended fill-factor values based on the reads-writes balance:
Tables with low number of updates
- ex. 100:1 read to write ratio: 100% fill-factor
Tables with high  number of updates 
- read to write ratio less than 1 (more writes than read) : 50%-70% fill-factor
Tables with average number of updates - 80%-90% fill-factor

DECLARE @from int
DECLARE @to int
DECLARE @fillFactor int
SET @from=1
SET @to=10000
SET @fillFactor=90
DECLARE @dateFrom datetime
DECLARE @table varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name from(
SELECT  ROW_NUMBER() OVER (ORDER BY table_name) AS [SortNum], table_name FROM information_schema.tables
WHERE table_type = 'base table')
AS t1 WHERE sortnum>=@from and sortnum<=@to
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY SET @dateFrom = getdate()
DBCC DBREINDEX(@table,' ',@fillFactor)
print 'Table ['+@table+'] indexed for: '+ CAST( DATEDIFF ( ms , @dateFrom , getDate() ) as nvarchar(10)) + ' ms'
END TRY
BEGIN CATCH
print 'Number of errors: '+CAST(ISNULL( ERROR_NUMBER(),'') as nvarchar)
print 'Error: '+ISNULL(ERROR_MESSAGE(),'')
END CATCH

FETCH NEXT FROM TableCursor INTO @table
END
CLOSE TableCursor
DEALLOCATE TableCursor

Change Collation of a Mirrored Databse


If you have already tried this directly you have probably seen messages like the action is not available on databases involved in mirroring or database unable to be locked and so on.

So first you have to the database from the mirror session, then do the necessary change actions and restore the mirror session. Ex,

ALTER DATABASE SET PARTNER OFF
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS

On the mirrored database:
RESTORE DATABASE WITH RECOVERY


Error Messages:

  • The database could not be exclusively locked to perform the operation. 
  • The operation cannot be performed on database it is involved in a database mirroring session or an availability group 

Windows God Mode

What is that?

This is your windows command center with a lot of configurable setting to control your windows.
It is simple to be the Windows God by simply creating a new folder named

GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

Here we go. The folder contains a lot of options making you the master of your computer.



Cheers!


Speed up Visual Studio in few easy steps

1. Delete C:\Users\[username]\AppData\Local\Microsoft\WebSiteCache content
2. Delete C:\Users\[username]\AppData\Local\Temp\Temporary ASP.NET Files content
3. Uncheck Tools -> Options -> Debugging -> Edit and Continue -> Enable Edit and Continue
4. Uncheck Tools -> Options -> Environment -> Use hardware graphics acceleration if available

Easy way to write messages in javascript console without firebug

Very simple solution w/o any prerequisites. No need to install anything like firebug or javascript library to log activities.

function writeLog(log) {
    setTimeout(function() { throw new Error(log); }, 0);
}

The javascript exceptions are automatically caught in the console, thus forcing an error writes into the console. In order to not brake any js execution use setTimeout to run the error into another thread.

The Fastest Bitcoin Exchange MCXNow is Back

The fastest and maybe the most reliable bitcoin exchange MCXNow has been reopened. Just for a few days it got all its users back and even more - it gained thousands of new users. Earn from trading and get interest for all deposited money. Additionally shares could be bought to increase your revenue by receiving dividents every 3 hours.

Happy trading!

How to move windows between monitors with Windows 7

I had this problem for some time. I use multiple monitors and my video settings are set always like that, even when I do not really need all monitors and use only the primary one. It is very often to lose a window of an application on another monitor seeing its icon on the taskbar of my primary screen. Restore, cascade, show stacked commands are just useless in this case as they only rearrange the windows of the main monitor. But there is a way to fix that by a few key shortcuts.

Win+Shift+Left - moves window to the left monitor
Win+Shift+Right - moves window to the right monitor
and maybe Up and Down to move to the upper or lower monitor - I do not have monitors above and below so I couldnt test these.

Win+Left - adheres the window to the left half of the screen. Continuous clicking causes moving the window to another monitor.
Win+Right - adheres the window on the right half of the screen. Continuous clicking causes moving the window to another monitor.
Win+Up - maximizes the window
Win+Down - Minimizes and restores if maximized



How to find the largest sql objects in an SQL Server database

Sometimes you may need know how big some tables are, how many rows they have and how much disk space they use.

The following query looks for the biggest tables in size (MB) and number of rows:

SELECT * FROM (
SELECT 
    tbl.NAME AS TableName,
    ind.name as indexName,
    sum(p.rows) as RowCounts,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables tbl INNER JOIN 
    sys.indexes ind ON tbl.OBJECT_ID = ind.object_id INNER JOIN 
    sys.partitions p ON ind.object_id = p.OBJECT_ID AND ind.index_id = p.index_id INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    tbl.NAME NOT LIKE 'dt%' AND
    ind.OBJECT_ID > 255 AND   
    ind.index_id <= 1
GROUP BY     tbl.NAME, ind.object_id, ind.index_id, ind.name 
) as t1  ORDER BY TotalSpaceMB desc ,UsedSpaceMB desc

How to replace a character in Excel with a new line

Here is the trick:

1. Select the character or string you would like to replace
2. Copy (Ctrl+C)
3. Open Find Dialog (Ctrl+F) and open Replace Tab
4. Paste the string to be repalced in the firts textbox (Ctrl+V)
5. Enter in the second textbox Alt+010 which represents a new line with its ASCII equivalent.
6. Click replace and enjoy.



Extract url / adress from hyperlink cell in Excel

1. Go to Visual Basic for Applications module by clicking Alt+F11
2. Create new Module by Insert->Module
3. Create a simple function in the newly created module
Function GetCellUrl(LinkCell as Range)
   If LnkCell.Hyperlinks.Count = 0 Then
      GetCellUrl = ""
   Else 
      GetCellUrl = LnkCell.Hyperlinks(1).Address
       End If


     End Function

SQL - How to delete table content quickly

The deletion of table content may take significant amount of time when the number of rows is large. This is so because every singe row deletion is logged into the database transaction log.
In the cases that the deletion log is not important you can simply remove the data rows w/o logging any individual row deletion by truncating the table:

TRUNCATE TABLE [TableName]


Reference:
http://msdn.microsoft.com/en-us/library/ms177570.aspx

100% CPU usage by SQL Server - Look for Active (running) SQL queries

I found a very useful posts regarding analysis of currently running SQL queries and high SQL server load in Pinal Dave's blog.

http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/

Using these tricks the culprit of the high load can be easily indicated.

Until the problem resolution is implemented KILL [session_id] can stop the problematic query w/o server restart or long waiting.

Windows 7: How to access password protected shared folder

How to pass current Windows credentials to password protected network share

1. Create user/password on destination computer matching the user/password of current computer
2. Share a folder on the destination computer giving access to the new created user
3. On current computer change the local security settings as following:
3.1 Open Local Security Policy Console
3.2 Select Local policies -> Security Options -> Network security: LAN Manager authentication level
3.3 Select Send LM and NTLM response and click OK.


You can also specify different user to connect by during mapping a network drive on the destination computer.

IIS Redirection with Page and Query String (UPDATED)

By default IIS redirects to a given domain and/or application.
The web page and query string can be transferred to the destination url by using the respective parameters $S and $Q.

Ex.
template: http://example.com$S$Q
redirection from
http://old-example.com/default.aspx?id=1
will lead to
http://example.com/default.aspx?id=1

In IIS 6 the checkbox "Exact Url" will avoid appending the web page to the query string.

UPDATE:
In order to achieve that behaviour in IIS 7 you have to first enable "Redirect all requests to exact destination" and append the sufix $S$Q to the url.
If you rely in the automatic url redirection of IIS (by default, w/o $S$Q in url) the query string is actually cut.

How to share Windows 7 folders with anonymous access including registry fix

I found very interesting forum thread describing how to share Windows 7 folder to other machines without password protection. It includes a few registry tricks to unlock that feature, that might had been set by Anti-virus software. http://www.acryan.com/forums/viewtopic.php?f=41&t=2269