Change the email across all Job alerts using script in SQL Server

As a DBA sometimes we get request for add/update/delete of DL’s – Distribution Lists (email groups) across all jobs on a SQL Server Instances. If the frequency of such request is very frequent then it’s better to write a script and run it on requests by simply modifying the email addresses.Here is how we will do this:We will take help of 2 tables  msdb.dbo.sysjobsteps and msdb.dbo.sysjobstepsQuery to retrive the list of jobs along with steps which satisfies the condition to search for an email (Simply update...

Continue Reading →

Reset MySQL root password in Windows and UNIX

1.    Shutdown the MySQL Server – a.    Use windows services to shut down: Start > run > services.msc  > MySQL > right click and select STOP  b.    Using mysqladmin by running following command in command prompt: mysqladmin -u root shutdown (make sure to provide the correct path for mysqladmin if it has not been added in windows path) 2.   ...

Continue Reading →

Simple script to find the free space in sql server db files (data, log) against all databases

Here is a simple SQL script to retrieve the free spaces on db files (data, log). The below script will give you the File size, used space and free space of currently selected database only. select       name     , filename     , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB     , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))...

Continue Reading →

Space Management Report - Get Drive Size and Drive Free Space :SQL Server

One of the regular DBA practice is to maintain the free space on Drives where the data files are located. Here i have created a script (Space Management) which gives us the database files, its location,size, growth rate, the drive where its located, drive size, drive free space etc... Most of the thing is very easy to retrieve through SQL Queries as those are maintained in tables under msdb databases, but the free space and...

Continue Reading →

Script all the jobs in SQL Server

This is very interesting fact that people always look for shortcuts especially while accomplishing the big tasks. Here i have came up with a scenario: I have got a task to setup a new server B (lets assume) exactly as a replica of Existing Server A. It involve a lot of steps in which there is a step to copy all the EXISTING JOBS FROM SERVER A to SERVER B (Please note i have written All the Jobs).  Here is a easiest way...

Continue Reading →

Database is in Transition. try the statement later: SQL Server

Problem: I was trying to take the database offline to perform some maintenance on one of our QA server. We have got a strange error "Database is in Transition. try the statement later Error 952" Even i was not able to run the sp_who and sp_who2 also as it was also giving the same error. Resolution:  What i have tried? I closed the SSMS (SQL Server Management Studio) and restarted it again. Then to confirm and check for any blocking query, i ran sp_who and sp_who2 and it ran successfully.  Then to...

Continue Reading →