tag:blogger.com,1999:blog-79026497488430031282024-03-12T21:45:54.860-05:00SP's Microsoft SQL Server Tips & TricksSandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-7902649748843003128.post-20482872283391050212012-07-20T15:35:00.001-05:002012-07-20T15:35:54.939-05:00SQL Server 2008 – High Memory Utilization / Memory Leak / Not releasing Memory<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
I have been working with SQL 2000 and since that knowing
that SQL server ill use and release memory depending on its requirement and unitization.
<span style="mso-spacerun: yes;"> </span>This is not true with SQL 2008 while it
is installed on Windows server 2008.</div>
<div class="MsoNormal">
SQL 2008 will utilize as much memory assign to SQL Server
while high resource oriented task running. It will never releasing this memory
back to system though task finish. If any other task needed memory SQL
internally flush out the older data from the memory. To release memory reserved
by SQL 2008 you need to re-start SQL services.<span style="mso-spacerun: yes;">
</span></div>
<div class="MsoNormal">
I also observed sometime in task manager also assigned
memory not showing against the SQL Server.</div>
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-31119944042698989252012-07-19T09:11:00.000-05:002012-07-19T09:11:00.432-05:00How to use RunAs with SQL Server Management Studio and creating Quick Lunch<br />
DBA having two separate account is normal practice in industry standard. In that case it is require often require to connect SSMS with different account. Run As is option for that.<br />
<br />
There are two ways to connect SSMS with Run As <br />
1. Press and Hold Shift Key and Right Click on SSMS gives you option for Run As.<br />
2. Run as different user using Command Prompt and creating short cut helps you to quick login<br />
<br />
Open command prompt and type runas /?<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-NfqqEmXP3WY/UAgVNXYU1OI/AAAAAAAAFPo/sHtmEXfoLYE/s1600/cmd.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" hda="true" height="244" src="http://2.bp.blogspot.com/-NfqqEmXP3WY/UAgVNXYU1OI/AAAAAAAAFPo/sHtmEXfoLYE/s320/cmd.png" width="320" /></a></div>
Enter below command in command prompt to open SSMS and it will prompt for password. You need to replace domain\spatel with your credentials.<br />
<br />
C:\runas /user:domain\spatel "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"<br />
<br />
It is easy to create shortcut for this.<br />
<br />
Right click on desktop and click on create new short cut<br />
<br />
And type above command in target. Adding this short cut to Quick Launch helps you to easy login in SSMS with ran as option. This will only ask for password so no need to enter user name everytime.Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-1370862637539235682011-09-21T13:02:00.001-05:002011-09-21T13:05:12.968-05:00Controls the concatenation results with NULL are treated as NULL or empty string values with SET CONCAT_NULL_YIELDS_NULL<div class="MsoNormal" style="text-align: justify;">When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, <code><span style="font-size: 10pt; line-height: 115%;">SELECT 'Bunty' + NULL</span></code> yields <code><span style="font-size: 10pt; line-height: 115%;">NULL</span></code>. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, <code><span style="font-size: 10pt; line-height: 115%;">SELECT 'Bunty' + NULL</span></code> yields <code><span style="font-size: 10pt; line-height: 115%;">Bunty</span></code>.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">PRINT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: red;">'Setting CONCAT_NULL_YIELDS_NULL ON'</span><span style="color: grey;">;</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">CONCAT_NULL_YIELDS_NULL</span> <span style="color: blue;">ON</span><span style="color: grey;">;</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: red;">'Bunty'</span> <span style="color: grey;">+</span> <span style="color: grey;">NULL</span> <span style="color: grey;">;</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">CONCAT_NULL_YIELDS_NULL</span> <span style="color: blue;">OFF</span><span style="color: grey;">;</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: red;">'Bunty'</span> <span style="color: grey;">+</span> <span style="color: grey;">NULL;</span> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span><br />
<br />
<div style="color: black;"><span style="font-family: "Courier New"; font-size: 10pt;">Below TSQL returns the current setting value for CONCAT_NULL_YIELDS_NULL</span></div><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')</span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;"> </span></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-4353905544257090952011-09-08T15:31:00.000-05:002011-09-08T15:31:27.731-05:00How to change Dynamic Port of the SQL Server Named Instance to Static port on stand alone or SQL 2005 cluster?<!--[if !mso]> <style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style> <![endif]--><!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves>false</w:TrackMoves> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<br />
<div class="MsoNormal" style="text-align: justify;">SQL server the default instance has fixed TCP/IP port 1433 while for the named instances it has random dynamic selected port assigned. This is the default selection while you are installing SQL server. You might require changing the SQL server dynamic port to static port, especially when clients connect to the SQL server thru firewall. In that case particular port requires opening on firewall to allow connection to SQL Server.</div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;">Standalone Instance:</div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;">Start<span style="font-family: Wingdings;"><span>à</span></span>Program Files<span style="font-family: Wingdings;"><span>à</span></span>Microsoft SQL 2005<span style="font-family: Wingdings;"><span>à</span></span>Configuration tools<span style="font-family: Wingdings;"><span>à</span></span>SQL Server Configuration manager.</div><div class="MsoNormal" style="text-align: justify;">Selection SQL Server 2005 network configuration, click on the “Protocols for <instancename>” and on right hand side right click<span> </span>and go to properties of TCP/IP as shown in below screenshot.</instancename></div><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-EGcdel69WNA/Tmkl5ms4D6I/AAAAAAAACfA/NDRWI_xZjfU/s1600/Fig1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="119" src="http://4.bp.blogspot.com/-EGcdel69WNA/Tmkl5ms4D6I/AAAAAAAACfA/NDRWI_xZjfU/s320/Fig1.bmp" width="320" /></a></div><div class="MsoNormal"><span><br />
</span></div><div class="MsoNormal">Click on the IP Addresses tab on the top and keep the Dynamic port row Blank and write the require port number on TCP Ports row in the IP ALL section as shown in below screenshot.</div><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/-1gxv8amZsGk/Tmkl9Vnc56I/AAAAAAAACfE/8Fbatm6RCsc/s1600/fig2.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://1.bp.blogspot.com/-1gxv8amZsGk/Tmkl9Vnc56I/AAAAAAAACfE/8Fbatm6RCsc/s320/fig2.bmp" width="292" /></a></div><div class="MsoNormal"><span><br />
</span></div><div class="MsoNormal">It require the restart the SQL server services to change the take effects. </div><div class="MsoNormal">You can verify the setting by below registry key</div><div class="MsoNormal"><span style="font-size: 10pt; line-height: 115%;">HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance name=""> \MSSQLServer\SuperSocketNetLib\Tcp</instance></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Clustered Instance:</div><div class="MsoNormal"><br />
</div><div class="MsoNormal" style="text-align: justify;">It requires following the same above steps but sometimes when you restart the services / node port change back to dynamic port. This is because while you restart the services local copy of registry not change and checkpoint get the old port number.</div><div class="MsoNormal">So you need to follow below steps.</div><div class="MsoNormal">Disable the check pointing to the quorum </div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0.0001pt 0.5in;"><span style="font-family: "Times New Roman","serif"; font-size: 10pt;">Cluster res "SQL Server (Instance Name)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"</span><span style="font-family: "Times New Roman","serif"; font-size: 12pt;"></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Change the Dynamic port of the SQL server to static on all nodes as above mentioned steps.</div><div class="MsoNormal">Enable the check pointing to the quorum</div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0.0001pt 0.5in;"><span style="font-family: "Times New Roman","serif"; font-size: 10pt;">Cluster res "SQL Server (Instance Name)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"</span></div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0.0001pt 0.5in;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0.0001pt 0.5in;"><span style="font-family: "Times New Roman","serif"; font-size: 10pt;">Make sure to give appropriate path for MSSQLSERVER. In above example I have MSSQL.3 because it is third instance I have installed. You may have different values.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal">Bring the SQL Server online after change.</div><div class="MsoNormal">Make sure port change after SQL services online.</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com1tag:blogger.com,1999:blog-7902649748843003128.post-90664426387558050442011-09-08T13:51:00.003-05:002011-09-08T13:53:44.192-05:00How to find text/word inside SQL Server store procedure<div class="MsoNormal">Any of the following snippet allows you find a specific word or words from all SQL store procedure. This can be very useful if you want to find all references to table/specific word and remove/replace them. Below code should work on SQL server 2005/2008 (R2).</div><div class="MsoNormal"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">DISTINCT</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;"> </span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">OBJECT_NAME</span><span style="color: grey;">(</span>id<span style="color: grey;">)</span> <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">syscomments</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">text</span> <span style="color: grey;">LIKE</span> <span style="color: red;">'%Search_word%'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> ROUTINE_NAME <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> ROUTINE_DEFINITION</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">INFORMATION_SCHEMA</span><span style="color: grey;">.</span><span style="color: green;">ROUTINES</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> ROUTINE_DEFINITION <span style="color: grey;">LIKE</span> <span style="color: red;">'%</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">Search_word</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">%'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">AND</span> ROUTINE_TYPE <span style="color: grey;">=</span> <span style="color: red;">'PROCEDURE'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> Name</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">procedures</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">OBJECT_DEFINITION</span><span style="color: grey;">(</span><span style="color: magenta;">object_id</span><span style="color: grey;">)</span> <span style="color: grey;">LIKE</span> <span style="color: red;">'%</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">Search_word</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">%'</span> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">OBJECT_NAME</span><span style="color: grey;">(</span><span style="color: magenta;">object_id</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">sql_modules</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">Definition</span> <span style="color: grey;">LIKE</span> <span style="color: red;">'%</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">Search_word</span></span><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: red;">%'</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: grey;">AND</span> <span style="color: magenta;">OBJECTPROPERTY</span><span style="color: grey;">(</span><span style="color: magenta;">object_id</span><span style="color: grey;">,</span> <span style="color: red;">'IsProcedure'</span><span style="color: grey;">)</span> <span style="color: grey;">=</span> 1</span></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com2tag:blogger.com,1999:blog-7902649748843003128.post-4183279095580303792011-07-28T11:47:00.001-05:002011-07-28T11:48:16.939-05:00SSIS Import/Export Wizard: “Unexpected Unrecoverable Error” in SQL 2008 R2.<div class="MsoNormal" style="line-height: normal;"></div><div class="MsoNormal">In my last class I was teaching Import/Export feature for SQL 2008 on windows 7 thru SQL Server Management Studio’s import and export wizard. I am getting “Unexpected and Unrecoverable Error” message with Abort,Retry and Cancel button. Any button you press it just abnormally close the import export wizard. This was very annoying and I was not able to get my work done. </div><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-bj1OGOPbuRg/TjGSbPSf-GI/AAAAAAAACeo/mqstlI6tKJs/s1600/untitled.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="http://3.bp.blogspot.com/-bj1OGOPbuRg/TjGSbPSf-GI/AAAAAAAACeo/mqstlI6tKJs/s400/untitled.bmp" width="390" /></a></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"></div><div class="MsoNormal">After some research I have found that you need to<span style="font-size: small;"><b style="color: magenta;"> install the Extended .Net Framework 4.</b></span> After installing this everything works as expected. You need to make sure to do windows update after the install as there is security patch for it.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Some people also suggest different method to edit “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe.config" and comment out/delete <supportedruntime version="v4.0">. I tried this option but in my case it not works. </supportedruntime></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">I suggest to install .Net framework 4.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-26622953078492232712011-07-14T13:57:00.002-05:002011-07-14T13:57:28.739-05:00How to select / delete/update NULL record?<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal">You cannot use<span> </span>= NULL , you have to use IS NULL</div><div class="MsoNormal">e.g</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Create temporary table</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">TABLE</span> #TempSP <span style="color: grey;">(</span>eid <span style="color: blue;">INT</span> <span style="color: blue;">IDENTITY</span><span style="color: grey;">,</span> Value1 <span style="color: blue;">varchar</span><span style="color: grey;">(</span>10<span style="color: grey;">))</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Insert sample values</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #TempSP <span style="color: blue;">values</span> <span style="color: grey;">(</span><span style="color: red;">'Value1'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #TempSP <span style="color: blue;">values</span> <span style="color: grey;">(</span><span style="color: red;">'Value2'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #TempSP <span style="color: blue;">values</span> <span style="color: grey;">(</span><span style="color: red;">'Value3'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #TempSP <span style="color: blue;">values</span> <span style="color: grey;">(NULL)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Select query for check what's in table</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> #TempSP </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Select query where value1 is NULL</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> #TempSP </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> value1 <span style="color: grey;">IS</span> <span style="color: grey;">NULL</span> <span style="color: green;">-- WHERE value1 = NULL won't work</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Delete rows where value1 is NULL</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DELETE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">FROM</span> #TempSP </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> Value1 <span style="color: grey;">IS</span> <span style="color: grey;">NULL</span><span> </span><span style="color: green;">-- WHERE Value1 = NULL won't work</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Select query for check what's in table</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> #TempSP</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Drop temporary table</span></div><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%;">DROP</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: blue;">TABLE</span> #TempSP</span>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-10757265416099943502011-06-29T13:35:00.000-05:002011-06-29T13:35:00.749-05:00ACID Properties:<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal" style="text-align: justify;"></div><div class="MsoNormal" style="text-align: justify;">ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee database <u>transactions</u> are processed reliably.</div><div class="MsoNormal" style="text-align: justify;"><b><u>Transaction:</u></b> A transaction is one or more actions that are defined as single unit of work.</div><div class="MsoNormal" style="text-align: justify;"><b><u><span style="font-size: 20pt; line-height: 115%;">A</span></u></b><u>tomicity: All or nothing execution.</u></div><div class="MsoNormal" style="text-align: justify;">In a sequence of steps either all actions occur or none. Atomicity means that users do not have to worry about the effect of incomplete transactions. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.</div><div class="MsoNormal" style="text-align: justify;">Example: Moving money from savings to checking accounts , withdrawing money from bank accounts</div><div class="MsoNormal" style="text-align: justify;"><b><u><span style="font-size: 20pt; line-height: 115%;">C</span></u></b><u>onsistency : Execution of transaction results in consistent database. </u></div><div class="MsoNormal" style="text-align: justify;">The database is transformed from one valid state to another valid state.</div><div class="MsoNormal" style="text-align: justify;">Example: If someone moves 5,000$ from their bank account that information is logged. If it can't be logged then the money is not transferred.</div><div class="MsoNormal" style="text-align: justify;"><b><u><span style="font-size: 22pt; line-height: 115%;">I</span></u></b><u>solation : Many transactions may execute concurrently but each is unaware of the others. </u></div><div class="MsoNormal" style="text-align: justify;">The results of a transaction are invisible to other transactions until the transaction is complete.</div><div class="MsoNormal" style="text-align: justify;">Example: <span> </span>If someone querying balance amount at 12.00 AM , now at 12.01 AM user Bunty deposits 250$. The query results even if it finishshes at 12.04AM will not show the changes made by Bunty.</div><div class="MsoNormal" style="text-align: justify;"><b><u><span style="font-size: 24pt; line-height: 115%;">D</span></u></b><u>urability : Persistence.</u></div><div class="MsoNormal" style="text-align: justify;">Once transaction is committed (completed), the results of a transaction are permanent and survive future system and media failures.</div><div class="MsoNormal" style="text-align: justify;">Example: Once your seat is reserved on an airplane, even if the system crashes the moment it finishes writing that information in the database your seat will still be reserved.</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-33372167892411396942011-06-27T16:19:00.003-05:002011-10-19T15:24:04.956-05:00SQL Server : List all failover clustered instance nodes on which an instance of SQL Server can run and Find active node query<div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> <span style="color: grey;">::</span>fn_virtualservernodes<span style="color: grey;">()</span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Above query returns the list of Nodes on which SQL Server can run. If the current server instance is not a clustered server, fun_virtualservernodes returns empty rowset.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">User must have VIEW SERVER STATE permission to execute above query successfully. </div><div class="MsoNormal"><br />
</div><div class="MsoNormal">Note : if you have SQL 2000 , Microsoft recommending to use </div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Select</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: grey;">*</span> <span style="color: blue;">from</span> <span style="color: green;">sys.dm_os_cluster_nodes</span></span><br />
<br />
How to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?<br />
<div style="color: blue;">Select ServerProperty('ComputerNamePhysicalNetBIOS')</div><br />
<br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-65269922139082582712011-06-27T09:32:00.001-05:002011-06-27T11:26:50.542-05:00SQL Server : Last Backup / Restore date of the database.<div class="MsoNormal"></div><div class="MsoNormal">Please find below two methods to know last <b>backup date of SQL database</b>.</div><div class="MsoNormal"><br />
</div><div class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;">1.<span style="font: 7pt "Times New Roman";"> </span>In SSMS , right click on database and check the properties. Refer Last database backup which is available under General tab.</div><div class="MsoListParagraphCxSpFirst" style="text-indent: -0.25in;"><br />
</div><div class="MsoListParagraphCxSpMiddle" style="text-indent: -0.25in;">2.<span style="font: 7pt "Times New Roman";"> </span>Query : you can query msdb..backupset table.</div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> database_name <span style="color: grey;">,</span></span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">MAX</span><span style="color: grey;">(</span>backup_finish_date<span style="color: grey;">)</span> <span style="color: blue;">AS</span> backup_finish_date</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> msdb<span style="color: grey;">..</span>backupset</span></div><div class="MsoListParagraphCxSpMiddle" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> database_name <span style="color: grey;">=</span> <span style="color: red;">'Database_Name'</span></span></div><div class="MsoListParagraphCxSpLast"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%;">GROUP</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: blue;">BY</span> database_name</span></div><div class="MsoListParagraphCxSpLast"><br />
</div><div class="MsoNormal"><b>Last Restore date of database Query</b></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"> <span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> destination_database_name <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">MAX</span><span style="color: grey;">(</span>restore_date<span style="color: grey;">)</span> <span style="color: blue;">AS</span> restore_date</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-indent: 0.5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> msdb<span style="color: grey;">..</span>restorehistory</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-indent: 0.5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> destination_database_name <span style="color: grey;">=</span> <span style="color: red;">'Database_Name'</span></span></div><div class="MsoNormal" style="text-indent: 0.5in;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt; line-height: 115%;">GROUP</span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: blue;">BY</span> destination_database_name</span><br />
<span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> </span></div><div class="MsoListParagraph"><b>Find the all databases restore date with SourceDB , SourceFile and Backup Date</b><br />
<br />
USE msdb<br />
GO<br />
SELECT DBRestored = destination_database_name ,<br />
RestoreDate = restore_date ,<br />
SourceDB = b.database_name ,<br />
SourceFile = physical_name ,<br />
BackupDate = backup_start_date<br />
FROM RestoreHistory h<br />
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id<br />
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id<br />
ORDER BY RestoreDate<br />
<br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-24027081400485054662011-06-20T16:01:00.000-05:002011-06-20T16:01:55.212-05:00SQL Server : sa account locked out / FIX Error : 18486 Login Failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. Unlock SA login<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">There may be multiple situations which end up with SA password locked. </span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Brute force attack: attempt to guess a password using different possible combination,if you have password policy setting enable which locked the account after certain attempts. </span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Multiple unsuccessful attempts to guess ‘SA’ password. Etc...</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Fix:</span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">1.</span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Login with other SQL user which has sysadmin role.</span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Unlocked ‘SA’ login WITH ALTER command</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">ALTER</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">LOGIN</span> sa <span style="color: blue;">WITH</span> PASSWORD <span style="color: grey;">=</span> <span style="color: red;">'currentpassword'</span> UNLOCK <span style="color: grey;">;</span></span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">GO</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">2. Unlocked with SSMS. </span></div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Microsoft SQL Server management studi</span><span style="font-family: Wingdings; font-size: 10pt; line-height: 115%;"><span>o--></span></span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Logins</span><span style="font-family: Wingdings; font-size: 10pt; line-height: 115%;"><span>--></span></span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">sa</span><span style="font-family: Wingdings; font-size: 10pt; line-height: 115%;"><span>--></span></span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Properties</span><span style="font-family: Wingdings; font-size: 10pt; line-height: 115%;"><span>--></span></span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Status</span><span style="font-family: Wingdings; font-size: 10pt; line-height: 115%;"><span>--></span></span><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">unchecked the Login is locked out check box</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">3. Sometime because of Enforce password policy enable , SQL server not giving you specific message when you trying to unlocked the account. In this case first you need to unchecked the ‘Enforced password policy’ , unchecked the Login is locked press OK and again the set the the ‘Enforced Password policy’ option once account is unlocked.</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">It is good practice to disable the sa user to prevent SQL server from hacking. Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. As a best practice you can disable the sa account and use another account with same privileges.in this case you have different user should have same access as ‘SA’</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">it is also possible that to not have a user with sytem admin rights in your database and you only have activated SQL Server authentication.</span></div><div class="MsoNormal" style="text-align: justify;"><br />
</div><div class="MsoNormal" style="text-align: justify;"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">It is possible to change the Server authentication with change value of the registry key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now you can login to SQL server with windows authentication and change the password with the procedure discuss in above steps.</span></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-26267198211041552442011-06-17T00:19:00.003-05:002011-06-17T00:34:14.470-05:00Microsoft SQL Server Cheat Sheets<div class="MsoNormal"></div><div class="MsoNormal">Some useful cheat sheet for SQL Server :</div><div class="MsoNormal"><br />
</div><div class="MsoNormal">SQL Server 2008 System Views Map (from Microsoft)</div><div class="MsoNormal"><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en">http://www.microsoft.com/downloads/en/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en</a></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">SQL Server developers factsheet</div><div class="MsoNormal"><a href="http://www.dotnet4all.com/snippets/2008/04/factsheet-for-sql-server-developers.html">http://www.dotnet4all.com/snippets/2008/04/factsheet-for-sql-server-developers.html</a></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">SQL Server Cheat Sheet</div><div class="MsoNormal"><a href="http://blog.sqlauthority.com/2009/05/20/sql-server-download-pdf-sql-server-cheat-sheet/">http://blog.sqlauthority.com/2009/05/20/sql-server-download-pdf-sql-server-cheat-sheet/</a></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">SQL Server 2005 System views map</div><div class="MsoNormal"><a href="http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14606">http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14606</a></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">SQL Server Cheat Sheet</div><div class="MsoNormal"><a href="http://www.addedbytes.com/cheat-sheets/sql-server-cheat-sheet/">http://www.addedbytes.com/cheat-sheets/sql-server-cheat-sheet/</a></div><br />
<div class="MsoNormal">SQL Server TSQL Cheat Sheet</div><div class="MsoNormal"><a href="http://www.petefreitag.com/cheatsheets/sqlserver/">http://www.petefreitag.com/cheatsheets/sqlserver/</a> <br />
<br />
Convert Microsoft Access (JET-SQL) to SQL Server (T-SQL) Cheat Sheet <br />
<a href="http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx"><u>http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx</u></a></div><div class="MsoNormal"><u><br />
</u></div><div class="MsoNormal"> </div><div class="MsoNormal"> </div><div class="MsoNormal"><br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-27939097680725888362011-06-15T10:22:00.000-05:002011-06-15T10:22:22.971-05:00SQL Server System Databases<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
table.MsoTableGrid
{mso-style-name:"Table Grid";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-priority:59;
mso-style-unhide:no;
border:solid windowtext 1.0pt;
mso-border-alt:solid windowtext .5pt;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-border-insideh:.5pt solid windowtext;
mso-border-insidev:.5pt solid windowtext;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal" style="line-height: normal;"><b><u><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></u></b></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">SQL Server 2008/2005 contains <b>five system</b> databases.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Master, Model, Msdb, tempdb <span> </span>and Mssqlsystemresource (aka resource). Other than this ReportServer and ReportServerTempDB we can consider as system databases if reporting services installed. You may count distributor as system database if replication configure.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: medium none;"><tbody>
<tr> <td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 55.45pt;" valign="top" width="74"> <div class="MsoNormal" style="line-height: normal;"><b><u><span style="color: #254061; font-family: "Arial","sans-serif"; font-size: 10pt;">Master</span></u></b><b><u><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></u></b></div></td> <td style="border-color: windowtext windowtext windowtext -moz-use-text-color; border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 423.35pt;" valign="top" width="564"> <div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings.</span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The master database is also where SQL Server stores information about the other databases on this instance and the location of their files.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The first database in the SQL Server startup process</span><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">, If the master database is not present, SQL Server cannot start.</span><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"> needs to reside in the same directory as the Resource database</span></div><div class="MsoNormal" style="line-height: normal;"><strong><span style="font-family: "Arial","sans-serif"; font-size: 10pt; font-weight: normal;">Always</span></strong><b><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"> take regular backups of the master database.</span></b></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Good Practice: </span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Do not create users objects in the master. Otherwise, master must be backed up more frequently. Normally when you open the SQL server and firing any query without proper USE statement it will create all object in default master database so keep practicing of always uses USE statement at top of any queries.</span><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div></td> </tr>
<tr> <td style="border-color: -moz-use-text-color windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 55.45pt;" valign="top" width="74"> <div class="MsoNormal" style="line-height: normal;"><b><u><span style="color: #254061; font-family: "Arial","sans-serif"; font-size: 10pt;">MSDB</span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div></td> <td style="border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-style: none solid solid none; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 423.35pt;" valign="top" width="564"> <div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The <strong><span style="font-family: "Arial","sans-serif";">msdb</span></strong> database is used by SQL Server Agent for scheduling alerts and jobs, Operators, Alerts and by other features such as Service Broker and Database Mail.</span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">MSDB database is used to store the information related to the database backups and restore information. You need to make sure of purging old back up history from the msdb.</span></div></td> </tr>
<tr> <td style="border-color: -moz-use-text-color windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 55.45pt;" valign="top" width="74"> <div class="MsoNormal" style="line-height: normal;"><b><u><span style="color: #254061; font-family: "Arial","sans-serif"; font-size: 10pt;">Model</span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div></td> <td style="border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-style: none solid solid none; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 423.35pt;" valign="top" width="564"> <div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The <strong><span style="font-family: "Arial","sans-serif";">model</span></strong> database is used as the template for all databases created on an instance of SQL Server. Because <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong> is created every time SQL Server is started, the <strong><span style="font-family: "Arial","sans-serif";">model</span></strong> database must always exist on a SQL Server system.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">You can change most database properties, create users, stored procedures, tables, views, etc. – whatever you do will be applied to any <strong><span style="font-family: "Arial","sans-serif";">new</span></strong> databases.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The database configurations such as the recovery model for the Model database are applied to future user defined databases.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Outside of its role as a template, model doesn’t do anything else.</span></div></td> </tr>
<tr> <td style="border-color: -moz-use-text-color windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 55.45pt;" valign="top" width="74"> <div class="MsoNormal" style="line-height: normal;"><b><u><span style="color: #254061; font-family: "Arial","sans-serif"; font-size: 10pt;">Tempdb</span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div></td> <td style="border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-style: none solid solid none; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 423.35pt;" valign="top" width="564"> <div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Is a workspace for holding temporary objects or intermediate result sets. Purpose of temporary database to store temporary tables, table variables, cursors, create or rebuilding indexes sorted in Tempdb etc.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><strong><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Tempdb</span></strong><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"> is re-created every time SQL Server is started so that the system always starts with a clean copy of the database, so permanent objects cannot be crated in tempdb database.</span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong> to be saved from one session of SQL Server to another.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Backup and restore operations are not allowed on <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong>.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The size of <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong> can affect the performance of a system. For example, if the <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong> size is too small, the system processing could be too occupied with auto growing the database to support your workload requirement every time that you start SQL Server. You can avoid this overhead by increasing the size of <strong><span style="font-family: "Arial","sans-serif";">tempdb</span></strong>.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div></td> </tr>
<tr> <td style="border-color: -moz-use-text-color windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 55.45pt;" valign="top" width="74"> <div class="MsoNormal" style="line-height: normal;"><b><u><span style="color: #254061; font-family: "Arial","sans-serif"; font-size: 10pt;">Resource</span></u></b><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div></td> <td style="border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-style: none solid solid none; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 423.35pt;" valign="top" width="564"> <div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the <strong><span style="font-family: "Arial","sans-serif";">Resource</span></strong> database, but they logically appear in the <strong><span style="font-family: "Arial","sans-serif";">sys</span></strong> schema of every database.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure.<span style="color: black;"> When a service pack / hot fix are installed the resource database is updated.</span></span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <<span class="parameter">drive</span>>:\Program Files\Microsoft SQL Server\MSSQL10_50.<<span class="parameter">instance name</span>>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. </span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">Read-only database that is not accessible via the SQL Server 2005 tool set</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The database ID for the Resource database is 32767</span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">The Resource database does not have an entry in master.sys.databases</span></div><div class="MsoNormal" style="line-height: normal;"><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">To determine the version number of the Resource database, use:</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><a href="" name="CodeSnippetname"></a><a href="" name="CodeSpippet0"></a><span></span><a href="" title="Copy to clipboard."><span><span style="color: blue; display: none; font-family: "Arial","sans-serif"; font-size: 10pt;">Copy</span></span><span></span></a><span><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10pt;">SELECT SERVERPROPERTY('ResourceVersion');</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10pt;">GO </span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span><span style="font-family: "Arial","sans-serif"; font-size: 10pt;">To determine when the Resource database was last updated, use:</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span><a href="" name="CodeSpippet1"></a></span><a href="" title="Copy to clipboard."><span><span style="color: blue; display: none; font-family: "Arial","sans-serif"; font-size: 10pt;">Copy</span></span><span></span></a><span style="font-family: "Arial","sans-serif"; font-size: 10pt;"></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10pt;">SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: black; font-family: "Arial","sans-serif"; font-size: 10pt;">GO</span></div><div class="MsoNormal" style="line-height: normal;"><br />
</div></td> </tr>
</tbody></table><div class="MsoNormal" style="line-height: normal;"><br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-49064706695113620172011-06-14T14:44:00.000-05:002011-06-14T14:44:27.890-05:00How to find out how long a SQL Server database backup took? <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DECLARE</span><span style="font-family: "Courier New"; font-size: 10pt;"> @dbname <span style="color: blue;">SYSNAME</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SET</span><span style="font-family: "Courier New"; font-size: 10pt;"> @dbname <span style="color: grey;">=</span> <span style="color: red;">'RealDB'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: green;">--set this to be whatever dbname you want</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span><span style="color: magenta;">user_name</span> <span style="color: blue;">AS</span> [User] <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>database_name <span style="color: blue;">AS</span> [Database] <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>server_name <span style="color: blue;">AS</span> [Server] <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>backup_start_date <span style="color: blue;">AS</span> [Backup Started] <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>backup_finish_date <span style="color: blue;">AS</span> [Backup Finished] <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: magenta;">CAST</span><span style="color: grey;">((</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>s<span style="color: grey;">,</span> bup<span style="color: grey;">.</span>backup_start_date<span style="color: grey;">,</span> bup<span style="color: grey;">.</span>backup_finish_date<span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">INT</span><span style="color: grey;">)</span> <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">/</span> 3600 <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">)</span> <span style="color: grey;">+</span> <span style="color: red;">' hours, '</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">+</span> <span style="color: magenta;">CAST</span><span style="color: grey;">((</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>s<span style="color: grey;">,</span> bup<span style="color: grey;">.</span>backup_start_date<span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>backup_finish_date<span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">INT</span><span style="color: grey;">)</span> <span style="color: grey;">)</span> <span style="color: grey;">/</span> 60 <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">+</span> <span style="color: red;">' minutes, '</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">+</span> <span style="color: magenta;">CAST</span><span style="color: grey;">((</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>s<span style="color: grey;">,</span> bup<span style="color: grey;">.</span>backup_start_date<span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>backup_finish_date<span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">INT</span><span style="color: grey;">)</span> <span style="color: grey;">)</span> <span style="color: grey;">%</span> 60 <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">+</span> <span style="color: red;">' seconds'</span> <span style="color: blue;">AS</span> [Total Time]</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>backupset bup</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>bup<span style="color: grey;">.</span>backup_set_id <span style="color: grey;">IN</span><span style="color: blue;"> </span><span style="color: grey;">(</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: blue;">SELECT</span><span> </span><span style="color: magenta;">MAX</span><span style="color: grey;">(</span>backup_set_id<span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: blue;">FROM</span><span> </span>msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>backupset</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: blue;">WHERE</span><span> </span>database_name <span style="color: grey;">=</span> <span style="color: magenta;">ISNULL</span><span style="color: grey;">(</span>@dbname<span style="color: grey;">,</span> database_name<span style="color: grey;">)</span> <span style="color: green;">--if no dbname, then return all</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">AND</span> <span style="color: blue;">type</span> <span style="color: grey;">=</span> <span style="color: red;">'D'</span> <span style="color: green;">--only interested in the time of last full backup</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: blue;">GROUP</span> <span style="color: blue;">BY</span> database_name <span style="color: grey;">)</span> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">AND</span> bup<span style="color: grey;">.</span>database_name <span style="color: grey;">IN</span><span style="color: blue;"> </span><span style="color: grey;">(</span> <span style="color: blue;">SELECT</span><span> </span>name</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span> </span><span style="color: blue;">FROM</span><span> </span><span style="color: blue;">master</span><span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: green;">sysdatabases</span> <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">ORDER</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">BY</span> bup<span style="color: grey;">.</span>database_name</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal"> ref : mssqltips.com</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-30411294474149880892011-06-14T14:35:00.000-05:002011-06-14T14:35:01.550-05:00SQL Server – Restore Database – Time Estimate or Progress / How can we query how much time SQL database restore will takes thru T-SQL query?<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<div class="MsoNormal">While database restore is in progress or database is in restoring mode, you can check the estimated restore percentage complete , estimated completion time, start time with below DMV.</div><div class="MsoNormal"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>sp<span style="color: grey;">.</span>name <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>percent_complete <span style="color: blue;">AS</span> <span style="color: red;">'PERCENTAGE COMPLETE'</span><span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: magenta;">DATEADD</span><span style="color: grey;">(</span><span style="color: blue;">second</span><span style="color: grey;">,</span> estimated_completion_time <span style="color: grey;">/</span> 1000<span style="color: grey;">,</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">())</span> <span style="color: blue;">AS</span> <span style="color: red;">'Est Completion Time'</span> <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: magenta;">GETDATE</span><span style="color: grey;">()</span> <span style="color: blue;">AS</span> <span style="color: red;">'Current Time'</span> <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span><span style="color: blue;">minute</span><span style="color: grey;">,</span> start_time<span style="color: grey;">,</span> <span style="color: magenta;">GETDATE</span><span style="color: grey;">())</span> <span style="color: blue;">AS</span> running <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>estimated_completion_time <span style="color: grey;">/</span> 1000 <span style="color: grey;">/</span> 60 <span style="color: blue;">AS</span> <span style="color: red;">'Completion Time'</span> <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>start_time <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>command</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">dm_exec_requests</span> req</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span><span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">sysdatabases</span> sp <span style="color: blue;">ON</span> sp<span style="color: grey;">.</span><span style="color: blue;">dbid</span> <span style="color: grey;">=</span> req<span style="color: grey;">.</span>database_id</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"><span> </span>req<span style="color: grey;">.</span>command <span style="color: grey;">LIKE</span> <span style="color: red;">'%RESTORE%'</span></span></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com2tag:blogger.com,1999:blog-7902649748843003128.post-9987109782916790012011-06-10T03:19:00.001-05:002011-06-10T03:24:39.208-05:00Date Ranges<div class="MsoNormal" style="color: blue;"></div><div class="MsoNormal">I found below queries very useful when working with different date ranges.</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">TABLE</span> #CHKDT</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="font-family: "Courier New"; font-size: 10pt;"> SPNo <span style="color: blue;">int</span><span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> Date1 <span style="color: blue;">datetime</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">)</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #CHKDT<span style="color: blue;"> </span><span style="color: grey;">(</span>Date1<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'6/1/2011'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #CHKDT<span style="color: blue;"> </span><span style="color: grey;">(</span>Date1<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'6/8/2011'</span><span style="color: grey;">)</span> <span style="color: green;">-- </span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #CHKDT<span style="color: blue;"> </span><span style="color: grey;">(</span>Date1<span style="color: grey;">)</span> <span style="color: blue;">VALUES </span><span style="color: grey;">(</span><span style="color: red;">'6/15/2011'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Find date which falls somewhere in between current week.</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> Date1 <span style="color: blue;">as</span> <span style="color: red;">'INSIDE CURRENT WEEK'</span> <span style="color: blue;">FROM</span> #CHKDT</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> Date1 <span style="color: grey;">),</span> 0 <span style="color: grey;">)</span> <span style="color: grey;">=</span> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> <span style="color: magenta;">getdate</span><span style="color: grey;">()</span> <span style="color: grey;">),</span> 0 <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Find date which is from last week</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> Date1 <span style="color: blue;">as</span> <span style="color: red;">'DARE FROM LAST WEEK'</span> <span style="color: blue;">FROM</span> #CHKDT</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">where</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> Date1 <span style="color: grey;">),</span> 0 <span style="color: grey;">)</span> <span style="color: grey;">=</span> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> <span style="color: magenta;">getdate</span><span style="color: grey;">()</span> <span style="color: grey;">)</span> <span style="color: grey;">-</span> 1<span style="color: grey;">,</span> 0 <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Find date which is in beginning of the current week</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> <span style="color: magenta;">getdate</span><span style="color: grey;">()</span> <span style="color: grey;">),</span> 0 <span style="color: grey;">)</span> <span style="color: blue;">as</span> <span style="color: red;">'Begining of current week'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Find date which is in beginning of last week</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> <span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span> <span style="color: blue;">week</span><span style="color: grey;">,</span> 0<span style="color: grey;">,</span> <span style="color: magenta;">getdate</span><span style="color: grey;">()</span> <span style="color: grey;">)</span> <span style="color: grey;">-</span> 1<span style="color: grey;">,</span> 0 <span style="color: grey;">)</span> <span style="color: blue;">as</span> <span style="color: red;">'Begining of last week'</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">DROP</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">TABLE</span> #CHKDT</span></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-18607258800658278232011-06-06T14:36:00.001-05:002011-06-09T09:54:07.459-05:00SQL Server - List available fixed Hard Drive with Free space details from SSMS<div class="MsoNormal"><b><u><span style="font-size: 14pt; line-height: 115%;"></span></u></b></div><div class="MsoNormal">Monitoring free disk space on SQL Server is very important if database growth is relatively fast. As DBA one of your responsibilities is to monitor the disk space and always make sure you have enough space. There is number of different methods you can use , one of them is undocumented SQL server extended stored procedure ‘xp_fixeddrives’.</div><div class="MsoNormal">Xp_FixedDrives is very simple and you can use it from SSMS / Query Analyzer. </div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">EXEC</span><span style="font-family: "Courier New"; font-size: 10pt;"> master<span style="color: grey;">..</span>xp_fixeddrives</span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal">This results in record set that contains the number MBs of free space for each physical drive associated with SQL server machine.</div><div class="MsoNormal">Below query gives the similar results</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">*</span> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">OPENQUERY</span><span style="color: grey;">(</span>SQLServerName<span style="color: grey;">,</span><span style="color: red;">'set fmtonly off; exec master..xp_fixeddrives'</span><span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal">You can also store xp_fixeddrives results in table and thru SQL server agent better manage drive space with automated job.</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">TABLE</span> #DriveFS</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">(</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> Drive <span style="color: blue;">CHAR</span><span style="color: grey;">(</span>1<span style="color: grey;">)</span> <span style="color: grey;">,</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> FreeSpace <span style="color: blue;">INT</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">)</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><br />
</div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">INSERT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">INTO</span> #FreeSpace</span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;"> <span style="color: blue;">EXEC</span> xp_fixeddrives</span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><u><b><span style="font-family: "Courier New"; font-size: 10pt; line-height: 115%;">Note : Xp_FixedDrives does not show any information for Mounted Volumes.</span></b></u></div><div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt;"></div><div class="MsoNormal"><br />
</div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-28818059738502685492011-03-18T15:43:00.003-05:002011-07-21T14:59:04.064-05:00T-SQL Query Performance Tunning TricksI am trying to add some T-SQL query performance tricks for SQL server developers. Some of are very obvious but still i have seen professional developers not uses these.<br />
<div style="color: blue;"><b><u>1. IN / BETWEEN</u></b></div><br />
When you have choice of using IN / BETWEEN in TSQL, BETWEEN is good choice.<br />
<br />
SELECT eid,ename,salary FROM Employee WHERE eid IN (1,2,3,4,5,6,7,8,9,10)<br />
SELECT eid,ename,salary FORM Employee WHERE eid BETWEEN 1 AND 10<br />
<br />
BETWEEN is much more efficient than IN.<br />
<br />
<div style="color: blue;"><b><u>2. SUBSTRING/LIKE</u></b></div>Sometime it is possible to replace SUBSTRING character function with LIKE. SUBSTRING function forces the Table scan istead of allowing optimizer to use and Index.<br />
<br />
SELECT eid,ename,salary FROM Employee WHERE SUBSTRING(ename,1,1) = ‘S’<br />
SELECT eid,ename,salary FROM Employee WHERE ename LIKE ‘S%’<br />
<br />
Both query returns the same result but LIKE clause gives the batter performance in this case.<br />
<br />
<br />
<div style="color: blue;"><b><u>3. STRING FUNCTION , if possible avoid it.</u></b></div><div style="color: blue;"><br />
</div>SELECT eid,ename FROM Employee WHERE UPPER(ename) = ‘XYZ’<br />
<br />
We can rewrite the same query <br />
SELECT eid,enam FROM Employee WHERE ename = ‘XYZ’ or ename = ‘xyz’<br />
<br />
Second query run much more faster than the first query which has UPPER function.<br />
<br />
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
</style> <![endif]--> <br />
<b><u><span style="color: #0070c0;">4. Union / Union All </span></u></b><b><u><span style="color: #0070c0;"></span></u></b><br />
<div style="text-align: justify;"><span>If using the UNION statement, keep<span> </span>in mind that, by default, UNION performs the<span> </span>similar of a SELECT DISTINCT on the final output of the result query. In other words, UNION takes the results of two like<span> </span>recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows.</span></div><div style="text-align: justify;"><span>This process occurs even if there are no duplicate records in the final recordset.<br />
If you know that there are duplicate records, and this presents a problem for your application, then by all means use the UNION statement to eliminate the duplicate rows. But if not, use UNION ALL, which is less resource intensive. </span> </div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-43908404000910865022011-03-15T16:49:00.000-05:002011-03-15T16:49:27.230-05:00How many number of processors SQL server has?The simple way to count the number of processors SQL Server has <br />
<br />
<pre>exec xp_msver 'processorcount', 'processortype'</pre><pre> </pre>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com1tag:blogger.com,1999:blog-7902649748843003128.post-52605577960521477682011-03-15T15:52:00.000-05:002011-03-15T15:52:04.789-05:00Identifying the protocols being used by current connectionsThere are four different Net-Library options are available for SQL Server.<br />
<br />
Shared Memory<br />
TCP/IP<br />
Named Pipes<br />
VIA (Virtual Interface Adaptor)<br />
<br />
Below script provides you details about existing connections when connection was establish and which protocol used by particular connection.<br />
<br />
<span style="font-size: small;">SELECT session_id,connection_id, connect_time, net_transport, net_packet_size, client_net_address<br />
FROM sys.dm_exec_connections</span><br />
<br />
To learn more about protocol please <a href="http://msdn.microsoft.com/en-us/library/ms187892.aspx">click here</a>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-92224171488978798982011-03-09T13:21:00.000-06:002011-03-09T13:21:28.698-06:00MS SQL Server 2005 - Reporting Services Fix for slow loading on first time report loadConfiguring the new instance of SSRS (SQL Server Reporting Services) , i have observe that when you first time click on the SSRS reporting site it takes longer time to load sometime forever. Once it is load one time than it would be good and no issue. If you close the session and reload everything first time it takes again longer time.<br />
I have load balancing in Reporting server so i thought it was taking longer time but after reading online material and applying following change on IIS - App Pool setting , it resolved the issue.<br />
<br />
Select the second tab - Performance and there is setting of the App Pool, Shutdown worker processes after being idle for (time in minutes) : 20 mins. This is the default setting.Unchecked that, and you should see the performance improvement. On a site you which runs slowly you can see the direct improvement right away. It may require to restart IIS service. <br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://lh6.googleusercontent.com/-QTReU9sQekE/TXfSYM02Y-I/AAAAAAAACeI/KTgIoS12Hbg/s1600/03_09_2011.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="370" src="https://lh6.googleusercontent.com/-QTReU9sQekE/TXfSYM02Y-I/AAAAAAAACeI/KTgIoS12Hbg/s400/03_09_2011.png" width="400" /></a></div>Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-22020711888654002982011-03-04T13:27:00.000-06:002011-03-04T13:27:56.595-06:00How many number of TempDB data files?TempDB system database plays as important role on SQL Server performance.<br />
<br />
When you install SQL Server by default SQL Server will create one small TempDB data file and one log file in the default location for TempDB on your SQL Server instance. You should change the default file locations of TempDB, otherwise default file locations, the TempDB files will be in a sub-directory on the same drive where your SQL Server binary files are located. This is most likely your C: drive on your database server, which is not a good place. If this the case and your TempDB is in C: drive, good practice is to move it to fast, dedicated logical drive.<br />
<br />
You also need to create some additional TempDB data files, which should all be the same size. If all of the TempDB data files are the same size, SQL Server will use all of them equally. The reason to have multiple data files is to reduce possible allocation contention, as objects are created and destroyed in TempDB.<br />
<br />
Microsoft suggest to create one TempDB data file per physical process core. But now a days there is four,eight and more than that core processors. So I think this is unnecessary. In general consent you should start with four or more TempDB data files and be sure to make all of the same size and later look for allocation contention and make appropriate decision for additional TempDB data files.Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-29805554703492947862010-08-17T14:59:00.000-05:002010-08-17T15:00:48.616-05:00Find owner of database thru T-SQLFor all the database<br /> <br /> select suser_sname(owner_sid) from sys.databases<br /><br />For specific database<br /><br /> select suser_sname(owner_sid) from sys.databases where name = 'Northwind'Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-9560946869989391992010-08-17T14:56:00.000-05:002010-08-17T14:58:46.773-05:00Update Multiple ColumnMS SQL Update Multiple Column in single shot<br /><br />*****************<br /><br />UPDATE MyTable<br />SET (Col1, Col2, Col3, Col4) = (SELECT a, b, c, d FROM Mytable2 WHERE e =Mytable.Col5)<br />WHERE Col5 IN (SELECT d FROM Table2 )<br /><br />*****************Sandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0tag:blogger.com,1999:blog-7902649748843003128.post-1513491830749328452010-03-04T16:05:00.000-06:002010-03-04T16:08:41.416-06:00How to change SQL Server Instance Name?Query current Instance Configuration<br /><br /> 1. sp_helpserver<br /> 2. select @@servername<br /><br />Execute the below query to change the instance name<br /><br /> 1. sp_dropserver 'OldName'<br /> 2. go<br /> 3. sp_addserver 'NewName' , 'local'<br /> 4. go<br /><br />Vefiry SQL Server Instance Config. by running following queries.<br /><br /> 1. sp_helpserver<br /> 2. select @@servernameSandip Patelhttp://www.blogger.com/profile/02490074035587108661noreply@blogger.com0