Even the faintest ink beats the strongest memory.
-
T-SQL Script to Split Tempdb Data Files with Alignment of the Number of CPU Cores
“One tempdb date file per core” is proved to be a myth by Paul Randal and quoted from his blog it is a conlusion that : “If you have > 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.” Therefore the following script will never set…
-
T-SQL Script to Delete Older-Than-N-Days Backups in a Specific Folder
I’ve noticed there are quite some hits on this post and the old format of the T-SQL code looked a bit messy, so I’ve done a makeover of the script on 2014-04-10. This script requires a not-too-strict security setting for sql server’s service account, because it will have to execute Windows batch commands to achieve…
-
T-SQL Script to Quickly Truncate the Log File of a Database
Sometimes, as a DBA you are requested urgently by either users or your supervisors to quickly truncate a log file. It is not a very good practice first of all and you should have already avoided this situation if you have already standardized you DB’s backup and recovery plan. However besides the above reminder, this…
-
A very good guide on SQL Server related performance counters
There are more than abundant whitepapers, books and forum discussions on what performance counters to collect and how to interpret their values. I’ve recently bumped into a very good wrap-up for this trade of analyzing useful performance counter logs. Please go to the original link here to have a good read; courtesy to another SQL…
-
T-SQL Script Using sp_msforeachdb to Change All Databases’ Owner to ‘sa’ Account
Oftentimes, some DB’s in our inventory are modified or restored or created by different user accounts, which leads to having various DB owners in an instance. And a good ‘best practice’ claimed by Microsoft is to align all DB owners to the ubiquitous ‘sa’ account and here goes the script to do so without offending…
-
Two Ways to Know the Underlying OS Version Number by T-SQL
The very quick one is select @@version. Another way is to take advantage of extended stored procedure xp_msver, for example, exec xp_msver ‘WindowsVersion’.
-
Error: Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time.
I encountered this error message during a project to move system databases while having to restart SQL Server service with /f /T3608 trace flags and hence into single user mode. However when trying to log on and issues scripts for moving mssqlresource database, I constantly got denied to access by either SSMS or command line…
-
Two Quick Tips to Improve Backup and Restore Performance from Brent Ozar
[youtube=http://www.youtube.com/watch?v=NhOK_EujTCU&feature=player_profilepage]
-
Error: “Cannot generate SSPI context” when connect to a SQL Server instance after service account change
I’ve recently encountered this problem because to update our security standards, all SQL Server service accounts have to be changed to normal domain user accounts. And after changing them from LocalSystem to new accounts, this error starts to pop up. The cause is fairly simple and the following text is from page 113 of Microsoft’s ITP training…
-
How to Connect to SQL Server Configuration Manager Remotely
Very neat approach which I didn’t know up until today. Here is the link from Microsoft. However, I am still struggling with a server where either local or remote configuration manager fail to report correct statuses of all SQL Server related services. The error message are simple text “The remote procedure call failed. [0x800706be]” locally and…