Showing posts from February, 2011

How to run a stored procedure at SQL Server start-up

This is actually quite simple. There is 'startup' option that you can set to the procedure. There are a few limitations though: - sp must reside in the [master] database - it's ow…

alternate to IF EXISTS ..UPDATE ELSE ..INSERT

On some occasions we need to update an entry in a table in case there is an entry otherwise we need to insert that data into the table. The easiest way to do this is something like this IF EXISTS …

Script to Check and Fix Orphan Users

-- Script to check the orphan user EXEC sp_change_users_login ' Report ' --Use below code to fix the Orphan User issue DECLARE @username varchar (25) DECLARE fixusers CURSOR F…

DBCC UPDATEUSAGE and sp_updatestats

This may provide some light on the usage of DBCC UPDATEUSAGE and sp_updatestats DBCC UPDATEUSAGE corrects the rows, used, reserved, and dpages columns of the sysindexes table for tables and clustered…

sys.dm_io_virtual_file_stats

Excellent dmf that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I u…

Register an Extended Stored Procedure in SQL Server 2008 R2

Aside from those integrated into SQL Server (e.g. xp_cmdshell) it's not often I come across the need to use Extended Stored Procedures but an application I installed recently required that I add …

How to Enable xp_cmdshell

There's a great little feature of SQL Server that allows you to execute command-line statements, allowing you to run batch files, get directory listings or call executables.  The feature is acces…

Reset Default Database from Command Line

During a recent consolidation exercise I was migrating one database at a time and then taking the old copy offline, unfortunately one of the DBs had (un-knowingly) been my default database and next t…
Subscribe Our Newsletter