tag:blogger.com,1999:blog-77675046162510557892024-03-05T00:25:52.910-08:00Welcome to my World of SQLUnknownnoreply@blogger.comBlogger132125tag:blogger.com,1999:blog-7767504616251055789.post-79034591425610369242019-04-03T12:46:00.000-07:002019-04-03T12:46:01.702-07:00SQL Server Database Engine Performance Tuning Basics<div dir="ltr" style="text-align: left;" trbidi="on">
<head>
<script async="" src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<script>
(adsbygoogle = window.adsbygoogle || []).push({
google_ad_client: "ca-pub-3989215620761893",
enable_page_level_ads: true
});
</script>
</head>
<div dir="ltr" style="text-align: left;" trbidi="on">
As the market share of SQL Server grows over time, there has been a increasing demand for SQL Server performance tuning. There has been various approaches taken by different teams and individuals for improving the performance of the SQL Server, and I think it might be beneficial to the community to have the basic steps documented for troubleshooting and improving the SQL Server Performance for various applications.<br />
<b>Disks:</b><br />
For SQL Server to perform optimally, monitoring and optimizing the SQL Server Disk sub-system is one of the important aspects. We have very specific disk performance requirements.<br />
Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. The following list shows ranges of possible values and what the ranges mean:<br />
Less than 10 ms - very good <br />
Between 10 - 20 ms - okay <br />
Between 20 - 50 ms - slow, needs attention <br />
Greater than 50 ms – Serious I/O bottleneck<br />
<b>Identifying IO bottlenecks:</b><br />
You can use the following Performance Monitor counters (perfmon.exe) to correctly identify disk performance issues.<br />
<ul>
<li><b>PhysicalDisk</b><b> Object: Avg. Disk Queue </b>Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, you might have an I/O bottleneck.</li>
<li><b>Avg. Disk Sec/Read</b> is the average time, in seconds, of a read of data from the disk.</li>
<li><b>Avg. Disk Sec/Write</b> is the average time, in seconds, of a write of data to the disk. The guidelines for the <b>Avg. Disk Sec/Read</b> values apply here.</li>
</ul>
<ul>
<li><b>Physical Disk: %Disk Time</b> is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, there is an I/O bottleneck.</li>
<li><b>Avg. Disk Reads/Sec</b> is the rate of read operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity. </li>
<li><b>Avg. Disk Writes/Sec</b> is the rate of write operations on the disk. Ensure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity. </li>
</ul>
<b>For more information on how-to create a Data Collector Set from Performance Monitor check: </b><a href="http://technet.microsoft.com/en-us/library/cc722148.aspx">http://technet.microsoft.com/en-us/library/cc722148.aspx</a><br />
<b></b><br />
<b></b><br />
<b>Disk Drive Placements:</b><br />
You need to use separate drives for different purposes.<br />
Individual disk latency requirements:<br />
Database >15ms, Logs> 2ms Tempdb> 2ms<br />
Prioritise for faster disk.<br />
<i><b>Do not</b></i><i> use the default allocation unit when formatting the Disk drives for SQL Server Log files & Data files:</i><br />
<br />
<b>Format drives with 64k Cluster Allocation Unit</b><br />
<br />
Antivirus programs can create issues with SQL Server functionality, and it is important to exclude them from their scope, by adding them to the exclusions list.<br />
File types to exclude: *.mdf, *.ndf, *.ldf, *.bak<br />
<br />
<b></b><b>Always assign the maximum memory setting on the SQL Server Instance level properties</b><br />
<br />
Note: The maximum memory setting is only for the SQL Server buffer cache, and does not include memory requirements for other SQL Server functions like replication etc.<br />
In order to determining the Non-Buffer Pool Usage, use the following:<br />
Requirements other than SQL Server’s buffer pool.<br />
· 2GB for Windows OS. Recommended <3 GB for 64 Bit systems.<br />
· <i>x</i><i> times </i>GB for SQL Server worker threads. You configure out how many threads your instance will use. Each thread will use 0.5MB on x86, 2MB on x64 and 4MB on Itanium.<br />
· 1GB for multi-page allocations, linked servers, and other consumers of memory outside the SQL Server.<br />
· 1–3GB for other applications that might be running on the system, such as backup programs.<br />
For example, on a server with eight CPU cores and 16GB of RAM running SQL Server 2012 x64 and a third-party backup utility, you would allow the following:<br />
· 3GB for Windows (2GB for 32 Bit Windows)<br />
· 1GB for worker threads (576 × 2MB rounded down)<br />
· 1GB for MPAs, etc.<br />
· 1-2 GB for the backup program.<br />
You can find more information on <b><i>‘max worker threads Option’ </i></b><i>here</i>: <a href="http://technet.microsoft.com/en-us/library/ms187024%28v=sql.105%29.aspx">http://technet.microsoft.com/en-us/library/ms187024(v=sql.105).aspx</a> (For SQL Server 2008).<br />
<b>TempDB</b><b> Optimizations:</b><br />
By default, you only one Datafile and a transaction log file. However for optimal performance, follow the recommended best practices given below:<br />
Storage Planning for TempDB<br />
<ul>
<li>Set the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.</li>
<li>Do not allow for TempDB files to automatically grow. This reduces the CPU overhead of managing a dynamic file growth.</li>
<li>Have multiple data file for TempDB (Total Number of TempDB Primary Data File = Number of Processors available to SQL)</li>
<li>Each data file should be of equal size.</li>
<li>Try to keep each data files in separate disk drives for IO Parallelism.</li>
<li>TempDB Data and Log files should be kept in faster disk drive (Preferably RAID 1 if possible)</li>
<li>Use RAID-10 or SSD Disks.</li>
<li>Pre-size TempDB files</li>
<li>25% of largest DB size.</li>
<li>Set Auto Growth to fixed size < 200 MB</li>
<li>You should have the same number of data files as the number of CPUs up to a maximum of 8.</li>
</ul>
<b>Enable the Lock Pages in Memory Option (Windows)</b><br />
This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.<br />
<br />
This can give you improved performance, especially when there is a memory pressure.<br />
Max Degree of Parallelism<br />
Defines how many CPUs can execute parallel queries<br />
<br />
Many Microsoft products like SharePoint & MS Dynamics CRM have a recommendation of setting this to ‘1’.<br />
For LOB applications, when you see that there are too many CXPACKETS waits in your SQL Server, you can consider setting this option to ‘1’.<br />
Fill Factor Settings<br />
If your SQL Server have a very high number of transactions, and your index is having high level of fragmentation, set the Fill Factor Setting to ‘<b>80</b>’.<br />
<b>Detecting index fragmentation</b><b>: </b><br />
SELECT<br />
db_name(ps.database_id)AS'Database Name',<br />
object_name(ps.OBJECT_ID)AS'Database Object',<br />
ps.index_id,<br />
b.name,<br />
ps.avg_fragmentation_in_percent<br />
FROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL)ASps<br />
INNERJOINsys.indexesASbONps.OBJECT_ID=b.OBJECT_ID<br />
ANDps.index_id=b.index_id<br />
WHEREps.database_id=DB_ID()<br />
ORDERBY<br />
ps.avg_fragmentation_in_percentDESC<br />
GO<br />
<br />
<br />
<b>Performance Monitor (Perfmon.exe)</b><br />
In order to capture SQL Server specific perfmon counters, you can use the following:<br />
Processor: % Processor Time - Should average below 75% (and preferably below 50%).<br />
System: Processor Queue Length Should average below 2 per processor. For example, in a 2-processor machine, it should remain below 4.<br />
Memory—Pages/sec Should average below 20 (and preferably below 15).<br />
Memory—Available Bytes Should remain above 50 MB.<br />
Physical Disk—% Disk Time Should average below 50%.<br />
Physical Disk—Avg. Disk Queue Length Should average below 2 per disk. For example, for an array of 5 disks, this figure should average below 10.<br />
Physical Disk—Avg. Disk Reads/sec Used to size the disk and CPU. Should be below 85% of the capacity of the drive.<br />
Physical Disk—Avg. Disk Writes/ sec Used to size the disk and CPU. Should be below 85% of the capacity of the drive.<br />
Network Interface—Bytes Total/sec Used to size the network bandwidth.<br />
SQL Server: Buffer Manager— Buffer Cache Hit Ratio Should exceed 90% (and ideally approach 99%).<br />
SQL Server: Buffer Manager—Page Life Expectancy Used to size memory. Should remain above 300 seconds.<br />
SQL Server: General Statistics— User Connections Used to size memory.<br />
SQL Server: Databases— Transactions/sec Used to size disks and CPU.<br />
SQL Server: Databases—Data File(s) Size KB Used to size the disk subsystem.<br />
SQL Server: Databases—Percent Log Used to size the disk subsystem.<br />
Hope you find this information useful.</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-10706045057832679262015-07-27T10:44:00.001-07:002015-07-27T10:44:05.314-07:00SQL Max Memory Limit Too Low: Increase SQL server Maximum Memory from Command Prompt<p>SQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server access to whatever memory is assigned to the server, which it will try to use at times at the expense of operating system tasks. Best practice is therefore to reduce the memory allocated to the instance to reserve some for the OS, but <strong>be careful!</strong> If you accidentally enter 10 thinking you’re allocating 10 GB to SQL or simply hit return too early, you’re in for a rude awakening.</p> <p>The Maximum Server Memory setting is measured in <strong>Megabytes</strong>. If you allocate too little memory (such as 10 MB), you will bring the SQL instance and its databases to their knees and won’t be able to get back into SQL Management Studio to correct it. If you try, you’ll get the following error message:</p> <p><em>A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)</em></p> <p>I this recently on one of my production SQL server 2012 boxes, it took a few minutes for the users to realize that they are being kicked out from the database. One of my coworker set it to 28MB instead of 28GB.  We tried to stop SQL Server , and start it using Command prompt.  The OS shows pending task, but couldn't started the service. After waiting for 10 minutes, we decided reboot the computer. In a nutshell these were the steps we followed </p> <ol> <li>Stop all SQL Services thru “services.msc” , if  you set the memory to way low, SQL Server Configuration Manager wont work.</li> <li>Run CMD as administrator, and run the statement  sqlservr -f -m”SQLCMD”  </li> <li>If you see the same connection error, follow step 4 otherwise go to step </li> <li>Next step is to disable the auto start for SQL Server service and restart the server, you need to run “services.msc” and do it</li> <li>Once the system is back online, run step 2. you should be able to connect to your sql instance</li> </ol> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTcuEzZahzUbncAyWQ2m13zeH8i4h-mfWL8CipUAWqCw2MQd-LgENxeRd7x1wJPtt2baRxGuf7IdrJY8YlPnntDUT8a5tFVhpFqkzo-GSYAZCzUkPZ8dG9ASzpE1H7oTxsDDyr-Z3cwaY/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCFE4CSMkSaMEiDT4N7GYvNLBc9EJCs25JsuanwZhvqJAbOAYRQNCoG1ZqAAqw8IHp65o5qjcwEgj4LeCAq37unIII8CwgUqAI4yGiR7nCdJ8Hygfjt0gC2q2ZiQtilG5meo2i86qkOo4/?imgmax=800" width="586" height="281" /></a> </p> <p>   6.  Now, leave the above command prompt and open another command prompt as admin and run the following statement, press ctrl+c to close the connection</p> <blockquote> <p><strong>SQLCMD <br />sp_configure ‘show advanced options’,1; <br />GO <br />RECONFIGURE; <br />GO <br />sp_configure ‘max server memory’,4096; <br />GO <br />RECONFIGURE; <br />GO</strong></p> </blockquote> <p> </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig3LqX8IkjFRcGYxrbqf2I7quYBCtU4ptE64UqJeEur-Wgi4aSDuIy4m4vcjRgHEDEfQQ191Drmi-GHgYDo6uKM8Xp-l8gnEuU6NlMyJZMzOk6SWRzpl0fq2HBMwGtjWyDR1e4d5iVM_o/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYhxLREUJdBfMvtQyBrby7yrZJwgfME6J5lisAIj4LFsoqBr6SiCLLBZFygcdQtcf9lNI3l_cvIigN3DXZqFcs3sgNDx6XNNV8muyEGEE9hI9NyxWVQ2BH61LSmOigK3sE2-ayGrcM52s/?imgmax=800" width="627" height="323" /></a> </p> <p> </p> <p> 7. Close the command prompts</p> <p> 8.  Set the services to auto start, and start them from services</p> <p> 9. Connect to the Server using SSMS  and verify the memory settings. </p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-85047507917558518622015-07-16T15:27:00.001-07:002015-07-27T10:45:03.702-07:00What's the best PAGE_VERIFY setting<p>I always set this value to <strong>CHECKSUM. </strong>When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.</p> <p>The following query, generate a script to  change the page verify option to CHECKSUM in case its different.</p> <p></p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:35a97fd2-d54a-461d-8255-072c5a43be18" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 530px; height: 109px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #008080;">--</span><span style="color: #008080;"> Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM</span><span style="color: #008080;"><br /></span><span style="color: #000000;"> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> N</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">ALTER DATABASE [</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #808080;">+</span><span style="color: #000000;"> db.name </span><span style="color: #808080;">+</span><span style="color: #000000;"> N</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.databases </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> db <br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> db.page_verify_option_desc </span><span style="color: #808080;"><></span><span style="color: #000000;"> N</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">CHECKSUM</span><span style="color: #FF0000;">'</span><span style="color: #000000;">;</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p></p><br /><br /><p> </p><br /><br /><p>Note: Remember, for existing database, the changes wont take effect immediately after you make the change. The database engine will calculate it only when the page is read thru any of  DML statement. </p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p>For more info check this post  <a title="http://www.littlekendra.com/2011/01/25/pageverify/" href="http://www.littlekendra.com/2011/01/25/pageverify/">http://www.littlekendra.com/2011/01/25/pageverify/</a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-27181011942809596492015-03-24T13:42:00.001-07:002015-03-24T13:42:49.588-07:00What causes Multi-Page allocations?<p>In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the <a href="http://msdn.microsoft.com/en-us/library/ms190737.aspx">-g memory_to_reserve</a> startup parameter. </p> <p>So what kinds of query result in MPA activity? Here are few scenarios </p> <p>1. A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA. </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTVjHYpoSj1UKcCRLRh0-LcigK4Rw2MhgrWQ1pUPDi6gMniNWbanc1EK64RQB870PRZXHGsuhbsPjhOKx00bXBnU78Wi0-GZlYrfCrPjDRY89mkFAX-5TxRdS6QGauZQhVoX1rKMR08w4/s1600-h/clip_image001%25255B3%25255D.png"><img title="clip_image001" style="border-left-width: 0px; border-right-width: 0px; border-bottom-width: 0px; display: inline; border-top-width: 0px" border="0" alt="clip_image001" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiobi1YMgbXAHMJ3pyW6iYdwn0O4LFohSCzjuZJn3V424PvHtOGR8xIt-_5k_PafqXn3ERkyzNnzqXtqMJmxUQmllcSep89pCfvjCnVTfQCRfsprew7dYOn3-hJKUM3cgvomhv6fDXjqd4/?imgmax=800" width="199" height="100" /></a></p> <p>2. Another workload would be large adhoc batches of the form: </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbXIA9EYZ2ZHSe7XGhV1qY6cRQhAgXYSw3x8kp_ALu2gVhkaVRDrPH3K6b_U0v3nkWsSGWx3D8Ioi5Dn9GlC6pBZBYOBxVEFX6Gq-fvoZqkuQj7gf6q2oGi3uJTtT8PkQzumt8OotqLaI/s1600-h/clip_image002%25255B3%25255D.png"><img title="clip_image002" style="border-left-width: 0px; border-right-width: 0px; border-bottom-width: 0px; display: inline; border-top-width: 0px" border="0" alt="clip_image002" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTQN18dWEaefVC6WE1RgRRWFx7ZPuEyVp4-y4wxdG9aWkIyey70u7oD4P3-a9ceSwz-bU4vHU68T0w7G34V5Xea3VE5ymV4M2cAEb6S-AL9-JrJBOLeLgByrFVtB2cC1NZOYVNZ4hbwN0/?imgmax=800" width="244" height="47" /></a></p> <p>In SQL 2012 all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-61777955619451466352015-03-24T13:40:00.001-07:002015-03-24T13:40:51.311-07:00SQL Maintenance Plan error “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”<p>Some body have this error : <em>Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.</em> When i execute my maintenance plan.</p> <p> </p> <p><strong>Resolution </strong></p> <p>The issue is related to the default email profile, you have to set a default email profile in order for the maintenance plan send the email alert. </p> <p> </p> <p>Please Follow These steps to Resolve this Problem</p> <p>1. Go to Database mail</p> <p>2. Right Click  Click on Configure Database mail.</p> <p>3. wizard will click next</p> <p>4.select Manage Profile security</p> <p>5. Click Next</p> <p>6. Beside Profile name  by default there will be no , Please make it yes</p> <p>7. Finish</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-56874024201205507382015-02-25T09:24:00.001-08:002015-03-24T13:39:13.336-07:00Difference between DateTime2, DateTime and SmallDateTime<p>Here are the main differences you should know about these three date types: <br /></p> <table cellspacing="0" cellpadding="0" border="0"><tbody> <tr> <td width="98">  </td> <td width="265">Range of Dates</td> <td width="172"> <p>Accuracy</p> </td> <td width="148"> <p>Size</p> </td> <td width="273">usage</td> <td width="87">Applies to</td> </tr> <tr> <td>smalldatetime</td> <td width="265"> <p>January 1, 1900 - June 6, 2079</p> </td> <td width="172"> <p>one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.</p> </td> <td width="148">4 bytes</td> <td> <p>DECLARE @myDate SmallDatetime</p> </td> <td width="87">SQL 2000 >+</td> </tr> <tr> <td>Datetime</td> <td width="265"> <p>January 1, 1753 - December 31, 9999</p> </td> <td width="172"> <p>three-hundredths of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).Values are rounded to increments of .000, .003, or .007 seconds</p> </td> <td> <p>8 bytes</p> </td> <td> <p>DECLARE @myDate Datetime</p> </td> <td width="87">SQL 2000 >+</td> </tr> <tr> <td>Datetime2</td> <td width="265"> <p>January 1, 0001 - December 31, 9999</p> </td> <td width="172"> <p>100ns</p> </td> <td width="148"> <p>can take 6-8 Bytes; by default it will take 7 bytes. 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes</p> </td> <td> <p>DECLARE @myDate   Datetime2(7)</p> </td> <td width="87">>=SQL 2008</td> </tr> </tbody></table> <p> </p> <p> </p> <p>Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-46137263041880600102015-02-05T13:45:00.001-08:002015-02-05T13:45:22.254-08:00Identifying Unused Databases<p>This is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use.  The first step in this process is simply looking at the date & time stamps of the .MDF and .LDF files, which could help in some cases,To evaluate whether it was worth keeping the databases online, I had some investigation to do. <br /><strong>Step 1 : Who is Connected?</strong> <br />The following TSQL summarizessummarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50) <br /></p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:81823fd6-85b3-4f52-902c-c2573e6bd1b9" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 405px; height: 100px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> loginame , nt_username, </span><span style="color: #FF00FF;">COUNT</span><span style="color: #000000;">(</span><span style="color: #808080;">*</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> Connections<br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.sysprocesses<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> spid </span><span style="color: #808080;">></span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;"> </span><span style="color: #808080;">and</span><span style="color: #000000;"> spid </span><span style="color: #808080;">!=</span><span style="color: #000000;"> </span><span style="color: #008000; font-weight: bold;">@@SPID</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">GROUP</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> loginame , nt_username<br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">COUNT</span><span style="color: #000000;">(</span><span style="color: #808080;">*</span><span style="color: #000000;">) </span><span style="color: #0000FF;">DESC</span><span style="color: #000000;"><br /></span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p><strong>Step2 : Check data</strong> </p><br /><br /><p>Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!<br /> <br /></p><br /><br /><div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:0c9303d9-3755-4973-aa3a-6d4964c247bf" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 530px; height: 219px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> </span><span style="color: #FF00FF;">DB_NAME</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Database</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , s.name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">SchemaName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , o.name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">TableName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_lookup</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_lookup</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_scan</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_scan</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_seek</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_seek</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.dm_db_index_usage_stats </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> i<br /></span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.objects o </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> o.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> i.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.schemas s </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> o.schema_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> s.schema_id<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">DB_ID</span><span style="color: #000000;">()<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> s.name </span><span style="color: #808080;"><></span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">sys</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">GROUP</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, s.name, o.name<br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, s.name, o.name<br /></span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p><strong>Step3 : When were tables last  accessed</strong> </p><br /><br /><p>Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.<br /> <br /></p><br /><br /><div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:eed33423-ae7a-4b46-bc5b-5669b7db5eb6" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 530px; height: 219px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> </span><span style="color: #FF00FF;">DB_NAME</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Database</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , s.name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">SchemaName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , o.name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">TableName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_lookup</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_lookup</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_scan</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_scan</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> , </span><span style="color: #FF00FF;">MAX</span><span style="color: #000000;">(i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_seek</span><span style="color: #FF0000;">]</span><span style="color: #000000;">) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">last_user_seek</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.dm_db_index_usage_stats </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> i<br /></span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.objects o </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> o.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> i.</span><span style="color: #FF00FF;">object_id</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.schemas s </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> o.schema_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> s.schema_id<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">DB_ID</span><span style="color: #000000;">()<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> s.name </span><span style="color: #808080;"><></span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">sys</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">GROUP</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, s.name, o.name<br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> i.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">database_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, s.name, o.name<br /></span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p><strong>Step4 : Check the latest date values on the database</strong></p><br /><br /><p>Assuming that there is a datatime  column and is populated periodically, this could give a better picture whether the data has been altered recently. </p><br /><br /><p> </p><br /><br /><div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:8f45c486-0ebd-4b27-a276-26b494c7bbb6" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 530px; height: 696px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@cols</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TABLE</span><span style="color: #000000;"><br />(<br /> r </span><span style="color: #0000FF;">INT</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">IDENTITY</span><span style="color: #000000;"> ,<br /> TableName </span><span style="color: #0000FF;">VARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,<br /> ColumnName </span><span style="color: #0000FF;">VARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)<br />);<br /></span><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@i</span><span style="color: #000000;"> </span><span style="color: #0000FF;">INT</span><span style="color: #000000;"> ,<br /> </span><span style="color: #008000;">@nSQL</span><span style="color: #000000;"> </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">1000</span><span style="color: #000000;">); <br /></span><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@MaxData</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TABLE</span><span style="color: #000000;"><br />(<br /> TableName </span><span style="color: #0000FF;">VARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">) ,<br /> MaxDate DATETIME2<br />);<br /><br /></span><span style="color: #0000FF;">INSERT</span><span style="color: #000000;"> </span><span style="color: #0000FF;">INTO</span><span style="color: #000000;"> </span><span style="color: #008000;">@cols</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> TABLE_NAME ,<br /> COLUMN_NAME<br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> INFORMATION_SCHEMA.COLUMNS<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> DATA_TYPE </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">Date</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">datetime</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">datetime2</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> )<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">OBJECTPROPERTY</span><span style="color: #000000;">(</span><span style="color: #FF00FF;">OBJECT_ID</span><span style="color: #000000;">(TABLE_NAME), </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">IsSystemTable</span><span style="color: #FF0000;">'</span><span style="color: #000000;">)</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">; <br /><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #008000;">@i</span><span style="color: #808080;">=</span><span style="color: #008000; font-weight: bold;">@@ROWCOUNT</span><span style="color: #000000;">;<br /><br /></span><span style="color: #0000FF;">WHILE</span><span style="color: #000000;"> </span><span style="color: #008000;">@i</span><span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">BEGIN</span><span style="color: #000000;"> <br /> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #008000;">@nSQL</span><span style="color: #808080;">=</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">SELECT </span><span style="color: #FF0000;">'</span><span style="color: #808080;">+</span><span style="color: #FF0000;">''''</span><span style="color: #808080;">+</span><span style="color: #000000;">TableName</span><span style="color: #808080;">+</span><span style="color: #FF0000;">''''</span><span style="color: #808080;">+</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">, MAX(</span><span style="color: #FF0000;">'</span><span style="color: #808080;">+</span><span style="color: #000000;">ColumnName<br /> </span><span style="color: #808080;">+</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">) FROM </span><span style="color: #FF0000;">'</span><span style="color: #808080;">+</span><span style="color: #000000;">TableName ,<br /> </span><span style="color: #008000;">@i</span><span style="color: #000000;"> </span><span style="color: #808080;">-=</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> </span><span style="color: #008000;">@cols</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> r</span><span style="color: #808080;">=</span><span style="color: #008000;">@i</span><span style="color: #000000;">;<br /><br /> </span><span style="color: #0000FF;">INSERT</span><span style="color: #000000;"> </span><span style="color: #0000FF;">INTO</span><span style="color: #000000;"> </span><span style="color: #008000;">@MaxData</span><span style="color: #000000;"><br /> ( TableName, MaxDate )<br /> </span><span style="color: #0000FF;">EXECUTE</span><span style="color: #000000;"> ( </span><span style="color: #008000;">@nSQL</span><span style="color: #000000;"><br /> );<br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;">;<br /><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> </span><span style="color: #008000;">@MaxData</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;"> </span><span style="color: #0000FF;">DESC</span><span style="color: #000000;">; </span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p> </p><br /><br /><p><strong>Step5 : Audit / run Trace</strong></p><br /><br /><p>Audit the database logins associated with the database, or run a trace on that database ; Some applications, access the database on some specific times like year end,  end financial period etc. I wont suggest you go and delete the database if all the above steps intended towards deletion. You could take the database offline for few months before you delete it.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-74671628919355828232015-01-28T14:46:00.001-08:002015-01-28T14:46:18.011-08:00Backup database to disk = ‘Nul’<p>To understand what this is doing, first we need to understand what Nul is, in the context of a file. It is not just a misspelling of NULL.</p> <p>DOS, since the earliest days, has had a number of devices that can be treated like files, eg LPT1, COM1 and CON. NUL is another of those devices (virtual files, if you like) and is equivalent to the unix \dev\nul, a ‘file’ that deletes any data that’s written to it.</p> <p>What are the impact of this Backup location</p> <p>Anything that’s written to NUL is discarded. So when a transaction log backup is taken at ‘NUL’ , you are actually breaking the transaction log chain. That means, you wont be able to restore the transaction logs. As SQL thinks that the log chain is intact, it discards the log records that were sent to Nul as it would after a normal log backup. It does not switch the DB into a pseudo-simple recovery model. Inactive log records are not discarded at a checkpoint and future log backups will succeed.</p> <p>That’s the main difference. Log records will still be retained after a backup to NUL. Log backups will succeed after a backup to NUL. They’re useless for restoring the DB because there’s a log backup missing, but they’ll succeed.</p> <p><strong>Warning!</strong> <br />This command runs great accept under the following conditions:</p> <ol> <li>If you run a differential backup, don’t run this command! The reason is that the differential backup picks up pages that have changed. Running the backup command resets the flag on the page. Since the backup to a NUL: device reads the page, but does not back it up to media, the flag is reset as if it HAD been backed up… so the database does NOT back it up during the next differential. If you have run a backup to NUL: and do differential backups, run a FULL backup immediately and everything will be fine. </li> <li>If you have heavy disk IO now, a backup to a NUL: device will only add more stress slowing things down more. </li> <li>For reason #2, run this command in off hours.</li> </ol> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-62696097013879317622015-01-28T12:34:00.001-08:002015-01-28T12:34:02.027-08:00How to recover space from a huge transaction log file<p>Most of the novice dba’s forget to take the transaction log backups, which eventually ends up consuming a lot of disk space. This is one of the questions people ask me very often. Here are the steps which I tell them to follow. </p> <ol> <li>Take the full backup if the database </li> <li>Check to see if something is keeping SQL from reusing existing log space: <br />SELECT name, log_reuse_wait_desc <br />FROM sys.databases <br />WHERE name = '<your_db_name>'</li> <li>Change the recovery to ‘Simple’ </li> <li>Find the fileID, use the following statement </li> USE <your_db_name> <br />EXEC sp_helpfile <li>Shrink the log file </li> </ol> <blockquote> <p>USE <your_db_name> <br />DBCC SHRINKFILE ( 2 )</p> </blockquote> <p>     6.  Resize the log file  so that it returns free space to the OS</p> <blockquote> <p>ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 4GB )</p> </blockquote> <p>     7.  Change the recovery to <strong>Full</strong> and take another backup <br /></p> <ol> <p></p> </ol> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-36618746792861514872015-01-08T13:23:00.001-08:002015-01-08T13:23:34.869-08:00What causes Multi-Page allocations?<p>In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the <a href="http://msdn.microsoft.com/en-us/library/ms190737.aspx">-g memory_to_reserve</a> startup parameter. </p> <p>So what kinds of query result in MPA activity? Here are few scenarios </p> <p>1. A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA. </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTVjHYpoSj1UKcCRLRh0-LcigK4Rw2MhgrWQ1pUPDi6gMniNWbanc1EK64RQB870PRZXHGsuhbsPjhOKx00bXBnU78Wi0-GZlYrfCrPjDRY89mkFAX-5TxRdS6QGauZQhVoX1rKMR08w4/s1600-h/clip_image001%25255B3%25255D.png"><img title="clip_image001" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image001" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiobi1YMgbXAHMJ3pyW6iYdwn0O4LFohSCzjuZJn3V424PvHtOGR8xIt-_5k_PafqXn3ERkyzNnzqXtqMJmxUQmllcSep89pCfvjCnVTfQCRfsprew7dYOn3-hJKUM3cgvomhv6fDXjqd4/?imgmax=800" width="199" height="100" /></a></p> <p>2. Another workload would be large adhoc batches of the form: </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbXIA9EYZ2ZHSe7XGhV1qY6cRQhAgXYSw3x8kp_ALu2gVhkaVRDrPH3K6b_U0v3nkWsSGWx3D8Ioi5Dn9GlC6pBZBYOBxVEFX6Gq-fvoZqkuQj7gf6q2oGi3uJTtT8PkQzumt8OotqLaI/s1600-h/clip_image002%25255B3%25255D.png"><img title="clip_image002" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="clip_image002" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTQN18dWEaefVC6WE1RgRRWFx7ZPuEyVp4-y4wxdG9aWkIyey70u7oD4P3-a9ceSwz-bU4vHU68T0w7G34V5Xea3VE5ymV4M2cAEb6S-AL9-JrJBOLeLgByrFVtB2cC1NZOYVNZ4hbwN0/?imgmax=800" width="244" height="47" /></a></p> <p>In SQL 2012 all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-56941449576113443642014-12-17T14:42:00.001-08:002014-12-17T14:42:42.710-08:00How to Recover from a Lost SA Password in SQL Server<p> </p> <p>I saw many people asking this question over and over again and today, I’d like to share with you a backdoor to SQL Server 2012 which would help you gain <b>SYSADMIN</b> access to your production SQL Servers. However, that would mean your Windows account will need to be a member of the local administrators group on Windows Servers where SQL Server 2012 Services are running.</p> <p>SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.</p> <p><b>Steps to take control of your SQL Server 2012 as an SA:</b></p> <p><b></b></p> <p> </p> <ol> <li>Stop the sql server services</li> <li>Start the SQL Server 2012 instance using single user mode from command prompt by launching  the command prompt as an administrator. (Note: You can also start SQL Server 2012 using minimal configuration which will also put SQL Server in single user mode)</li> <li> From the same command prompt ( as Administrator) type: <b><i>SQLServr.Exe –m</i></b> (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window.</li> </ol> <blockquote> <p>*You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have SQL Server 2012 Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server 2012 machine. Usually the Binn folder is located @ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn></p> <p>4. Once SQL Server 2012 service has been started in single user mode or with minimal configuration, you can now open up another command line window as an administrator and use SQLCMD command from command prompt to connect to SQL Server 2012 instance:</p> </blockquote> <blockquote> <p><b><i>SQLCMD –S <Server_Name\Instance_Name></i></b></p> </blockquote> <p>                   You will now be logged in to SQL Server. Keep in mind that you are now logged in as an Admin on this server instance.</p> <p align="justify">5. Create a login: 1> CREATE LOGIN 'Login_Name>' with PASSWORD='<Password>' <br />                                 2> GO <br />6. Give the user SA rights:  SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN' <br />       </p> <p align="justify">7. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)</p> <p align="justify">8 . Log in to the SQL Server 2012 management studio or from the command prompt using “SQL_SALEEM” account and it’s respective password, you now have system admin access to your SQL Server 2012 instance. You may now reset the SA password and take control of your production SQL Server boxes.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-38038686594069689062014-11-25T10:45:00.001-08:002014-11-25T10:45:31.570-08:00SQL SERVER – Error: SSMS Database Compatibility Level Drop Down is Empty<p>I recently installed SQL server 2014 on my machine; I was using the SSMS for SQL 2012 as it as all the pluggins installed for my daily use.  Now for testing an application, i have restored a database from sql server 2008,  I changed the compatibility programmatically </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKAsk5iMXYjBTPxBNMMFIwEp6yiqwZz5TpMgglfZQ3c9bDzhC9q_XMHt1Yj4saq2zgzbOI6KtcRpJxKhbapvCnakvpn4YwDH3TEVPXoH7aefjN8RPZl0T3N2rjONpnldaX6wa8nQNGY3I/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgH1zhdRTNtOSaMcIIDFrTRfbFSjLm6JpvqS9coU_f_QKpzshAWbtN6uVIlJ7UihxbglWJ3mj9P2vxcHR_UdMxJBwjQlHWRreT-v6JRLyEtUlFpzWjft5e4M5yHKBVJpk52qNb4hz6ms9s/?imgmax=800" width="357" height="35" /></a> </p> <p>When i queried the sysdatatabases, it showed as cmplevel 120 </p> <p>Unfortunately when i connected to this server from my existing SQL Server management studio, in the options sections, I saw the compatibility level drop down empty. there is nothing to choose as shown below; I queried the sysdatabases and i see it is 120. After spending some time I realized that I was using the older version of SSMS. </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhscG30FrFFi2NHFmKM1tTwUQQd2SFSbb6DkGJypYIZEbM2xpC_4LlTiP6SW_Z8CaLXdeLFixss5HxHpUH08BGRx_E642EmWm-wW8mThcDqCqu3154Pgy1n4JH7dbWpN_d23L1LVRcVars/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaDm8Gcaz5JSNK4b6QVtA1YjO2r11o8HbgdjMNm3-oYb_RIvFO23o4eAKZdJTkMz971GNtsbWspS4tZ-t3_dSpu-kDAOjuo0__SkR1DqrAOFDgdOuRO74DIx0k1E7fGKZbQpg2UIXOHFo/?imgmax=800" width="420" height="373" /></a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-86258004502769398412014-11-19T16:04:00.001-08:002014-11-19T16:04:25.838-08:00Could not update the metadata that indicates database is enabled for Change Data Capture. The failure occurred when executing the command SetCDCTracked(Value = 1)<p>I got this  error message when I was trying to enable CDC on a SQL Server  database for </p> <p>“ </p> <p><font color="#ff0000">Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193 <br />Could not update the metadata that indicates database XXXXX is enabled for Change Data Capture. <br />The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: <br />'Could not obtain information about Windows NT group/user 'XXXX\xxxxxxx', error code 0x5.'. <br />Use the action and error to determine the cause of the failure and resubmit the request.</font></p> <p></p> <p>“</p> <p> </p> <p>The fix for this is to change the database owner to "sa" by executing the below script.</p> <p>USE <Database></p> <p>GO</p> <p>EXEC sp_changedbowner 'sa'</p> <p>Read the below KB article for more details.</p> <p><a href="http://support.microsoft.com/kb/913423">http://support.microsoft.com/kb/913423</a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-25594211415675033262014-11-18T07:41:00.001-08:002014-11-18T07:42:06.910-08:00How to list role members in SQL Server<p> </p> <p>Below is the query to list the users in a specific role.  if you are looking for the users who can manage the sql server agent jobs, use the filter ( Where rp.name = ‘SQLAgentRole’)  and run this under ‘msdb’ database. otherwise remove the filter </p> <p> </p> <p></p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:a837b7f9-d63c-462d-942e-3197b6468f83" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 326px; height: 466px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #000000;">;</span><span style="color: #0000FF;">WITH</span><span style="color: #000000;"> RoleMembers (member_principal_id, role_principal_id) <br /></span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> <br />(<br /> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> <br /> rm1.member_principal_id, <br /> rm1.role_principal_id<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.database_role_members rm1 (NOLOCK)<br /> </span><span style="color: #0000FF;">UNION</span><span style="color: #000000;"> </span><span style="color: #808080;">ALL</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> <br /> d.member_principal_id, <br /> rm.role_principal_id<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> sys.database_role_members rm (NOLOCK)<br /> </span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> RoleMembers </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> d <br /> </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> rm.member_principal_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> d.role_principal_id<br />)<br /></span><span style="color: #0000FF;">select</span><span style="color: #000000;"> </span><span style="color: #0000FF;">distinct</span><span style="color: #000000;"> rp.name </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> database_role, mp.name </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> database_user<br /></span><span style="color: #0000FF;">from</span><span style="color: #000000;"> RoleMembers drm<br /> </span><span style="color: #808080;">join</span><span style="color: #000000;"> sys.database_principals rp </span><span style="color: #0000FF;">on</span><span style="color: #000000;"> (drm.role_principal_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> rp.principal_id)<br /> </span><span style="color: #808080;">join</span><span style="color: #000000;"> sys.database_principals mp </span><span style="color: #0000FF;">on</span><span style="color: #000000;"> (drm.member_principal_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> mp.principal_id)<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> rp.name </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">SQLAgentUserRole</span><span style="color: #FF0000;">'</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-34325032906779806992014-11-05T10:21:00.001-08:002014-11-05T10:21:36.655-08:00Restrict user from connecting to the database from SSMS<p>The following logon trigger will restrict a user from connecting thru SQL Server management studio. As always, test this first before implementing.</p> <p>This will deny all the connections made by the user ‘test’  from the application ‘ssms’</p> <p> </p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:c9386ac4-7b64-46c0-8e25-00a748d6e229" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 895px; height: 162px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">CREATE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TRIGGER</span><span style="color: #000000;"> ltr_restrictUserFromSSMS<br /></span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> </span><span style="color: #808080;">ALL</span><span style="color: #000000;"> SERVER <br /></span><span style="color: #0000FF;">FOR</span><span style="color: #000000;"> LOGON<br /></span><span style="color: #0000FF;">AS</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">BEGIN</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">IF</span><span style="color: #000000;"><br />(PROGRAM_NAME() </span><span style="color: #808080;">LIKE</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">Microsoft SQL Server Management Studio%</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> ORIGINAL_LOGIN() </span><span style="color: #808080;">=</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">test</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> )<br /></span><span style="color: #0000FF;">BEGIN</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">ROLLBACK</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">END</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">END</span><span style="color: #000000;"><br /><br /><br /></span><span style="color: #0000FF;">DROP</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TRIGGER</span><span style="color: #000000;"> ltr_restrictUserFromSSMS </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> </span><span style="color: #808080;">ALL</span><span style="color: #000000;"> SERVER</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p>Sometimes the connections from SSMS will listed as ‘Microsoft SQL Server Management Studio –query’; so if you want to restrict all the connections made from ssms and the queries run from them, it is good to use   <strong>PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%'</strong> </p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuW58sXc2PDwm6kRDqwhMMtzUFLqtdiz0LzKQhMC5EvJT9jBRuuQ1EpyFC87X2JHcrfb4Nzqt7d_skOZQ5Pne64ptXMoUhG18-26CsEshW-euaxxgZtvzwjjfJU82lRExD8yUDnprjEkg/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ9t7YFf0s36dAyxItR8ZO9P7QepahU-LyCTtjcyZsYqwzBSnURUAeEdWedaEmKjAHvXyI5ELLe6EnXSayM1pISERhYCG01fFyyZz73LaGTka5xUALwyTyI-bHuoX9VeBFUifdPeyolSI/?imgmax=800" width="598" height="275" /></a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-54925746780883498562014-11-05T09:23:00.001-08:002014-11-05T09:23:23.350-08:00DBCC PAGE to Examine SQL Server Table and Index Data<p>I was writing another article on the smallest dataset possible in sql server, and i came across a situation where I need to use <strong>DBCC PAGE ()</strong> statement for a table. <strong>DBCC PAGE ()</strong> statement, uses <strong><em>database name, file number, page number, </em></strong>and <strong><em>print Option</em></strong> as arguments. In my case, i needed to find the page number of the table in order for me to use this  statement. Then I came across the undocumented <strong>DBCC IND() </strong>statement which lists the ‘data’ and ‘index’ pages of the table. </p> <p>For the test purpose, I created a database , and a table , when I ran DBCC IND() statement against it, it returned no rows. </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxZodZOl0vF_vQqy-7ltyo7jlEXlgVw5nA0I9noPWq6Et3Lrd0nTqeHvU7VJf4fbX5k6onknKy21Hp9NYNdH7z1MmKDHQQLcDfFQsuQR1eMHuEHMqFSDlMdQ6spy0BH-_vyKx7JFJ3yPc/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj88Eh3yyoClvkdWeabQsn7_Bz0aJG7NPNQ1JklXZMVudo_Is9sNg77Ds740Fnmnby0-J39Lj6AAJ0buhooryTQ5rGI6P1RILsrhGUW7u5rDPl5ye_YHwS6JtcEN-FT-KCEQaZKtkHG_Ng/?imgmax=800" width="658" height="189" /></a> </p> <p>This was the expected behavior, since the table was empty, sql server engine hasn’t allocated any pages for it. </p> <p>In the next step, i insert a row into the table to see how many pages it has allocated. </p> <p> </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuHSqldFJRZVQh-tRRqI2qECIeqie7J4z6rWJbfu-WKiEv13eheEB3WnQg_KPFc4tqY_cv0goZAOr4UVMcmfAJ2V9ReOoZo_2LC3sWZjQELSd5CvR5wulFQ13taYVsYQTvoRl6BTwps1M/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyFD9RN1FwZ4EAPfqi3_gnEXHouBvyAXrwYyB0aNMzHBPKS4TnOGlmTUnXjeMis6ucCBHBiYF0OwhaJUhsaqrcwBAiQKgbneMJoK8BITM0dIN8RO7Q_xp1MNe7HmqBRzahglP32vzMEuU/?imgmax=800" width="1130" height="213" /></a> </p> <p> </p> <p>What does all this data mean? For purposes of this tip, we'll focus on a couple of key columns. The columns PageFID and PagePID represent a file number where pages reside and a page number within the file where data lives, respectively. IndexID is the index' index_id as found in sys.indexes. PageType dictates the kind of page. Type = 1 is a data page, Type = 2 is an index page, and Type = 10 is the IAM page that maintains the index itself. IndexLevel is the level within the IAM structure the page falls. If level = 0, then this is a leaf level page for the index. For more detailed information on all the columns (this <i>is</i> an undocumented command after all), this <a href="http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx">MSDN blog</a> by former Microsoft storage engine expert Paul Randal breaks them all down.</p> <p>I hope this help you in some manner.  please let me know your thoughts and comments. </p> <p> </p> <p>Aneesh </p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-57635482169495522622014-11-03T15:45:00.001-08:002014-11-18T07:43:06.710-08:00When statistics was updated ?<p>Statistics are very important for sql server engine,  it is  used by the SQL Server optimizer to choose the most efficient plan.  When we don’t have up to date statistics <br />it may end with SQL server optimizer choosing inefficient query plan. You can use below query to identify when SQL Server statistics were last updated</p> <p> </p> <p></p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:9190e355-95e1-42b6-94c1-4859787f1b6f" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 405px; height: 1337px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">DB_ID</span><span style="color: #000000;">() </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> dbid ,<br /> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> ) </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">CHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">12</span><span style="color: #000000;">), rows)<br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> ( </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> rows<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> dbo.sysindexes i2<br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i2.id</span><span style="color: #808080;">=</span><span style="color: #000000;">i.id<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i2.indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> )<br /> ) </span><span style="color: #008080;">--</span><span style="color: #008080;"> ''-''</span><span style="color: #008080;"><br /></span><span style="color: #000000;"> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> rowcnt ,<br /> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> ) </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> rowmodctr<br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">BIGINT</span><span style="color: #000000;">, rowmodctr)<br /> </span><span style="color: #808080;">+</span><span style="color: #000000;">( </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> rowmodctr<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> dbo.sysindexes i2<br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i2.id</span><span style="color: #808080;">=</span><span style="color: #000000;">i.id<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i2.indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> )<br /> )<br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> row_mods ,<br /> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> rows<br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">BIGINT</span><span style="color: #000000;">, </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> )<br /> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">BIGINT</span><span style="color: #000000;">, rowmodctr)<br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> rowmodctr<br /> </span><span style="color: #808080;">+</span><span style="color: #000000;">( </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">BIGINT</span><span style="color: #000000;">, rowmodctr)<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> dbo.sysindexes i2<br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i2.id</span><span style="color: #808080;">=</span><span style="color: #000000;">i.id<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i2.indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> )<br /> )<br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">/</span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (NUMERIC(</span><span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">), ( </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">BIGINT</span><span style="color: #000000;">, rows)<br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> rows<br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> dbo.sysindexes i2<br /> </span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> i2.id</span><span style="color: #808080;">=</span><span style="color: #000000;">i.id<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i2.indid </span><span style="color: #808080;">IN</span><span style="color: #000000;"> ( </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> )<br /> ))</span><span style="color: #808080;">*</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)<br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> pct_mod ,<br /> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">, u.name</span><span style="color: #808080;">+</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">.</span><span style="color: #FF0000;">'</span><span style="color: #808080;">+</span><span style="color: #000000;">o.name) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> objname ,<br /> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> i.status</span><span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x800040</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0x800040</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">AUTOSTATS</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> i.status</span><span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x40</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0x40</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i.status</span><span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x800000</span><span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">STATS</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">INDEX</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> type ,<br /> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">, i.name) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> idxname ,<br /> i.indid ,<br /> </span><span style="color: #FF00FF;">STATS_DATE</span><span style="color: #000000;">(o.id, i.indid) </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> stats_updated ,<br /> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> i.status</span><span style="color: #808080;">&</span><span style="color: #800000; font-weight: bold;">0x1000000</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">no</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">ELSE</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">*YES*</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> norecompute ,<br /> o.id </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> objid ,<br /> rowcnt ,<br /> i.status<br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> dbo.sysobjects o ,<br /> dbo.sysindexes i ,<br /> dbo.sysusers u<br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> o.id</span><span style="color: #808080;">=</span><span style="color: #000000;">i.id<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> o.uid</span><span style="color: #808080;">=</span><span style="color: #000000;">u.uid<br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> i.indid </span><span style="color: #808080;">BETWEEN</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;"> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">254</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> o.type</span><span style="color: #808080;">=</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> pct_mod </span><span style="color: #0000FF;">DESC</span><span style="color: #000000;"> ,<br /> </span><span style="color: #FF00FF;">CONVERT</span><span style="color: #000000;"> (</span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">, u.name</span><span style="color: #808080;">+</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">.</span><span style="color: #FF0000;">'</span><span style="color: #808080;">+</span><span style="color: #000000;">o.name) ,<br /> indid<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /></span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-81497200727146668422014-10-20T08:37:00.001-07:002014-10-20T08:40:05.803-07:00How to find SQL License type<p>Since SQL Server 2012 , you can find this information in the SQL Server Error Logs.</p> <p>Before SQL Server 2008 R2 there used to be some properties maintained  but is now unused</p> <p>Mode of this instance of SQL Server.</p> <p>PER_SEAT = Per Seat mode</p> <p>PER_PROCESSOR = Per-processor mode</p> <p>DISABLED = Licensing is disabled.</p> <p> In SQL Server 2012 , a check on the SQL Server error logs will report on information required</p> <p> </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0VlbQop6fVLS4fieP7jhWp33xg77L8zppNTcL2RCa0mpOqAouNl__lHjnK2z76jmAx48ZGFckyI4FvG0xipYAs7CaouCIbDl3qF36zh0fvO_IRPZ6SR_SsHAxf-dqLa6QbEjqcgIkN78/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjnSPrJ5FCklhbZ_qEVMXedfPtczlesRO3KFXwtsObtZ1lR9RTKiAgfEXlOqGH2Knqk4XS8ODTyoZqy0hL3jmUOXpnfgaFxV5hBsUCwYS_OVwL5Qnf6ND4KZsEOV6m16A_9ZFkfZ6c2J0I/?imgmax=800" width="873" height="260" /></a> </p> <p></p> <p> </p> <p>Here is what you see when you run the same query on SQL 2008 R2 </p> <p> </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRurOMi8wpZCQEuAzOu5Rv4cIZwZMrxIdmYrkudpevPUFzBhf54mnE4RJvDQS2Y-P79sgBZBFxx-1soHiRkxxSwnqGnMzOmuqx9307Z2gSmqKaVDI1l7f47wNEl1Itpapcj9yFarFb3Pg/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; border-bottom: 0px; border-left: 0px; display: inline" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidNpdLraKXrmRjiMECvYYVdUIfG5ttKQkUOQ-VWN66DC3fGP4Xdchs1OzorbtO_fH9XHiNlXf7M-TKzMMl2NIP8v1ySCF729rKxB052cJnjI4lvEDJLCk6iFr3sQBzmW-StKI4_DPh85k/?imgmax=800" width="808" height="231" /></a></p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-71576097058113685262014-10-16T14:33:00.001-07:002014-10-16T14:33:11.859-07:00SQL SERVER – Fix – Error – Agent XPs component is turned off<p><font color="#ff0000"></font></p> <p>Today I was working on updating operator emails on SQL Server and when i ran the script  it gave me given below error.</p> <p><font color="#ff0000">SQL Server blocked access to procedure 'dbo.sp_update_operator' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.</font></p> <p>I then realized that, the sql server agent is not running on that server. Basically, <strong>Agent XPs</strong> option enables the SQL Server Agent extended stored procedures on the SQL Server and the reason why this error occurs is because <strong>Agent XPs</strong> is not enabled in SQL Server. Also, if this option is not enabled, SQL Server Agent node will not display in SQL Server Management Studio Object Explorer.</p> <p><strong>Resolutions</strong></p> <p><strong>Method 1:</strong> <br />In this method, we will execute the script to enable <strong>Agent XPs</strong>. You need to execute the given below script in Query window.</p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:369a4a22-a752-4e4f-9ef5-c602f6782175" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 405px; height: 143px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">EXEC</span><span style="color: #000000;"> sp_configure </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">show advanced options</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">RECONFIGURE</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">EXEC</span><span style="color: #000000;"> sp_configure </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">Agent XPs</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, </span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">RECONFIGURE</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">GO</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p><strong>Method 2:</strong></p><br /><br /><p>In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. It will automatically enable <strong>Agent XPs</strong>.</p><br /><br /><p><img alt="Agent Xps component is turned off.1.3" src="http://raresql.files.wordpress.com/2013/10/agent-xps-component-is-turned-off-1-3.png?w=500&h=319" /></p><br /><br /><p> </p><br /><br /><p><strong>Method 3 :</strong> </p><br /><br /><p>Start –> run –> services.msc </p><br /><br /><p>find the SQL Server agent service , right click and start. </p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-81012480745130525002014-10-15T13:07:00.001-07:002014-10-15T13:07:41.091-07:00Database backup and restore history<p>Here is the script that will help you the database backup and restore histories </p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:accc767c-57e7-4e3f-af95-0507e9efcb98" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 800px; height: 200px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TOP</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;"> b.database_name<br /> , BMF.physical_device_name </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> BackupFileName <br /> , B.backup_finish_date </span><span style="color: #0000FF;">as</span><span style="color: #000000;"> BackupDate<br /> , B.backup_size</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">1024.0</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">1024.0</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> BackupSizeMB<br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"><br /> msdb.dbo.backupset B<br /> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> msdb.dbo.backupmediafamily BMF </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> B.media_set_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> BMF.media_set_id<br /></span><span style="color: #008080;">--</span><span style="color: #008080;">WHERE database_name = '<YourDatabaseName>'</span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> B.backup_finish_date </span><span style="color: #0000FF;">DESC</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p>Restore history </p><br /><br /><div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:e88d29f2-8957-46a4-bf5c-036461970806" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 800px; height: 238px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TOP</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;"><br /> RH.destination_database_name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Database</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> RH.</span><span style="color: #FF00FF;">user_name</span><span style="color: #000000;"> </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Restored By</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> RH.restore_date </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Restore Started</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> BMF.physical_device_name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Restored From</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> RF.destination_phys_name </span><span style="color: #0000FF;">AS</span><span style="color: #000000;"> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Restored To</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> RH.</span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"><br /> msdb.dbo.restorehistory RH<br /> </span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> msdb.dbo.backupset BS </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> RH.backup_set_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> BS.backup_set_id<br /> </span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> msdb.dbo.restorefile RF </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> RH.restore_history_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> RF.restore_history_id<br /> </span><span style="color: #0000FF;">INNER</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> msdb.dbo.backupmediafamily BMF </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> BMF.media_set_id </span><span style="color: #808080;">=</span><span style="color: #000000;"> BS.media_set_id<br /></span><span style="color: #008080;">--</span><span style="color: #008080;">WHERE destination_database_name = '<DatabaseName>'</span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> RH.restore_history_id DES</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-36636689870368558942014-10-15T12:58:00.001-07:002014-10-15T12:58:53.317-07:00Find all permissions for all users in the database<p> </p> <p>Here is the query that lists all the user permissions, you need to run this on proper database to list the permissions, You can also put a filter on user </p> <p> </p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:c7377603-4770-4796-992d-f75c037a2351" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 800px; height: 1800px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">WITH</span><span style="color: #000000;"> cteUserPermissions<br /></span><span style="color: #0000FF;">AS</span><span style="color: #000000;"><br />(<br /></span><span style="color: #008080;">--</span><span style="color: #008080;"> Permissions provisioned to a sql user </span><span style="color: #008080;"><br />--</span><span style="color: #008080;"> or windows user/group directly </span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> <br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">S</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> L.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">S</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">SQL User</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">Windows User</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">DatabaseUserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Role</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #0000FF;">null</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionState</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">state_desc</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.permission_name,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">OBJECT_NAME</span><span style="color: #000000;">(P.major_id),<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> O.type_desc <br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> <br /> sys.database_principals DP<br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.login_token L </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">sid</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> L.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">sid</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.database_permissions P </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">grantee_principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.objects O </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">major_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> O.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">object_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">in</span><span style="color: #000000;"> (</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">S</span><span style="color: #FF0000;">'</span><span style="color: #000000;">,</span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;">) <br /><br /></span><span style="color: #0000FF;">UNION</span><span style="color: #000000;"><br /><br /></span><span style="color: #008080;">--</span><span style="color: #008080;"> Permissions provisioned to a sql user or windows user/group </span><span style="color: #008080;"><br />--</span><span style="color: #008080;"> through a database or application role</span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> <br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">S</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> L.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">CASE</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">S</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">SQL User</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">WHEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">U</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #0000FF;">THEN</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">Windows User</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">END</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">DatabaseUserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Role</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> R.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionState</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">state_desc</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.permission_name,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">OBJECT_NAME</span><span style="color: #000000;">(P.major_id),<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> O.type_desc<br /> <br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> <br /> sys.database_role_members DRM<br /> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.database_principals R </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> R.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DRM.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">role_principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.database_principals M </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DRM.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">member_principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.login_token L </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> M.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">sid</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> L.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">sid</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.database_permissions P </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">grantee_principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> R.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.objects O </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">major_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> O.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">object_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /><br /></span><span style="color: #0000FF;">UNION</span><span style="color: #000000;"><br /><br /></span><span style="color: #008080;">--</span><span style="color: #008080;"> Permissions provisioned to the public role, </span><span style="color: #008080;"><br />--</span><span style="color: #008080;"> which everyone gets by default</span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">{All Users}</span><span style="color: #FF0000;">'</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">UserType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">{All Users}</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">DatabaseUserName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">{All Users}</span><span style="color: #FF0000;">'</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">Role</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;">, <br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionState</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">state_desc</span><span style="color: #FF0000;">]</span><span style="color: #000000;">,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">PermissionName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.permission_name,<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectName</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF00FF;">OBJECT_NAME</span><span style="color: #000000;">(P.major_id),<br /> </span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">ObjectType</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> O.type_desc<br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"><br /> sys.database_principals DP<br /> </span><span style="color: #808080;">LEFT</span><span style="color: #000000;"> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.database_permissions P </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">grantee_principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">principal_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /> </span><span style="color: #808080;">JOIN</span><span style="color: #000000;"> sys.objects O </span><span style="color: #0000FF;">ON</span><span style="color: #000000;"> O.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">object_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> P.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">major_id</span><span style="color: #FF0000;">]</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">WHERE</span><span style="color: #000000;"><br /> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">type</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">R</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #008080;">--</span><span style="color: #008080;"> only roles</span><span style="color: #008080;"><br /></span><span style="color: #000000;"> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> DP.</span><span style="color: #FF0000;">[</span><span style="color: #FF0000;">name</span><span style="color: #FF0000;">]</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">public</span><span style="color: #FF0000;">'</span><span style="color: #000000;"> </span><span style="color: #008080;">--</span><span style="color: #008080;"> only public role</span><span style="color: #008080;"><br /></span><span style="color: #000000;"> </span><span style="color: #808080;">AND</span><span style="color: #000000;"> O.is_ms_shipped </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"> <br />)<br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> cteUserPermissions<br /></span><span style="color: #008080;">--</span><span style="color: #008080;">WHERE UserName in ('{All Users}', 'myusername')</span><span style="color: #008080;"><br /></span><span style="color: #0000FF;">ORDER</span><span style="color: #000000;"> </span><span style="color: #0000FF;">BY</span><span style="color: #000000;"> UserName, ObjectName, PermissionState, PermissionName</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-92170850945779630592014-10-15T08:39:00.001-07:002015-01-28T12:48:37.621-08:00SSIS Variables<div dir="ltr" style="text-align: left;" trbidi="on">
<h3>
</h3>
Variables have been available in SSIS since first introduced in. They can be used for a number of different purposes: <br />1. Define the path of a package <br />2. Hold system information, such as error information <br />3. Provide a means to create custom logging <br />4. Hold result set(s) to iterate through<br />
Let’s begin with a simple example such as using a variable to define the path of a package. Consider that you are having to execute a data flow task and based on the number of rows loaded will dictate what task should occur next. This is simple enough to using a variable and precedence constraint. The first step is to create a variable, which is done in the variable pane in SQL Server Data Tools:<br />
<img alt="variablepane" src="http://sqlsafety.com/wp-content/uploads/2014/09/variablepane-300x128.jpg" height="128" width="300" /><br />
<h3>
Scope</h3>
The first thing to note from the variable pane is the scope. The scope defines where the variable will be available. For example the scope for my variable “<em>RowCount</em>” is set to my package which I have named RowCount. This means that any task within the package, including child packages, will have access to the variable. It is best practice to insure that the variable scope is limited to only where it is needed. This package will have a total of three tasks:<br />
1. Data flow task that will take the data from the Adventureworks2012.Person.Person table through the Row Count transformation and to a flat file destination<br />
2. A script task that executes if the row count is greater than 1,000<br />
3. A script task that executes if the row count is less than 1,000<br />
In this case the scope is set to the package since all 3 tasks will need access to the variable. If the package contained other control flow tasks that did not require access to the variable then scope could be limited by placing the three affected tasks in a sequence container and setting the scope to the container.<br />
<h3>
Data Type</h3>
The data type defines the type of object and the acceptable values that the variable can be held. Another important factor is to assign the most efficient data type for a variable. For example, if the variable will hold a numeric value between -128and 128 the most efficient data type is SByte rather than UInt, which can hold a numeric value between o and 65,535. MSDN documents the data types <a href="http://msdn.microsoft.com/en-us/library/system.typecode.aspx">here</a>.<br />
<h3>
Value</h3>
This holds the value of the variable. This value can be changed within the package using script task, expression task, execute SQL task, as well as several other.<br />
<h3>
Expression</h3>
The expressions provides a means of assigning a value to the variable using an SSIS expression. Again this can be changed later within the package, but once package execution begins and the variable scope is invoked the value or value from the expression provides the variable value until and if it is changed.<br />
With the variable configured now I will include my data flow task that will record the value of all rows that go from the source, Adventureworks2012.Person.Person, to the destination, to the flat file destination using a row count transformation. This is easy enough to do as once connecting the data source to the row count you will be prompted for the variable that will be used to hold this numeric value.<br />
<img alt="DataFlowCount" src="http://sqlsafety.com/wp-content/uploads/2014/09/DataFlowCount.jpg" height="241" width="252" /><br />
<h3>
Precedence Constraint</h3>
Keeping in mind the requirements we now must define the path of execution based on the success of the data flow and the number of rows processed, which is now held within our variable. By connecting both script tasks using our <em>On Success</em> precedence constraint we can then change both constraints to use Expression and Constraint we can define one constraint to use the expression <em>@[User::RowCount]</em> > 1000 and the other <em>@[User::RowCount]</em> < 1000. This method will leave one path uncovered, that is if the RowCount == 1000. In this case we are not concerned about that so we will let it ride.<br />
<img alt="precedence" src="http://sqlsafety.com/wp-content/uploads/2014/09/precedence-300x223.jpg" height="223" width="300" /><br />
Each script task will be used to access the variable and display it in a message box and then reset the variable to 0. This requires that both tasks will need to have read and write access to the variable.<br />
<img alt="ScriptVariable" src="http://sqlsafety.com/wp-content/uploads/2014/09/ScriptVariable-300x254.jpg" height="254" width="300" /><br />
I will use VB.NET in each script task using the following code: <br />< style=”font-size: 12px;”> <br />Public Sub Main() <br />‘ <br />MessageBox.Show(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString) <br />Dts.Variables(“RowCount”).Value = 0 <br />MessageBox.Show(“The row count was: ” + Dts.Variables(“RowCount”).Value.ToString)<br />
Dts.TaskResult = ScriptResults.Success <br />The final package in the control flow looks like this:<br />
<img alt="FinalPackage" src="http://sqlsafety.com/wp-content/uploads/2014/09/FinalPackage-300x127.jpg" height="127" width="300" /><br />
You will notice that the precedence constraints are green, meaning that the path will only be taken if the preceding task completes successfully. You will also note that there is an “<em>fx</em>” next to each precedence constraint which means that the constraint is also based on a precedence.<br />
This is obviously a very simplistic example, but the first in the series so we progress with each new post.<br />
The sample package, completed in SSDT 2012, can be downloaded <a href="http://www.sqlsafety.com/SamplePackages/RowCount.zip">here</a>.<br />
<br />
One of the more interesting values that can be held within an objects is a full result set, which can then be treated similar to a collection. One thing that I want to point out is just because you can doesn’t mean you should. Quite often I will be asked how a result set can be contained in a variable in SSIS and when I ask why I am most often told that it would be used to do complex transformations on the results in the control flow. HOLD ON!! That is exactly what the data flow task is for!! In response to this I am most often told that the transformations are most easily done in a script task. Once again there is a script component in the data flow that can act as a source, transformation, and/or destination and such complex transformations should be kept in the data flow as a best practice as well as for performance considerations.<br />
If I haven’t scared you away already let me point out several other performance considerations. First keep the scope of the variable limited to only what is needed, task, container, and only if necessary the package. The memory required to hold an object will be dictated by the value(s) that are stored in the variable so extra attention is required to limit the exposure to only what is required. Boxing and Unboxing can also carry performance costs, MSDN documents boxing and unboxing <a href="http://msdn.microsoft.com/en-us/library/yz2be5wk.aspx">here</a>.<br />
Enough of the gloom and doom, let’s take a look at an object variable and how to work with it. To properly present the demonstration let’s present the requirements of the sample package. A result set from an execute SQL task needs to be captured within the object variable that will consist of two columns, FirstName and LastName. The result set will then be iterated through to execute complex logic, in this case just pass the values to a script task that will present the name in a message box(I know!! This is just a simple example though). This package will require three variables,<br />
1. Names Object<br />
2. FirstName String<br />
3. LastName String<br />
<img alt="objvariable" src="http://sqlsafety.com/wp-content/uploads/2014/09/objvariable-300x104.jpg" height="104" width="300" /><br />
With the variables created we can now begin to populate the Names variable with our execute SQL task. The task will need to set the <em>Result Set</em> property to full result set and the <em>Connection Type</em> will use an OLEDB connection to my localhost default instance connecting to the Adventureworks2012 database. The query is simplistic enough:<br />
SELECT FirstName, <br />LastName <br />FROM Person.Person<br />
We now need to define the <em>Result Set</em> settings in the task to specify that the results will be captured within our Names object variable. From within the <em>Result Set </em>tab you must specify the zero based index value of the results to be stored in the <em>Name</em> property, since our query only returns a single result set this will be set to 0, and then the <em>Variable Name</em> of the SSIS variable that will be used to hold the result set, in this case Names.<br />
<img alt="resultset" src="http://sqlsafety.com/wp-content/uploads/2014/09/resultset-300x254.jpg" height="254" width="300" /><br />
Our package will now populate the Names variable with the results of our query and we now have several methods that we can work with this variable.<br />
<h3>
ForEach Container</h3>
The first way to work with an object is to use a For Each container and set the <em>Enumerator</em> to a <em>Foreach ADO Enumerator </em>and define the <em>ADO object source variable</em> to our Names variable:<br />
<img alt="foreach" src="http://sqlsafety.com/wp-content/uploads/2014/09/foreach-300x254.jpg" height="254" width="300" /><br />
Now keep in mind that the Names is an object, very much like an array or collection, so we need to take both columns, FirstName and LastName, and place them in their own variables. This is done on the <em>Variable Mapping</em> page by again mapping the zero based index value of the ordinal position of the columns to the appropriate variables:<br />
<img alt="varmap" src="http://sqlsafety.com/wp-content/uploads/2014/09/varmap-300x254.jpg" height="254" width="300" /><br />
<em>**I intentionally placed the LastName and FirstName out of order to demonstrate how the variable mapping is done based on the zero based index and not by the order in which the variables are mapped to the columns</em><br />
Within the ForEach container place a script task that has <em>ReadWriteVariables</em> or <em>Read Only Variables</em> set to both the FirstName and LastName:<br />
<img alt="script" src="http://sqlsafety.com/wp-content/uploads/2014/09/script-300x254.jpg" height="254" width="300" /><br />
You may ask why access is not given to the Names variable. The answer is that the ForEach container accesses the Names object variable and iterates though each row placing the FirstName column in the FirstName variable and LastName column in the LastName variable and passes those variable, one at a time, to the script task so access is not needed to the object.<br />
The C# script task simply calls the MessageBox Show method to display the names one at a time:<br />
MessageBox.Show(“The name is: ” + Dts.Variables[0].Value.ToString() +” ” + Dts.Variables[1].Value.ToString());<br />
<h3>
Script Task</h3>
Another way to work with the variable is directly within a script task. Since the script task will now be iterating through the Names object variable directly the variable needs to be assigned to the script tasks <em>ReadWriteVariables</em> or <em>ReadOnlyVariables:</em><br />
<img alt="scriptnammes" src="http://sqlsafety.com/wp-content/uploads/2014/09/scriptnammes-300x254.jpg" height="254" width="300" /><br />
The C# script task first creates an OleDbAdapter and a data table and calls the OleDbAdapters Fill method to populate a data table with the Names object variable. Once the data table is populated a foreach loop is used to iterate through the results and display the results using the MessageBox Show method.<br />
//Populate a data table with the Names Variable <br />OleDbDataAdapter A = new OleDbDataAdapter(); <br />System.Data.DataTable dt = new System.Data.DataTable(); <br />A.Fill(dt, Dts.Variables["User::Names"].Value);<br />
// Iterate through the data table <br />foreach (DataRow row in dt.Rows) <br />{ <br />string FirstName; <br />string LastName; <br />object[] array = row.ItemArray; <br />FirstName = array[0].ToString(); <br />LastName = array[1].ToString(); <br />MessageBox.Show(“FirstName=” + FirstName + ” AND LastName=” + LastName);<br />
The sample package outlined can be downloaded <a href="http://www.sqlsafety.com/SamplePackages/ObjectResultSet.zip">here</a>.<br />
<br />
<h3>
Default Value Assignment</h3>
Once a variable is declared, scoped, and a data type defined a default value can be assigned within the <em>Value </em>field. This is the value that the variable will take once it falls in scope. This will remain the variable value until a task changes that value:<br />
<img alt="variablevalue" src="http://sqlsafety.com/wp-content/uploads/2014/09/variablevalue-300x98.jpg" height="98" width="300" /><br />
<h3>
Expression</h3>
An SSIS expression can be used to assign a value to a variable which, like the <em>Value</em> property, is the value that will be assigned once the variable falls into scope. In SSIS 2005-2008R2 the variable expression value had a limitation to 4,000 characters. This could be overcome by using an expression to concatenate several together. I outlined this technique <a href="http://www.sqlsafety.com/?cat=7">here</a>.<br />
<img alt="varexpress" src="http://sqlsafety.com/wp-content/uploads/2014/09/varexpress-300x228.jpg" height="228" width="300" /><br />
<h3>
Expression Task</h3>
An <a href="http://msdn.microsoft.com/en-us/library/hh213137.aspx">expression task</a>, first introduced in SSIS 2012, provides the ability to use an SSIS expression to assign a value to a variable once the task is execute:<br />
<img alt="Expressiontask" src="http://sqlsafety.com/wp-content/uploads/2014/09/Expressiontask-300x295.jpg" height="295" width="300" /><br />
<h3>
Script Task</h3>
A script task provides two separate means for variable value assignment.<br />
<h4>
ReadOnly/ReadWrite</h4>
The first, and probably the easiest, is to list the variables for ReadOnly or ReadWrite access within the components configuration pane:<br />
<img alt="Scriptassign" src="http://sqlsafety.com/wp-content/uploads/2014/09/Scriptassign-300x254.jpg" height="254" width="300" /><br />
With the variable(s) being entered within the component code is written that we don’t see that handles the locking and unlocking of the variable(s) for both read and write. The system generated code allows us to interact directly with the variables without having to first lock the variables. For example the below VB.NET code will use the MessageBox Show method to display the value of the variable “<em>Today</em>” that was enabled for ReadWrite access, then change the value, and once again display the new value in a message box:<br />
MessageBox.Show(Dts.Variables(0).Value.ToString) <br />Dts.Variables(0).Value = Now.AddDays(-1) <br />MessageBox.Show(“The new date is ” + Dts.Variables(0).Value.ToString) <br />This provide quick and easy access to the variable for read and write, but does limit when the variable is locked and unlocked based on the system generated code. For more granular control over variable locking you can utilize the <a href="http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx">VariableDispenser </a>within your code.<br />
<h4>
VariableDispenser</h4>
Utilizing the VariableDispenser method does require more code, but again provides complete control over the locking and unlocking of variables. <strong>DO NOT</strong> include the variable within the script configuration ReadOnly or ReadWrite property when using this method or an error will arise when you attempt to programmatically lock or unlock the variables since the components system generated code handles this. The below VB.NET code demonstrates using the VariableDispenser to work with variable directly within a script task:<br />
Public Sub Main() <br />Dim vars As Variables = Nothing <br />Dim myVar As Variable <br />Dts.VariableDispenser.LockForRead(“User::Today”) <br />Dts.VariableDispenser.GetVariables(vars)<br />
For Each myVar In vars <br />MessageBox.Show(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name) <br />Next<br />
Dts.VariableDispenser.LockForWrite(“User::Today”)<br />
For Each myVar In vars <br />myVar.Value = Now <br />Next<br />
For Each myVar In vars <br />MessageBox.Show(“Variable value ” + myVar.Value.ToString + ” and name is ” + myVar.Name) <br />Next <br />vars.Unlock()<br />
Dts.TaskResult = ScriptResults.Success <br />End Sub<br />
The above code uses a for each loop to iterate through all locked variables, which in this case is only the <em>User::Today</em> variable. To access the variable properties and methods directly the vars(<em>i</em>) can be called, where “<em>i</em>” is the zero based indexed value of the variable. For example, to re-assign the today variables value the following code could be used to assign the current<br />
vars(0).Value = Now<br />
<h3>
Execute SQL Task</h3>
The execute SQL task can also be used to assign a variable a value in two different ways.<br />
<h4>
Output Parameter</h4>
Variable assignment can be done by using a query that assigns the result(s) to a placeholder of a “<em>?</em>“. For example the below query would take the result of SELECT GETDATE() and assign it to the output parameter that is being held with the “<em>?</em>”<br />
SELECT ? = GETDATE()<br />
<img alt="sqloutput" src="http://sqlsafety.com/wp-content/uploads/2014/09/sqloutput-300x159.jpg" height="159" width="300" /><br />
Using the query with the placeholder alone does not complete the assignment and still requires the mapping of the variable to the output parameter which is done on the <em>Parameter Mapping </em>page. On the parameter mapping you select the variable, in this case User::Today, the direction, which is output, and the ParameterName, which will be the zero based index of the value, in this case 0. The question often comes up as to whether a named output parameter can be used rather than the indexed value and the truth is that it depends upon the connection manager. This <a href="http://www.sqlsafety.com/?p=649">post </a>outlines the different configuration methods available for output parameters.<br />
<img alt="outputparam" src="http://sqlsafety.com/wp-content/uploads/2014/09/outputparam-300x254.jpg" height="254" width="300" /><br />
<h4>
Result Set</h4>
Another way to use an execute SQL task to assign a value to a variable is by using a result set. This is very similar to an output parameter and only differs in how the query is written, there is no “<em>?</em>” place holder or parameter name, but rather just the result set type and result set mapping. On the execute SQL configuration page result set type must be defined as <em>None, Single row, Full result set, or XML</em>, the type is completely dependant upon the type of result(s) that will be provided from the query:<br />
<img alt="resultsetpane" src="http://sqlsafety.com/wp-content/uploads/2014/09/resultsetpane-300x254.jpg" height="254" width="300" /><br />
In the <em>Result Set</em> pane you again map the zero based index results to the return value(s) of the query. In this case there is only one query that will return a result so the indexed value is zero.<br />
<img alt="resultsets" src="http://sqlsafety.com/wp-content/uploads/2014/09/resultsets-300x254.jpg" height="254" width="300" /><br />
One benefit that you may immediately notice is that if you have multiple variables requiring assignment, such as the current date and the last date a process ran, you can do this within one single execute SLQ task using output parameters, while it would require 2 execute SQL tasks each with a single row result set. There is the possibility to utilize one execute SQL task using a result set of a full result set or possibly XML this would be overkill in such a situation and more easily accomplished with output parameters.<br />
<h3>
Data Flow Task</h3>
The data flow task can be used for variable assignment, for example using the Row Count transformation to assign the number of rows that pass between two data flow components, or a complete result set using a Recordset destination. To go back to my previous <a href="http://www.sqlsafety.com/?p=980">post </a>that covered the object variable data type I used an execute SQL task to populate the variable. Rather than an execute SQL task I can use a data flow task that gets the FirstName and LastName columns from the Adventureworks2012.Person.Person table and sends the result set to a Recordset destination mapping my object variable, in this case the User::Names variable:<br />
<img alt="recordset" src="http://sqlsafety.com/wp-content/uploads/2014/09/recordset-300x219.jpg" height="219" width="300" /><br />
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-81726549567544143962014-10-15T08:30:00.001-07:002014-10-15T08:30:00.476-07:00Temp Tables Scope in Dynamic SQL<h3>I was recently came across a question in one of the user forums where  a stored procedure that required using both temporary tables and dynamic SQL.  Seems like user wasn’t aware of the fact that the dynamic SQL creates a new scope making the temporary unavailable to the parent scope.  </h3> <p>To demonstrate this consider the following statement:</p> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:7326ebc7-74a0-44ea-8abf-26624a08761f" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 338px; height: 269px;background-color:White;overflow: auto;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">USE</span><span style="color: #000000;"> AdventureWorks2012;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /><br /></span><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"> </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">400</span><span style="color: #000000;">)<br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> </span><span style="color: #FF0000;">'</span><span style="color: #FF0000;">SELECT FirstName, <br /> MiddleName, <br /> LastName<br /> INTO #temp<br /> FROM Person.Person <br /> SELECT *<br /> FROM #temp</span><span style="color: #FF0000;">'</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">EXEC</span><span style="color: #000000;"> sp_executesql </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"><br /><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> #</span><span style="color: #0000FF;">temp</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /></span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /><br /><p> </p><br /><br /><p>Executing this statement results in 19,972 rows being inserted into the #temp temporary table and then returning those 19,972 rows from the select statement embedded within the dynamic SQL statement, however an error message is returned from select query outside of the dynamic SQL:</p><br /><br /><p><font color="#ff0000">msg 208, level 16, state 0, line 12<br /> <br />invalid object name ‘#temp’.</font></p><br /><br /><p>The reason for this is the fact that the scope of the temporary table is within the dynamic SQL and not the outer parent query.  Fortunately the fix is quite simple, rather than using SELECT INTO to dynamically create the temporary table explicitly create the #temp table using DDL outside of the dynamic SQL and then use an INSERT statement to populate the table within the dynamic SQL.  The below query demonstrates how explicitly creating the table and using dynamic SQL to populate it insures that the table remains in scope.</p><br /><br /><p> </p><br /><br /><p><br /> <div id="scid:9D7513F9-C04C-4721-824A-2B34F0212519:71c4d60f-7d7e-433f-837b-17a4568717dd" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=" width: 394px; height: 306px;background-color:White;overflow: visible;"><div><!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="color: #0000FF;">USE</span><span style="color: #000000;"> AdventureWorks2012;<br /></span><span style="color: #0000FF;">GO</span><span style="color: #000000;"><br /><br /></span><span style="color: #0000FF;">CREATE</span><span style="color: #000000;"> </span><span style="color: #0000FF;">TABLE</span><span style="color: #000000;"> #</span><span style="color: #0000FF;">temp</span><span style="color: #000000;">(<br />FirstName </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">),<br />MiddleName </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">),<br />LastName </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">50</span><span style="color: #000000;">))<br /><br /></span><span style="color: #0000FF;">DECLARE</span><span style="color: #000000;"> </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"> </span><span style="color: #0000FF;">NVARCHAR</span><span style="color: #000000;">(</span><span style="color: #800000; font-weight: bold;">400</span><span style="color: #000000;">)<br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"> </span><span style="color: #808080;">=</span><span style="color: #000000;"> ‘</span><span style="color: #0000FF;">INSERT</span><span style="color: #000000;"> #</span><span style="color: #0000FF;">temp</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> FirstName, <br /> MiddleName, <br /> LastName<br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> Person.Person <br /> </span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /> </span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> #</span><span style="color: #0000FF;">temp</span><span style="color: #000000;">’<br /></span><span style="color: #0000FF;">EXEC</span><span style="color: #000000;"> sp_executesql </span><span style="color: #008000;">@cmd</span><span style="color: #000000;"><br /><br /></span><span style="color: #0000FF;">SELECT</span><span style="color: #000000;"> </span><span style="color: #808080;">*</span><span style="color: #000000;"><br /></span><span style="color: #0000FF;">FROM</span><span style="color: #000000;"> #</span><span style="color: #0000FF;">temp</span><span style="color: #000000;">;<br /></span><span style="color: #0000FF;">GO</span></div></pre><!-- Code inserted with Steve Dunn's Windows Live Writer Code Formatter Plugin. http://dunnhq.com --></div><br /></p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p> </p><br /><br /><p>Another option is to use global temporary tables, but unfortunately deploying this as a stored procedure would cause the error “<em>Object already exists</em>” if two users were to execute the procedure at the same time.</p><br /><br /><p></p><br /><br /><p>One common reason that dynamic SQL is needed in the first place is that the columns of the temp table are variable. So while the solution above (moving the CREATE TABLE statement outside of the dynamic SQL) is the preferred solution, it sometimes is not possible. </p><br /><br /><p>A couple other workarounds in this case:</p><br /><br /><p>1) Use a global temp table (as mentioned above), with a GUID embedded in the table name to avoid the concurrency issue highlighted in this post. </p><br /><br /><p>2) Push ALL the work needed to be done on the temporary table into the single block of dynamic SQL, so that losing access to the variable after the dynamic batch is completed isn't an issue.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-74070716345095743442014-05-29T11:51:00.001-07:002014-05-29T11:51:46.549-07:00How to uninstall SQL Service Reporting Services<p>From SQL Server Reporting Services(SSRS) 2008, the SSRS is Internet Information Server(IIS) independence. This means there is no directory for Reporting Services under IIS.</p> <p>In this case, please make sure the SSRS service is running, otherwise, we are not able to configure the Web Service URL and the Report Manager URL(means configuring the virtual directories). </p> <p>Anyway, in order to uninstall SQL Server Reporting Services without uninstalling other SQL Server components, please follow this guide:</p> <ol> <li>In Add/Remove programs </li> <li>Click the 'Uninstall/Change' button for "SQL Server 2008 R2" </li> <li>In the coming dialog, click 'Remove' </li> <li>Click 'OK' </li> <li>In the 'Select Instance' dialog,  select the appropriate instance, and then click 'Next' </li> <li>In the 'Select Features' dialog, please only select 'Reporting Services' </li> <li>Then click 'Next' and 'Next' till the uninstalling process starting </li> </ol> <p>If you have any more questions, please feel free to ask.</p> Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7767504616251055789.post-4587391587915148092014-05-28T09:55:00.001-07:002015-03-24T13:40:14.787-07:00SQL Maintenance Plan error “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”<p>Some body have this error : <em>Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.</em> When i execute my maintenance plan.</p> <p> </p> <p><strong>Resolution </strong></p> <p>The issue is related to the default email profile, you have to set a default email profile in order for the maintenance plan send the email alert. </p> <p> </p> <p>Please Follow These steps to Resolve this Problem</p> <p>1. Go to Database mail</p> <p>2. Right Click  Click on Configure Database mail.</p> <p>3. wizard will click next</p> <p>4.select Manage Profile security</p> <p>5. Click Next</p> <p>6. Beside Profile name  by default there will be no , Please make it yes</p> <p>7. Finish</p> Unknownnoreply@blogger.com0