tag:blogger.com,1999:blog-68069924538233386002024-02-10T16:49:59.754-05:00SQL Server NewbieJerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.comBlogger68125tag:blogger.com,1999:blog-6806992453823338600.post-12746195192117629702023-06-14T22:19:00.001-04:002023-06-14T22:19:05.109-04:00Task Manager, show Disk Performance = diskperf -Y<p> <span style="background-color: white; color: #202124; font-family: "Google Sans", arial, sans-serif; font-size: 20px;">Enable Disk Performance Monitor</span></p><span class="hgKElc" style="background-color: white; color: #202124; font-family: "Google Sans", arial, sans-serif; font-size: 20px; padding: 0px 8px 0px 0px;">Ensure the Task Manager is closed. Open the Command Prompt with Elevated permission. run the following command: <span style="background-color: rgba(80, 151, 255, 0.18); color: #040c28;">diskperf -Y</span></span>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-58068277355750579362022-08-09T06:24:00.003-04:002022-08-09T06:53:16.718-04:00MSDB transaction log was full due to 'CHECKPOINT' despite disk has space. Had to rebuild from scratch<p>Today I came across something new in my 20 years of DBA life - <i>MSDB transaction log was full due to 'CHECKPOINT'</i></p><p>Due to the secure environment I'm not able to copy-and-paste out anything but you'll get the idea</p><p><b>Issue</b></p><p></p><ul style="text-align: left;"><li>We had a customer disk running out of space, at 0 bytes, it caused everything to stop</li><li>After some cleanup and emergency actions</li><ul><li>Removed pagefile</li><li>Emptied Recycle Bin and disabled saving space for the drives</li></ul><li>We managed to free up 20~80GB+ space, yet the SQL Agent still won't start up, and the error log/event view shows - <i><b>MSDB transaction log was full due to 'CHECKPOINT'</b></i></li></ul><p></p><p><b>Things Tried (and did NOT work)</b></p><p></p><ul style="text-align: left;"><li>Obviously restart SQL Server and Agent</li><li>Put MSDB into Emergency mode - nope, not allowed</li><li>Rebuild Log</li><li>Backup Log</li><li>Adding new Log file to MSDB</li><li>Change MSDB recovery model (from SIMPLE to FULL or anything)</li><li>Reboot the physical server</li><li>^ all above made sense because the LOG is full and cannot do any action</li></ul><p></p><p><b>Resolution</b></p><p></p><ul style="text-align: left;"><li>Had to rebuild MSDB using this method</li><ul><li><a href="https://sites.google.com/site/sqlservermsdn/rebuild-msdb-database">https://sites.google.com/site/sqlservermsdn/rebuild-msdb-database</a></li></ul><li>We lost the Backup plan, all SQL Jobs</li><ul><li>had to re-create Backup plan using Maintenance Wizard</li><li>created the jobs elsewhere and paste and re-create</li></ul></ul><p></p><p><b>Things to Do AFTER or Prepare beforehand</b></p><p></p><ul style="text-align: left;"><li>Script out your SQL Jobs if possible</li><li>Backup if possible, for restore AFTER the rebuild</li><li>AFTER - had to re-grant permissions to MSDB on SQL Logins</li></ul><p></p>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-55691665641481962302021-02-28T20:03:00.003-05:002021-02-28T20:04:50.925-05:00Every Query Plan Operator Listed and Explained<p> Every SQL Server DBA needs to bookmark this, myself included :)</p><p><br /></p><p>Every Query Plan Operator Listed and Explained</p><p><a href="https://sqlserverfast.com/epr/operator-list/">https://sqlserverfast.com/epr/operator-list/</a></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSefixIy6qJuh9N9fM0H_WfxNCqt4nJf2xQkcYe39_dWnH5BQAM2sOfVGUqKlkxyKD4Yl9LH4Z8wkpVGoNxdRCmHHKdjLxq2WeHR9HX8gRjZyFwIF7-h4yLFil_h8jGh9kd56LHkWLtog/" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="282" data-original-width="1153" height="157" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSefixIy6qJuh9N9fM0H_WfxNCqt4nJf2xQkcYe39_dWnH5BQAM2sOfVGUqKlkxyKD4Yl9LH4Z8wkpVGoNxdRCmHHKdjLxq2WeHR9HX8gRjZyFwIF7-h4yLFil_h8jGh9kd56LHkWLtog/w640-h157/image.png" width="640" /></a></div><br /><br /><p></p>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-33291205477020449722018-11-05T22:58:00.001-05:002020-08-17T01:46:37.479-04:00SQL & SSMS Tricks<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Just posting some tricks I've used or just learned</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><a href="http://www.e-squillace.com/ssms-tricks-shortcuts/">http://www.e-squillace.com/ssms-tricks-shortcuts/</a><br /></span>
</span><br />
<div style="font-size: 11pt; margin: 0in;">
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-weight: bold;">SSMS Tricks or Options I like</span></div>
<div style="margin: 0in;">
</div>
<ul>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;"><a href="https://www.mssqltips.com/sqlservertip/2786/column-and-block-text-selection-using-sql-server-management-studio/">ALT + SHIFT =multi-line select > cool</a></span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;">Options > Text Editor > All Languages > Scroll Bars > <b><u>Use map mode for vertical scroll bar</u></b> > cool</span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;"><a href="http://www.sqlservercentral.com/blogs/arthurs-blog/2019/03/02/ssms-tip-splitting-the-same-query-window/">Splitting query windows</a></span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;">Options > Text Editor > All Languages > check Line Numbers</span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;"><b>Dark theme/Font</b></span></li>
<ul>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;"><a href="https://sqlstudies.com/2018/04/04/ssmss-dark-theme/">https://sqlstudies.com/2018/04/04/ssmss-dark-theme/</a></span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">or Dark fonts only <a href="https://sqlstudies.com/2015/11/16/ssms-setup/">https://sqlstudies.com/2015/11/16/ssms-setup/</a></span></li>
<li style="font-size: 11pt;"><a href="https://blogs.sentryone.com/aaronbertrand/making-ssms-pretty-my-dark-theme/"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Making SSMS Pretty : My Dark Theme - SentryOne Team Blog</span></a></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;"><a href="https://github.com/tcartwright/SSMSThemeEditor">https://github.com/tcartwright/SSMSThemeEditor</a></span></li>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;"><br /></span></li>
</ul>
<li style="font-size: 11pt;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-size: 11pt;">Options > keyboard > Keyboard: search command <b>Query.ChangeConnection</b></span> , press shortcut keys <b>ALT + G</b>, and Assign</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-family: inherit;"><span style="font-size: 11pt;">Options > keyboard > Query shortcuts; Set to</span></span><span style="font-size: 15px;"> anything you want. In future, highlight a table name (with or without schema) and CTRL+3. these are my settings</span></span></li>
<ul>
<li><span style="font-family: inherit;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;">ALT + F1 = sp_help</span></span></li>
<li><span style="font-family: inherit;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 11pt;">CTRL + F1 = sp_helptext</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 1 sp_who</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 2 sp_lock</span></span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-size: 11pt;">CTRL + 3 </span><span face="" style="font-family: calibri;"><span style="font-size: 15px;">SELECT TOP 100 * FROM </span></span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 4 SELECT COUNT(*) FROM</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 5 SELECT * FROM</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 6</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 7 sp_helpdb</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 8 USE <database name></span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 9 sp_helptext</span></span></li>
<li><span style="font-size: 15px;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">CTRL + 0 sp_whoisactive</span></span></li>
</ul>
<li><span face="" style="font-family: calibri;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">CTRL + SHIFT + R to refresh IntelliSense</span></span></li>
<li><span face="" style="font-family: calibri;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">ALT + SHIFT + T to move down 1 line</span></span></li>
<li><span face="" style="font-family: calibri;"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">CTRL + SHIFT + ENTER to create new empty line below</span></span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">Options > Query Execution > Advanced - uncheck SET ARITHABORT</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">Options > Query Results - Play the Windows default beep</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;">Change Environment (Object Explorer font) to go bigger</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;"><a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/df551a49-d1a9-45e6-8b01-f89daacc708c/script-current-tableview-permissions?forum=sqltools">Script out permissions in SSMS</a></span></li>
<ul>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif; font-size: 15px;"><span face="" style="background-color: white; color: #333333; font-family: "segoe ui", "lucida grande", verdana, arial, helvetica, sans-serif; font-size: 14px; line-height: 20.162px;">In SSMS (Management Studio) you need to go to Tools-->Options then look for "SQL Server Object Explorer" and expand it and go to "Scripting".Then look for Object scripting options and change "<b>Script Permissions" to "True</b>".</span></span></li>
</ul>
</ul>
<a href="http://www.sqlservercentral.com/blogs/dba-mastery/2018/10/02/starting-ssms-with-a-specific-connection-and-script-file/"><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Starting SSMS with a specific connection and script file - SQLServerCentral</span></a><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-family: inherit;"><br /></span>
<a href="https://www.sqlskills.com/blogs/paul/configuring-ssms-for-presenting/">Configuring SSMS for presenting - Paul S. Randal</a></span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><br /></span>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><b><span style="font-family: inherit;"></span><span style="font-family: inherit;">SSMS Shortcut Keys</span></b></span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Bookmark - CTRL K, K to bookmark, N for Next, P for Previous</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">ALT+F8 to open Object Explorer</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">SHIFT+F1 on a keyword/object (or DMV) to open up in browser</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><span style="font-family: inherit;"><br /></span></span>
<b style="font-family: "helvetica neue", arial, helvetica, sans-serif;">SSMS Add-Ons (FREE)</b><br />
<br />
<ul>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><a href="https://www.red-gate.com/dynamic/products/sql-development/sql-search/download">Red Gate SQL Search</a></span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><a href="https://www.apexsql.com/sql-tools-complete.aspx">APEX SQL Complete</a> - free SQL complete</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><a href="https://www.apexsql.com/sql-tools-refactor.aspx">APEX SQL Refactor</a> - free SQL Formatter</span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><a href="https://www.apexsql.com/sql-tools-search.aspx">APEX SQL Search</a></span></li>
<li><span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Poor SQL <a href="http://poorsql.com/">http://poorsql.com/</a> - SQL Formatter</span></li>
</ul>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">
<span style="font-family: inherit;"><br /></span>
<b><span style="font-family: inherit;">SQL Code tricks</span></b></span><br />
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">GO</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">--instead of (NOLOCK) on every table</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">SET TRANSACTION ISOLATION LEVEL READ COMMITTED</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">GO</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><br /></span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">-- /* First line. Removing the two dashes activates the block comment</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">SELECT </span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">patientname,</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Patientid,</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Language</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">FROM whatevertable</span></div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">Where name = 'Whatever'</span></div>
<div>
</div>
<div>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;">-- */ Last line. When the block comment is on, this terminates it</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><br /></span>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><br />Resources</span><br />
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><a href="http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/">http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/</a> </span>
<span face="" style="font-family: "helvetica neue", arial, helvetica, sans-serif;"><br /></span></div>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-30114460516854982582018-10-18T22:18:00.002-04:002018-10-18T22:18:57.336-04:00DBA Salary<br />
Sharing interesting info as I was reading Brent's blog<br />
<br />
- at least I was doing pretty OK in Canada, relatively<br />
- DBA in Canada do earn (much less) than USA<br />
<br />
<a href="https://www.brentozar.com/archive/2018/10/what-should-we-change-about-the-data-professional-salary-survey-for-2019/">What Should We Change About the Data Professional Salary Survey for 2019?</a><br />
<a href="https://www.brentozar.com/archive/2018/01/2018-data-professionals-salary-survey-results/">The 2018 Data Professionals Salary Survey Results - Brent Ozar Unlimited®</a><br />
<a href="https://www.desertislesql.com/wordpress1/?p=1988">Analyzing Salary Data with Power BI and R – Part 1 |</a><br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.desertislesql.com/wordpress1/wp-content/uploads/2018/01/PowerBISalaryData.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="481" data-original-width="800" height="384" src="https://www.desertislesql.com/wordpress1/wp-content/uploads/2018/01/PowerBISalaryData.png" width="640" /></a></div>
<br />
<br />Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-71408003009412365852018-10-18T21:32:00.003-04:002018-10-18T21:33:22.109-04:00Enable Telnet in Windows 10Telnet is off by default in Windows 10<br />
<br />
<b>How to enable it?</b><br />
Open Command prompt as Admin<br />
Run this code - <span style="background-color: white; color: #222222; font-family: "arial" , "tahoma" , "helvetica" , "freesans" , sans-serif; font-size: 13px; line-height: 18.4799995422363px;">dism /online /Enable-Feature /FeatureName:TelnetClient</span><br />
Voila!<br />
<br />
<b>Do it via GUI</b><br />
<a href="https://social.technet.microsoft.com/wiki/contents/articles/38433.windows-10-enabling-telnet-client.aspx">https://social.technet.microsoft.com/wiki/contents/articles/38433.windows-10-enabling-telnet-client.aspx</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-14003769290361971462018-09-27T21:58:00.001-04:002018-09-27T21:58:37.297-04:00SSMS XEvent Profiler to read Extended EventsI'm learning the new SQL Profiler, found in SSMS 17.3+, called XEvent Profiler which uses Extended Events<br />
<br />
<br />
<b>Resources</b><br />
<a href="https://www.mssqltips.com/sqlservertip/5176/overview-of-xevent-profiler-in-sql-server-management-studio/">Overview of XEvent Profiler in SQL Server Management Studio</a><br />
<br />
<a href="https://www.sqlhammer.com/inside-xevent-profiler/">Inside the XEvent Profiler - SQL Hammer | SQL Hammer</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-56011266120996263532018-09-19T22:28:00.001-04:002018-09-19T22:28:16.376-04:00I didn't know this - DBCC CLONEDATABASETo copy a database for debug/troubleshoot - but only copy structure & stats<br />
<br />
<pre style="background-color: white; box-sizing: inherit; font-family: monospace, monospace; font-size: 16px; line-height: 21.6000003814697px; overflow: auto;">DBCC CLONEDATABASE(SourceDB, TargetDB);</pre>
<br />
SQL 2014 and above<br />
<br />
<a href="https://www.itprotoday.com/data-privacy/why-you-should-be-using-dbcc-clonedatabase-performance-tuning-queries">https://www.itprotoday.com/data-privacy/why-you-should-be-using-dbcc-clonedatabase-performance-tuning-queries</a><br />
<br />
<a href="https://support.microsoft.com/en-us/help/3177838/how-to-use-dbcc-clonedatabase-to-generate-a-schema-and-statistics-only">How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1</a><br />
<br />Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-85584950151743457242018-09-05T22:52:00.000-04:002018-09-06T01:30:35.132-04:00List All Groups, DisplayName, ServerName in CMS (Central Management Server)If you use CMS<br />
If you have a large group of servers and can't memorize all the names/IPs<br />
Use below code to show them all (and copy to Excel to format)<br />
<br />
Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)<br />
<br />
WITH MyCTE<br />
AS (<br />
--root, anchor<br />
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel<br />
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G<br />
WHERE is_system_object <>1 AND parent_id = 1<br />
UNION ALL<br />
--child<br />
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1<br />
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G<br />
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id<br />
)<br />
SELECT<br />
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END<br />
,G.name, G.ParentName<br />
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'<br />
FROM MyCTE G<br />
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id<br />
WHERE 1 = 1<br />
ORDER BY<br />
GroupLevel ASC, Parent, Child, [Display name]<br />
<br />Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-10081656146831033042018-08-29T04:46:00.000-04:002018-09-05T22:22:23.952-04:00Testing SQL connection using file.udl trickBesides the usual<br />
<b>>telnet SQLname 1433</b><br />
<br />
One can create a file and open it (note: show the extention in your File Explorer settings)<br />
<em style="background-color: #fefefe; color: #555555; font-family: Geneva, Verdana, sans-serif; font-size: 14px;">TestDatabase.udl</em><span style="background-color: #fefefe; color: #555555; font-family: "geneva" , "verdana" , sans-serif; font-size: 14px;"> </span><br />
<br />
<br />
Source<br />
<a href="https://dougrathbone.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed">https://dougrathbone.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-49502574774087957102018-08-24T05:01:00.002-04:002018-08-24T05:01:27.920-04:00Set Default template for SSMS New Query button by editing SQLFile.sql<br />
<br />
<br />
<ol>
<li>Go to SQL install path, for me it's "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql"</li>
<li>Edit the <b>SQLFile.sql</b> in Admin mode</li>
<li>Type in whatever you want for default</li>
<li>Save and try in SSMS [New Query] button</li>
</ol>
<br />
<br />
<br />
Source<br />
<a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56b9ed6-1786-4ad6-87c9-7e4e81068672/how-to-set-the-default-query-for-the-ssms-new-query-button?forum=transactsql">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56b9ed6-1786-4ad6-87c9-7e4e81068672/how-to-set-the-default-query-for-the-ssms-new-query-button?forum=transactsql</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-73475534473437963602018-05-18T11:51:00.002-04:002018-05-18T15:06:37.474-04:00SQL Cluster Registry Checkpoint<b>Error</b><br />
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication (status 2)).<br />
<div>
<br /></div>
<div>
<br /></div>
<br />
<b>Fix - </b><b>Populate the registry to the other node</b><br />
<b><br /></b>
<a href="https://www.mssqltips.com/sqlservertip/3440/registry-check-pointing-in-a-windows-cluster-to-bring-sql-server-online/">https://www.mssqltips.com/sqlservertip/3440/registry-check-pointing-in-a-windows-cluster-to-bring-sql-server-online/</a><br />
<br />
<a href="https://sqlserver-help.com/2015/04/17/information-checkpoint-in-sql-server-cluster-resources/">https://sqlserver-help.com/2015/04/17/information-checkpoint-in-sql-server-cluster-resources/</a><br />
<br />
> cluster res /checkpoints<br />
<br />
Find your resource name to replace below<br />
Run in cmd (Admin)<br />
<br />
<blockquote class="tr_bq">
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Cluster" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\MSSQLServer" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Providers" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerSCP" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\CPE" </blockquote>
<blockquote class="tr_bq">
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerAgent"</blockquote>
<blockquote class="tr_bq">
<br /></blockquote>
<blockquote class="tr_bq">
<br /></blockquote>
</blockquote>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-25978351982497102722018-04-17T15:52:00.000-04:002018-05-02T15:01:24.074-04:002008 Cluster install fails on newer Windows 2012/2016Tried to install SQL 2008 R2 on Windows 2016 = fails, cluster verification fails<br />
SQL 2014 was a-okay<br />
<br />
PowerShell (Admin mode)<br />
<strong style="background: rgb(255, 255, 255); border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin: 0px; padding: 0px; vertical-align: baseline;"><em style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;">Install-WindowsFeature -Name RSAT-Clustering-AutomationServer</span></em></strong><br />
<strong style="background: rgb(255, 255, 255); border: 0px; color: #333333; font-family: Georgia, 'Bitstream Charter', serif; font-size: 16px; line-height: 24px; margin: 0px; padding: 0px; vertical-align: baseline;"><em style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><br /></span></em></strong>
Error to install SQL Server 2008 on Windows 2012 – Marcelo's Spaces<br />
<a href="https://marcelodba.wordpress.com/2014/08/17/error-to-install-sql-server-2008-on-windows-2012/">https://marcelodba.wordpress.com/2014/08/17/error-to-install-sql-server-2008-on-windows-2012/</a><br />
<br />
<a href="https://manjusullad.wordpress.com/2015/11/26/unable-to-install-sql-2008-on-windows-server-2012-failover-cluster/">https://manjusullad.wordpress.com/2015/11/26/unable-to-install-sql-2008-on-windows-server-2012-failover-cluster/</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-22394195679980827882017-08-15T15:37:00.004-04:002018-05-18T11:16:55.296-04:00SQL Server 2008 Add Cluster Node workaroundsI haven't installed/added SQL 2008 cluster node for so long, but just today I had to use 2 workarounds just to get it working. Whew<br />
<br />
<b>First workaround</b>, Cluster Validation was good but SQL installer still complained<br />
<a href="https://support.microsoft.com/en-ca/help/953748/error-message-when-you-install-sql-server-2008-on-a-windows-server-200">https://support.microsoft.com/en-ca/help/953748/error-message-when-you-install-sql-server-2008-on-a-windows-server-200</a><br />
<br />
<span style="background-color: white; font-family: "segoe ui" , "segoe ui web" , "segoe ui symbol" , "helvetica neue" , "bbalpha sans" , "s60 sans" , "arial" , sans-serif; font-size: 15px; font-weight: 600; line-height: 20px;">Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode</span><br />
<span style="background-color: white; font-family: "segoe ui" , "segoe ui web" , "segoe ui symbol" , "helvetica neue" , "bbalpha sans" , "s60 sans" , "arial" , sans-serif; font-size: 15px; font-weight: 600; line-height: 20px;"><br /></span>
<span style="background-color: white; font-family: "segoe ui" , "segoe ui web" , "segoe ui symbol" , "helvetica neue" , "bbalpha sans" , "s60 sans" , "arial" , sans-serif; font-size: 15px; font-weight: 600; line-height: 20px;"><br /></span>
<b>Second Workaround</b> is actuall a MS Bug, where I had to comment out the pre-saved license key in the installer folder and entered it manually instead<br />
<a href="https://digitaljive.wordpress.com/2009/06/22/%E2%80%9Cthe-current-sku-is-invalid%E2%80%9D-when-adding-second-node-to-sql-2008-cluster/">https://digitaljive.wordpress.com/2009/06/22/%E2%80%9Cthe-current-sku-is-invalid%E2%80%9D-when-adding-second-node-to-sql-2008-cluster/</a><br />
<br />
<a href="https://support.microsoft.com/en-nz/help/957459/fix-error-message-when-you-try-to-add-a-second-node-to-a-sql-server-20">https://support.microsoft.com/en-nz/help/957459/fix-error-message-when-you-try-to-add-a-second-node-to-a-sql-server-20</a><br />
<br />
Basically go to <b>\x64</b> folder and modify the <span style="background-color: white; font-family: "segoe ui" , "segoeui" , "helvetica neue" , "helvetica" , "arial" , sans-serif; font-size: 15px;"><b>Defaultsetup.ini</b></span> file to comment out the serial number (and paste it in during install)<br />
<blockquote class="tr_bq">
<blockquote class="tr_bq">
;SQLSERVER2008 Configuration File</blockquote>
<blockquote class="tr_bq">
[SQLSERVER2008]</blockquote>
<blockquote class="tr_bq">
;PID="XXXXXXXXXXXXX"</blockquote>
</blockquote>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-84907126426681101352017-02-16T11:18:00.000-05:002017-02-16T11:18:08.129-05:00Create SQL Server Alias using CliConfg.exe if you don't have SQL Configuration Manager<br />
<br />
source:<br />
<a href="https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/">https://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/</a>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-13329842460759785122014-11-26T14:58:00.002-05:002014-11-26T14:58:52.707-05:00Set DEADLOCK_PRIORITY to update Database in Single_User mode<br />
If you need to access a Database in Single_user mode, to either take it offline or change to MULTI_USER, change the DEADLOCK_PRIORITY so your process does not get picked as deadlock victirm.<div>
<br /></div>
<div>
<blockquote>
<blockquote>
SET DEADLOCK_PRIORITY HIGH</blockquote>
<blockquote>
--KILL <span class="Apple-tab-span" style="white-space: pre;"> </span>58 -- kill the session using the database</blockquote>
<blockquote>
ALTER DATABASE [DB_NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE</blockquote>
<blockquote>
--ALTER DATABASE [DB_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE</blockquote>
<blockquote>
SET DEADLOCK_PRIORITY NORMAL</blockquote>
</blockquote>
</div>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-38128323032415945282014-08-13T14:46:00.004-04:002014-08-13T14:46:49.781-04:00Unable to manage cluster using failover cluster manager = Start the Server serviceI had trouble connecting to my cluster in Failover Cluster Manager today and Google found me this<br />
<br />
Easy resolution<br />
<span style="background-color: white; color: #333333; font-family: 'Segoe UI', Arial, Verdana, Tahoma, sans-serif; font-size: 13px;">Open services console and start the Server service.</span><br />
<br />
<a href="http://support.microsoft.com/kb/2462468">Unable to manage cluster using failover cluster manager. Error Received: "Connection to the cluster is not allowed since you are not an administrator on the clu...</a><br />
<br />Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-66140411151414697762014-08-13T14:45:00.000-04:002014-08-13T14:45:23.590-04:00Changing the default SQL Server backup folder, using SSMS FacetI found out that I cannot input a file share location during SQL Server 2014 installation, under Database Engine Configuration -> Data Directories tab -> Backup directory (I could do it in 2008 for sure)<br />
e.g. <b>\\fileserver\sql_backup</b><br />
<br />
Therefore I had to input a local Backup path, and change the setting after install<br />
<br />
I always thought I had to hack the registry (something like changing the key value in <b>Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer</b>, value: <b>BackupDirectory</b>)<br />
<br />
But instead there's an easier way, by using SSMS -> right-click and choose "<b>Facet</b>"<br />
<br />
Source:<br />
The old registry way - <a href="http://www.mssqltips.com/sqlservertip/1583/changing-the-default-sql-server-backup-folder/">Changing the default SQL Server backup folder</a><br />
The <b>NEW Facet</b> way - <a href="http://www.quickstepit.net/changing-default-sql-server-backup-folder/">Changing the default SQL Server backup folder</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.quickstepit.net/wp-content/uploads/2013/02/Facets.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.quickstepit.net/wp-content/uploads/2013/02/Facets.png" height="366" width="400" /></a></div>
<br />Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-85809706281670368792013-08-27T22:07:00.001-04:002013-08-27T22:07:14.216-04:00The application is unable to start correctly 0xc0000005 after windows update >> Uninstall Microsoft UpdateI don't know how this Windows Update got by Microsoft QA but be warned - it has caused issue on 2 of our Windows 2008 R2 Enterprise servers.<br />
<br />
I had to uninstall the KB and reboot.<br />
FYI: I didn't do the strikeout steps, and both reboot times were normal and not long<br />
<br />
<br />
<h1 class="wrapWord" id="threadTitle" style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 18px; margin: 0px 0px 20px; overflow: hidden; padding: 0px; position: relative; vertical-align: top; width: auto; word-wrap: break-word;">
<a href="http://answers.microsoft.com/en-us/windows/forum/windows_7-system/the-application-is-unable-to-start-correctly/bbb9c037-77b3-4fae-93b9-ded70f7f9a17">the application is unable to start correctly 0xc0000005 after windows update</a></h1>
<div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
<strike>1. Disconnect from the internet </strike></div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
2. Open Control Panel</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
3. Open Program & Features</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
4. Click "View installed updates" on the left-hand side</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
<span style="color: #333333;">5. Find "</span><span style="color: red;">Security update for Microsoft Windows (KB2859537)</span><span style="color: #333333;">" under the Microsoft Windows section</span></div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
6. Uninstall that update and restart you PC (may take a very-VERY long time to configure)</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
7. Go to Windows Updates and hide (KB2859537)</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
8. Reconnect to the internet</div>
<div style="-webkit-font-smoothing: antialiased; -webkit-text-size-adjust: none; background-color: white; border: 0px; color: #333333; font-family: 'Segoe UI', Arial, sans-serif; font-size: 13px; padding: 0px; vertical-align: top;">
<strike>9. Fix your System Restore feature; You might have to remove a fake C: drive and enable System Restore on the correct Windows drive.</strike></div>
</div>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-80520653662139635822013-04-01T10:13:00.001-04:002013-05-29T10:24:36.067-04:00Use [Ctrl+Alt+End] in RDP session to change the password, instead of [Ctrl+Alt+Del]Use [Ctrl+Alt+End] in RDP session to change the password, instead of [Ctrl+Alt+Del]Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-64331457580336182292012-03-08T10:21:00.002-05:002012-03-08T10:22:22.056-05:00Email SQL Job Notification to Multiple RecipientsIt's actually quite simply if you tried it<br />
<br />
1. Create a New Operator<br />
SQL Server Agent -> Operators -> right-click -> New Operator<br />
<br />
2. Choose a Name (e.g. "Group") and enter multiple emails in the E-mail name: field, separated by ;<br />
<a href="mailto:a@gmail.com">a@gmail.com</a>; <a href="mailto:b@gmail.com">b@gmail.com</a> <br />
<br />
3. In your SQL Job Notification, select "Group" as Operator and that's it<br />
<br />
Source<br />
<a href="http://www.dbanation.com/?p=416">http://www.dbanation.com/?p=416</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.dbanation.com/wp-content/uploads/2010/03/image_thumb6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img aea="true" border="0" src="http://www.dbanation.com/wp-content/uploads/2010/03/image_thumb6.png" /></a></div>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com1tag:blogger.com,1999:blog-6806992453823338600.post-57513599748710123792012-01-23T10:14:00.000-05:002012-01-23T10:15:34.338-05:00Restoring SYSTEM databasesI had the opportunity to restore a few system databases due to disk inconsistency/corruption. Actually my first time in a long time and was it fun. I have a few tips to share<br />
<br />
All below were done on a SQL Server 2005 instance<br />
<br />
<strong><span style="color: red;">Restoring MASTER Database!!</span></strong><br />
- start SQL Server in Single User mode (add -m to the startup parameter)<br />
- You can Google this with lots of results. <a href="http://msdn.microsoft.com/en-us/library/ms190679.aspx">Microsoft How to on SQL 2008 R2</a><br />
<br />
<strong><span style="color: red;">Restoring MSDB Database</span></strong><br />
- This is easy, just restore like a user database<br />
<br />
<strong><span style="color: red;">Restoring MODEL Database!</span></strong><br />
- Restore model in RECOVERY mode, never in NORECOVERY mode as SQL Server will stop (due to tempdb unable to create, without a model database)<br />
- If you did NORECOVERY, the only way to get in is manual work. <br />
For me, I started it and detach-and-attach the model database that I copy from another location, as one cannot issue RESTORE command in Single-User mode<br />
<br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">net start MSSQLSERVER /c /m /T3608 (you can go into SQL BINN and run sqlserver -c -m -T3608 as well)</span><br />
<br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">sqlcmd -E (to connect to the SQL Server)</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><br /><span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><em>Detach and Re-attach model database</em></span></span><span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">sp_detach_db 'model'</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">go</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">sp_attach_db 'model', 'DATA.mdf', 'LOG.ldf'</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">go</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">use model</span><br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">go</span><br />
<br />
<span style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-color: white; color: #333333; display: inline !important; float: none; font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"></span><br />
<table cellspacing="1" class="table" style="-webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; background-clip: initial; background-color: white; background-origin: initial; border-bottom-width: 2px; border-left-width: 3px; border-right-width: 3px; border-top-width: 0px; color: black; font: 11px/16px "Segoe UI", Arial, Verdana, Tahoma, sans-serif; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><tbody>
<tr><th style="background-clip: initial; background-color: #cecfce; background-origin: initial; font-weight: bold; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px;">Command line parameter</th><th style="background-clip: initial; background-color: #cecfce; background-origin: initial; font-weight: bold; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px;">Use</th></tr>
<tr><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">-c</td><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">Forces an instance of SQL Server not to start as a service.</td></tr>
<tr><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">-m</td><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">Allows updates to the system catalog.</td></tr>
<tr><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">-T3608</td><td style="background-clip: initial; background-color: #f7f7ff; background-origin: initial; padding-bottom: 5px; padding-left: 5px; padding-right: 5px; padding-top: 5px; vertical-align: top;">Recovers the<span class="Apple-converted-space"> </span><b>master<span class="Apple-converted-space"> </span></b>database only.</td></tr>
</tbody></table>Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-85502392239139180922011-07-13T10:15:00.000-04:002011-07-13T10:15:47.074-04:00There is a ($50 USD) benefit to Read and Comment Simple Talk articles!Today is my lucky day as I unexpectedly found a $50 USD Amazon.com gift card in my email inbox, courtesy of simple-talk, all because I read <em>and</em> commented on Tony's "<a href="http://www.simple-talk.com/community/blogs/tony_davis/archive/2011/06/24/102055.aspx">Cloud Backup: Getting the Users' Backs Up</a>" article<br />
<br />
You can subscribe to <a href="http://www.simple-talk.com/sql/">Simple-Talk's SQL and RSS</a>, and usually each newsletter will end with this comment<br />
<em>Reply to my blog post to be in with the chance of winning a $50 Amazon voucher. </em><br />
<br />
I just love Red Gate more and more (as Simple-Talk is owned by Red Gate)Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com0tag:blogger.com,1999:blog-6806992453823338600.post-65868112426235059852011-07-13T10:07:00.000-04:002011-07-13T10:08:06.243-04:00Change the [Change Connection] shortcut in SSMSI recently found this shortcut and it has been a lifesaver to manage multiple SQL servers in the [New Query] window<br />
<br />
I can now paste/type the SQL statement first, <strong>ALT+G</strong>, enter new SQL name, <strong>F5</strong> to execute<br />
<br />
<a href="http://narfle.com/blog/2006/07/ssms-keyboard-shortcut-for-change.asp">http://narfle.com/blog/2006/07/ssms-keyboard-shortcut-for-change.asp</a><br />
<br />
<blockquote>
1.Open a Query Editor window connected to your favorite server.</blockquote>
<blockquote>
2.Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.<br />
<br />
3.Right click on Change Connection button again and then change the command name "C&hange Connection..." to "Chan&ge Connection...". This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.<br />
<br />
4.Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both "Image and Text". This exposes the Alt-G command to the Query Editor.<br />
<br />
5.You can now close the Customize dialog and use Alt-G as your hot key!</blockquote>
Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com3tag:blogger.com,1999:blog-6806992453823338600.post-50999403297244399852009-07-24T15:41:00.000-04:002009-07-24T15:41:38.314-04:00Is DBA Enough?IT is a very broad field filled with every position and every dream<br />
<br />
I thought my transition from a Database Developer, then a Developer/DBA, and finally becoming a true DBA with MCITP certification will be the finish line. Now I am asking myself - is this it?<br />
<br />
Let's ignore the shift from Junior DBA to Senior DBA with salary increase for the moment, I sometimes wonder if SQL is all I can talk about for my whole life. This thought freightened me, just as I do not like settling my life and always wanting to do more, at my job, at my personal life, at my goals<br />
<br />
I have found the answer to my question, which means more efforts for the next few years, but I know it will be worthwhile, to challenge myself to be all I can be.Jerry Hunghttp://www.blogger.com/profile/00173468802063778170noreply@blogger.com2