Neil Jacobson


Technical Article

Build a script for CREATE DATABASE/FOR ATTACH

It is easy enough to detach and reattach databases in SQL 2000 providing you have less than 16 files to attach.I manage some very large databases and these sometimes need to be detached and reattached (i.e. for moving a database file from one volume to another).I have more than 16 files to contend with on […]

4.67 (3)

You rated this post out of 5. Change rating

2003-01-22

2,845 reads

Technical Article

Build Restore Scripts for All User Databases

Something I really hate is typing something repetitive.  To prepare for our Disaster Recovery plan, I wrote this script.It is a bit strange in that it requires 2 run iterations to generate the desired result.I use a naming standard for backup files of the form:D_DBName.Bakand a naming standard for database files of the form:DBName_Dx.Mdf for […]

You rated this post out of 5. Change rating

2003-01-22

574 reads

Technical Article

Generate script to tidy up DB Logical File Names

You may be a tidy person who likes to keep naming standards for everything in SQL Server.  It is a good practice since it makes generating scripts for everyday DBA tasks a lot simpler.  I make all database files follow the following standard:DBName_D1.mdf     for primary filegroup dataDBName_I1.ndf     for index filegroupDBName_L1.ldf     for transaction […]

You rated this post out of 5. Change rating

2003-01-22

261 reads

Technical Article

SP to determine database file growth at intervals

I wrote this SP to be run from a job every night. Change the myDB reference to be a database on your system for DBA use.I just set up a job to run:Exec sp_CatchFileChangesThen I run in another step:Select * from mydb.dbo.tbl_sysaltfiles_3and output to a log file.Alternatively you could insert into another table with a […]

You rated this post out of 5. Change rating

2003-01-13

281 reads

Technical Article

Calculate Earth global distances

I wrote this sp to be able to calculate the distance between two locations on the earth if the latitude and longitude is known for each.  Could be useful for GPS work or maps.Usage:If I know the following city locations:Sydney         151.2 E             33.87 SCanberra       149.15 E             […]

5 (1)

You rated this post out of 5. Change rating

2003-01-09

765 reads

Technical Article

Check if batch is running.

WARNING:  This is resource intensive. Don't use except as a last resort.I wrote this to be able to find if a TSQL batch is running already on a SQL Server.  I use this rarely when I have to know to prevent running something twice.Usage:DECLARE @RC intDECLARE @SQLBatch nvarchar(255)-- Set parameter valuesEXEC @RC = [master].[dbo].[sp_RunningCheck] @SQLBatch […]

You rated this post out of 5. Change rating

2003-01-09

350 reads

Technical Article

Convert Degrees Minutes Seconds to Decimal UDF

I wrote this function to work with my sp_EarthDistance stored procedure.It converts the regular degrees/minutes/seconds representation of angles to a decimal number.Remember to use '' instead of ' within a string to be treated a character and not an end quote.Sydney location    151¦12'0 "E               33¦52'0 "S and in […]

4 (1)

You rated this post out of 5. Change rating

2003-01-09

767 reads

Technical Article

Proc to script a user role and object permissions

I wanted to be able to prepare a script of User Roles and permissions for objects in a database so I can be prepared for backup and disaster recovery scenarios. I also wanted to be able to audit the permissions for each role.I wrote this stored procedure to do this.  The usage is:Use NorthwindgoDECLARE @RC […]

You rated this post out of 5. Change rating

2002-10-09

916 reads

Technical Article

Check for orphaned user or sid in syslogins

When rebuilding a server or setting up a backup or DR server the logins need to be added into master and synchronised with the user databases.Inevitably there are orphaned users is sysusers or the sid's do not match with syslogins.I use this stored procedure to check for a particular user when the user experiences a […]

You rated this post out of 5. Change rating

2002-03-07

797 reads

Blogs

Elevate Your Data Leadership: A Unique Intensive Learning Experience

By

I’ve collaborated with two industry experts to design a transformative course for data leaders:...

A New Word:Maugry

By

maugry – adj. afraid that you’ve been mentally deranged all your life and everybody...

Friday Flyway Tips–Capture the Filegroup for Tables

By

A customer asked recently why Flyway doesn’t detect the filegroup for some changes. I...

Read the latest Blogs

Forums

Visual Design for Safety

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Visual Design for Safety

A New Audit

By Steve Jones - SSC Editor

Comments posted to this topic are about the item A New Audit

Forget the Models, Your Data is the Secret Sauce for Generative AI

By Amit Rai

Comments posted to this topic are about the item Forget the Models, Your Data...

Visit the forum

Question of the Day

A New Audit

I have a brand new D: drive on my system that is formatted, but contains no files or folders. What happens when I execute this code?

CREATE SERVER AUDIT audit2 TO FILE ( FILEPATH ='D:\audit' );
GO

See possible answers