Query below lists tables in a database without primary keys.
'select schema_name(tab.schema_id) as [schema_name],
tab.[name] as table_name
from sys.tables tab
left outer join sys.indexes pk
on tab.object_id = pk.object_id
and pk.is_primary_key = 1
where pk.object_id is null
order by schema_name(tab.schema_id),
tab.[name]'...
https://dataedo.com/kb/query/sql-server/find-tables-without-primary-keys
'In some scenarios we might find different SQL Server collations between the server instance and its databases. Sometimes the collation is fixed in some table columns or inside stored procedures to solve relationship problems with columns that have different collations. The collation differences may have been setup purposely, but in other cases they may have been a mistake. In this tip we will look at different ways to change the SQL Server collation for the instance and databases and things to be aware of when making these changes.'...
https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/
'Coming back after a long weekend – on a Monday morning, we found one of our services to be down in our Test environment.
Looking at the server event logs, we found the error message in the stack trace – ‘System.Data.SqlClient.SqlException: Login failed for user‘.
We tracked the issue back to a service account which got removed from the database. Once we added the account, the service was up and running.
Now the next obvious question was who/when/why made the change to the service account? Did someone manually make the change or was it caused by a deployment? Was the change intentional or just a mistake?
While troubleshooting issues, viewing the SQL Server logs is beneficial since it contains information about user-defined events and also few system events.
You will be able to find information like – when the SQL Server instance has stopped and restarted, Memory issues, Login Failures or any other potential issues. However I did not find any information relating to adding/deleting user accounts or user permission changes on a SQL Instance.
'...
https://samirbehara.com/2017/12/07/tracking-sql-server-database-permission-changes/
'SQL Server Performance Tuning is what I do for leaving and every single day, I get different problems to solve. Just another day I was hired for just 1 hour to solve one specific problem of my client with whom I had worked on Comprehensive Database Performance Health Check. This time I was able to help them with Query Specific Wait Statistics.'...
'I'm having an issue with trying to figure out how to delete files in a folder location older than a certain number of days in this case lets say 10. I have looked far and wide online and most of the "solutions" that I find are hard to understand and do not give a complete picture of how to do it, they give about 80% and leave the 20% that are needed to make the solution work.'...
https://www.sqlservercentral.com/forums/topic/deleting-files-older-than-n-days-using-ssis
'In SQL Server 2008 R2, the Reporting Services execution log table is called dbo.ExecutionLogStorage (whereas in 2000 and 2005, it was called ExecutionLog). This table logs an entry each time the Report Server interacts with a report after it's been deployed to the Report Server.
Execution Log Views
Since Microsoft recommends against querying the Report Server tables directly, it provides 3 standard views in the ReportServer database.
https://www.sqlchick.com/entries/2011/2/6/querying-the-report-server-execution-log.html
'It is easy to get this one wrong. SSISDB is just a database, after all, and I’ve seen a number of instances where it was restored to another server and a number of errors/issues crop up. After all, SSISDB is the backend database for the SSIS Catalog. Restoring this database to another server is similar to migrating all the SSIS projects you have deployed to Integration Services for the SQL Server Instance. So what is the proper way to restore SSISDB?
1. Back up the SSISDB from the source SQL Instance.
2. If you know the password for the Database Master Key for SSISDB you can skip this step. If you no longer have the password used for the master key when the SSISDB was created, then back up the master key now:
backup master key to file = ‘C:\MSSQL\SQL_masterkey’ –Replace with the location where you can save it.
encryption by password = ‘REPLACE WITH PASSWORD’ –replace with password
3. If you are restoring the backup to a SQL Server instance where the SSIS Catalog was never configured, you’ll have a number of extra steps to perform. It will be easier if you just enable SSIS catalog on the new server first. You can do that through SSMS. Navigate to the Integration Services Catalog and right click to “Create Catalog.”'...
https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/
'Part 1: Service Broker Basics
Microsoft first introduced Service Broker as part of the relational engine of SQL Server 2005. Service Broker is an asynchronous messaging framework with which you can implement scalable, distributed, high available, reliable, and secure database applications based on SQL Server. Here are some examples:
With asynchronous database triggers you are able to defer long running tasks inside a trigger to a later time. This will improve the performance of your database applications.
With distributed message-based database applications you are able to decouple related tasks within your database applications and process these tasks independently from each other at different times. This will help you with the scale out of your database applications.
As we start looking at the details of Service Broker, I want to give you a quick overview of the Service Broker objects with which you will be interacting, when implementing a basic Service Broker application.'...
https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics
'In this article, we will review triggers in SQL Server, different types of trigger events, trigger order and NOT FOR REPLICATION in triggers. A trigger is a database object that runs automatically when an event occurs. There are three different types of events.
DML Events
DDL Events
LOGON Event – Logon trigger is fired when a LOGON event occurs i.e. when a user session is being established'...
'Have you ever wondered how much memory to assign your SQL Server instance?
Here’s a script to do it for you. If you want to understand how it works, read on!
This is based on an algorithm that Jonathan Kehayias wrote in his book, Troubleshooting SQL Server: A Guide for the Accidental DBA, and repeated in his blog post, How much memory does my SQL Server actually need?.
In broad strokes, you should give the operating system the following (quoted from the blog post):
1 GB of RAM for the OS
plus 1 GB for each 4 GB of RAM installed from 4 – 16 GB
plus 1 GB for every 8 GB RAM installed above 16 GB RAM
Whatever is left over, can go to your dedicated SQL Server instance. If you’re running anything else on the server, you will need to adjust the Max Server Memory downwards accordingly.
Keep in mind that some internal and peripheral devices may have additional memory requirements (see the blog post above).
Please feel free to use and share this link.'...
https://bornsql.ca/blog/proposed-sql-server-defaults-max-server-memory/