Thursday 7 May 2015

Shrink Database - SQL JOB that runs every mornign

1. Open "Microsoft SQL Server Management Studio" MSSMS
2 Connect to server
3. In Object explorer expand
     I. Server Instance
     II. SQL Server Agent
     III. Jobs
4. Right click on Jobs folder and click on "New Job..." and provider following information
   I. In General Tab
        a. Name (e.g. Shrink Database)
        b. Description (e.g. Currntly having issue with disk space, setting up the job so that it runs every morning and I don't have to restart box or run sql manually)
  II. In Steps Tab
       a. Click New button
           1. In General Tab
               I. Step name (e.g. SHRINK DATABASE)
               II. Command (e.g. EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE ("?",0)'  )
           2. Advanced Tab
              I. Run as user (e.g. xyz\kaleemkhan)
  III. In Schedules Tab
          a. Name (e.g. Run every morning)
          b. Schedule Type (e.g. Recurring) 
          c. Enable: yes
          d. Frequency: Daily
          e. Recurs: 1 day(s)
          f. Occurs once at 08:00:00
          g. Start date (e.g. creation date)
 

Click Okay to opened windows to setup the SQL job.
     


 

sql for database names created in desc order

I got more than 50 dev crm org, and I created one and forgot which one is the latest one, in order to find that I used following sql it lists all the database names order by created date in desc

SELECT * FROM sys.databases ORDER BY create_date  desc