DBCC Checkdb gives error when you restoring database with CONTINUE_AFTER_ERROR

DBCC Checkdb gives error when you have finished restoring database with CONTINUE_AFTER_ERROR and you run DBCC CHECKDB. This will gives you an Error 8967 there may be inconsistancies in database or metadata which may lead to throw this error message....there is a workaround released by Microsoft Read workaround for Error 8967

Read More

Why rollback is taking much longer time

Have you ever thought what is a reason that makes Rollback time consuming process?
Whenever you kill some spid or rollback transaction it would take 3 or 4 times more time then it has taken to execute that particular batch/transaction!!!! The reason is that the Rollback is single threaded and hence it most likely to take much longer time read complete article here

Read More

How T-SQL Kill Command works for MS SQL Server

Often I've seen a question wherein user has posted a query that what is kill command is doing in background? What is sleeping session status in sp_who2 output ? What is awaiting session status in sp_who2 output?
Awaiting or sleeping session status is a connection waiting for query from client, i.e. you've logged in using the application and you are yet to open and run a report, so this idle time is showing as a awaiting or sleeping session
You can find some more insight on How T-SQL Kill Command works!! and What is Awaiting/Sleeping status!!

Read More

DBCC Shrinkfile and DBCC Shrinkdb is taking more time

DBCC Shrinkfile and DBCC Shrinkdb would likely to take more time for SQL Server 2005 then it would take for SQL Server 2000. The question is why it is so!!! I was reading through some articles and find a wonderfull explaination on this, there is additional logic added to compact text/image (LOB datatypes) in SQL Server 2005 and hence it is taking some more time....Read complete article here

Enjoy reading....

Read More

SQL Server 2005 SP3 is available for download

Microsoft has released SP3 for SQL Server 2005 and it is available for download. There are many things that were fixed and added to SQL Server 2005 SP3 below are the links

Find out what is new SQL Server 2005 SP3 http://technet.microsoft.com/en-us/library/dd353312(SQL.90).aspx
List of bugs that were fixed in SP3 are listed here http://support.microsoft.com/?id=955706

Download SQL Server 2005 SP3 here http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

Read More

Backup, Restoring and Disaster Recovery

Hi All,

Here is an excellent piece of information on Backup,Restoration and Disaster Recovery with the suggestion and related source of information.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

Happy reading.

Read More

How to create partitioned table and index in SQL Server 2005

I have found good article on how to create partitioned table and index in SQL Server 2005 here is the link of the article http://msdn.microsoft.com/en-us/library/ms345146.aspx

hope this would helps

Read More

What is Stored Procedure and How to write Stored Procedure

Stored Procedure (SP, here after I will refer it as SP) is as its name indicates stored in a database . SP contains one or more then single T-SQL in it, and it is re-usable. The beauty or advantage of SP is, it will get complied and stored in Database, now when ever you required to run the T-SQL or bunch of T-SQL you don't need to write it and compile it again, just call that Stored Procedure(SP).
Whereas in case of individual T-SQL it will get complied every time you run them; so it helps to reduce your time writing T-SQL, it saves CPU and I/O times it would take to compile and to create execution plan.

There are some advantages of using SPs, they are:
1. You do not need to rewrite your T-SQL which you would required to run often
2. It will get complied and stored in DB, and this will helps you saving your CPU & I/O cycle. * In case if some modification is done in SP you would required to re-compile it.
3. Can accept parameter as input value and will return output based on it
4. You can call one SP in another
5. You can have security configured for them, i.e. can grant or deny exeecute permission for particular user/role/group
6. SP which has parameters can help you protect from SQL Injection Attack
7. SP will help you reduce your network round-trip and hence network traffic

Here are some basic instruction and input on how to write SP(s).
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx http://www.sql-server-performance.com/articles/dev/create_stored_procedures_tsql_p1.aspx

Read More

should I shrink my database

I have come across to the post "should I shrink my database" many times, this is certainly not recommended unless it is required. When we have ample space on the hard drive let it be in the size it is.

Because what happens is when you shrink it and as time passes by it would required to grow again and this time it again involves your CPU and I/O cycle which would be costly affair; again, herealso there may be counter arguments too but this is what certainly not recommended on other hand it *depneds* on case to case bases.

What we should really do is to investigate the reasons, why it has grown that big (if you feel so) for example
1. Auto growth option,
2. Activities like BCP/Bulk insert,
3. Reindex,
4. defregment of database

Please refer Tibor Karsazi's article here on the same topic http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Hope this would helps.

Read More

New KB Articles on SQL Server 2005 and SQL Server 2008

Here are link of new KB Articles released on SQL Server 2005 and SQL Server 2008
List of Builds that are released after SQL Server 2005 SP2
http://support.microsoft.com/kb/937137/
List of Builds that are released after SQL Server 2008
http://support.microsoft.com/kb/956909/

FIX: When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order http://support.microsoft.com/kb/926292

Distribution Agent do not skip error 20598 SQL Server 2008 http://support.microsoft.com/kb/956376

The syntax for '' is incorrect in SQL Server 2005 Analysis Services http://support.microsoft.com/kb/956039

Read More

SQL Server 2008 - download November CTP now!!!

SQL Server 2008 November CTP is available for download, click here to grab your copy of SQL Server 2008

Here are some more resources on SQL Server 2008

SQL Server 20008 Product Overview

SQL Server 2008 Videos

SQL Server 2008 White Papers

SQL Server 2008 Webcasts

Happy Learning all :-)

Read More

MVP Again - New year gift from Microsoft

I got my MVP award renewal for the 2nd year, this has been a wonderful gift on New Year.

Read More

Learn SQL Server 2008 from Microsoft

Since some times their is a discussion on what's new in SQL Server 2008, when the reference material will be available!! Here is an answer to this, Microsoft has introduce a course at its E-Learning, the course aims to educate IT Professional who has prior experience working with SQL Server. Microsoft has made learning SQL Server 2008 easy by offering e-learning course, the course is devided into three category:

Enterprise Data Plateform

Business Intellegence

Database Developement

more details on this course is available here, all you need to have is a Windows Live Account https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=139087

Read More

Some Tips for Merge Replication

* SQLAgent service should run under same domain account, which has local admin privilege and “Log in as a service” permission. This requires on both the server. In case, if SQL Server is not part of any Domain. We can use the user account which has identical name and password at servers i.e. ReplUser on SRVA with password ReplUser! and identically ReplUser on SRVB with password ReplUser!

* User account that is used to run the SQLAgent services have to have sysadmin privilege on both server, and it should be added in to db_owner fixed db role for database which is being replicated/replicating.

* Should include all the tables which are referenced, this will help to keep data consistency.

* If there are text/image column we need to update those column explicitly Updatetext statement. http://msdn2.microsoft.com/en-us/library/aa260658(SQL.80).aspx

* Try to avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.

* Define both server (Publisher and Subscriber) as remote server at each other.

* Allocate adequate space for distribution, subscription and publication db.

* Avoid using Identity column as PK but if it is already implemented modify the property of Identity column as ‘Not for Replication’.

* Rowguid column is must, which also can be added at the time of configuring Merge replication.

* sp_dropmergearticle can be used to drop article from publication.

* sp_addmergearticle can be used to add article to publication.

Below are some links that will be usefull for replication troubleshooting, they are:

Troubleshooting Merge Replication http://support.microsoft.com/kb/315521 and

http://msdn2.microsoft.com/en-us/library/aa237486(SQL.80).aspx

Managing Identity values in Replication environment http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx

Violation of Primary Key (PK) Constraint http://support.microsoft.com/kb/813494

Merge agent incorrectly deletes rows after PK violation error

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

Adding and Droping an article in existing publication http://msdn2.microsoft.com/en-us/library/ms152493.aspx

Working with BLOB data in Replciation http://technet.microsoft.com/en-us/library/ms151206.aspx

Read More

Estimating the size of Table

Many time it happens we were wrong with the Estimation of Table Size, wrong estimation of Row Size can trouble us with the growth of the database and ultimately we are ending up with the space crunch, and all this because of wrong Estimation of Table Size. So what we should consider the most with Estimating Table Sizing!!! Nullbitmap is the most important criteria here to take into consideration whenever you do a Estimation of Row Size, here is an excellent article on Row Size Estimation http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/ms175991.aspx

Read More