Number of rows and size of tables in MS SQL 2005

There are times when you need to know what in your database is taking up all the disk space.

Or you may just want to know which tables contain the most number of records.

The following Query in MS SQL 2005 will give you the answer:

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Converting certificate from PFX to PEM

openssl pkcs12 -export -in abc.crt -inkey abc.key -out abc.pfx

Windows Firewall keeps turning on after adding to domain

After adding servers to Windows server 2008 domain controller the firewall keeps turning on afert each reboot.

This is because the domain is configured in this way.

To turn this off, on the domain controller, go to:

"Group Policy Management" ->
forest ->
Domains ->
relevant domain ->
Group Policy Object ->
Default Domain Policy.

Click right mouse button on the above and edit.

This will open up the Group Policy Management Editor.

expand:
Computer configuration ->
Policies ->
Administrative templates ->
Network ->
Network Connections ->
Windows Firewall ->
Domain Profile ->
Windows Firewall: Protect all network connections

Set the above to "Disabled"

HowTo: Firefox and Integrated Windows Authentication

As per original article:
http://markmonica.com/2007/11/20/firefox-and-integrated-windows-authentication/


Do you have an Intranet or a similar web site that requires the use of Integrated Windows Authentication? If so the default Firefox browser settings will always prompt you for a username and password first before accessing a site using Integrated Window Authentication.

Fortunately Firefox has the slick ability to easily modify it's configuration to use Integrated Windows Authentication.
How to configure Firefox

Open Firefox
In the address bar type: about:config
Firefox3.x and later requires you to agree that you will proceed with caution.
After the config page loads, in the filter box type: network.automatic
* Modify network.automatic-ntlm-auth.trusted-uris by double clicking the row and enter http://www.replacewithyoursite.com or http://your-intranet-server-name
* Multiple sites can be added by comma delimiting them such as http://www.replacewithyoursite.com, http://www.replacewithyourintranetsite.com

Package for Large Installs

If you are a network administrator that has a lot of installs to do, you can modify the Firefox installer.

Use a tool such as 7-zip to extract Firefox Setup 2.x.x.exe
Extract browser.xpi from the setup
Edit all.js contained in browser.xpi contained in binjreprefs
Modify the entries as in items 4 and 5 above
Re-package browser.xpi and use the extracted setup to install Firefox

* Special Notes

To specify all subdomains use .replacewithyoursite.com instead of www.replacewithyoursite.com, help.replacewithyoursite.com, pictures.replacewithyoursite.com

Managing mssql tempdb database and files

If tempdb is set to auto grow, then it will grow only to fill the disk it is in and no more.

So I have created a 1GB ram disk (http://memory.dataram.com/) and set the tempdb to be on the ram disk. This has significantly improved the performance of mssql although I thought tempdb is supposed to be in ram most of the time anyway!!!

The below allows you to see current details of tempdb and files and how they are set to grow:

SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0 AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO

An the below, lets you specify the exact size of the data and log files and how they grow. The values in the below are all in Mega Bytes.

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 900 , FILEGROWTH = 50)
go
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 50 , FILEGROWTH = 5)
go

If you want you can set FILEGROWTH to 0 which means it will not grow but this is not required as the tempdb will never grow past the desk space it lives on.

Making VIM display UTF-8 characters correctly

First you need to chose a font which has the characters you need to display

I have found this is a good one:

:set guifont=Consolas

Then you need to set encoding to utf-8

:set enc=utf-8

MS SQL performance tuning. (Identifying which query is taking the longest time)

Run the below query against a database to get very useful information about how long each query is taking and how many times it has been executed

SELECT TOP 100
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;


You can run the below to clear the cache at any time:

DBCC FREEPROCCACHE

Moving MS SQL TEMPDB

If you need to move the location of the tempdb data or log files you can use the following:

This tells you where everything is now:

USE TempDB
GO
EXEC sp_helpfile
GO

This sets the new location for the data and log file. If you want you can move just one or both files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\newlocation\tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\newlocation\templog.ldf')
GO

You must restart MS SQL service after this. This will automatically create the files in the correct location for you.

If you got the wrong path and the service is not starting then you need to follow the below steps:

Restart SQL Server with only the master database.
NET START MSSQLSERVER /f /T3608

Using SSMS or SQLCMD, execute the ALTER DATABASE commands to move the tempdb files.
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='new path\file')
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='new path\file')
(Repeat for all tempdb files if you have multiple data files)

Stop SQL Server and restart it normally.

Restricted SSH connection

If you want to allow a user to only execute certain commands on a server which they have autologin ssh access to, you can acheive by using the following.

The below shows how to only allow user to run svn specific commands via their ssh autologin access.

insert the following in the relevant users authorized_keys file ni the users home/.ssh

command="svnserve -t -r / --tunnel-user=username",no-port-forwarding,no-agent-forwarding,no-X11-forwarding ssh-dss AAAAB3....rp9vp9Q== username@hostname

Windows Server 2008, multiple IP addresses, primary IP, secondary IP confusion

When multiple IP addresses are assigned to a single Windows Server 2008 OS, the OS seems to randomly choose any one of the multiple IP addresses assigned as the primary IP address. Even though the GUI would suggest otherwise.

The problem with this is that if you have firewall rules allowing only the primary IP address of this server to other resources, requests from this server are going to be blocked as the target is not seeing the primary IP address you specified, it sees one of the other IP addresses instead.

This can also be a problem with mail servers as you may have specified SPF records which expect emails to be sent only from one IP address, but instead are being sent from another IP address on the same machine because of the same problem.

Solution is explained in below article.

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

1. Find the correct patch for your OS, download, install and reboot. This patch will add "skipassource" capability to "netsh" command.

2. Remove all secondary IP addresses and add them not using the GUI, but using the following comand:

  
   
Netsh int ipv4 add address "Local Area Connection" 192.168.x.x 255.255.255.0 skipassource=true

The Skipassource=true causes that IP address never to be used as a primary IP address.

This should resolve the issue.

IIS features to enable in order to install MS SQL 2005 on Windows Server 2008

Before installing MS SQL 2005 on Windows Server 2008 make sure you enable features described in following Microsoft article:

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

Windows Server 2008 Rsync dot folder problem

I had a simple script which contained a number of rsync commands to copy a number of folders from one machine to another.

For some reason when I execute the script, the target/destination server first creates a dot (.) folder and then copies the files in there!!!!


I was using vim to create my scripts on a windows machine.

I found that by changing the file format to a unix file format using command ":set ff=unix" and then saving the script and running it again, the files synchronised into the right folder.