tag:blogger.com,1999:blog-22786534474983754502024-03-07T21:24:38.393-08:00MS SQL ServerMy MS SQL Server learningsPradeep Nhttp://www.blogger.com/profile/04030670588614352190noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-2278653447498375450.post-33871092561965395152011-12-06T06:10:00.001-08:002011-12-07T01:37:38.139-08:00[SQLSTATE HY000] (Error 1204)<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">Recently I moved a database used
by Vcenter application, to a new instance, this instance had 2 GB RAM. AS part
of the move there were jobs that had to be moved as well.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">Three jobs in particular started
failing immediately thereafter and the message logged in job history was as
below:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"><br /></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #DBE5F1; border-collapse: collapse; border: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt;" valign="top"><div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<i><span style="color: #c00000; font-family: Cambria, serif;">“The instance of
the SQL Server Database Engine cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users. Ask the database
administrator to check the lock and memory configuration for this instance,
or to check for long-running transactions. [SQLSTATE HY000] (Error 1204). The step failed.”<o:p></o:p></span></i></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I had not observed this on the
earlier server, and that instance had up to 6 GB memory. And so, I increased
the RAM to 3 GB, on the new instance. Then, post restart the problem seemed to
resolve. But next day the issue was back.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I have read that up to 60% of the
available memory is the maximum that SQL server will use for locks. So I tried checking the amount of memory used
by the lock manager, using PERFMON counter lock memory (KB) and it read 1.7 GB,
which was nearly 57 % of the total 3 GB available to the instance. But then, before
increasing the RAM up to 6 GB, as it was in the old server, I wanted to check the
DML fired by the jobs.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I reviewed each of the jobs, and
these called stored procedures, all three are similar, used to purge historical
data, (3 jobs for 4 separate tables, all structurally same) <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">The section of code in the procedure is below:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"><br /></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #DBE5F1; border-collapse: collapse; border: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">BEGIN
TRANSACTION <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">DELETE
FROM <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">VPX_HIST_STAT2
WITH (ROWLOCK) <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">WHERE
TIME_ID IN (SELECT TOP (@batchsize)
TIME_ID FROM <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">VPX_HIST_STAT2
WHERE NOT EXISTS ( SELECT 1 FROM <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">VPX_SAMPLE_TIME2
VTD <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">WHERE
VTD.TIME_id=VPX_HIST_STAT2.time_id ))
<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">SET
@deletedHistStatsCount = @@ROWCOUNT<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">COMMIT
TRANSACTION<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I checked for the total number of
records and found table- VPX_HIST_STAT1 had over 10 million rows, similarly
other jobs deleted rows from tables similarly structured, with almost as many
rows VPX_HIST_STAT2, VPX_HIST_STAT3, VPX_HIST_STAT4<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I noticed on these tables there
was not a usable index which the DELETE’s WHERE clause (in this case, column
name TIME_ID) could make use of.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">The column name TIME_ID was part
of the composite PK clustered index on the table, but this column was the
second column, and was therefore of no use to query.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">I decided to add a non clustered
index on time_id column, and wanted to analyze the query using database tuning
advisor. As expected, it suggested the same index.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">The section of code is below:<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"><br /></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #DBE5F1; border-collapse: collapse; border: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr>
<td style="border: solid windowtext 1.0pt; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">CREATE
NONCLUSTERED INDEX [NIDX_TIME_ID] ON [dbo].[VPX_HIST_STAT2] <o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">(<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;"> [TIME_ID] ASC<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">)<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">INCLUDE
([COUNTER_ID],<o:p></o:p></span></div>
<div class="MsoNormalCxSpMiddle" style="margin-bottom: 0.0001pt;">
<span style="color: #c00000; font-family: Cambria, serif;">[STAT_VAL])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE
= OFF) ON [PRIMARY]</span><span style="font-family: Cambria, serif;"><o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormalCxSpMiddle">
<span style="font-family: Cambria, serif;"><o:p> </o:p></span><span class="Apple-style-span" style="font-family: Cambria, serif;"> </span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;"> I created the required index, re-ran the jobs,
all success and didn’t require a memory increase as I initial would have wanted
to do.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">Another point I would like to
mention here is that each of the delete DML included a rowlock query hint. The
delete query affecting a table with nearly 10 million, meant the rowlock would
request that many rowlocks, causing a high number of locks and hence an increase
in the lock memory. Under normal cases, with no rowlock query hint, by default SQL
server would request a rowlock, but when it figures a large part of the table
is being affected, higher level locks, like page lock or table lock are
requested ( known as <b><i>lock escalation</i></b> ). Note, that lock
escalation can be an issue in certain other circumstances. In this case, I
felt, with a delete affecting so many rows, rowlock hint was not required;
rather allowing SQL Server to decide the type of lock was a much better choice.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Cambria, serif;">In fact, I want to test this
scenario on the old server. Reduce the memory from 6 GB to 2 GB, not create the
new index, and rather eliminate the rowlock query hints. Hopefully, I can do it
sometime soon and validate my understanding and resolution further. <o:p></o:p></span></div>
<br />
<span class="Apple-style-span" style="font-family: Cambria, serif;">This issue is also covered under an article at </span><a href="http://support.microsoft.com/kb/323630">http://support.microsoft.com/kb/323630</a><br />
<br /></div>Pradeep Nhttp://www.blogger.com/profile/04030670588614352190noreply@blogger.com3tag:blogger.com,1999:blog-2278653447498375450.post-75626066211338025702011-10-15T14:19:00.000-07:002011-10-15T14:22:39.959-07:00Error 5184<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"></span><br />
<br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">Server: Msg 5184, Level 16, State 2, Line 1. Cannot use file '%.*ls' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;"><br />
</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">Last night we had a planned database file movement from a slower disk to a new faster disk. This database is on a two node cluster. I was informed all actions pertaining to cluster were already taken care off.</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">All I had in my plate was to detach the database, move the relevant files over to the new disk and attach it back. I detached, I moved the file, so far so good.</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">I tried to attach the files, after pointing to the new locations. This is when I received this error 5184. The error message was self explanatory and I figured that the disk was not added to the dependencies list for SQL server service in the cluster group.</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">So to achieve this, I took the SQL service offline from cluster administrator, and included the new disk in the dependency list from the properties page of the SQL server service. Then proceeded to start the SQL server, and tried attaching the database. And it succeeded this time.</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;"><br />
</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">This is also covered in this article:</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><span class="Apple-style-span" style="font-size: 14px; line-height: 16px;">http://support.microsoft.com/kb/295732</span></span><br />
<div style="font-size: 14px; line-height: 16px;"><span class="Apple-style-span" style="color: #333333; font-family: Georgia, serif;"><br />
</span></div><br />
</div>Pradeep Nhttp://www.blogger.com/profile/04030670588614352190noreply@blogger.com1tag:blogger.com,1999:blog-2278653447498375450.post-30282886424901272302011-04-26T10:43:00.000-07:002011-04-26T21:10:48.672-07:00Whats better, temporary table or table variable?<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">For some time now, I believed table variables were a better choice than temp tables and that was because I read table variables were in-memory structures. I recently read something otherwise. So I thought I would write this down so it stays “in-memory”.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"> </span><br />
<div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">A DMV useful here is <i style="mso-bidi-font-style: normal;">sys.dm_db_session_space_usage</i>, and the columns of interest to us are <i style="mso-bidi-font-style: normal;">session_id, database_id</i> and <i style="mso-bidi-font-style: normal;">user_objects_alloc_page_count</i> which will show us the pages allocated for objects in <i style="mso-bidi-font-style: normal;">tempdb</i>. </span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Let us create a temporary table as below then query the <i style="mso-bidi-font-style: normal;">sys.dm_db_session_space_usage</i> DMV to see the number of pages allocated for this object and the database in which it is allocated.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;">create table #temptable (COL1 INT)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;">select session_id, db_NAME (database_id) AS DBName, user_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;"><br />
</span></span></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #F2F2F2; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 242; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">session_id<o:p></o:p></span></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">DBName<o:p></o:p></span></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">user_objects_alloc_page_count<o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">55<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">tempdb<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">0<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><u><br />
</u></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">When a create table is run, SQL Server does not allocate page to the table upfront, but rather when the first data is inserted, pages are allocated. So, let us go ahead and insert a single row that is all we need to see the allocation of pages.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;">insert into #temptable (col1) values (1)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Now, let us run the DMV query again, we get the results below:</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;">select session_id, db_NAME (database_id), USER_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;"><br />
</span></span></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #F2F2F2; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 242; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">session_id<o:p></o:p></span></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">DBName<o:p></o:p></span></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">user_objects_alloc_page_count<o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">55<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">tempdb<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">1<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">We see that 1 page is now allocated to the table <i style="mso-bidi-font-style: normal;">#temptable</i> in <i style="mso-bidi-font-style: normal;">tempdb</i>.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Let us see if this is the case with table variables as well. So, create a table variable and insert a row in a single batch, (since the scope a table variable is limited to the batch it is declared, unlike a temporary table whose scope is the entire session that created it) as shown below:</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">DECLARE @TABLEVAR TABLE (COL1 INT) </span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">INSERT INTO @TABLEVAR (COL1) VALUES (1)</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">GO</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Let us query the DMV again, </span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><span style="text-transform: uppercase;">select session_id, db_NAME (database_id) AS DBName, user_objects_alloc_page_count from master.sys.dm_db_session_space_usage where session_id = @@SPID<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #F2F2F2; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 242; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="height: 15.0pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td nowrap="" style="border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><u><span style="color: #c0504d;">session_id<o:p></o:p></span></u></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><u><span style="color: #c0504d;">DBName<o:p></o:p></span></u></div></td> <td nowrap="" style="border-left: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><u><span style="color: #c0504d;">user_objects_alloc_page_count<o:p></o:p></span></u></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: .95in;" valign="top" width="91"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">55<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 76.5pt;" valign="top" width="102"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">tempdb<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 2.5in;" valign="top" width="240"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c0504d;">2<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">We see that a new page was allocated, and even in case of a table variable, page was allocated in the <i style="mso-bidi-font-style: normal;">tempdb</i> and not in the memory.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">So both temporary tables and table variables are not in-memory structures, and that being said, let us look at specific leverages temp tables provide as against table variables. </span><br />
<span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"></span><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Temporary tables allow constraints, indexes, statistics, and schema changes on them, just like any permanent table would. Also, "select into" and "insert into ... exec" are allowed with a temp table. </span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">On the contrary, a table variable allows only constraints, and hence indexes associated with primary key and unique key constraints. Otherwise, we cannot create indexes or statistics; neither can the schema be changed once the table variable is declared. Although indexes, associated with primary key or unique are available, these are not useful, since they are not available at compile time. No statistics, no useful indexes means that the query plan can be bad.</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">So, with all this information at hand, how do we decide which one to use in our TSQL code or when to use. </span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span></div><div class="MsoNormal" style="line-height: normal;"><span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">Although, temp tables do allow constraints, indexes and statistics, we know for a fact, on small tables, an index scan will be relatively more costly than a table scan. So for smaller tables, we don’t quite see a need for indexes or statistics, and hence making table variables a better choice. Also, if the table is small enough, and assuming there is enough memory, the entire table might well be available in the memory, think about it - I guess that is where this myth about table variables being "in-memory structures" originated. Or more so, because the table variables are declared as variables, and variables have been in-memory objects in programming languages. </span><br />
<span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: Georgia, 'Times New Roman', serif;">And, needless to say, if you foresee more number of rows, a temporary table is a better choice to make.</span></div></div>Pradeep Nhttp://www.blogger.com/profile/04030670588614352190noreply@blogger.com3tag:blogger.com,1999:blog-2278653447498375450.post-21981630656971555402011-04-19T07:39:00.000-07:002011-04-19T10:33:02.169-07:00My encounters with DBCC CHECKDB - 1<div dir="ltr" style="text-align: left;" trbidi="on"><br />
<div class="MsoNormal" style="line-height: normal;"></div><div class="MsoNormal" style="line-height: normal;"></div><div class="MsoNormal">After recovering from a disk crash on one of the production SQL Server 2000 servers, I ran DBCC CHECKDB on every database to ensure we don’t have any errors. It turned out otherwise, and we did have 2 consistency errors reported.</div><div class="MsoNormal">The part of DBCC CHECKDB output containing the error is copied below:</div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #D9D9D9; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0; mso-yfti-lastrow: yes;"> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Server: Msg 8928, Level 16, State 1, Line 1<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Object ID 1993058136, index ID 2: Page (6:23238457) could not be processed. See other errors for details.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Server: Msg 8944, Level 16, State 1, Line 1<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Table error: Object ID 1993058136, index ID 2, page (6:23238457), row 10. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 28416 and 37.</span></div></td> </tr>
</tbody></table><div class="MsoNormal"><br />
</div><div class="MsoNormal">And the last part of CHECKDB output, summarized as below:</div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #D9D9D9; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0; mso-yfti-lastrow: yes;"> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">CHECKDB found 0 allocation errors and 2 consistency errors in database 'MIRA_OTHERS_CONTENT_DB'.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MIRA_OTHERS_CONTENT_DB ).<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span><span style="color: #1f497d;"><o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal"><br />
</div><div class="MsoNormal">To start with, here is some info pertaining to SQL Server, using <b style="mso-bidi-font-weight: normal;">SELECT @@VERSION<o:p></o:p></b></div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #D9D9D9; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0; mso-yfti-lastrow: yes;"> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal"><br />
</div><div class="MsoNormal">The database involved is called: MIRA_OTHERS_CONTENT_DB;</div><div class="MsoNormal">Using the object ID value from the above output, I could find out the table on which errors were reported.</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SELECT OBJECT_NAME<span style="text-transform: uppercase;"> (1993058136)</span> as TableName<o:p></o:p></b></div><div class="MsoNormal">TableName: Docs</div><div class="MsoNormal">Going back to output above, it was obvious that the error was more precisely on an index, index id 2. Using this information and the object id, one could easily identify the index name affected:</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">USE MIRA_OTHERS_CONTENT_DB;<o:p></o:p></b></div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">SELECT NAME AS IndexName from SYSINDEXES WHERE ID = 1993058136 and INDID = 2;<o:p></o:p></b></div><div class="MsoNormal">Returned, IndexName: Docs_IdUnique</div><div class="MsoNormal">Next, I ran <b>SP_HELPINDEX Docs</b>, for better understanding of the index definition.</div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="background: #D9D9D9; border-collapse: collapse; border: none; mso-background-themecolor: background1; mso-background-themeshade: 217; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;"><tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;"> <td style="border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 1.45in;" valign="top" width="139"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Index Name<o:p></o:p></span></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 164.8pt;" valign="top" width="220"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Index Description<o:p></o:p></span></div></td> <td style="border-left: none; border: solid black 1.0pt; mso-border-alt: solid black .5pt; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-themecolor: text1; mso-border-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 35.4pt;" valign="top" width="47"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Index Keys<o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 1;"> <td nowrap="" style="border-top: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 1.45in;" valign="top" width="139"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Docs_IdUnique<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 164.8pt;" valign="top" width="220"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">nonclustered, unique, unique key located on PRIMARY<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 35.4pt;" valign="top" width="47"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Id<o:p></o:p></span></div></td> </tr>
<tr style="height: 15.0pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes;"> <td nowrap="" style="border-top: none; border: solid black 1.0pt; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 1.45in;" valign="top" width="139"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">Docs_PK<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 164.8pt;" valign="top" width="220"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">clustered, unique, primary key located on PRIMARY<o:p></o:p></span></div></td> <td nowrap="" style="border-bottom: solid black 1.0pt; border-left: none; border-right: solid black 1.0pt; border-top: none; height: 15.0pt; mso-border-alt: solid black .5pt; mso-border-bottom-themecolor: text1; mso-border-left-alt: solid black .5pt; mso-border-left-themecolor: text1; mso-border-right-themecolor: text1; mso-border-themecolor: text1; mso-border-top-alt: solid black .5pt; mso-border-top-themecolor: text1; padding: 0in 5.4pt 0in 5.4pt; width: 35.4pt;" valign="top" width="47"><div class="MsoNormal" style="line-height: 115%; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: #c00000;">SiteId, DirName, LeafName<o:p></o:p></span></div></td> </tr>
</tbody></table><div class="MsoNormal"><br />
</div><div class="MsoNormal">Once the index name and the table name are known, all one has to do is to just re-create the non clustered index. We could use DBCC DBREINDEX or CREATE INDEX… DROP_EXISTIN to re-create the non-clustered index to eliminate corruption errors as below:</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;"><u>Option 1:<o:p></o:p></u></i></b></div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">DBCC DBREINDEX ( ‘Docs’,’ Docs_IdUnique’)<o:p></o:p></b></div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;"><u>Option 2:<o:p></o:p></u></i></b></div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">CREATE UNIQUE INDEX [Docs_IdUnique] ON [dbo].[Docs] ([Id])<o:p></o:p></b></div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]<o:p></o:p></b></div><div class="MsoNormal">Initially, I thought I could manually drop and create the index, as shown below</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">DROP INDEX Docs. Docs_IdUnique<o:p></o:p></b></div><div class="MsoNormal">But this statement failed, since the unique index is bound by the unique constraint. So then I thought I will drop the constraint. So I ran,</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;">ALTER TABLE Docs DROP CONSTRAINT Docs_IdUnique<o:p></o:p></b></div><div class="MsoNormal">And to my surprise, I figure this index enforces the full-text key for Docs table</div><div class="MsoNormal"><b style="mso-bidi-font-weight: normal;"><i style="mso-bidi-font-style: normal;">Cannot drop index 'Docs_IdUnique' because it enforces the full-text key for table 'Docs'.<o:p></o:p></i></b></div><div class="MsoNormal">At that time, I thought over, and figured I could use one of the above two options. If they did not work, I would do a drop and create the clustered index, which in SQL Server 2000 would force the non-clustered index to be rebuilt.</div><div class="MsoNormal">Note: I would like to remind, that rebuild index behavior is different in SQL Server 2005 and 2008.</div><br />
<br />
<br />
</div>Pradeep Nhttp://www.blogger.com/profile/04030670588614352190noreply@blogger.com2