John Liu Blog

Every drop counts

SQL Server Relocate Tempdb file location

There might be time you would like to relocate tempdb file to a new location. Make sure the SQL Server service account has required permission to the destination folder. Run the following command to move each database file to the new location: ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'E:\Temp\tempdb.mdf') Restart SQL Server service and tempdb should be relocated to the new location. If you encounter any issue that caused the SQL Server service fail to start, you can try to start the service in emergency mode and then connect to it make any required adjustment.

Posted by John Liu Sunday, June 18, 2023

SQL Server with minimal configuration

There are time that the SQL Server instance might not be able to start up due to tempdb files are not accessable. To change the tempdb file location, you can start up the instance with minimal configuration, using parameter -f net start mssql$NamedInstance -f You can also use trace flag 3608 that tells SQL Server not to start up/recover any database but master. Once the instance is start up, you can then connect using either SQLCMD for SSMS to change the tempdb file location.

Posted by John Liu Sunday, June 11, 2023

How to convert a single-client database to multi-client database

Consider you have a database (with single dbo schema) that’s design to be used by a single client. How can you turn the database to be multi-client database with minimal impact to your existing application? Row Level Security (RLS) could provide a considerably easily solution. Some assumptions: Your application code or database object (trigger, SP, function etc.) code doesn’t hard code schema name (as we will using schema to separate each client) Your application needs to be able to using different login for different client when connect to the database Same login will not be access more than one client data Following are the steps to convert the database.

Posted by John Liu Monday, April 10, 2023

SQL Server Performance tool scripts

Buffer cache usage summary SELECT D.name ,MBUsed = COUNT(*) * 8 / 1024 ,MBEmpty = SUM(CAST([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) FROM sys.dm_os_buffer_descriptors B INNER JOIN sys.databases D ON B.database_id = D.database_id GROUP BY D.name Current memory status --https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/dbcc-memorystatus-monitor-memory-usage DBCC MEMORYSTATUS; Index usage SELECT DatabaseName = DB_NAME() ,TableName = o.name ,IndexName = i.name ,s.index_id ,i.type ,i.is_primary_key ,i.is_unique_constraint ,s.last_user_seek ,s.user_seeks ,SeekPercentage = CASE s.user_seeks WHEN 0 THEN 0 ELSE s.user_seeks*1.0 /(s.

Posted by John Liu Sunday, April 9, 2023

SSMS Extensions

With SSMS 19, when trying to install Poor Mans TSQL Formatter SSMS extension, it doesn’t appear working after install. This is due to the install didn’t create/copy the package file onto folder C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions\Extensions. To make it work, manually copy the PoorMansTSqlFormatterSSMSPackage.pkgdef from the Extension folder for SSMS 18 and past into the Extension folder for SSMS 19. You may need to create the Extension folder if it doesn’t exist.

Posted by John Liu Friday, March 17, 2023