tag:blogger.com,1999:blog-291448612024-03-06T14:18:11.504+13:00SQL Server BlogTim Brown hopefully providing you with useful SQL Server InformationUnknownnoreply@blogger.comBlogger40125tag:blogger.com,1999:blog-29144861.post-55398662894209725862019-07-21T17:35:00.000+12:002019-07-21T17:35:16.997+12:00 free index maintenance tool for SQL Server and Azure<span lang="EN-AU" style="font-family: "Calibri",sans-serif; font-size: 11.0pt; mso-ansi-language: EN-AU; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: EN-US; mso-fareast-theme-font: minor-latin;"><a href="https://www.codeproject.com/Articles/5162340/SQL-Index-Manager-Free-GUI-Tool-for-Index-Maintena">https://www.codeproject.com/Articles/5162340/SQL-Index-Manager-Free-GUI-Tool-for-Index-Maintena</a>
</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZKNl5rj_ebLrxxW-oCVEs-XYy-6TmOW2FpJesEjcnRoEeAx1DBYzP4XSu4HWjQrrdZ0ytx8-RW3kfZKnSyW0GDPhbwXlL2n6zAbKiddHby5Qb96Q0GOUkiGxz3khwocxIyhPR/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="771" data-original-width="631" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZKNl5rj_ebLrxxW-oCVEs-XYy-6TmOW2FpJesEjcnRoEeAx1DBYzP4XSu4HWjQrrdZ0ytx8-RW3kfZKnSyW0GDPhbwXlL2n6zAbKiddHby5Qb96Q0GOUkiGxz3khwocxIyhPR/s320/Capture.JPG" width="261" /></a></div>
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-75440315201702194062018-12-17T16:37:00.001+13:002020-03-15T19:08:22.496+13:00Microsoft gives us options for SQL Server 2008 / 2008 R2 End of Support<br />
<div class="MsoNormal" style="background: white; line-height: normal; margin-bottom: 9.0pt; mso-outline-level: 1;">
<span style="background-color: transparent; color: #444444; font-family: "segoe ui" , sans-serif; font-size: 10pt;">If your business has a legacy application that uses SQL 2008 or 2008 R2
and you have a constraint that won’t allow you to upgrade, you will most likely
be all too aware that extended support for SQL Server 2008 and 2008 R2 will end
on July 9, 2019.</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="color: #444444; font-family: "segoe ui" , sans-serif; font-size: 10.0pt;">Microsoft have announced a potential solution that may allow you to
sleep easy at night (well for another three years anyway), they have announced
that if you move your SQL Server 2008 or 2008 R2 server to a <b><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0cm; padding: 0cm;"><a href="https://azure.microsoft.com/en-us/services/virtual-machines/"><span style="color: #1570a6;">Azure Virtual Machine</span></a></span></b> Microsoft
will provide Extended Security Updates to help secure your workloads <b style="mso-bidi-font-weight: normal;">for three more years after the end of
support deadline</b>.<o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<br /></div>
<div class="MsoNormal">
<a href="https://azure.microsoft.com/en-us/blog/announcing-new-options-for-sql-server-2008-and-windows-server-2008-end-of-support/">https://azure.microsoft.com/en-us/blog/announcing-new-options-for-sql-server-2008-and-windows-server-2008-end-of-support/</a>
<o:p></o:p></div>
<div class="MsoNormal">
If you would like to know more or would like some assistance
upgrading or migrating your SQL 2008 or SQL 2008 R2 instances give us a call - </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Datasentinel - <a href="http://www.datasentinel.co.nz/" target="_blank">www.datasentinel.co.nz</a><o:p></o:p></div>
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-67774542298720632152018-10-12T11:58:00.001+13:002018-12-17T16:37:31.569+13:00Top 10 Reasons to Choose SQL Server 2019<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBSbKYoD6HkIrMK5MxXL9MUXtsSumfTM8_hTPaqTZMaeIYtHNElOYLfXDd0tyjW2aJNNPamVNB3r7PnbjNadLuv4z19OyxVDsMfYEDxs_jHMUIqog2gj_o_mgvB2F0BHbQgLn_/s1600/1Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="642" data-original-width="952" height="215" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBSbKYoD6HkIrMK5MxXL9MUXtsSumfTM8_hTPaqTZMaeIYtHNElOYLfXDd0tyjW2aJNNPamVNB3r7PnbjNadLuv4z19OyxVDsMfYEDxs_jHMUIqog2gj_o_mgvB2F0BHbQgLn_/s320/1Untitled.png" width="320" /></a></div>
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-41983102236787476352018-06-18T07:42:00.003+12:002018-06-18T20:12:08.556+12:00Link to a great article, that explains each flavor of Power BI and their associated pricing<a href="http://blog.exposedata.com.au/2018/04/05/power-bi-cheat-sheet/" target="_blank">http://blog.exposedata.com.au/2018/04/05/power-bi-cheat-sheet/</a><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi7srdi3GvMVX7u_nI6Z_jBvFQhw_jwwuPLctRp2VKBmJd_Dd-YxgTmx4Vvg-bhUssrMfWORdLXdo3ihnbOXftprG6wIuQPleVtDBeYhN63Pnphu5o_aJ3Tz5inkKNsFNH46tR/s1600/blogCapture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="874" data-original-width="1253" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi7srdi3GvMVX7u_nI6Z_jBvFQhw_jwwuPLctRp2VKBmJd_Dd-YxgTmx4Vvg-bhUssrMfWORdLXdo3ihnbOXftprG6wIuQPleVtDBeYhN63Pnphu5o_aJ3Tz5inkKNsFNH46tR/s320/blogCapture.JPG" width="320" /></a></div>
<br />Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-38006775139288907632018-04-06T14:52:00.006+12:002018-04-06T14:52:56.296+12:00Handy - SQL Scripts to show high CPU usage<div style="background: 0px 0px rgb(255, 255, 255); border: 0px; color: #333333; font-family: "segoe ui", "pt sans", sans-serif; font-size: 14.5116px; margin-bottom: 17px; padding: 0px;">
The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes.</div>
<div style="background: 0px 0px rgb(255, 255, 255); border: 0px; color: #333333; font-family: "segoe ui", "pt sans", sans-serif; font-size: 14.5116px; margin-bottom: 17px; padding: 0px;">
<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />— Find queries that take the most CPU overall<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />SELECT TOP 50<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,TextData = qt.text<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DiskReads = qs.total_physical_reads --The worst reads, disk reads<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,MemoryReads = qs.total_logical_reads -- Logical Reads are memory reads<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,Executions = qs.execution_count<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,TotalCPUTime = qs.total_worker_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,AverageCPUTime = qs.total_worker_time/qs.execution_count<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DiskWaitAndCPUTime = qs.total_elapsed_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,MemoryWrites = qs.max_logical_writes<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DateCached = qs.creation_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DatabaseName = DB_Name(qt.dbid)<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,LastExecutionTime = qs.last_execution_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />FROM sys.dm_exec_query_stats AS qs<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />ORDER BY qs.total_worker_time DESC</div>
<div style="background: 0px 0px rgb(255, 255, 255); border: 0px; color: #333333; font-family: "segoe ui", "pt sans", sans-serif; font-size: 14.5116px; margin-bottom: 17px; padding: 0px;">
— Find queries that have the highest average CPU usage<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />SELECT TOP 50<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,TextData = qt.text<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DiskReads = qs.total_physical_reads — The worst reads, disk reads<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,Executions = qs.execution_count<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,TotalCPUTime = qs.total_worker_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,AverageCPUTime = qs.total_worker_time/qs.execution_count<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DiskWaitAndCPUTime = qs.total_elapsed_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,MemoryWrites = qs.max_logical_writes<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DateCached = qs.creation_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,DatabaseName = DB_Name(qt.dbid)<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />,LastExecutionTime = qs.last_execution_time<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />FROM sys.dm_exec_query_stats AS qs<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" />ORDER BY qs.total_worker_time/qs.execution_count DESC<br style="font-size: 14.5131px; margin: 0px; padding: 0px;" /></div>
Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-29144861.post-58346000850116910332017-11-28T16:51:00.003+13:002017-11-28T16:51:44.367+13:00Power BI - Airborne<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2JnRQPzYRSV77jp8Qqvt6uHHQQef5bacHx65btyaLzFc7DveXQquAc_iPylF1L3hvON2LdMHhKqaI3jXH3d-PLJCeI-DeIkrL4zNPuZunGUcDxe3fzD2Eg4ByoB95y2avZyTg/s1600/c48127e8-0fd1-480a-8433-b2af09cb7da8-original.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="627" data-original-width="1200" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2JnRQPzYRSV77jp8Qqvt6uHHQQef5bacHx65btyaLzFc7DveXQquAc_iPylF1L3hvON2LdMHhKqaI3jXH3d-PLJCeI-DeIkrL4zNPuZunGUcDxe3fzD2Eg4ByoB95y2avZyTg/s320/c48127e8-0fd1-480a-8433-b2af09cb7da8-original.png" width="320" /></a></div>
<span class="ember-view" style="background: 0px 0px rgb(255, 255, 255); border: 0px; box-sizing: inherit; color: rgba(0, 0, 0, 0.7); font-family: "Source Sans Pro", Helvetica, Arial, sans-serif, "Hiragino Kaku Gothic Pro", Meiryo, "Hiragino Sans GB W3", "Noto Naskh Arabic", "Droid Arabic Naskh", "Geeza Pro", "Simplified Arabic", "Noto Sans Thai", Thonburi, Dokchampa, "Droid Sans Thai", "Droid Sans Fallback", -apple-system, ".SFNSDisplay-Regular", "Heiti SC", "Microsoft Yahei", "Segoe UI"; font-size: 15px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline; white-space: pre-wrap;"><span style="background: 0px 0px; border: 0px; box-sizing: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;"><br /></span></span>
<span class="ember-view" id="ember1387" style="background: 0px 0px rgb(255, 255, 255); border: 0px; box-sizing: inherit; color: rgba(0, 0, 0, 0.7); font-family: "Source Sans Pro", Helvetica, Arial, sans-serif, "Hiragino Kaku Gothic Pro", Meiryo, "Hiragino Sans GB W3", "Noto Naskh Arabic", "Droid Arabic Naskh", "Geeza Pro", "Simplified Arabic", "Noto Sans Thai", Thonburi, Dokchampa, "Droid Sans Thai", "Droid Sans Fallback", -apple-system, ".SFNSDisplay-Regular", "Heiti SC", "Microsoft Yahei", "Segoe UI"; font-size: 15px; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline; white-space: pre-wrap;"><span style="background: 0px 0px; border: 0px; box-sizing: inherit; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">So we’ve introduced Airborne, a 3-step service which helps you create a tailored business intelligence solution, no matter your size, budget or needs. check it out </span></span><a class="feed-link ember-view" href="https://lnkd.in/edurEGC" id="ember1390" style="background: 0px 0px rgb(255, 255, 255); border: 0px; box-sizing: inherit; color: #827be9; font-family: "Source Sans Pro", Helvetica, Arial, sans-serif, "Hiragino Kaku Gothic Pro", Meiryo, "Hiragino Sans GB W3", "Noto Naskh Arabic", "Droid Arabic Naskh", "Geeza Pro", "Simplified Arabic", "Noto Sans Thai", Thonburi, Dokchampa, "Droid Sans Thai", "Droid Sans Fallback", -apple-system, ".SFNSDisplay-Regular", "Heiti SC", "Microsoft Yahei", "Segoe UI"; font-size: 15px; font-weight: 700; margin: 0px; padding: 0px; touch-action: manipulation; vertical-align: baseline; white-space: pre-wrap;" target="_blank">https://lnkd.in/edurEGC</a>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-68810601508624160422016-08-17T08:55:00.001+12:002016-08-17T08:55:41.447+12:00<h3 style="background: white; margin-top: 0cm;">
<b><span style="color: #222222; font-family: "Arial",sans-serif;"><a href="http://www.sqlservercentral.com/Forums/Topic1103972-357-1.aspx"><span style="color: #660099; text-decoration: none; text-underline: none;">Extremely slow
log shipping restore in standby mode ...</span></a></span></b><span style="color: #222222; font-family: "Arial",sans-serif;"><o:p></o:p></span></h3>
<div class="MsoNormal" style="background: white; line-height: 12.0pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<br /></div>
<div class="MsoNormal" style="background: white; line-height: 19.5pt; margin-bottom: 19.5pt; vertical-align: baseline;">
<span style="color: #323232; font-family: "inherit",serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-NZ;">Recently I was working on an environment
where the requirement was to use a log shipped secondary database for reporting
purposes. The catch here was that the restore of all the transaction log
backups needed to complete within a fixed time.<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: 19.5pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="color: #323232; font-family: "inherit",serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-NZ;">I
understand that the restore of transaction logs are dependent on a number of parameters
like disk performance, number of transactions present in the log backup,
resource availability on the server where the restore is occurring, But considering
that all the factors remain constant, there is a basic difference between a
secondary log shipped database in <b><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0cm; padding: 0cm;">No Recovery</span></b> and
in <b><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0cm; padding: 0cm;">Standby</span></b> mode. In Standby mode, a partial recovery
is done with the help of a transaction undo file to get the database into a
read-only state. This partial recovery operation and undo that occurs while
performing the next log restore (with the use of the transaction undo file)
requires time and resources and can slow down the time of restore for each
transaction log backup that is restored on the secondary database, especially
if there are resource contention issues on the server.<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: 19.5pt; margin-bottom: .0001pt; margin-bottom: 0cm; vertical-align: baseline;">
<span style="color: #323232; font-family: "inherit",serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-NZ;">There
can be a significant amount of time saved when operating using<b><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0cm; padding: 0cm;">norecovery</span></b> as compared to <b><span style="border: none windowtext 1.0pt; mso-border-alt: none windowtext 0cm; padding: 0cm;">standby</span></b> mode for log shipping. Since it is not
supported to directly modify the log shipping jobs, you can create your own job
which executes the necessary T-SQL commands and invokes the necessary log
shipping jobs in the correct order.<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: 19.5pt; margin-bottom: 19.5pt; vertical-align: baseline;">
<span style="color: #323232; font-family: "inherit",serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-NZ;">The correct flow of events would be:<o:p></o:p></span></div>
<div class="MsoNormal" style="background: white; line-height: 19.5pt; margin-bottom: 19.5pt; vertical-align: baseline;">
<span style="color: #323232; font-family: "inherit",serif; font-size: 12.0pt; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-NZ;">1. Change log shipping restore mode
to norecovery using the stored procedure
sp_change_log_shipping_secondary_database<br />
2. Start the log shipping restore job<br />
3. Change the log shipping restore mode to standby using the stored procedure
sp_change_log_shipping_secondary_database<br />
4. Initiate the log backup job on the primary server<br />
5. Initiate the log copy job<br />
6. Initiate the restore job in the same order to ensure that the new restore
mode is affected as an operating mode changes only after a new transaction log
backup is restored.<o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<br /></div>
Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-29144861.post-71007343766087895042016-02-09T09:14:00.002+13:002016-02-09T09:14:39.475+13:00Free SQL eBooks on 2016 <div class="MsoNormal">
<span lang="EN-AU">Just a
heads-up that Microsoft have published 2 new free eBooks for 2016 as follows:<o:p></o:p></span></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]--><span lang="EN-AU">-<span style="font-size: 7pt; font-stretch: normal;">
</span></span><!--[endif]--><span lang="EN-AU">Introducing
MS SQL 2016<o:p></o:p></span></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18.0pt;">
<!--[if !supportLists]--><span lang="EN-AU">-<span style="font-size: 7pt; font-stretch: normal;">
</span></span><!--[endif]--><span lang="EN-AU">Enterprise
Cloud Strategy<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span lang="EN-AU">They can be
downloaded from the following location:<o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<span lang="EN-AU"><a href="https://mva.microsoft.com/ebooks">https://mva.microsoft.com/ebooks</a><o:p></o:p></span></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-67573866896685283842015-09-23T11:38:00.003+12:002015-09-28T18:04:59.600+13:00Access locked out SQL instances, i.e. sa account or windows/sql logins with sysadmin passwords are lost or disabled<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">David Lister from SQL Services ... Info on Breaking into SQL ;) </span></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;"><br /></span></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">PSExec tool gives you an option to run a
remote process or an application using System account, if SYSTEM account has
permission on that application. Although there are many more features that
are <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">For example, if SQL Server has NT
AUTHORITY\SYSTEM as one of its logins, then you can use this account to login
to SQL Server. However, you can’t use SQL Server Management Studio or sqlcmd to
login to SQL Server using NT AUTHORITY\SYSTEM. Try it yourself! <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">However, PSExec gives you an opportunity to
login to SQL Server using NT AUTHORITY\SYSTEM. Once you download PSExec tool
bundle from <a href="http://technet.microsoft.com/en-us/sysinternals/bb897553.aspxuse"><span style="color: windowtext; text-decoration: none; text-underline: none;">http://technet.microsoft.com/en-us/sysinternals/bb897553.aspxuse</span></a>
command prompt and type following command:<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">D:\PSTools>psexec -i -s "C:\Program
Files (x86)\Microsoft SQL
Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">Here,<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">–i allow the program to run so that it
interacts with the desktop of the specified session on the remote system. If no
session is specified the process runs in the console session.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">-s runs the process in SYSTEM account.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">The above command will launch SQL Server
Management Studio and gives you a “Connect to Server” window and the User Name
will be pre-populated with NT AUTHORITY\SYSTEM. If NT AUTHORITY\SYSTEM has a
login authority to the server, you will be able to login to the SQL Server. If
you do not use “–i” switch, you won’t be able to launch management studio! So,
make sure you use –i and –s both!<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;">However, if there is a deny to this login,
for example because of Trigger etc., then you won’t be able to login to SQL
Server using this technique.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="font-family: "Segoe UI",sans-serif; font-size: 9pt;"><a href="http://blogs.msdn.com/b/sqlserverfaq/archive/2012/12/18/how-to-login-to-sql-server-with-nt-authority-system-using-psexec-tool.aspx"><span style="color: windowtext; text-decoration: none; text-underline: none;">http://blogs.msdn.com/b/sqlserverfaq/archive/2012/12/18/how-to-login-to-sql-server-with-nt-authority-system-using-psexec-tool.aspx</span></a><o:p></o:p></span></div>
<br />
<div class="MsoNormal">
<br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-91674640734331528832014-01-25T12:29:00.000+13:002014-01-25T12:29:03.786+13:00 2012 Feature Pack<span style="background-color: white; color: #2f2f2f; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Arial, Verdana, sans-serif; font-size: 12px; line-height: 20.79360008239746px;">The SQL Server 2012 Feature Pack is a collection of stand-alone packages which provide additional value for SQL 2012. </span><br />
<span style="background-color: white; color: #2f2f2f; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Arial, Verdana, sans-serif; font-size: 12px; line-height: 20.79360008239746px;">You can download the feature pack from here: http://www.microsoft.com/download/en/details.aspx?id=29065</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-76315271705057139602013-09-23T14:26:00.004+12:002013-09-23T14:27:29.213+12:00 SQL Enterprise to SQL Standard - Handy Script to remove compression.<br />
<div align="left" class="MsoNormal" style="background: white; line-height: 18pt; margin: 0cm 0cm 0pt; text-align: left; vertical-align: baseline;">
<span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">The following script displays the indexes and
tables that use compression and generates a script that removes the
compression.<br style="mso-special-character: line-break;" />
</span></div>
<br />
<div align="left" class="MsoNormal" style="background: white; line-height: 18pt; margin: 0cm 0cm 0pt; text-align: left; vertical-align: baseline;">
<span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">SELECT DISTINCT</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘ALTER TABLE ['</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> + </span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">SCHEMA_NAME</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">(</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">schema_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">) + </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">'].['</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> + NAME + </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =
NONE);’ </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">FROM </span><span style="border: 1pt windowtext; color: green; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">sys.partitions</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> p<br />
join </span><span style="border: 1pt windowtext; color: green; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">sys.objects</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> o<br />
on p.</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">object_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> = o.</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">object_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">WHERE </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">o.</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">TYPE </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">= </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘u’</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
and data_compression_desc != </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘NONE’</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">UNION </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">SELECT </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘ALTER INDEX ALL ON ['</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> + </span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">SCHEMA_NAME</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">(</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">schema_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">) + </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">'].['</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> + NAME + </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =
NONE);’</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">FROM </span><span style="border: 1pt windowtext; color: green; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">sys.partitions</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> p<br />
join </span><span style="border: 1pt windowtext; color: green; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">sys.objects</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> o<br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">on </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">p.</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">object_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"> = o.</span><span style="border: 1pt windowtext; color: magenta; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">object_id</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">WHERE </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">o.</span><span style="border: 1pt windowtext; color: blue; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">TYPE </span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">= </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘u’</span><span style="border: 1pt windowtext; color: black; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;"><br />
and data_compression_desc != </span><span style="border: 1pt windowtext; color: red; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-border-alt: none windowtext 0cm; mso-fareast-language: EN-NZ; padding: 0cm;">‘NONE’</span><span style="color: #333333; font-family: "Georgia","serif"; font-size: 12pt; mso-ansi-language: EN-NZ; mso-fareast-language: EN-NZ;"><o:p></o:p></span></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-6007125201204194142013-08-05T09:33:00.000+12:002013-08-05T09:33:17.640+12:00Tuning the Performance of Backup Compression in SQL ServerHere is a great read on <strong>Tuning the Performance of Backup Compression in SQL Server</strong><br />
<strong></strong><br />
<a href="http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx">http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx</a><br />
<br />
And a cheeky query to help give you a guide on the size of your backup if it was compressed !<br />
<br />
<strong></strong><br />
<span style="font-family: Courier New; font-size: x-small;">SELECT <br /> b.database_name 'Database Name', <br /> CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)', <br /> CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)', <br /> CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / <br /> CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio', <br /> DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)' <br />FROM <br /> msdb.dbo.backupset b <br />WHERE <br /> DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 <br /> AND b.backup_size > 0 <br />ORDER BY <br /> b.backup_finish_date DESC</span><br />
<span style="font-family: Courier New; font-size: x-small;"></span><br />
Table 1 shows the output of the above query after taking an uncompressed and a compressed backup.<br />
<span style="font-family: "Times New Roman","serif"; font-size: 12pt; mso-fareast-font-family: "Times New Roman";"></span><table cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin: auto auto auto 4.65pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-table-layout-alt: fixed; mso-yfti-tbllook: 1184;"><tbody>
<tr style="height: 31.5pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;"><td style="background: rgb(238, 236, 225); border: 1pt solid windowtext; height: 31.5pt; mso-background-themecolor: background2; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.25pt;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Database Name</span></b></td><td style="background: rgb(238, 236, 225); border-color: windowtext windowtext windowtext rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 31.5pt; mso-background-themecolor: background2; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 85.5pt;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Uncompressed Backup Size (MB)</span></b></td><td style="background: rgb(238, 236, 225); border-color: windowtext windowtext windowtext rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 31.5pt; mso-background-themecolor: background2; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.5in;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Compressed Backup Size (MB)</span></b></td><td style="background: rgb(238, 236, 225); border-color: windowtext windowtext windowtext rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 31.5pt; mso-background-themecolor: background2; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Compression Ratio</span></b></td><td style="background: rgb(238, 236, 225); border-color: windowtext windowtext windowtext rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 31.5pt; mso-background-themecolor: background2; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1in;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Backup Time (Seconds)</span></b></td><td style="background: rgb(238, 236, 225); border-color: windowtext windowtext windowtext rgb(240, 240, 240); border-style: solid solid solid none; border-width: 1pt 1pt 1pt medium; height: 31.5pt; mso-background-themecolor: background2; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 72.9pt;"><b style="mso-bidi-font-weight: normal;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Comments</span></b></td></tr>
<tr style="height: 15pt; mso-yfti-irow: 1;"><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.25pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">BCTEST</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 85.5pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">292705</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.5in;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">95907</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">3.05</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1in;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">1705</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 72.9pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Compressed backup</span></td></tr>
<tr style="height: 15pt; mso-yfti-irow: 2; mso-yfti-lastrow: yes;"><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext; border-style: none solid solid; border-width: medium 1pt 1pt; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.25pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">BCTEST</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 85.5pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">292705</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.5in;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">292705</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 76.5pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">1</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1in;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">3348</span></td><td style="background-color: transparent; border-color: rgb(240, 240, 240) windowtext windowtext rgb(240, 240, 240); border-style: none solid solid none; border-width: medium 1pt 1pt medium; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 72.9pt;"><span style="font-family: "Calibri","sans-serif"; font-size: 10pt; mso-ascii-theme-font: minor-latin; mso-bidi-font-size: 12.0pt; mso-hansi-theme-font: minor-latin;">Uncompressed backup</span></td></tr>
</tbody></table>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-83922846897867251782013-07-12T13:38:00.001+12:002013-07-12T13:38:14.207+12:00Hekaton Explained Here are some good sessions I found on SQL Server 2014 and the <strong>In-Memory OLTP </strong>(codenamed ‘Hekaton’) feature. <br />
<ul>
<li><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B204" target="_blank">Microsoft SQL Server In-Memory OLTP: Overview of Project "Hekaton"</a>. This session introduces the motivations and high-level design of the in-memory OLTP system, and Rick Kutschera (from <a href="http://www.youtube.com/watch?v=nO200qJ_i-Y" target="_blank">BWin</a>) sharing their experience migrating to the new ecosystem.</li>
</ul>
<br />
<ul>
<li><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B307" target="_blank">Microsoft SQL Server In-Memory OLTP Project "Hekaton": App Dev Deep Dive</a>. Here, Sunil presents the developer side of In-Memory OLTP, this talk is very useful if you are planning to migrate parts of your workload to Hekaton.</li>
</ul>
<br />
<ul>
<li><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B308" target="_blank">Microsoft SQL Server In-Memory OLTP Project "Hekaton": Management Deep Dive</a>. This session goes into depth about the storage structures, durability, transaction logging, backup / restore etc. AMAZING stuff!</li>
</ul>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-68328116360556141352013-05-29T16:51:00.003+12:002013-05-29T16:52:20.425+12:00Virtual Infrastructure SQL Server won't start after SP Install<span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
As your virtual infrastructure is growing and changing rapidly a result is that
the configuration of your SQL Servers could be changing also. Virtual
infrastructure growth can result in additional drives and drive letter changes
for our SQL servers.</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
Unfortunately, if the default Data and Log paths are not updated when the drive
letters change, there will be errors after installing (or uninstalling) a
Service Pack or Cumulative Update. The GUI portion of the install will complete
successfully, but there are updates that occur on the initial service start
after the GUI portion of the install is complete. One update that occurs that
that initial service start is database upgrades. If your default paths for data
and log files are not correct the upgrade of the master database will fail and
the SQL Server service will crash. </span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
In a production setting, this can induce fear, panic, and mass hysteria (or at
the very least mass Google searching, which is likely how you arrived at this
page to begin with). However, the solution is pretty simple as the default
paths are stored in the Windows registry.</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
Launch Registry Editor (regedit.exe) and navigate to the following section of
the registry tree: </span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\{SQL Version}\MSSQLServer</span><br />
<br />
Note: If you are running a 32-bit version of SQL Server on a
64-bit OS, you'll need to navigate to the Wow6432Node tree
(HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\{SQL
Version}\MSSQLServer)<br />
<br />
SQL 2000 {SQL
Version} = MSSQLServer<br />
<span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">SQL 2005 {SQL
Version} = MSSQL.1</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">SQL 2008 {SQL
Version} = MSSQL10.MSSQLSERVER</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">SQL 2008 R2
{SQL Version} = MSSQL10_50.MSSQLSERVER</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">SQL 2012 {SQL
Version} = MSSQL11.MSSQLSERVER</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
<br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">Then update
the paths specified in the following keys:</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-bidi-font-weight: bold; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">DefaultData</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-bidi-font-weight: bold; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">DefaultLogs</span><span lang="EN-AU" style="color: #333333; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Arial; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;"><br />
<br />
</span><span lang="EN-AU" style="color: #222222; font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-bidi-font-family: Helvetica; mso-hansi-theme-font: minor-latin; text-decoration: none; text-underline: none;">Once the
paths are correct, the master database upgrade will complete successfully (as
SQL Server will be able to create the temporary files in the default paths) and
once all other system and user DBs are upgraded you should be back up and
running. </span><br />
<br />
Note: The database upgrades will take some time if you have a
lot of user DBs, but you can monitor the ERRORLOG to observe the progress.<br />
<span lang="EN-GB" style="font-family: "Calibri","sans-serif"; mso-ascii-theme-font: minor-latin; mso-hansi-theme-font: minor-latin;"><o:p><span style="color: navy;"></span></o:p></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-47055279133121345702013-01-21T13:18:00.003+13:002013-01-21T13:18:52.859+13:00Show Database last access time
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: #1f497d;"><span style="font-family: Calibri;">Here is a script that I use to
get info of when a database has last been accessed without having to set up a trace </span></span></div>
<span style="color: #1f497d;"><span style="font-family: Calibri;"><span style="color: black; font-family: Times New Roman;">Note : </span>It gets it info from the <strong>DMV’s
</strong>so if there is a server restart then the info is gone.. </span></span><br />
<br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- Get Last Restart time <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT<o:p></o:p></span></div>
<span style="font-family: "Courier New"; font-size: 10pt;">crdate <o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><br />
s<span style="color: green; font-family: "Courier New"; font-size: 10pt;">ysdatabases</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span><br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE </span><span style="font-family: "Courier New"; font-size: 10pt;">name <span style="color: grey;">=</span> <span style="color: red;">'tempdb'<o:p></o:p></span></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">go<o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: green; font-family: "Courier New"; font-size: 10pt;">-- get last db access time (Null = no
access since last reboot) <o:p></o:p></span></div>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> name<span style="color: grey;">,</span> last_access <span style="color: grey;">=(</span><span style="color: blue;">select</span> X1<span style="color: grey;">=</span> <span style="color: magenta;">max</span><span style="color: grey;">(</span>LA<span style="color: grey;">.</span>xx<span style="color: grey;">)<o:p></o:p></span></span></div>
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">from </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: blue;">select</span> xx <span style="color: grey;">=<o:p></o:p></span></span><br />
<span style="color: magenta; font-family: "Courier New"; font-size: 10pt;">max</span><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="font-family: "Courier New"; font-size: 10pt;">last_user_seek<span style="color: grey;">)<o:p></o:p></span></span><br />
<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;">max</span><span style="color: grey;">(</span>last_user_seek<span style="color: grey;">)is</span> <span style="color: grey;">not</span> <span style="color: grey;">null<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">union</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">all<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> xx <span style="color: grey;">=</span> <span style="color: magenta;">max</span><span style="color: grey;">(</span>last_user_scan<span style="color: grey;">)<o:p></o:p></span></span><br />
<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;">max</span><span style="color: grey;">(</span>last_user_scan<span style="color: grey;">)is</span> <span style="color: grey;">not</span> <span style="color: grey;">null<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">union</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">all<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> xx <span style="color: grey;">=</span> <span style="color: magenta;">max</span><span style="color: grey;">(</span>last_user_lookup<span style="color: grey;">)<o:p></o:p></span></span><br />
<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;">max</span><span style="color: grey;">(</span>last_user_lookup<span style="color: grey;">)</span> <span style="color: grey;">is</span> <span style="color: grey;">not</span> <span style="color: grey;">null<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">union</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">all<o:p></o:p></span></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> xx <span style="color: grey;">=</span><span style="color: magenta;">max</span><span style="color: grey;">(</span>last_user_update<span style="color: grey;">)<o:p></o:p></span></span><br />
<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;">max</span><span style="color: grey;">(</span>last_user_update<span style="color: grey;">)</span> <span style="color: grey;">is</span> <span style="color: grey;">not</span> <span style="color: grey;">null)</span> LA<span style="color: grey;">)<o:p></o:p></span></span><br />
<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: blue;">master</span><span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: green;">sysdatabases</span> sd <o:p></o:p></span><br />
<span style="color: grey; font-family: "Courier New"; font-size: 10pt;">left</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">outer</span> <span style="color: grey;">join</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">dm_db_index_usage_stats</span>
s <o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">on</span><span style="font-family: "Courier New"; font-size: 10pt;"> sd<span style="color: grey;">.</span><span style="color: blue;">dbid</span><span style="color: grey;">=</span> s<span style="color: grey;">.</span>database_id <o:p></o:p></span><br />
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">group</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">by</span> sd<span style="color: grey;">.</span>name<o:p></o:p></span><br />
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 0pt;">
<span style="color: #1f497d;"><o:p><span style="font-family: Calibri;"></span></o:p></span><br /></div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-29144861.post-40703507361715761072013-01-11T14:26:00.006+13:002013-01-11T14:30:59.053+13:00Replication & AlwaysOn Availability GroupsReplication supports the following features on Availability groups: <br />
<br />
A publication database can be part of an availability group. The publisher instances must share a common distributor. Transaction, merge, and snapshot replication are supported.<br />
<br />
In an AlwaysOn Availability Group an AlwaysOn secondary cannot be a publisher. Republishing is not supported when replication is combined with AlwaysOn.<br />
<br />
Four new stored procedures provide replication support for AlwaysOn.<br />
<br />
<span style="color: yellow;">· </span><a href="http://msdn.microsoft.com/en-us/library/hh759120.aspx"><span style="color: yellow;">sp_redirect_publisher (Transact-SQL)</span></a><br />
<span style="color: yellow;">· </span><a href="http://msdn.microsoft.com/en-us/library/hh759142.aspx"><span style="color: yellow;">sp_get_redirected_publisher (Transact-SQL)</span></a><br />
<span style="color: yellow;">· </span><a href="http://msdn.microsoft.com/en-us/library/hh759079.aspx"><span style="color: yellow;">sp_validate_redirected_publisher (Transact-SQL)</span></a><br />
<span style="color: yellow;">· </span><a href="http://msdn.microsoft.com/en-us/library/hh759080.aspx"><span style="color: yellow;">sp_validate_replica_hosts_as_publishers (Transact-SQL)</span></a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-33752775913143833962012-07-03T09:28:00.001+12:002012-07-03T09:29:27.876+12:00DMV - SPROC with the highest average CPUThis handy bit of code shows the stored procedures with the highest average CPU time in SQL Server..<br />
<br />
<div class="li1">
<span style="color: black;">SELECT TOP 50 * FROM</span></div>
<div class="li2">
<span style="color: black;">(SELECT OBJECT_NAME(s2.objectid) AS ProcName,</span></div>
<div class="li1">
<span style="color: black;">SUM(s1.total_worker_time/s1.execution_count) AS AverageCPUTime,s2.objectid,</span></div>
<div class="li2">
<span style="color: black;">SUM(execution_count) AS execution_count</span></div>
<div class="li1">
<span style="color: black;">FROM sys.dm_exec_query_stats AS s1</span></div>
<div class="li2">
<span style="color: black;">CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2</span></div>
<div class="li1">
<span style="color: black;">GROUP BY OBJECT_NAME(s2.objectid),objectid) x</span></div>
<div class="li2">
<span style="color: black;">WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1</span></div>
<div class="li1">
<span style="color: black;">AND EXISTS (SELECT 1 FROM sys.procedures s</span></div>
<div class="li2">
<span style="color: black;">WHERE s.is_ms_shipped = 0</span></div>
<div class="li1">
<span style="color: black;">AND s.name = x.ProcName )</span></div>
<div class="li2">
<span style="color: black;">ORDER BY AverageCPUTime DESC</span></div>
<div class="li2">
<br /></div>
<div class="li2">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUwGlC0OHUNJ6NS6O8gTqnu1UUn-i7p07n0Wv1NMHjsYhGgXUvObZMy6MQ7lR7pvzu3B203gZJsi8FLonuK_j16R9WUStWQu_Qn6Qp4Gwq9hjPiR2OCZmWntNdDKj56WmFmBV_/s1600/op.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="117" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUwGlC0OHUNJ6NS6O8gTqnu1UUn-i7p07n0Wv1NMHjsYhGgXUvObZMy6MQ7lR7pvzu3B203gZJsi8FLonuK_j16R9WUStWQu_Qn6Qp4Gwq9hjPiR2OCZmWntNdDKj56WmFmBV_/s320/op.JPG" width="320" /></a></div>
<div class="li2">
<br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-3730147069111872072012-02-20T16:51:00.004+13:002012-02-20T16:51:50.956+13:00SQL 2012 Upgrading using your SAWhen you upgrade from SQL Server 2008 R2 (or below) to SQL Server 2012 using your <u><strong>Software Assurance rights</strong></u> you can continue to use your existing license model until the <strong>end of your Software Assurance cycle</strong>. This means that if you have CPU licenses under SQL Server 2008 R2 you can continue to use those CPU licenses under SQL Server 2012 until your Software Assurance expires. Once it expires you will need to true up on the number of CPU Cores.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-70913814209077608142012-01-09T10:54:00.003+13:002012-01-09T10:54:21.569+13:00Free SQL Server Tools, Software and UtilitiesFree SQL Server Tools, Software and Utilities<br />
<br />
<a href="http://www.sqlserverutilities.com/SQL-Server-Utilities-Free-SQL-Server-Tools-Software-and-Utilities-for-the-DBA-and-Developer.htm">http://www.sqlserverutilities.com/SQL-Server-Utilities-Free-SQL-Server-Tools-Software-and-Utilities-for-the-DBA-and-Developer.htm</a>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-78780743057895486682011-12-12T11:00:00.001+13:002011-12-12T11:06:54.699+13:00ZoomIt ... Top Tool<br />
Yesterday I got send a link "cheers Dean'o" for a Microsoft
product called "Zoomit"..<o:p></o:p><br />
<br />
<br />
Putting it simply it’s a tool that allows you to zoom in on areas of your
screen while doing a preso …<span style="mso-spacerun: yes;"> </span>it is
simple to use and it works ..<span style="mso-spacerun: yes;"> </span>no more mucking
round with screen resolution in the middle of preso’s because the guy at the
back can’t see and is too cool to don his glasses “ Yes that’s you Al” ..<span style="mso-spacerun: yes;"> </span><o:p></o:p><br />
<o:p> </o:p><br />
<br />
It’s a good tool ..<span style="mso-spacerun: yes;"> </span>free<span style="mso-spacerun: yes;"> </span>..<span style="mso-spacerun: yes;">
</span>download it and have a play <o:p></o:p><br />
<o:p> </o:p><br />
<br />
<a href="http://technet.microsoft.com/en-us/sysinternals/bb897434"><span style="color: blue;">http://technet.microsoft.com/en-us/sysinternals/bb897434</span></a>
<span style="mso-spacerun: yes;"> </span><o:p></o:p><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwlA7x15HElmla2rdZ_xS_dVEhzMRMFTfdFoj_Je8-tlDT_eGW-95Zw5AjywzAKJ4D36RCn-8Aad9XQMNCO-52FTZl8G9BsQ5iXLtlfbY79UvkVWMTHjcRLDGL5LM-hVuf62LY/s1600/ZoomIt-Intro-main_Full.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="308" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwlA7x15HElmla2rdZ_xS_dVEhzMRMFTfdFoj_Je8-tlDT_eGW-95Zw5AjywzAKJ4D36RCn-8Aad9XQMNCO-52FTZl8G9BsQ5iXLtlfbY79UvkVWMTHjcRLDGL5LM-hVuf62LY/s320/ZoomIt-Intro-main_Full.jpg" width="320" /></a></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-78440552707321922162011-11-23T12:16:00.001+13:002011-11-23T12:16:54.931+13:00SQL Server 2012 RC0 is now available for downloadSQL Server 2012 RC0 is now available for download,and <a href="http://blogs.msdn.com/b/robertbruckner/archive/2011/11/17/what-s-new-in-power-view.aspx">here</a> is a useful post summarising all the lovely new features that have been added since CTP3.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-7658815025263989042011-11-07T20:27:00.000+13:002011-11-07T20:27:12.153+13:00SQL Azure AccountIf you are using Azure for testing (like me), and you want to minimize your chances of being billed, then just drop the user databases you have created. You don’t need to drop your Azure subscriptionUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-2480073356472386592011-10-19T09:06:00.000+13:002011-10-19T09:06:18.912+13:00Column-store indexes<strong>Column-store indexes (Code Named Project Apollo)</strong> – <br />
Normally SQL Server stores all of the data for a particular row together, Column store indexes store each column’s data together.<br />
<br />
The important thing to notice is that the columns are stored individually. If your queries are just doing <code><span style="background-color: #eaeaea; font-family: Consolas; font-size: x-small;">SELECT FirstName, LastName</span></code> then you don’t need to read the long, drawn-out musical tastes. You read less off disk. This is fantastic for data warehouses, where column store indexes have been all the rage for the last few years.<br />
<br />
BUT<br />
<br />
It’s not perfect for everything, though: for starters, when you add a column store index to a table, that table instantly becomes <strong><em><u><span style="color: red;">read-only</span></u></em></strong>.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-82632991599995618362011-10-19T09:01:00.001+13:002011-10-19T09:01:12.375+13:00The Best Thing About the Next Version of SQL Server 2012<h3>
AlwaysOn Availability Groups (Clustering + Mirroring + Replication)</h3>
Us data plumbers have a killer new HA and scaling tool: AlwaysOn Availability Groups. It’s so important that some of my clients are already planning their 2011/2012 schedule around the deployment. In the past, we’ve struggled with combinations of clustering, log shipping, database mirroring, and replication to get the right mix of performance and availability. These tools work great, but using them all correctly requires a lot of training and experience. Microsoft heard our complaints about our lack of talent, and AlwaysOn aims to replace most of the functionality of all those different tools. <br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-29144861.post-83289401296219875482011-10-03T19:02:00.002+13:002011-10-03T19:02:41.311+13:00SQL Server codename “Denali” CTP3SQL Server codename “Denali” CTP3 has been released to the public after much anticipation. You may ask yourself, “What happened to CTP2?” The answer is simple. It was a private build for some Microsoft partners. By private, I mean it was not released to the general public. The good news is that you did not miss much in CTP2. A lot of areas were incomplete or not working. But that is to be expected as it is a work in progress.<br /><br />This blog post will serve as a means to gather resources such as links and blog posts regarding SQL Server “Denali” CTP3. Check back soon as I will be adding new resources. If you have a blog post about SQL Server Denali CTP3 please pingback or email me to add it to the list.<br /><br />Downloads:<br /><br />SQL Server DENALI CTP3 Demo VHD<br /> <a href="http://www.microsoft.com/download/en/details.aspx?id=27253">http://www.microsoft.com/download/en/details.aspx?id=27253</a><br /><br />A HyperV image of SQL Server Denali CTP3 in action, including fully configured services and integration with SharePoint 2010 and Office 2010<br /> The following software is configured on the virtual machine:<br /> •SQL Server “Denali” CTP3<br /> •SharePoint 2010<br /> •Office 2010<br /><br />SQL Server codename “Denali” Community Technology Preview 3 CTP3<br /> <a href="https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx">https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx</a><br /><br />SQL Server code name “Denali” Express Core Community Technology Preview 3 (CTP 3)<br /> <a href="http://www.microsoft.com/download/en/details.aspx?id=26784">http://www.microsoft.com/download/en/details.aspx?id=26784</a><br /><br />SQL Server code name ‘Denali’ Community Technology Preview 3 (CTP 3) Feature Pack<br /> <a href="http://www.microsoft.com/download/en/details.aspx?id=26726">http://www.microsoft.com/download/en/details.aspx?id=26726</a><br /><br />The SQL Server code name “Denali” CTP 3 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® code name ‘Denali’ CTP 3. It includes the latest versions of tool and components an add-on providers.<br /><br />Includes:<br /> •SQL Servercode name “Denali” Master Data Service Add-in for Excel CTP 3<br /> •SQL Servercode name “Denali” Semantic Language Statistics CTP 3<br /> •SQL ServerReport Builder for SQL Servercode name “Denali” CTP 3<br /> •SQL Servercode name “Denali” PowerPivot for Excel CTP 3<br /> •SQL Servercode name “Denali” Reporting Services Add-in for SharePoin Technologies<br /> •SQL Servercode name “Denali” Data-Tier Application Framework CTP 3<br /> •SQL Servercode name “Denali” Transact-SQL Language Service CTP 3<br /> •SQL Servercode name “Denali” Transact-SQL ScriptDom CTP 3<br /> •SQL Servercode name “Denali” Transact-SQL Compiler Service CTP 3<br /> •SQL ServerCompact 4.0<br /> •SQL ServerCompact 4.0 Books On-line<br /> •SQL ServerJDBC Driver 4.0 Community Technology 2 (CTP 2)<br /> •Connector 1.1 for SAP BW for SQL Server code name “Denali” CTP 3<br /> •System CLR Types for SQL Server code name “Denali” CTP 3<br /> •SQL Servercode name “Denali” Remote Blob Store CTP 3<br /> •SQL Servercode name “Denali” Books On-line CTP 3<br /> •SQL Servercode name “Denali” Upgrade Advisor CTP 3<br /> •SQL Servercode name “Denali” Native Client CTP 3<br /> •OLEDB Provider for DB2 v4.0 for SQL Server code name “Denali” CTP 3<br /> •SQL Servercode name “Denali” Command Line Utilities CTP 3<br /> •SQL ServerService Broker External Activator for SQL Server code name “Denali” CTP 3<br /> •Windows PowerShell Extensions for SQL Server code name “Denali” CTP 3<br /> •SQL Servercode name “Denali” Shared Management Objects CTP 3<br /> •SQL Servercode name “Denali” ADOMD.NET CTP 3<br /> •Analysis Services OLE DB Provider for SQL Servercode name “Denali” CTP 3<br /> •SQL Servercode name “Denali” Analysis Management Objects CTP 3<br /> •SQL ServerDriver for PHP 2.0<br /> •SQL ServerMigration Assistant<br /> 1.Microsoft SQL Server Migration Assistant for Access<br /> 2.Microsoft SQL Server Migration Assistant for MySQL<br /> 3.Microsoft SQL Server Migration Assistant for Oracle<br /> 4.Microsoft SQL Server Migration Assistant for Sybase<br /> 5. Microsoft SQL Server Migration Assistant 2008 for Sybase PowerBuilder Applications<br /> •SQL ServerStreamInsight v1.2<br /><br />Adventure Works sample databases for SQL Server codename Denali CTP3<br /> <a href="http://msftdbprodsamples.codeplex.com/releases/view/55330">http://msftdbprodsamples.codeplex.com/releases/view/55330</a><br /><br />Denali CTP3 Adventure Works Sample Databases Readme<br /> <a href="http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx">http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx</a><br /><br />Includes:<br /> •AdventureWorks2008R2 Data File<br /> •AdventureWorksDWDenali Data File<br /> •SSAS Multidimensional Model Projects Denali CTP3<br /> •SSAS Tabular Model Projects Denali CTP3<br /> •SSAS AMO2Tabular Denali CTP3<br /><br />Don’t forget to read the sample databases readme file:<br /> <a href="http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx">http://social.technet.microsoft.com/wiki/contents/articles/sql-server-samples-readme.aspx</a><br /><br />Tutorials<br /><br />Tutorials for SQL Server “Denali”<br /> <a href="http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx">http://msdn.microsoft.com/en-us/library/hh231699(v=sql.110).aspx</a><br /><br />Includes:<br /> •Multidimensional Modeling (Adventure Works Tutorial)<br /> •Tabular Modeling (Adventure Works Tutorial)<br /> •Tutorial for Project CrescentUnknownnoreply@blogger.com3