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 →