Free Monitoring Tool For SQL Server

http://sqlmonitor.codeplex.com/

 

If you are looking for a tool to monitor performance information for SQL Server in real-time, there is a free tool available on Codeplex called SQL Live Monitor that has a GUI interface and does not require installation.  From the site, these are the features that it supports:

• Realtime SQL and System performance data
• Colour coded alerts
• Capture data logging to CSV - sample interval configurable
• PAL Perfmon counter logged to .blg or .csv for offline analysis using PAL - sample interval configurable
• Detailed view of certain SQL Areas via DMVs
• List Top 20 Queries by CPU, IO and Execution Count
• View SQL Waits by category
• View SQL Scheduler and CPU worker threads
• Monitor Kernel Pools and System PTE's
• Supports SQL 2000,SQL 2005, SQL 2008
• Captures data from local and remote servers
• Captures data from default and named instances (stand alone & clustered)
• Supports both SQL and Windows Authetication
• Supports x86 and x64
• No installation required
• Application small in size with minimal overhead when running
• Run multiple instances on same PC

There is some light documentation for a Quick Start and how to monitor clusters, as well as export data to PAL (Performance Analysis of Logs), which I will cover in a separate post.

Save Even More Money with a TechNet Standard Subscription

Even though this is an old article from 2010, it's still may be of some use for some.

Save Even More Money with a TechNet Standard Subscription

Introducing Microsoft SQL Server 2012 Free eBook

Microsoft is offering a free eBook on SQL Server 2012 covering the new features. This is not a step by step guide but covers an overview of the new SQL 2012 features.

You can download it and get more information here.

Memory Use in SQL Server

This is an excellent article that explains Memory Usage in SQL Server

http://sqlnerd.blogspot.com/2006/07/memory-use-in-sql-server.html

How-To: Disable Scrict Name Checking in Windows

Allow connection to the remote server share via any DNS alias by creating the DisableStrictNameChecking registry key, follow these steps on the server with the share.

1.Create the CNAME record for the file server on the appropriate DNS server, if the CNAME record is not already present.
2.On the server click Start, click Run, type regedit, and then click OK.
3.Locate and click the following registry subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer\Parameters
4.Right click Parameters, point to New, and then click DWORD Value.
5.Type DisableStrictNameChecking, and then press ENTER.
6.Right-click DisableStrictNameChecking, and then click Modify.
7.In the Value data box, type 1, and then click OK.
8.Exit Registry Editor.
9.Restart your computer.

SQL Server Script: List Indexes That Are Not Used


SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;


SQL Server Script: Index Read/Write Statistics


SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] ,
i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;


Cluster & SQL Server is not supported on Windows Server 2003 or Windows Server 2008 Terminal Server application server

http://support.microsoft.com/kb/327270

This has come up at least 3 times lately where I someone wants to install Terminal Services on a SQL Cluster node. The above link explains why this can't and shouldn't be done.

How-To: Restore a Deleted Object Using the Windows 2008 R2 Active Directory Recycle Bin

If you are running Active Directory on a Windows 2008 R2 server with the Forest Functional Level at Windows 2008 R2, you can take advantage of the Active Directory Recycle Bin to totally restore deleted objects to their original state.  If you have not already enabled this feature yet, take a look at this to enable it:  How to enable Active Directory Recycle Bin in Windows 2008 R2 to restore deleted objects

In order to now restore a deleted object, you will need to use either ldp.exe or Powershell since there is no GUI built in by Microsoft.  There are free options out there, and the best and easiest to use is this one, by Overall Solutions, and it is free.

If you prefer to use the command line or Powershell, you can follow these instructions from the Microsoft MSDN site.

How to enable Active Directory Recycle Bin in Windows 2008 R2 to restore deleted objects

One of the new features of Windows 2008 R2 is the "Active Directory Recycle Bin", which allows you to restore deleted objects in AD.  In order to use this functionality, you will need to raise your Forest Functional Level to Windows 2008 R2.  Once that is done, you will need to enable the Active Directory Recycle Bin.  To do this, you can follow these steps in this Microsoft MSDN Article:

Enable Active Directory Recycle Bin

Find Free Space, File Location, File Size for All Databases, All Instances

From: http://www.mssqltips.com/sqlservertip/1426/collect-sql-server-database-usage-for-free-space-and-trending/


DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName


How-To: Recover the Private Key in IIS for an SSL Certificate

To assign the existing private key to a new certificate, you must use the Microsoft Windows Server 2003 version of Certutil.exe. To do this, follow these steps:

1. Log on to the computer that issued the certificate request by using an account that has administrative permissions.
2. Click Start, click Run, type mmc, and then click OK.
3. On the File menu, click Add/Remove Snap-in.
4. In the Add/Remove Snap-in dialog box, click Add.
5. Click Certificates, and then click Add.
6. In the Certificates snap-in dialog box, click Computer account, and then click Next.
7. In the Select Computer dialog box, click Local computer: (the computer this console is running on), and then click Finish.
8. Click Close, and then click OK.
9. In the Certificates snap-in, expand Certificates, right-click the Personal folder, point to All Tasks, and then click Import.
10. On the Welcome to the Certificate Import Wizard page, click Next.
11. On the File to Import page, click Browse.
12. In the Open dialog box, click the new certificate, click Open, and then click Next.
13. On the Certificate Store page, click Place all certificates in the following store, and then click Browse.
14. In the Select Certificate Store dialog box, click Personal, click OK, click Next, and then click Finish.
15. In the Certificates snap-in, double-click the imported certificate that is in the Personal folder.
16. In the Certificate dialog box, click the Details tab.
17. Click Serial Number in the Field column of the Details tab, highlight the serial number, and then write down the serial number.
18. Click Start, click Run, type cmd, and then click OK.
19. At the command prompt, type the following:
20. certutil -repairstore my "SerialNumber" SerialNumber is the serial number that you wrote down in step 17.  Also, you will need to include the quotes around the serial number in this command line.
21. In the Certificates snap-in, right-click Certificates, and then click Refresh.  The certificate now has an associated private key.