TempDB orignial and current file size

The following script will help you in finding the file size of current tempdb and the size when SQL server last restarted.

SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size <> files.size

Free Tools for the SQL Server DBA

Knowing what you don't know will always save you money. Before buying any tool you should consider asking a question, can I get a free tool for my requirement. Search around and analyse . To my surprise I found some companies brand their tool as free but after downloading it turns out to be not free rather free trial version. Don't put off by this. I have done it previously.

Always begin with an assumption that there is free tool that you could use. Do your research. It will save your company a lot of money and will make you a supremo.

Note also some of this free tools may not be user friendly. To mention just one SQLIO from Microsoft. It is not a tool that you just click and go. But have used it many times and very satisfied with the result that I got.

In general, free tools some times can compare with the tools that you spend a lot of money to do the same thing.

In addition, I came across an article written byDavid Bird for SQL server central and it will cover some of free tools that you will be able to use.

Distribution failure due to Data type differences

Recently I have faced with an issue were the published article column type is different from the subscriber column type. One of the publication articles column data type was varchar and the subscriber table (destination object ) data type was integer. The data in source object support to have a value similar to 012345 where the first character was the number zero and it didn't created any failures until somebody actually put in a value of C12345. This has lead to fail the replication. The only way to get around this was to delete the actual transaction from distribution database.
I used the following steps to do this:

use distributiondb
1. Get an article Id from MSarticles table
2. run exec sp_browsereplcmds @article_id = 'article Id'
3. get the xact_seqno of the article that caused this issue
4. delete the transaction from MSrepl_transactions.
- delete from MSrepl_transactions where xact_seqno =
5. delete the replication commands from MSrepl_commands
- delete MSrepl_commands where xact_seqno =
6. rerun the distribution agent
7. fix the route cause
Note that there is only one entry in MSrepl_transactions while you may have more than one entries in MSrepl_commands.

SQL Server 2000 virtual server install fails

Recently, I have tried to install SQL Server 2000 virtual server on windows server 2003 and the installation fails. I have installed numerous installations on windows 2000 server and never seen such an error. Tried to Google, went to various news groups and have been thinking of what it could be when I have discovered that this is a known issue that has been reported by Microsoft. For details of how to solve this issue go to
Microsoft help and support page

SQL Server Survival Guide

From time to time I refer to this sql server servival guide and would like to share with you. It focuses on SQL server 2000. For more details click SQL Server Survival Guide

Transactional replication optimisation

The following article from microsoft is a good starting point on how to optimise Transactional replication. Click the link for more info.Transactional replication Optimisation

Query replicated articles

Query replicated articles
If you are using an environment where there many publications and subscriptions it is difficult to find out the list of articles for particular subscriber.

The following code uses system tables in distribution to accomplish the task.
This query will retrieve publisher, publication, subscriber database, subsriber_id

select distinct pub.Publisher_db,pub.Publication, sub.subscriber_db,
sub.subscriber_id,art.article, art.destination_object from
distrib_.dbo.MSsubscriptions sub
join .dbo.MSPublications pub
on sub.Publication_id = pub.Publication_id
join .dbo.MSArticles art
on art.publication_id = pub.publication_id
where sub.subscriber_id = [subsriber_id]

If you have multiple distribution databases on the same server you can use union all with pre-fix of database name.

Removing registered servers

From time to time your registered servers will get out of date for example if the server is de-commissioned or the instance is removed. Using Enterprise manager some times frustrating. The easiest way to remove your registered server is by removing it from registry. The following step will help you how to do this.

a. Go to start menu and click run
b. Enter Regedit and click Ok
c. The registery editor will come up
d. Click on HK_Users and go to edit menu and click find
e. Type in Registered Servers X
f. Find the registered server from the list and delete

Adding the article's partition column(s)

Procedure to add articles partition column
exec sp_articlecolumn
@publication = N'PublicationName', @article = N'ArticleName', @column = N'ColumnName', @operation = N'add'
GO

Windows & SQL cluster

The following are usefull links if you are planning to do Windows & SQL cluster clustering project.
- Troubleshooting cluster node installations
- Designing and Deploying Clusters
- latest information about windows 2003
- Quorum Drive Configuration Information
- Recommended private 'Heartbeat' configuration on a cluster server
- Network Failure Detection and Recovery in a Server Cluster
-How to Change Quorum Disk Designation
- Server Clusters : Storage Area Networks

Password expiration dates

I have various questions from different people asking me how to set password expiration in previous version of sql server (2000 and 7). While trying to find out a way of setting password expiration, I have come accross this article from microsoft.
To view details of how to do this click How to implement password expiration dates for SQL Server 2000 or SQL Server 7.0 login IDs.

SQL Server Improvement Pillars

SQL Server 2008 improvement pillars

Creating Deadlocks

I have have been trying to re-create deadlocks in my database and after a few try I came up with a scripts which does it for me.

CREATE TABLE createDeadlock1 (deadLockId int)
go
Create Table createDeadlock2 (deadlockId int)
-- now populate the table with some data
insert into createDeadlock1
values (1)
insert into createDeadlock2
values(1)
-- now open two connections
steps 1 : In connection 1 run :
begin tran
update createDeadlock1
set deadlockId = 1

step2 : Then in connection 2 run
begin tran
update createDeadlock2
set deadlockId = 1
update createDeadlock1
set deadlockId = 1

step 3 - go back to connection 1 and run

update createDeadlock2
set deadlockId = 1


The above three steps will create a deadlock. Do it many times and you will see the counter value always increasing. To view the counter use sysperfinfo table as shown below.
select * from master.dbo.sysperfinfo
where instance_name = '_total' and counter_name like '%deadlock%'

sysperfinfo not time - adjusted

Recently, I come across a monitoring tool that uses sysperfinfo table to get out number of dead locks/second. It took me a good few minutes to figure out that using this value as a time – adjusted counter was wrong. I am not sure if it is by design or a bug in Microsoft that some of the counters are not time-adjusted. So, the values you are getting for this particular counter is an incremental value. While digging a bit into Microsoft knowledge base I came across an article written by Geoff Hiten (MVP) in February 25, 2004 and it has been reported as a bug. This shows that some of monitoring tools shows you wrong information. To view the article click sysperfinfo not time - adjusted. This article lists the counters that are affected.

SQL Server Katmai

MSDN Forums for Katmai - nice discussion going on. Click the link to participate: SQL Server Katmai

Tables and their row counts

Implication of using different methods of getting Tables and thier row counts in SQL server database :
Tables and their row counts

SQL Server 2008

Latest version of SQL Server 2008 CTP is out this june. More info on this version of SQL server can be found in SQL server 2008 CTP

Latest version of SQL server

To get an idea of what the latest version of SQL server will be, you can ask a SQL guru @Ask SQL Guru

Renaming a SQL Server

The following steps will help you in avoiding most of the issues that arise from renaming a SQL Server.

Open query analyser and to the following simple three steps

1. Drop the old server name (sp_dropserver 'old server name' )
2. Add new server name (sp_addserver 'new server name', 'local')
3. Update sysjobs table column that contain the original server name to new server name

SQL server script library

Very useful SQL server script on SQL server central. For more info SQL server script library