Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Look up at the sky. Remember we are all part of something bigger

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Do You Think About CheckDB?

Many of you are administrators, maybe accidental ones, but you have some responsibility for ensuring your databases run smoothly. Part of ensuring this happens is monitoring resource usage, configuring security, and patching your system. However, the long term health of your database requires some proactive work to ensure things continue to work well. Part of this using checkdb to assess the health of your database's internal structure.

Those of us that are full time focused DBAs likely run checkdb, though potentially not in the best way. There are implications to "offloading" this work to another machine. Those that are accidental DBAs, developers, or someone else with other duties might not think about checkdb as necessary.

Do you think about how you should run checkdb? Do you think about licensing issues? Do you think about the frequency and breadth of where you should run checkdb? I'm wondering after ran across a post from Brent Ozar on offloading this work. If you haven't dug into how checkdb works, you might not realize some of the things that Brent brings up. Even if you think you know how this works, read the post.

I've usually been able to run checkdb in production, but I recognize that more and more environments can't do that. Usually because of the resource contention. When I've had that issue, I've accepted a time lag to getting results. I don't worry about finding out about database corruption a day later. I can probably deal with that. I worry about finding out a month later, when reconstructing data is much, much harder.

This isn't to imply or recommend that you need to run checkdb on each production instance, but rather to get you to think about how checkdb works and choose a strategy that works well for your environment. Even if you have this set up, ensure your configuration still makes sense for your organization. Take a few minutes and read the post and then schedule a review with colleagues of your checkdb philosophy.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
  Featured Contents

How to Download Stocks on Schedule Using R

s.dumnov from SQLServerCentral

Learn how you can use R to download stock data from Yahoo and schedule a script to run as often as you would like.

Help Redgate to build the next generation of Database DevOps solutions

Additional Articles from Redgate

Help Redgate to build the next generation of Database DevOps solutions so that we can help you to deliver the next generation of your software. To get involved please complete our short 2-page survey on current development practices.

How I Use Streamlabs OBS for Streaming and Training Classes

Additional Articles from Brent Ozar Unlimited Blog

I’ve been working on my streaming setup for months now, and I’m just trying to save you some time if you really wanna take the streaming thing seriously.

From the SQL Server Central Blogs - What are you tuning for?

kleegeek from Technobabble by Klee from @kleegeek

People want to make things faster. It’s in our nature as IT professionals. What are you tuning for? I’ve been asking that a lot later. I have to sit...

From the SQL Server Central Blogs - Quickly Blocking Inbound Connections to SQL Server

John Morehouse from John Morehouse | Sqlrus.com

Sometimes the brilliance of a solution is lost in its simplicity.  Full disclaimer, this was Joey D ’Antoni’s (B|T) idea, however, he has given me permission to blog about...

 

  Question of the Day

Today's question (by khwabekhan):

 

Minus Query

I have the below data set: Employee Table-
INSERT INTO EMPLOYEE (PERSONID,FIRSTNAME) VALUES ('1','ANNE')
INSERT INTO EMPLOYEE (PERSONID,FIRSTNAME) VALUES ('2','DRIAAN')
Person Table-
INSERT INTO PERSON (PERSONID,FIRSTNAME) VALUES ('1','ANNE')
INSERT INTO PERSON (PERSONID,FIRSTNAME) VALUES ('2','GERRY')


I have written this query to fetch the firstname, which is/are present in the employee table but not in the person table and vice versa. Query:
(SELECT FIRSTNAME
 FROM EMPLOYEE
 ORDER BY FIRSTNAME
 EXCEPT
 SELECT FIRSTNAME
 FROM PERSON
 ORDER BY FIRSTNAME)
UNION
(SELECT FIRSTNAME
 FROM PERSON
 ORDER BY FIRSTNAME
 EXCEPT
 SELECT FIRSTNAME
 FROM EMPLOYEE
 ORDER BY FIRSTNAME);
Which of the following is the correct output for the above query

Think you know the answer? Click here, and find out if you are right.

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Getting the Mean

I have this dataframe in Python:

sales.head()
	Date	Day	Month	Year	Customer_Age	Age_Group	Customer_Gender	Country	State	Product_Category	Sub_Category	Product	Order_Quantity	Unit_Cost	Unit_Price	Profit	Cost	Revenue
0	2013-11-26	26	November	2013	19	Youth (<25)	M	Canada	British Columbia	Accessories	Bike Racks	Hitch Rack - 4-Bike	8	45	120	590	360	950
1	2015-11-26	26	November	2015	19	Youth (<25)	M	Canada	British Columbia	Accessories	Bike Racks	Hitch Rack - 4-Bike	8	45	120	590	360	950
2	2014-03-23	23	March	2014	49	Adults (35-64)	M	Australia	New South Wales	Accessories	Bike Racks	Hitch Rack - 4-Bike	23	45	120	1366	1035	2401
3	2016-03-23	23	March	2016	49	Adults (35-64)	M	Australia	New South Wales	Accessories	Bike Racks	Hitch Rack - 4-Bike	20	45	120	1188	900	2088
4	2014-05-15	15	May	2014	47	Adults (35-64)	F	Australia	New South Wales	Accessories	Bike Racks	Hitch Rack - 4-Bike	4	45	120	238	180	418

Which of these will give me the mean of the profit column?

Answer: sales['Profit'].mean()

Explanation: As with other methods, we should use the Sales[] notation to specify a specific column. We can then call the mean() method from here. Ref: pandas.DataFrame.mean() - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.mean.html

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
Assignment Help - I was wondering if anyone would be kind enough to help me with a couple of queries in my assignment that I have not been able to solve. Consider the following schema for a simple social network. User(uid, first_name, last_name,home_city, bio) Friend(uid_1, uid_2) Message(uid, text, from_city) c)Find the uids of users who are friends with […]
SQL Server 2017 - Development
Inserting an incremental range of decimal numbers into a table - Hi can anyone help me with some SQL code to insert some decimal numbers into a table please? I have a table with an ID field, a lower field, an upper field and a flat rate. What I'm trying to do is a script to insert into the lower field values 0.00, 1.01, 2.01,3.01,4.01,5.01......up to […]
Administration - SQL Server 2014
Nutanix - 'misaligned log IOs which required falling back to synchronous IO’ - Hi I have restored a SQL database onto a nutanix VM, the disks are formatted to 64k. When restoring or running I get error Error: ‘misaligned log IOs which required falling back to synchronous IO’ The database was restored off an old Dell physical 710 server that had disks set to bytes per sector &physical […]
Query - Hi I have query select T0.DocType,T0.Debit , T1.Credit , T1.account from tbl1 where Mdate <= @frDate. In this i want to add condition if @frdate Month = 3 and Date = 31 then documents of Type say TC should not be considered Thanks
Development - SQL Server 2014
Secured connection to AD with linked server (LDAPS) - Hi, Our system team warn us they detected unsecured connections when one of our linked server try to connet to the AD. We managed to find the concerned request : SELECT * FROM OPENROWSET('ADSDSOObject', 'adsdatasource';'LDAP_LOGIN';'Password', ' SELECT SAMAccountName FROM ''LDAP://ServerName'' WHERE SAMAccountName = ''NameTest'' and objectClass = ''user'' ') After some research, we should use […]
Index fill factor - Hi Experts , We have around 3000 indexes in our database . We have introduced a index maintenance plan to rebuild the indexes . Now while rebuilding the indexes we have option to specify the FillFactor  . My question is that , Is there a way to identify ideal FillFactor for all the 3000 indexes […]
SQL 2012 - General
2012 SQL Agent: Reloading agent settings - My SQL Agent 2012 log file is being filled with messages that say "Reloading agent settings".  What does this mean and how do I stop it? Some jobs did not run last night and I don't know if this is just a coincidence or is a cause.
SQL Server 2019 - Administration
How do I reattach my databases? - Yesterday I went through the process of replacing an old, failing hard drive, out of my Windows 10 Pro machine. I replaced it with an SSD. It gives my old desktop another couple years of use, I hope. I had SQL Server Developer Edition on a second hard drive. Naturally the .MDF and .LDF […]
SQL Server 2019 - Development
Processing Build Item Orders -- do I need a cursor? - say I'm working on a database like AdventureWorks2017 - the standard SalesOrderHeader, SalesOrderDetail (ProductID, QtyOrdered).  Then Parts and PartInventory (PartID, LocationID, QtyOnHand)... and I want to know if I have all the "pieces" to build all the items in a SalesOrder. Writing a query to get the quantity of each Part that's required is trivial. […]
DB Design/Normalization Question - Hello, I've read the posts on this site and elsewhere about normalization -- I have a scenario in a db I'm currently working on where I'd like to get some advice. I am using an Azure SQL Back End/MS Access Front End.  About 35 end users. The primary function of the database is to keep […]
Get dates between previous month date and current month date - BI have a requirement to write a query to get all the records between 21st of previous month to 20th of next month. So for eg: if the current date is 18-May, I should get records from 21- Apr to 20-May. This will go on till 20th May. Now from 21st May to 20-June, I […]
Week Ending Thursday, can some one tweek my code? - What I have missed from this code? I'm trying to show the DateTimeRaised field as a week ending Sunday and week ending Thursday. The FkIssueGroupID isn't unique but each row should have the same date. The MAX is being used to bring back a single row. Whilst the week ending value for Thursday is correct […]
SSRS 2016
Sorting by is not giving the expected results - Hello, Im new to SSRS and I was trying to order by SUM(linetotal) in my chart but its not working as expected, even though the query looks fine when I execute it in SQL. im using the advantureworks database and below what I have so far and this is the results that im getting, the […]
COVID-19 Pandemic
Daily Coping 1 Jun 2020 - Today's tip is to tell someone about an event in your life that was really meaningful. http://voiceofthedba.com/2020/06/01/daily-coping-1-jun-2020/
Daily Coping 29 May 2020 - Today’s tip is today link your decisions and choices to your purpose in life. Daily Coping 29 May 2020
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -