2023-07-20 07:41
Question: I'm getting this error when connecting Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
The certificate chain was issued… Read More
Blog Directory > Software Blogs > Programming Blogs > SQLSERVER-DBA.com programming Blog >
Sqlserver-dba.com Blog
sqlserver-dba.com
Tags:
sql server
server
subscription
command
driver xacircnbspfor sql
sql server lob
transaction log backup
script upgrade
backup
sql server columns
SQL Server consulting ,SQL Server support, SQL Server Performance Tuning and SQL Server Training
2023-07-10 10:09
Following changes in the feedburner service , sqlserver-dba.com is changing the rss feed and email notification subscription service to follow.it
Email notification subscribers … Read More
2023-06-23 15:39
Question: Is it possible to take a SQL Server database transaction log backup and full backup at the same time ? Are there any conflicts ?
Answer: Yes , taking a transaction log backup and a… Read More
2023-06-06 09:11
Question: I'm attempting to drop a drive on Windows 2016 but seeing space used . I've checked the drives but there are no files in the drive. I also checked "Hidden items" and no files appea… Read More
2023-05-26 10:54
Question: How can I find tables in a SQL Server database that have a Primary Key ?
Answer: To identify if a table has a Primary Key - you can use the OBJECTPROPERTY method with the pro… Read More
2023-05-17 08:11
Question: I want to find SQL Server table columns which are using user-defined types . I also want to find SQL Server columns in which the nulability at the column level is diffe… Read More
2023-05-03 07:17
Question: How can I find and report on SQL Server tables with LOB columns but where the table has no PRIMARY KEY? I'm classing an LOB column as 'TEXT', 'NTEXT','IMAGE' ,'XML', '… Read More
2023-04-18 06:51
During a routine SQL Server patching process on SQL Server some error messages appeared in the SQL Server Error Logs. As a result SQL Server was not able to start up
&nbs&hell…Read More
2023-04-06 08:52
Question: How can I find SQL Server Filtered Indexes ? We are woking on some migrations from SQL Server to other engine platform and part of the process includes loc… Read More
2023-03-27 07:47
Question: Do you have a SQL Script to locate SQL Server LOB columns? I need to find them as I'm organizing a SQL Server conversions and need to identify the SQL Server LOB column… Read More
2023-03-20 15:06
Question: Do you have a sql query to check every sql server database column and identify if a default value is applied to the column?
Answer : This query example wil… Read More
2023-02-20 11:48
Question: SQL Server won't start , so checked Event Viewer and getting the following message.
Server TCP provider failed to listen on [ 'any' 50010]. Tcp port is already in use.
How c… Read More
2023-02-07 09:00
Question: How to find all identity columns in SQL Server?
Answer:
USE myDB;
SELECT
SCHEMA_NAME(o.schema_id) AS [schema],
OBJECT_NAME(idc.object_id) AS [object],
o.type_desc,
i… Read More
2023-01-09 10:35
Question : What is the difference between Always On Clustered and Always On Clusterless ?
Answer: It is important to distinguish between these two flavours - Clustered vs Cluster… Read More
2022-12-15 11:28
Cross-database ownership chaining is a SQL Server security feature allowing database users access to other database objects hosted on the same SQL server instance, in the ca… Read More
2022-11-17 08:58
Question: I have a SQL Server Agent job in a disabled status. The disabled status is applied to the SQL Agent Job and the associated schedule. The problem is I'm logg… Read More
2022-10-19 12:02
Question: What does it mean on a SQL Script when the file was last modified? Here is an example of of a file Properties and I'm interested in the modified date .
A file’s… Read More
2022-10-13 10:40
Question: How can I check the current SQL Server Agent Job Status?
Answer: You have a few different options to check the SQL Server Agent Jobs Status.
Option 1 : SQL Server Job Activit… Read More
2022-10-05 08:56
Question: How do I enable SQL Server trace flags at startup ?
Answer: To add a trace flag as a SQL Server startup parameter, use SQL Server Configuration Manager. By addi… Read More
2022-10-04 12:46
Question: Do you have a query that returns the user permissions for the database objects ?
Answer: This query utilises the sys.database_principals view joined with the sys.database_per… Read More
2022-09-22 15:23
Question: How can I list all Foreign Keys referencing tables and columns within SQL Server tables?
Answer: This query will return 6 columns
FOREIGN_KEY = Name of Foreign Key
SCHEMA_NA… Read More
2022-09-05 09:43
Question: Does AWS RDS SQL Server support Dynamic Management Views ? I want to be able to return memory information , including total server memory and memory pressure
Answer: &n&hell…Read More
2022-09-02 07:48
SQL Server Horizontal Partitioning separates a table into multiple tables , all with the same number of columns, but with fewer rows. A typical example is where you might have da… Read More
2022-08-04 10:31
The SQL Server transaction log backup chain aka log chain is the series of sequential transaction log backups related to a database. The log backups are related to each other and are represe… Read More
2022-08-02 13:53
Question: We have a number of MS Access front ends using DSN connections through ODBC to a PostgreSQL database. All works fine. There is a now a new requirement to no store… Read More
2022-07-06 10:16
A common question from developers & data owners is what benefits does Always Encrypted offer over column level encryption aka cell level encryption? First thing… Read More
2022-07-05 13:15
Using a symetric key , how can I encrypt a column in a SQL Server database table? And what are some of the common pitfalls related to setting up column level encryption
In summary these are… Read More
2022-07-04 10:24
Question: I need some help with a Powershell function to generate some text output based on some imput parameters . Can you supply an example of a Powershell function or possibly inclu… Read More
2022-06-28 13:30
Question: In a recent meeting i heard the term MTLS , what does this mean and what is the difference between Transport Layer Security (TLS) versus Mutual Transport Layer (MTLS)?… Read More
2022-06-24 10:50
AWS re:Inforce 2019: The Fundamentals of AWS Cloud Security (FND209-R)
I watched an excellent Youtube hosted video related to AWS Cloud Security. Although it's from 2019 - the fundamental pr… Read More
2022-06-23 09:44
Our Privacy Policy was last updated on 23 June 2022.
This Privacy Policy describes Our policies and procedures on the collection, use and disclosure of Your information when You use the Serv… Read More
2022-06-21 15:43
TLS
SSL
Transparent Layer Security (TLS)
Secure Sockets Layer (SSL)
versions: SSL (1.0, 2.0, 3.0)
versions:TLS (1.0,1.1,1.2,1.3)
Vulnerabilities discovered in all versions
Vulnerabili… Read More
2022-06-20 16:02
Use the Powershell cmdlet New-SelfSignedCertificate
Warning: I only ever use this method for testing purposes - never use for Production environments. A typical test would be for SQL S… Read More
2022-06-09 16:07
Question: How do I set up the SQL Server Column Level Encryption ?
Answer: The setup for SQL Server Column Level Encryption is an easy process - but it does help to… Read More
2022-05-26 07:54
Question: I'd like to create a list of SQL Logins\users mapped to a database. Do you have a script which will create this list?
Answer: This code uses the undocumented stored procedure… Read More
2022-05-12 07:01
Question: I've read through these two posts - AWS-RDS SQL Server limitations & A simple guide to Hashicorp and SQL Server Secrets Engine . One of the statements… Read More
2022-05-09 10:24
Question: I normally use the Windows reg utility to get the Cluster File Share Witness sharepath information. This is an example command line on a server returning the File Share Witne… Read More
2022-05-03 15:02
Does AWS RDS SQL Server support built-in SQL Server Transparent Data Encryption (TDE)? Yes , is the short answer , but there are some limitations .
Before I dive into the A… Read More
2022-04-25 15:48
-The Hashicorp database secrets engine (database secrets engine) supports a plugin for SQL Server
-It supports root credential rotation, dynamic roles , static roles , username customi… Read More
2022-04-08 15:18
Question : When recovering a certificate created during a CREATE CERTIFICATE I get a "Warning: The certificate you created is expired" message , although the certificate is added to the serv… Read More
2022-03-30 16:00
Question: When the sqlcmd utility connects to a SQL Server what connection provider is used to interface with SQL Server ?
Answer: SQLCMD uses OLEDB as client inter… Read More
2022-03-15 12:31
How to create a trust relationship between on-premises domain and AWS Directory Service
Most SQL Servers use a large portion of the authentication as Windows Authentication - utilising Kerbe… Read More
2022-03-11 16:48
Best practices for running Microsoft Active Directory Services on AWS
To extend Active Directory Services from on-prem to AWS - that is the question
I watched a very informative video by Bor… Read More
2022-03-10 10:49
Microsoft documentation defines SQL Server blocking as " blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on… Read More
2022-03-09 15:30
How to migrate your SQL Server database to Amazon RDS
Main Points - These are summarized notes, for a full details I've added the video at the bottom of these notes
- RDS fully managed servi… Read More
2022-03-03 11:48
The requirement is to develop a workflow for migrating MS Access objects to SQL Server.
Reasons for migrating include:
-Access has a size limit of 2 GB
-Access has a concurrent users l… Read More
2022-02-25 13:12
Question: I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could… Read More
2022-02-23 17:00
Question: I am planning on TDE encrypting a bunch of large SQL Server databases. Due to potential IO contention issues during the work day , the plan is to encrypt these databases duri… Read More
2022-01-24 14:45
Instant File Initialization (IFI) enabled on SQL Server, for data files only, leads to faster execution on certain file operations, as it bypasses the zeroing p… Read More
2022-01-19 12:58
I find myself working on SQL Server 99.9% of the time - utilising a range of metrics , wait stats, and other methods of assessing performance on SQL Server databases. But how do you ap… Read More
2022-01-13 17:03
Question: How can I obtain a list of BUILTIN\Administrators members using Powershell ?
Answer: There is a Powershell cmdlet Get-LocalGroupMember. This cmdlet will give you a basic listing … Read More
2021-12-21 16:42
Question: How can I view all the character values based on the ASCII character number values?
I can do something like : SELECT CHAR(65) and return "A" - but I'd like to list out… Read More
2021-12-15 16:36
Question: Do you have a powershell script or t-sql script to find if ssl encryption is enabled on SQL Server? I don't want to check if connections are SSL enabled but… Read More
2021-12-14 17:30
Question: Using t-sql , How can check I Enforce a password policy is enabled on SQL Server ? I want to add this check for a regular SQL Server security configuration scan.
This i… Read More
2021-12-09 10:12
SQL Server Log truncation deletes inactive Virtual Log Files (VLF) from the SQL Server database transaction log . The Log truncation process frees space in the logical log for reuse by… Read More
2021-12-07 17:07
Question: How can I check the SQL Server authentication password configurations?
Answer: When you create a new SQL login - (SQL Server authentication) , there are some options … Read More
2021-11-29 11:01
A common SQL Server Security check is to identify if the guest id is enabled. By default , the SQL Server guest id is disabled , but for va… Read More
2021-11-25 16:15
This Powershell script will iterate through all available Domain Controllers associated with Active Directory . It will record the last logon time on the specific Domain Controllers.
T… Read More
2021-11-23 16:59
Question: I'm trying to capture a stored procedure output into a temporary table , which I can do using the INSERT INTO EXEC method. But I need to add an ext… Read More
2021-11-19 17:11
Microsoft Ignite Book of News - reveals a ton of developments - SQL Server 2022
Some topics for database professionals covered in the latest release
=>Azure Cosmos DB
=>Azure Da… Read More
2021-11-18 15:49
Question : I've SQL Server Management Studio (SSMS) installed and attempting to run it as a different user. I'm doing the Right Click on the ssms.exe - but can only see Run as Administ… Read More
2021-11-16 14:38
Recently read an article paper describing the process of developing a system to Automatically Indexing Millions of Databases in Microsoft Azure SQL Database.
Index Tuning is difficult , tuni… Read More
2021-11-10 16:53
I've registered for the Free PASS Data Community SUMMIT 2021 and there are some great sessions.
These are some of sessions I'm scheduled to attend
Accelerate SQL Server M… Read More
2021-11-09 11:23
Question: I'm getting some errors in a BULK INSERT step - and I need to get the SQL View definition : including the schema name , column_id , column name , data type , max length ,prec… Read More
2021-11-02 17:03
Question: Is there a Powershell method of passing data to the Windows Clipboard ? The aim is to extract data and then simply Ctl + V into an Excel spreadsheet .
Answer: It… Read More
2021-10-20 08:54
Benefits - Analyse the benefits of a SQL Server upgrade. Upgrades can be a significant effort , consideration should be given benefits and opportunity cost arising from dedicating resources… Read More
2021-10-07 10:14
Microsoft's recommended SQL Server upgrade workflow is to upgrade to the latest SQL Server but keep the source DB compatibility level, assess the workload impact via establishing a bas… Read More
2021-10-04 11:59
Question: I am logged onto my desktop with my primary Active Directory ID. I need to log onto a SQL Server with SQL Server Management Studio (SSMS) with another Active Directory… Read More
2021-09-23 15:56
Question : I have to capture logon information details for a specific logon on a SQL Server. Specifically - the client_hostname, nt_username & username. What i'm looking for… Read More
2021-09-15 09:48
Question: I want to create a Powershell script to add an Active Directory Group to the the local Administrator Group on a group of servers. The login privileges to execute the sc… Read More
2021-09-07 15:16
Question: How to view the current Powershell session security permissions? I'm trying to troubleshoot remote powershell commands and need to review the current powershell session… Read More
2021-08-31 15:10
Question : How can I find the Active Directory Group group scope - Local Domain, Global Group, Universal Group with Powershell?
Answer: Powershell has a range of cmdlets to… Read More
2021-08-26 14:08
Question: How can I use Powershell to return DNS information , specifically the fully qualified name of the server?
Answer: These are some examples you could incorporate into your Powershel… Read More
2021-08-03 15:53
Question: I have some t-sql code added to a job step on a SQL Server Agent job. The problem is I need to run the code as RUNAS . I though of executing the job with a proxy account - so progr… Read More
2021-07-29 10:29
Some AWS RDS SQL Server limitations
- Some ports are reserved for Amazon RDS, and you can't use them when you create a DB instance.
- Amazon RDS for SQL Server doesn't support importi… Read More
2021-07-26 16:13
In large scale migrations DBAs are asked to make decisions with far-reaching and with unforeseen consequences. Deciding which approach to take is influenced by a number of factors including… Read More
2021-07-14 15:37
Question: Got this message in the SQL Error Logs
Error: 3624, Severity: 20, State: 1.
A system assertion check has failed. Check the SQL Server error log for details. Typically, an ass… Read More
2021-07-12 15:46
Question: I'm attempting to create a backup of a SQL Server read only database , but getting an error message - BACKUP LOG cannot be performed because there is no current database backup
Why… Read More
2021-07-02 09:50
Question: On my laptop - SQL server SSMS crashed a couple of days ago and I lost the scripts I was working on. Is it possible to recover those scripts?
Answer:
For SSMS 18,the files ca… Read More
2021-07-01 09:07
If you want to troubleshoot a slow SQL Server Restore or just find out more about all the restore steps , use these techniques to give you an insight
Code to create the Event Session… Read More
2021-06-22 16:01
I've used the Ola Hallengren Maintenance Solution across various SQL Server environments . I was recently asked by a colleague about how adaptable they are to the AWS RDS SQL Server environm… Read More
2021-06-21 14:47
Question: How can I get the capacity & freespace on Windows drives including mount points using Powershell.
Answer: Win32_Volume class “represents an area of storage on a ha… Read More
2021-06-14 16:05
Question: How can I check for the SQL Server Analysis Service service status with Powershell? The status checks should be for remote servers
Answer: As long as you ha… Read More
2021-06-08 15:50
Question: How can I check for the SQL Server Reporting Service service status with Powershell? The status checks should be for remote servers
Answer: As long as you have networ… Read More
2021-06-04 10:24
Question: I want to get the count of values within a recordset . Currently when I get the recordset it's displayed
id color1 red2 red3 red4 blue5 blue6 blue7 black8 black
Whereas I'm l… Read More
2021-06-02 08:19
Question: I have a SQL Server Agent job which includes a BULK INSERT step , but is generating an error leaving step in a loading state.
BULK INSERT MY_REPORT
FROM 'E:\myreport.csv'… Read More
2021-06-01 10:03
Question: I'm completing some security reviews to develop a profile of the SQL Server environment. I want to assess the amount of encrypted connections active on SQL Servers & spec… Read More
2021-05-28 15:11
I was in conversation with an Application owners who said “I have a new product we need to install. The vendor documentation supports both co-located application and database server … Read More
2021-05-27 15:04
Question: I need to interrogate the module for a Powershell cmdlet but I can't find the actual physical location of the file on the host server. I can execute the cmd through the Power… Read More
2021-05-27 09:37
Use these sqlserver-dba.com banners for placement . Link to https://www.sqlserver-dba.com
Size : 350 x 60
Size: 350 x 300
Read more on Advertising options on sqlserver-dba.com Read More
2021-05-24 13:13
Question: I was attempting to create a constraint and getting this error
Msg 1752, Level 16, State 0, Line 5Column 'col1' in table 'MyTable' is invalid for creating a default constrain… Read More
2021-05-21 15:07
Question:There is a SORT_IN_TEMPDB option for SQL Server index create and rebuilds . Is it possible to exploit this option to improve the SQL Server index rebuild performance?
Answer: The sh… Read More
2021-05-19 14:31
I'm interested to know more about building a database system for DNA analysis. There is commentary below about the various situations in which your DNA can be stored in the UK. The UK h… Read More
2021-05-14 15:53
Question: I added a vendor based SSMS plug in , and when starting SSMS received the error message in a pop up window:
The 'S… Read More
2021-05-11 09:24
I received a question from a colleague asking how to register a Service Principal Name (SPN) for SQL Server. The specific situation relates to upgrades and new SQL Server instances deployed… Read More
2021-05-10 15:51
Question: I capture drive space details for capacity planning servers hosting SQL Server. The SQL Server policies on the organisations restrict the use of xp_cmdshell - which allows ac… Read More
2021-04-30 12:04
Questions: Could you give me a brief step through the process of the Service Principal Name (SPN) and the role as part of Kerberos based authentication to SQL Server?
Answer: A high level vi… Read More
2021-04-22 12:19
Question: I'm executing a Powershell Script . When I started the Powershell console , it was the "different user" option.
Within the powershell script - I want to check\confirm the curre… Read More
2021-04-20 10:40
Question: I have an SQL Server Instance - currently configured with a Dynamic Port. I'd like to change the setting from a Dynamic port configuration to a Static port configuration , us… Read More
2021-04-19 09:21
An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated… Read More
Subscribe to Sqlserver-dba.com
Get updates delivered right to your inbox!