How to fix Msg 7390 - The requested operation could be performed

Question: I have a SQL Server Linked Server configured , pointing to an ODBC - accessing a MongoDB driver . The test connections all work OK - and no problems running an OPENQUERY select statement using the Linked Server. But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table - I get an error message: declare @myStmt varchar(max) set @myStmt = 'select _id from mytable' CREATE TABLE #temp2 (_id varchar(50) ) INSERT INTO #temp2 EXECUTE (@myStmt) AT my_linked_server DROP TABLE #temp2 Msg 7390, Level 16, State 2, Line 6 The... Read more →

How to find physical path for SQL Server backup on Container

Question: I'm migrating some SQL Server databases to Openshift Containers. The SQL Server is set up with persistent disk , with a dedicated persistent disk partition for the SQL Server defaultbackup directory. I don't have access to the underlying files via command line and can only use command line. How can I get the physical disk device , which will then allow me to create a RESTORE DATABASE statement pointing to the device? Answer: Assuming you've taken the BACKUP DATABASE with at at least one backup device , such as this example , which backups to the default backup directory... Read more →

How to fix Msg 7347 - linked server returned data that does not match expected data length for column

Question: I've found an issue we have with BI Connector over MongoDB. I'm extracting data from MongoDB to SQL Server - using the MongoDB ODBC Data Source Configuration and Linked Server. Basically the BI Connector fails when the string in the column gets too long. When running the job to bring in the data to the production workflow data into a Datawarehouse application we get the following error. Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' for linked server 'MongoDB_PROD_mydb' returned data that does not match expected data length for column '[MSDASQL].mywork.mytasks.mytaskInfo'. The (maximum) expected data... Read more →

How to check SQL Server Filegroup file sizes

I had a question from a fellow DBA about how to list every file per SQL Server filegroup , and report on the size in MB. This will give them a quick way of checking which file could be creating a capacity issue. Quite often , alerting is set up to check the whole drive , but won't check every file. So some granular report can identify the file causing a capacity issue --Return file , size in MB , filegroup SELECT sdf.name AS [File_Name], sdf.size/128 AS [Size_MB], fg.name AS [FileGroup_Name], sdf.physical_name as [File_physical_name] FROM sys.database_files sdf INNER JOIN sys.filegroups... Read more →

How to check if SQL Server Agent is running using t-sql

Question: I want to check if SQL Server Agent is running using t-sql. I know I can check through the services.msc or other methods such as Powershell scripts to report on the SQL Server Agent status , but the requirement is to extract this information via t-sql. Do you have a script using t-sql to get the SQL Server Agent Status? Answer: If you check the SQL generic Refresher program in the sysprocesses list , it is possible to establish the SQL Server Agent status through t-sql. The SQL Generic Refresher is an app program. When the SQL Server Agent... Read more →

How to check if Puppet is reboots a windows server with Powershell

If you want a Powershell script to quickly check if Puppet has initiated a reboot on a Windows server , use this script . It uses the Powershell cmdlet -Get-WinEvent. Typically - a Puppet maintenance window is set per server. If a reboot is required , due to a pending patching change - than the reboot will occur during the designated window. A server owner may experience an unexpected outage - and will want to investigate the root cause. Part of the root cause may include analysing the Event viewer logs for clues. The Windows Event Logs stores loads of... Read more →

How To Use Transparent Data Encryption To Increase Your Security

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection. Setting up TDE in SQL Server is in summary Create a master key. Create or obtain a certificate protected by the master key. Create a database encryption key and protect it by using the certificate. Set the database... Read more →