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 […]
2003-01-22
2,863 reads
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 […]
2003-01-22
578 reads
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 […]
2003-01-22
276 reads
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 […]
2003-01-13
288 reads
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 […]
2003-01-09
778 reads
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 […]
2003-01-09
392 reads
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 […]
2003-01-09
817 reads
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 […]
2002-10-09
925 reads
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 […]
2002-03-07
815 reads
There has been some talk about the use of partitioned views in SQL Server 2000 to gain performance improvements for scale-out of applications. In this article by Neil Jacobson, he discusses his findings in respect of the speed of partitioned views.
2002-02-13
7,480 reads
By Steve Jones
This Friday is the NYC DevOps Devour hour, which is actually 3 hours. Plus...
Three years ago, when the first public preview of SQL Server 2022 (CTP 2.0)...
By Steve Jones
Redgate Monitor works with more than SQL Server. Some big changes were announced recently,...
Hi, I'm trying to make the below query SARGable by removing the ISNULL function...
We're about to upgrade our SQL 2017 Standard to 2022 standard. We have 1...
Comments posted to this topic are about the item Script-Drop Orphan users from SQL...
What is the minimal number of parameters to add a linked server with sp_addlinkedserver?
See possible answers