SQL dbo.Chris

US Market recap: The Dow Jones Industrial Average's for the week before Christmas 2018 is a staggering 6.7 percent loss, putting it on track for its worst week since the financial crisis in 2008. The Nasdaq Composite Index fell into a bear market. The Federal Reserve's rate hike on Wednesday drove the losses this week and fears of an extended government shutdown only added to the pain on Friday The Nasdaq lost 8.5 percent on the week and is now 22 percent below its record reached in August 2018. The S&P 500 lost 7 percent for the week and on Thursday hit a 14-month low. It's now down 18 percent from its record. The Dow also lost 7 percent and more than 1,600 points on the week. The Dow and S&P 500, which are both in corrections, are on track for their worst December performance since the Great Depression in 1931, down more than 12 percent each this month. The Dow is on track for its worst month since October 2008. Both the Dow and the S...

I am going to show around 90% of the screens once you have downloaded an update and will be using SQLServer2012-KB4091266-x64.exe which happens be the most recent at the time of this blog post On-demand hotfix update package for SQL Server 2012 SP4. You may have your favorite patching site that you prefer for SQL update, but here is an unofficial build chart lists site that has known Service Packs (SP), Cumulative Updates (CU), patches, hotfixes and other builds of MS SQL Server 2019, 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Microsoft SQL Server Version List  https://sqlserverbuilds.blogspot.com/

Say you wanted to grow the transaction log of a database from a few hundred megabytes to 100GB or even 500GB on 2 striped 1.73TB NVME drives in a 4 disk pool using Storage Spaces direct.  First this is something you would NOT normally do, but if you had some some time on a new fast server these are the sort of fun crazy things you do in your spare time.  Simple math shows that the Local NVME storage was 2.2 Times faster than the SSD SAN based storage connected over 40Gb FCoE. In this example: I grew the log file 500GB on an I3 AWS Local NVME instance that took 8 Minutes and 32 Seconds In this example: I grew the log file 100GB on a Dell Compellent SC8000 with an SSD backed lun that has around 15k Write and 30K Read IOPS taking around 3 minutes and 55 Seconds

Here is a quick script to find ALL databases on a server with size and file locations and a little more.  If multiple file groups exist it will put them in a pipe delimited format in the datafile or log file locations   Example: Query Information From All Databases On A SQL Server Instance -- Get database information, file locations and sizes -- xp_fixeddrives may need to be commented out in some environments -- comment out lines as needed  IF   OBJECT_ID ( 'tempdb..#DriveInfo' )  IS   NOT   NULL   DROP   TABLE  #DriveInfo CREATE   TABLE  #DriveInfo(Drive  CHAR (1),MBFree  INT ) INSERT   INTO  #DriveInfo        EXEC   master ..xp_fixeddrives ------------------------------------------------------------- -- Drop #Tmp_tblDatabaseInfo table if it exists IF   OBJECT_ID ( 'tempdb..#Tmp_tblDatabaseInfo' )  IS   NOT   NULL   DROP ...

How to run performance counters on Windows Server 2008 R2 Today I needed to setup a configuration to mirror what someone had sent me so that I can estimate a baseline of disk performance to calculate disk IOPs for a SQL server move to a different datacenter.  This will only be a simple setup to get at the numbers I need so I can start the compare.  If I have more time I will show values and calculations in another post. 1. Open Performance Monitor by running PERFMON in the “Run…” textbox or navigating to Icon under all programs 2. Under “Reliability and Performance”, expand “Data Collector Sets”. 3. Right-click on “User Defined” 4. Select “New” from the popup menu then select “Data Collector Set”. 5. Assign a name for the new log settings. Eg.: “ServerName_Perfmon.” 6. Under the “Create data logs” section, check the checkbox for “Performance counter”. 7. Click the “Add…” button to start adding counters for each “Performance object” you wish to add counters from. Add ...

I had some time at AWS re:invent to play with these I3 instances and a few others which are designed for I/O intensive workloads and are equipped with super fast NVMe SSD storage.  These instances can deliver lots of fast read and write IOPS making them a great fit for any workload that requires high throughput and low latency including relational databases for Microsoft SQL and Oracle databases server. When compared to the I2 instances, I3 instances deliver storage that is less expensive and more dense, with the ability to deliver substantially more IOPS and more network bandwidth per CPU core.  Additional, if you take advanatage of the  I will show some of the Read and Write benchmarks using the  Diskspd Utility : A Robust Storage Testing Tool (superseding SQLIO) with DiskSpd Batch that was created by HeraFlux Technologies which can be found HERE    as well as the quick button push of CrystalDiskMark.  So if you want to see all the numbers ...

Msg 15138, Level 16, State 1, Line 5 The database principal owns a schema in the database, and cannot be dropped. Things constantly change and one such thing is the addition and removal of users and servers.  In an attempt to remove a Machine account SQL Server login that was being replaced we know that removing a user from an Instance is not the same as removing them from a databases.  The Instance removal was simple however I came across this error message at the database level "The database principal owns a schema in the database, and cannot be dropped. (Msg 15138, Level 16, State 1, Line 5)".   Here are some steps to successfully review and drop the SQL Server user database logins: 1. What schema(s) does the user own? The following example finds the user and each schema owner. SELECT s . name , USER_NAME ( s . principal_id ) AS Schema_Owner FROM sys . schemas s -- WHERE s.principal_id = USER_ID('You user Name') ; B. Now re...

All software has bugs so patch your SQL Servers regularly and by that I mean every 1 or 3 or 6 or 12 or even 18 months.  Older versions of SQL like 2012 may not have frequent updates while current versions seem to have monthly updates. If you take 5 minutes to hop on over to any unofficial build chart list of the known Service Packs (SP) and (CUs) look through the hotfixes in each cumulative update starting with the newest and work your way down for the version of SQL server you are on.  Simply find something or a situation you may or may not have had but don't want to and you now have a reason to patch!  Today was a fun day in the aspect that OS patching of SQL servers caused multiple SQL FCI to go down at 8am so what happened. 1. We are running a two node SQL 2016 with Server 2016 running Storage Spaces Direct (S2D) each with it's own enclosure. 2. They all have a Cloud Witness. 3. They were all last patched in June with May updates and have been in a pat...

What is Azure Data Studio? Azure Data Studio is a cross-platform database tool for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows , MacOS , and Linux . Previously released under the preview name SQL Operations Studio, Azure Data Studio offers a modern editor experience with Intellisense, code snippets, source control integration, and an integrated terminal. It is engineered with the data platform user in mind, with built in charting of query result sets and customizable dashboards. Who should use it? Azure Data Studio is a pretty awesome tool for Data Engineers and Data Developers to manage on-prem and cloud data sources.  It additionally allows for collaboration among cross platform teams.  For DBAs that transition to 100 DevOps and don't run day to day DBA management and configuration setup tasks this tool could be especially helpful to run snippets quickly.  For a production DBA I personally don't ...

SQLSaturday is a day-long free training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance is free, all costs are covered by donations and sponsorships excluding lunch and a few other things. This Saturday October 27th was  SQLSaturday #767 hosted at Mahoney State Park between Lincoln and Omaha, Nebraska.  This even is a  great example of information sharing and a SQL server community that is simply awesome!!! Some presentation items can be reviewed and downloaded at  https://www.sqlsaturday.com/767/EventHome.aspx  Other sessions that you missed may still be reviewed if you look around on the presenters website, google or youtube.  Some of those examples are listed below. Monster Text Manipulation: Regular Expressions for the DBA by Sean McCown  - Video I'm It Survival Techniques of the Lone DBA by Monica Rathbun - Video on YouTube skip to around minute 7:00

If finally happened today that once retail giant Sears Holdings Corp., parent of the Sears and Kmart retail chains, files for bankruptcy protection and will close another 142 stores immediately. I am sad to see things move in the direction that it did and while they may not be the product and brands it once was, they have created many memories from its annual Christmas Wish Book catalog and Craftsman tool line that I will never forget.  I remember grabbing that catalog and circling everything I wanted for Christmas many years when I believed in Santa Clause.  I remember visiting the "Sears Tower" in Chicago when I was 10 years old and thinking that I could actually look over Lake Michigan and see the world curve.  The thing that I will remember the most is the times it allowed me to spend with my father: " Working on projects around the house.  Working on cars.  Buying tools and returning broken tools.  "  Eddie Lampert the current CEO will step do...

SQL Server GPT Disk Partition Alignment Best Practices 'The performance question here is not did we pick the correct allocation (unit size of the NTFS default of 4,096 bytes or has it been explicitly defined at 64 KB, which is a best practice for SQL Server), but do we have our disk partition alignment correct with every number ending in a whole complete integer .' Partition_StartingOffset ÷ Allocation_Unit_Size  -- should be a whole complete integer Example: Let's  look at Computer Management > Storage > Disk Management Here we will look at GPT Disk 3 which is 4.5TB presented from a SAN and labeled as the "V" drive Now we will open command prompt as an Administrator and run the following " fsutil fsinfo ntfsinfo v:  "  were we update the drive letter as needed Here we look at the Allocation Unit or  "Bytes per Cluster" and see we have 64KB or (65536 bytes) Finally we will use the following as it provides more detailed r...

You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....

Today October 4rd 2018 at around 1:02pm central time VSTS/Azure DevOps services were disrupted again in the South Central Region for the second day in a row.  Some services started to come back online within 45 minutes.  Consistency is key! These are helpful links when looking for downtime: Service Blog – Azure DevOps https://blogs.msdn.microsoft.com/vsoservice/ Azure DevOps home page https://azure.microsoft.com/en-us/support/devops/?nav=min Twitter @AzureDevOps Verified account https://twitter.com/AzureDevOps Azure Services status dashboard https://azure.microsoft.com/en-us/status/