Category: SQL Server
-
A Quick Tutorial for Auditing DML Actions in a Database
I was recently asked to “capture” which account made a possible delete/truncate operation regularly against a table in a specific database. And I landed on this article through google and found it a very useful tutorial for setting up an audit trail to watch DML actions. The most important takeaway I got is that when…
-
SQL Server Service Stuck in ‘Starting’ Status
Bumped into this problem today with MSSQLSERVER service stuck at “starting” status. Here is the original article with illustrative screen shots, I am going a bit “roughly speaking” below: Open a command window by pressing windows key + R Type “sc queryex MSSQLSERVER” to get basic info about this service Identify the PID of this…
-
T-SQL Stored Procedure to Get the Number of DB Connections and Logins per Database
[sourcecode language=”text” wraplines=”false”] /* Script to get the number of connections and their login names on an instance Last modified by Nick Xu on 2013-08-17 Usage: exec sp__GetDBConnections ‘master’ */ –For generic setting use master go if exists (select * from sysobjects where name = ‘sp__GetDBConnections’ and type = ‘P’) begin drop proc sp__GetDBConnections end…
-
T-SQL Stored Procedure to Get the Time and Backup File Location of the Last Data Restore
[sourcecode language=”sql” wraplines=”false”] /* .Synoposis Stored procedure to list the last data restore time AND backup file location for specific online user DB’s. .Last modified 2014-03-24 by Nick Xu .SQL Version 2005+ –Usage EXEC sp__GetlastDataRestoreTime @dbname = ‘DBADB’ GO EXEC sp__GetlastDataRestoreTime GO */ USE master GO /* –For generic setting IF EXISTS (SELECT * FROM…
-
T-SQL Stored Procedure to Get the Last Job Run Time and Status
[sourcecode language=”sql”] /* Stored procedure to run the last run time and run satus of a SQL job. SQL Version: 2005+ Last modified on 2013-07-03 by Nick Xu –Usage –returns the last successful run time exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status = N’success’ –returns the last failed runtime exec sp__GetLastJobRunTimeStatus @jobname = N’syspolicy_purge_history’, @status =…
-
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…
-
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…
-
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…