Question : I'd like to view the mounpoint size & freespace for a SQL Server installation , but do not have access to the server directly i.e through RDP.Also there are no user databases currently on the dedicated user database mountpoints so am not able to work out the physical name .
The only information I have is the server & share details . For example "\\myserver\h-share\mydata" .
Answer: If you're using Powershell and have sufficient privileges , one option is to investigate the Get-WmiObject Win32_Volume class on Get free disk space for mount points using Powershell
If you're getting Access Denied issues , than another option is the dir command , typically used to display the the durectory files and subdirectories .
To use it for the moutpoint details - you'd execute the command similar to : dir/s \\myserver\h-share\mydata . As well as supplying the folder and directory list - there'll be a summary of files size aggregates at the bottom of the result set . Adding these totals will give you the full size
Example :
Total Files Listed:
0 File(s) 99,000 bytes
9 Dir(s) 644,100,980,736 bytes free
Question: I'm trying to delete a TempDB ndf file from the TempDB file definitions. It is no longer required ,but getting an error message :
DBCC SHRINKFILE: Page xxxxxxxx could not be moved because it is a work table page.
How can I get around this problem? There is no activity on the server
Answer: The usual fix for removing the extra tempdb file or even the extra transaction log file is defined in Msg 5042 - SQL Server DBA
But sometime that does not work if certain circumstances are in place - such as the presence of a work table page. Sometimes it is always possible to restrat the SQL Server due to approval requests not met or other Production controls
This sequence of commands may help in this situation , and avoid the TempDB restart. There may be some performance hit when you execute these commands
Releases all unused cache entries from all caches.
DBCC FREESYSTEMCACHE ('ALL');
DBCC FREEPROCCACHE;
Question: I have a SQL Agent job with a Last modified date of : 1/11/23. What activity causes the last modified date to occur ? And also is there a way to find out the change?
I've used this query to identify the jobs and owners
use msdb go select j.name, j.date_modified, l.loginname from sysjobs j inner join sys.syslogins l on j.owner_sid = l.sid
Answer: Every time the "Save As " button is clicked the date_modified column changes . This means any aspect of the SQL Agent job changed will cause the Last Modified data to change.
This does make it difficult to Audit . You would need to develop an SERVER AUDIT process to monitor the changes made , store them in a log file . One method to audit and maintain records of SQL Agent Job Changeds is Extended Events
Useful Links to problems solved using Extended Events
How to capture query errors using SQL Server Extended Events
How to Audit user DML with SQL Server Extended Events
How to capture deadlocks using system_health Extended Events
Question: I'm getting this error when connecting Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
What is the fix for this error ?
Answer:
Step 1: Check if sql server Force Encryption is set to No . There are different wasys to check such as How to check SSL encryption is enabled on SQL Server with Powershell & How to check a SQL Server connection is encrypted with SSL
here is an example
DECLARE @force_encryption INT EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', 'ForceEncryption', @force_encryption OUTPUT SELECT CASE WHEN @force_encryption = 1 THEN 'Force Encryption = True ' ELSE 'Force Encryption = False' END
Step 2: Check if the client connection string explcitly states an encryption property value , or DSN
If both Step 1 & Step 2 return No , than the likelihood is your hitting the new default behaviour of the client drivers mentioned in your question :Microsoft OLE DB Driver 19 for SQL Server & Microsoft ODBC Driver 18.x for SQL Server.
These new versions of drivers assume the data encryption is on by DEFAULT - therefore upon connection , there is an attempt to validate the certificate .
There's a few different options to fix the issue
1) Rollback to MS ODBC Driver 17 for SQL Server
2) Review the connection string - does it specify Yes for Encryption , change it to No.
If Data Souce Name (DSN) - review the encryption setting - is set at mandatory ? Change to Optional
Following changes in the feedburner service , sqlserver-dba.com is changing the rss feed and email notification subscription service to follow.it
Feedburner no longer offers an email subscription & notification service
Email notification subscribers - We've moved your confirmed email subscription from feedburner to follow.it . There is no requirement to opt-in again to receive your content.
If you currently don't have an email notification subscription to sqlserver-dba.com than use the email subscription on the right panel , to receive regular updates on SQL Server DBA related troubleshooting and technology topics from sqlserver-dba.com
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 full back up at the same time doesn't cause a onflict . Although there a few points to consider.
1) A SQL Server full backup does not truncate data from the transaction log.
2) A typical transaction log backup has some cleanup activiyty . In the situation of a simultaneous transaction log \ full backup scenarion - log will not be cleaned until full backup completes. The truncation occurs at the first log backup after the full backup .
3) Although it sounds unusual - it can also potentially help you if a transaction log backup is taken - in the situation where the full backup fails.
4) Database backup does not cause blocking at a transactional level , but it also applies to not blocking a transaction log file
5) When you check the Backup history for a single SQL Server database , you'll notice references to the checkpoint_lsn and database_backup_lsn . Use these references to investigate the checkpoint times
6) In case you're concerned about the Log Backup Chain breaking - read through Reasons for Transaction Log Backup Chain breaking to understand some of the scenarios where the Log Backup Chain can occur . Take the appropriate steps to maintain backups to satisfy the Application RPO
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 appear . How can I identify the items taking up the space ?
Answer: These steps deal with a common problem of deleting items from the hidden folder $Recycle.bin
Note : If you think the issue it's related to the pagefile.sys you can Find Windows pagefile location through command line and wmic
Step 1 : assuming your dive is called : H:\. Open the windows cmd and execute this command:
dir /s H:
Step 2: You'll notice a single or series of folders that contain files that were deleted.
Step 3: Once you have completed all due diligence and checked that you want to clear the Recycle bin , execute the following command.
NOTE: This command sequence permanently deletes all files\folders in the Recycle Bin from all the users - and cannot be recovered.
rd /s /q H:\$Recycle.bin
In this example , you're clearing out the hidden $Recycle.bin folder of the H:\ drive. Customise according to your requirements
An alternative approach is to use Powershell read Find Recycle Bin location and clear down with Powershell
If you're using Windows 2012 - Windows Server 2012 empty recycle bin