tag:blogger.com,1999:blog-13219340295410217332024-09-06T05:59:52.022+05:30Facing any issue with SQL Server, check this blog and if you don't find it leave the post and I will get back to you as soon as posibleGauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.comBlogger34125tag:blogger.com,1999:blog-1321934029541021733.post-3247320272438444412012-07-02T14:14:00.001+05:302012-07-02T14:14:15.169+05:30

Yesterday when standing with my friends we started discussing best interview questions and one of the best questions is “how to delete duplicate records if there is no unique key in table”. So today I will tell you best way to achieve this, I heard many answers like using temp tables with while loop or cursors, sub tables etc etc. But best is CTE (Common table expression). I

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-91103436129412064662010-10-28T12:18:00.000+05:302010-10-28T12:18:01.693+05:30

Many times we have to create Stored Procedures (SPs) for INSERT, UPDATE and DELETE for all database tables in any given project. These SPs does not have any business logic but just a facilitator for said operation. While preparing for my next training session on Advance T-SQL, I come across a good article on MSDN (http://msdn.microsoft.com/en-us/magazine/cc188749.aspx). This talks about,

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-90090620143189252282010-06-14T15:36:00.001+05:302010-06-14T15:36:55.836+05:30

This query will let you know how much tempdb space is required by query.   GO SELECT * FROM sys.dm_db_session_space_usage WHERE SESSION_ID = @@spid GO <Your Query> GO SELECT * FROM sys.dm_db_session_space_usage WHERE SESSION_ID = @@spid    

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-49249338023079835852010-06-14T14:19:00.000+05:302010-06-14T14:22:26.924+05:30

Here is the nice document by Microsoft on infrastructure planning and design for SQL Server.    

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-27390925005746872522010-06-03T14:22:00.001+05:302010-06-03T14:22:32.839+05:30

Flag Trace Flag Description -1 Sets trace flags for all connections. Used only with DBCC TRACEON and TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems. 105 SQL Server 6.5 you can use maximum 16 tables or sub queries in a single select statement. There is no

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-89735103489513221602010-05-24T12:00:00.001+05:302010-05-24T12:00:57.829+05:30

How to rename table column using T-SQL   Again very simple just use system SP SP_RENAME     Make sure that you are in same DB whose table’s column you want to change EXEC sp_rename 'Schema.Table.ColumnName', 'NewName', 'COLUMN';      

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-9579333716848553452010-05-24T11:38:00.001+05:302010-05-24T11:38:43.546+05:30

How to rename table in SQL Server using T-SQL   How to rename big\huge tables?   The best way of renaming table is using system SP SP_RENAME   Make sure that you are in same DB whose table you want to change.   Use DB SP_RENAME ‘Table1’,’NewName’   SP_RENAME automatically renames the associated index whenever a PRIMARY KEY or UNIQUE

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-62072149938567528552010-05-24T11:13:00.001+05:302010-05-24T11:13:56.649+05:30

How to rename database using T-SQL in SQL Server 2005/2000/2008?   Very simple J Microsoft have provided one system SP which can be used here. Sp_RENAMEDB   This SP needs two parameters 1st Database name which needs to be change And 2nd New name.   SP_RENAMEDB ‘MyDB’ , ‘YourDB’   That’s it.

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-57543821733171698982010-05-14T10:00:00.001+05:302010-05-14T10:00:56.930+05:30

How to predict deadlock in SQL Server?   To know the process details involved in the deadlock one need to add two trace flag. These flag will enable log writer to write full information of deadlock in SQL server error logs.   DBCC TRACEON(1222,-1) DBCC TRACEON(1205,-1)   After this when deadlock happened use xp_readerrorlog to read error log.   Or open SQL

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-70514375899137525592010-05-10T15:00:00.000+05:302010-05-10T15:01:05.852+05:30

Here is the know fact,   The way you write query is never executed in same order, so today I am post the order in which query got executed by SQL Server   FROM JOINS WHERE CLAUSE GROUP BY HAVING SELECT DISTINCT ORDER BY TOP    

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-66267395726576066752010-04-28T11:17:00.001+05:302010-04-28T11:17:33.610+05:30

This script will let you know “how much your index is fragmented”   SELECT              OBJECT_NAME(o.object_id) AS TableName             ,i.name  AS IndexName             ,i.type_desc AS IndexType &

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-75795463449174629202010-04-27T15:12:00.001+05:302010-04-27T15:12:18.046+05:30

Upgrading to SQL 2008 is simple and easy. First you need to make sure that you have latest service pack applied on your current box, for SQL 2000 it SP4. Then run SQL 2008 upgrade advisor on current box and resolve all issues report then by upgrade advisor. Then run the step-up and choose upgrade from SQL 2000 and follow the instructions.   For more details: http://msdn.microsoft.com

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-20320027046201173232010-04-27T13:16:00.001+05:302010-04-27T13:16:38.079+05:30

  DECLARE @SPID int   CREATE TABLE #process (   SPID int NOT NULL , Status varchar (255) NOT NULL , Login varchar (255) NOT NULL , HostName varchar (255) NOT NULL , BlkBy varchar(10) NOT NULL , DBName varchar (255) null , Command varchar (255) NOT NULL , CPUTime int NOT NULL , DiskIO int NOT NULL , LastBatch varchar (255) NOT NULL ,

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-30251002208656790602010-04-14T16:34:00.000+05:302010-04-14T17:04:39.034+05:30

Here are script to create linkserver between SQL Server and MYSQL     EXEC master.dbo.sp_addlinkedserver @server = N’MYSQLDB’, @srvproduct=N'MySQL ODBC 5.1 driver', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.1 driver};Server=172.XXX.XXX.XXX;Port=3306;Option=131072;Stmt=;Database=trial;Uid=user;Pwd=@@'     EXEC master.dbo.sp_addlinkedsrvlogin @

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-57578444679850602702010-04-08T13:25:00.003+05:302010-04-08T13:25:54.779+05:30

How to change Collation of database (non systems)   ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-76106990009212841802010-04-08T13:25:00.001+05:302010-04-08T13:25:34.552+05:30

For this you need to rebuild your master db and you will lose all user databases so before that backup every thing.   Run following command on command prompt:   start /wait \\172.19.19.6\"sql dump on 10.105.98.68"\"SQL 2005 64bit CD1"\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXXXX SQLCOLLATION=

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-4125022789098307832010-04-01T13:03:00.000+05:302010-04-01T13:33:38.806+05:30

How to make query to wait for some time WAITFOR clause will cause a delay in the execution of the subsequent statements until the condition is met. WAITFOR clause can be used along with Time or Delay WAITFOR DELAY When WAITFOR DELAY is used a delay duration needs to be mentioned which is of the format HH:MM:SS format followed by one thousandth of a second’s value. In the following

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-75353208516671936632010-03-22T14:26:00.001+05:302010-03-22T14:26:25.436+05:30

  The buffer cache hit ratio value measures how well your pages are staying in the buffer cache.  The closer the buffer cache hit ratio is to 100 % the better your buffer cache is being utilized, and the better performance you will get out of your SQL Server.   Here is how you would use this DMV to calculate the buffer cache hit ratio:   SELECT cast((CAST(

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-43379806325550488622010-03-19T18:40:00.003+05:302010-03-19T18:51:45.617+05:30

Memory or RAM utilization by SQL Server Here is the Query: SELECT  cntr_value/1024 as 'MB used'FROM master.dbo.sysperfinfoWhere object_name = 'SQLServer:Memory Manager' and counter_name = 'Total Server Memory (KB)' or  DBCC MEMORYSTATUS  Apart from this you can use Prefmon with counters name SQLServer:Memory Manager, Total Server Memory (KB)And if server is only used for SQL

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-47518153390934358802010-03-10T11:56:00.001+05:302010-03-10T11:56:24.594+05:30

Prefmon Counter to be watch for High CPU utilization   Performance Counter Counter Object Threshold Notes % Processor Time Processor > 80% Potential causes include memory pressure, low query plan reuse, non-optimized queries. Context Switches/sec System > 5000 x processors

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-89385018965543407242010-03-08T10:04:00.001+05:302010-03-08T10:04:12.853+05:30

Method 1 to Shrink Tempdb loadTOCNode(2, 'summary'); This method requires you to restart SQL Server. Stop SQL Server. Start SQL Server by typing the following command: sqlservr -c -f Run Following Command ALTER DATABASE tempdb MODIFY FILE(NAME = 'tempdev', SIZE = target_size_in_MB) ALTER DATABASE tempdb MODIFY FILE(NAME = 'templog', SIZE = target_size_in_MB)  &

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-58338993390035112622010-03-03T12:55:00.001+05:302010-03-03T12:55:20.163+05:30

Try to avoid WHERE clauses that are non-sargable. The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument," which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-58837976167600430052010-03-03T11:19:00.000+05:302010-03-03T11:25:31.115+05:30

What to do if Virtual Address Fragmentation and getting OOM (out-of-memory) errors in your SQL Server.  More RAM is definitely not a solution here! Here are some sample error messages you might have seen in your SQL Errorlog which will indicate if this is physical memory or virtual memory issue: SQL 2000 WARNING: Failed to reserve contiguous memory of Size= 65536. SQL Server could

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0tag:blogger.com,1999:blog-1321934029541021733.post-30459808465884908802010-02-26T15:57:00.001+05:302010-02-26T15:57:35.030+05:30

DBA Morning Checklist Backups - Verify that the Network Backups are good by checking the backup emails. If a backup did not complete, contact _____ in the networking group, and send an email to the DBA group. - Check the SQL Server backups. If a backup failed, research the cause of the failure and ensure that it is scheduled to run tonight. - Check the database backup run duration of all

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com2tag:blogger.com,1999:blog-1321934029541021733.post-555810292105527772010-02-22T18:26:00.001+05:302010-02-22T18:29:21.041+05:30

Create function fn_CSVString (@CSVStr varchar(8000) ,@Delim varchar(10)) returns @CSV table (s varchar(1000)) as begin declare @i int , @j int select @i = 1 while @i <= len(@CSVStr) begin select @j = charindex(@Delim, @CSVStr, @i) if @j = 0 begin select @j = len(@CSVStr) + 1 end insert @CSV select substring(@CSVStr, @i, @j - @i) select @i = @j + len(@Delim) end Select * from @CSV return end

Gauravhttp://www.blogger.com/profile/01284879277606726422noreply@blogger.com0