Relocate and Re-size the TempDB

Often times it becomes necessary to move the tempdb.  Maybe your data files are becoming too large, or you want to relocate the files to achieve more optimal performance.  It is fairly easy to move the tempdb, but it will require a restart of the SQL Server service.  In this example I have both changed the location of the files, and added a couple of new ones.  The end result is a completely isolated TempDB -- on it's own drive, and separate from the user and other system databases.

Let's run this first to confirm the logical file names for the tempdb data files: 
 USE tempdb;
 EXEC sp_helpfile

Your results will look something like this:

name      fileid   filename                       filegroup        size        maxsize    growth    usage
tempdev       1            C:\MSSQL\Data\tempdb.mdf       PRIMARY        1092 KB   Unlimited   10%         data only
templog        2            C:\MSSQL\Log\templog.ldf         NULL                512 KB   Unlimited   10%         log only

The first step is to move TempDB to its own drive. I only set a 1mb file size because SQL Server does something a little screwy here. It does not matter if we tell it to use a different drive letter. SQL will still look for the given amount of free space on the drive that TempDB currently sits on. So, if SQL is installed at C:\Program Files\, and we try to create a 10GB TempDB file on a different drive, the server will look for 10GB on the C drive. Its a bug I learned about here:
      http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/

   -- Step 1.

   USE master;

   GO

   ALTER DATABASE TempDB MODIFY FILE (

      name = 'tempdev', 

   filename = 'F:\MSSQL\TempDB\tempDB.mdf', 

   size = 1MB

   )

   GO

   ALTER DATABASE TempDB MODIFY FILE (

   name = 'templog', 

   filename = 'F:\MSSQL\TempDB\templog.ldf', 

   size = 1MB

   )

   GO

At this point, you will need to restart the SQL Server service, in order for the changes to take effect. Take a look at the F:\ drive (or whatever you used), and note your new files. 

The second step is to expand the TempDB data file to the correct full size, and create three additional TempDB files. I always use Paul Randal's advice, which is to make one data file for every processor core. Sometimes I need more, but I pretty much always start with 4 and go from there.

You can see that I expanded tempdev to 250MB, and created the 3 new files at 250MB each, with 0 FILEGROWTH.  This is VERY important.  We don't want them to auto-grow frequently in production, because this causes disc fragmentation, and impedes activity during the file growth.  Ideally, we preallocate TempDB to take whatever disc we have reserved for it.  Basically, build it proactively, not re-actively.  

  -- Step 2.
  -- Expand tempdev
    USE master;
    ALTER DATABASE TempDB MODIFY FILE (
   name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf', 
   size = 250MB,
   filegrowth = 0
     )
     GO

   -- Add three new data files
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev2', 
   filename = 'F:\MSSQL\TempDB\tempdev2.ndf', 
   size = 250MB, 
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
      name = 'tempdev3', 
   filename = 'F:\MSSQL\TempDB\tempdev3.ndf', 
   size = 250MB, 
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev4', 
   filename = 'F:\MSSQL\TempDB\tempdev4.ndf', 
   size = 250MB, 
   filegrowth = 0
     )     
     GO

Now I will rename the original 'tempdev' data file to 'tempdev1'.  This is totally NOT a requirement. I just like to keep all of my files consistently named.  (tempdev1, tempdev2, tempdev3, etc.):

   /* Rename files */

   ALTER DATABASE tempdb

   MODIFY FILE (
       NAME ='tempdev', 
       NEWNAME = 'tempdev1'
   )

Lastly, I will increase the size of the tempdb log to 2GB.  Remember, this is just an example.  The size of your tempdb log will be dictated by your SQL Server utilization.

   /* Increase TempDB Log file.  */
   ALTER DATABASE tempdb
   MODIFY FILE (
      NAME = 'templog', 
      SIZE = 2048MB
   );

There are many other different references out there on tempdb performance, such as the ones I've linked to below.   I encourage you to do some research.  TempDB is not something where we have a lot of room for errors.  Take a look at these references, and let me know if I can help any further.

      Optimizing tempdb Performance 
      Do you need more TempDB files?

Script to backup all SQL Server databases

This post covers two topics --  First, it is a very simple method to backup all of the user databases. Secondly, it is an example of a cursor.  I typically stay away from cursors within any real data manipulations, but I don't mind using them for simple functions such as this. The tSQL is a very simple BACKUP command, and the cursor just allows us to walk through all of the databases with that command, very quickly and very efficiently.  

/*

Use cursor to backup all user databases.  */

DECLARE 

@DBName VARCHAR(25),   -- database name  

@BkupPath VARCHAR(255),   -- the path for backup files  

@BkupFileName VARCHAR(255),   -- the backup filename

@FileDate VARCHAR(10)   -- to put date into filename

 

-- Where is the backup directory?

SET @BkupPath = 'C:\MSSQL\Backup\'  

 

-- Construct the BkupFileName

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 

-- Declare your cursor

DECLARE DBCursor CURSOR FOR  

SELECT name 

FROM master.dbo.sysdatabases 

WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude the system databases

 

-- Open your cursor

OPEN DBCursor   

FETCH NEXT FROM DBCursor INTO @DBName   

 

WHILE @@FETCH_STATUS = 0   

BEGIN   

  SET @BkupFileName = @BkupPath + @DBName + '_' + @FileDate + '.BAK'  

  BACKUP DATABASE @DBName TO DISK = @BkupFileName  

 

  FETCH NEXT FROM DBCursor INTO @DBName   

END   

 

-- Close your cursor

CLOSE DBCursor   

DEALLOCATE DBCursor

Note, you can revise the resulting .bak filename, if you'd like something different than 'DatabaseName_YYYYMMDD.bak'.  Also note the WHERE clause on the cursor declaration. You could adjust this to IN or NOT IN, and customize the database list to whatever databases you'd like to target.  This is the clause that I used in my test:     WHERE name IN ('master','model','msdb')

And this is my output:

Take a look at that run time.  All three sys dbs backed up to in 5 seconds.  Not bad.

That's pretty much it.  Like I said, you should modify that WHERE clause to suit your needs.  This is a very flexible, easily customized routine for backing up multiple databases via cursor. 

See here for more information on cursors and the BACKUP command:

  Cursors -  http://msdn.microsoft.com/en-us/library/ms180169(v=sql.110).aspx

  Backup -  http://msdn.microsoft.com/en-us/library/ms186865(v=sql.110).aspx

Can you tell when a stored procedure was last executed?

Yep.  You certainly can.  The sys.dm_exec_query DMV returns aggregate performance statistics for cached query plans.  Important note:  It is only cached query plans.  When a plan is removed from the cache (or you restart the SQL Server service), the corresponding rows are eliminated from this reference.

     SELECT 
          qt.[text] [ProcedureName], 
          qs.last_execution_time [LastRan], 
          qs.execution_count [ExecutionCount]   
     FROM 
          sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     WHERE 
          qt.text LIKE '%your procedure name%'

To perform my test, I fired sp_who2 several times, and sp_who a couple times.  The above captured these results:  

     ProcedureText                             LastRan                                ExecutionCount
     create procedure sys.sp_who       2014-07-29 18:10:19.850          2
     create procedure sys.sp_who2     2014-07-29 18:11:27.520           5  
     create procedure sys.sp_who2     2014-07-29 18:11:27.523          5

Please take a look at these for more information:

    sys.dm_exec_query_stats -
         http://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx
    sys.dm_exec_sql_text -
         http://msdn.microsoft.com/en-us/library/ms181929(v=sql.110).aspx

You should also take a look at this one:

Stored Procedure - Created or Last Modified Date

Query for Stored Procedure parameter details.

This is just a real quick tip for collecting parameter details for all user defined functions and stored procedures.  I've find this very helpful when I take on a new customer, and 'inherit' a database that someone else has created.  This just helps me to analyze all of the objects with their parameter details.

Take a look, let me know what you think.

     /*
     Return procedures and functions with parameter details.  */

     SELECT 
         SCHEMA_NAME(SCHEMA_ID) [Schema], 
          so.name [ObjectName],
         CASE WHEN so.Type_Desc = 'SQL_STORED_PROCEDURE' THEN 'Procedure'
                  WHEN so.Type_Desc = 'SQL_SCALAR_FUNCTION' THEN 'Function' END [ObjectType],
        p.name [Parameter],
        TYPE_NAME(p.user_type_id) [ParameterDataType]
     FROM 
        sys.objects so INNER JOIN sys.parameters p
           ON so.OBJECT_ID = p.OBJECT_ID
     WHERE 
        so.OBJECT_ID IN (
                        SELECT OBJECT_ID 
FROM sys.objects
WHERE TYPE IN ('P','FN') )
     ORDER BY 
       [Schema], 
       so.name


Take a look at each of these topics for more details:

  sys.objects - http://msdn.microsoft.com/en-us/library/ms190324(v=sql.110).aspx
  sys.parameters - http://msdn.microsoft.com/en-us/library/ms176074(v=sql.110).aspx

How do we collect SQL Server backup details from the system tables?

A customer told me yesterday that no SQL Server database backups were running.  Yet when I queried msdb..backupset, I could see backups were being recorded every day. There weren't any Agent jobs, so I wasn't sure exactly how the backups were being performed, or even where they were being written.

I threw this logic together to return all of the backup details to me, to include name of the software that is creating the backup. When we ran it for the customer, we found the backups were being run with Dell's AppAsure backup software. 

/* 
Returns backup details for every database on the targeted instance.  To include - 
   BkupSoftware
   Server
   Database
   CompatibilityLevel
   RecoveryModel
   BackupType
   BackupStart/Finish Dates
   PhysicalDevice
   DeviceType
   BackupSize(Bytes)
   CompressedBackupSize(Bytes) - (Same as BackupSize(Bytes) if you are not compressing.)    */

USE msdb; 
SELECT 
    bms.software_name [BkupSoftware],
    bs.server_name [Server],
    bs.database_name [Database],
    CASE bs.compatibility_level 
         WHEN 80 THEN 'SQL v2000'
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008' 
         WHEN 110 THEN 'SQL v2012'
    END AS CompatibilityLevel, 
    recovery_model [RecoveryModel],
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END [BackupType],
    bs.backup_start_date  [BackupstartDate],
    bs.backup_finish_date    [BackupFinishDate],
    bmf.physical_device_name [PhysicalDevice],
    CASE device_type 
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END [DeviceType],
    bs.backup_size [BackupSize(Bytes)], 
    compressed_backup_size [ConmpressedBackupSize(Bytes)] 
FROM
msdb.dbo.backupset bs INNER JOIN msdb.dbo.backupmediafamily bmf 
     ON ( bs.media_set_id = bmf.media_set_id ) INNER JOIN msdb.dbo.backupmediaset bms
      ON bs.media_set_id = bms.media_family_count
GROUP BY
    bms.software_name,
    bs.server_name,
    bs.database_name,
    CASE bs.compatibility_level
         WHEN 80 THEN 'SQL v2000' 
         WHEN 90 THEN 'SQL v2005 '
         WHEN 100 THEN 'SQL v2008'
         WHEN 110 THEN 'SQL v2012' 
    END,
    recovery_model,
    CASE bs.type 
         WHEN 'D' THEN 'Database' 
         WHEN 'I' THEN 'Database Differential' 
         WHEN 'L' THEN 'Log' 
         WHEN 'F' THEN 'File or filegroup' END,
    bs.backup_start_date,
    bs.backup_finish_date,
    bmf.physical_device_name,
    CASE device_type
         WHEN 2 THEN 'Disk - Temporary' 
         WHEN 102 THEN 'Disk - Permanent' 
         WHEN 5 THEN 'Tape - Temporary'
         WHEN 105 THEN 'Tape - Temporary' 
         ELSE 'Other Device' END,
    bs.backup_size,
    compressed_backup_size
ORDER BY 
bs.backup_start_date DESC 

Take a look at each of these references to see more detail for each of these backup objects located within the msdb:

    backupset - http://technet.microsoft.com/en-us/library/aa260602(v=sql.80).aspx
    backupmediafamily - http://msdn.microsoft.com/en-us/library/ms190284.aspx
    backupmediaset - http://msdn.microsoft.com/en-us/library/ms189513.aspx

What is your Database Mirror status?

If your databases are Mirrored, this one is for you.  As you know, SQL Server generates events if the status changes in your mirroring session.  But what if you want to get in and just check on things yourself?  How do we check our database mirroring state?  We have the Database Mirroring Monitor, which can be launched to return mirroring status details.  Personally, I am not a big fan of GUI tools.  They really are memory pigs!  

To avoid the GUI, I like to check my mirror status with the script below.  You can see I am using sys.database_mirroring, which contains details regarding the state of the mirrored databases.  You can run this by hand to check mirror status, or you could even wrap it into a job that would notify you if the mirrored state changes, or becomes suspect.  I will try to put that together, and post a sample job soon.

     /*
What is the state of your mirrored databases?    */

-- declarations
DECLARE  
@DatabaseID INT, 
@State VARCHAR(55), 
@IsMirrored INT,
@String VARCHAR(155) 

DECLARE @databases TABLE (
DatabaseID INT, MirroringState VARCHAR(55)
)

-- query status for mirrored databases 
INSERT @Databases (DatabaseID,MirroringState)
SELECT database_id, mirroring_state_desc 
FROM sys.database_mirroring 
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR') 
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING') 

-- walk through all mirrored databases 
WHILE EXISTS (
SELECT TOP 1 DatabaseID 
FROM @Databases 
WHERE MirroringState IS NOT NULL
) 

BEGIN
SELECT TOP 1 @DatabaseID = DatabaseID, @State = MirroringState
FROM @Databases 

SET @String = 'Host: '+@@SERVERNAME+'.'+CAST(DB_NAME(@DatabaseID) AS VARCHAR)+ ' - DB Mirroring is '+@State +'.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'yourEmailAddress', @body = @string, @subject = @string 
DELETE FROM @Databases 
         WHERE DatabaseID = @DatabaseID
END

-- You can also check to see if there is no mirroring, when there should be
SELECT @IsMirrored = COUNT(*) 
FROM sys.database_mirroring 
WHERE mirroring_state IS NOT NULL 

IF @IsMirrored = 0 
BEGIN
SET @string = 'Host: '+@@SERVERNAME+' - No databases are mirrored.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'YourEmailAddress', @body = 
     @string,  @subject = @string 
END



Take a look, let me know what you think.  You should also take a look at both of these for additional information regarding the catalog view and the Database Mirroring Monitor tool:

    Database Mirroring Monitor -
              http://msdn.microsoft.com/en-us/library/ms365786.aspx
    sys.database_mirroring catalog -
             http://technet.microsoft.com/en-us/library/ms178655(v=sql.110).aspx

When was the last transaction log backup?

I was performing a health check on a customer's inventory recently, and I produced a list of databases where the transaction log was larger than the data file.  Of course you know, this can happen if the transaction log dumps aren't occurring, or aren't occurring often enough.  The Customer was fairly adamant that the logs were being backed up, though, so I needed a quick way to confirm the last backup time, per database.

Run this.  It will give you the RECOVERY MODEL and last transaction log dump time for all of your databases.

   SELECT

       d.name [Database],

       d.recovery_model_desc [RecoveryModel],

       MAX(b.backup_finish_date) [LastTranLogBkup]

   FROM

  master.sys.databases d LEFT OUTER JOIN msdb..backupset b

         ON b.database_name = d.name

         AND b.type = 'L'

   GROUP BY 

       d.name, 

       d.recovery_model_desc

   ORDER BY 

       LastTranLogBkup DESC

When was CHECKDB last run on your databases?

Hopefully, as SQL Server DBAs, we all know the value of CHECKDB.  I started a new contract recently, and found that it had never been run.  When I asked, I was just told that 'we didn't think it was that important'.  Long story short, CHECKDB consistency checks allow us to proactively monitor for database corruption, and maintain the integrity of our databases.  Proactive awareness is far better than reactive.  In my book, I don't think you can run it too frequently.  

Here's a quick way to confirm when CHECKB was last run on your databases.  

-- A couple temp tables 

CREATE TABLE #DBInfo (

  ID INT IDENTITY(1,1), 

  ParentObject VARCHAR(255),

  Object VARCHAR(255),

  Field VARCHAR(255),

  Value VARCHAR(255) 

)

CREATE TABLE #Value(

  DatabaseName VARCHAR(255),

  LastCheckDB VARCHAR(255)

)

-- Populate temp tables

EXECUTE sp_msforeachdb 'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');

INSERT INTO #Value (DatabaseName) SELECT Value FROM #DBInfo WHERE Field IN (''dbi_dbname'');

UPDATE #Value 

SET LastCheckDB = (SELECT TOP 1 Value FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) 

WHERE LastCheckDB IS NULL;

TRUNCATE TABLE #DBInfo';

-- Return the output 

SELECT * FROM #Value

-- Drop temp tables 

DROP TABLE #DBInfo

DROP TABLE #Value

These are the results from my own instance:

       Database                        LastCheckDB
       master                             2014-05-12 11:26:20.610
       tempdb                           1900-01-01 00:00:00.000 --- no biggie. no need to check tempdb
       model                              2014-05-12 14:52:32.743
       msdb                               2014-05-12 14:52:34.053
       Mine                               2014-05-12 19:58:46.717
       AdventureWorks2012       2014-05-12 19:58:38.850



If you are not running CHECKDB consistency checks regularly, I strongly encourage you to put something into place -- soon!  You can take a look at my User Database Maintenance posting:   
    http://www.sqlfingers.com/2011/01/user-database-maintenance.html

Or take a look at Ola Hallengren's site for a much larger overview of the approach that I use in my maintenance tasks:    
    http://ola.hallengren.com/sql-server-integrity-check.html

Identify the SQL Server Service Accounts using tSQL.

Every so often you are going to need to check the SQL Server service accounts.  Maybe you've just taken on a new customer, or you've inherited someone else's SQL inventory, and you don't know what accounts the services are running under.  You can remote into the servers one at a time, and run SQL Server Configuration Manager, but --  that is kind of time consuming.  Use this piece for a much quicker way to find your service accounts.  You can even run it across a CMS (Central Management Server) Group, and collect this data from all of your servers with one execution.

    SELECT  
         ds.servicename [ServiceName],
         ds.startup_type_desc [StartupType],
         ds.status_desc [ServiceStatus],
         ds.service_account [ServiceAccount],
         ds.filename [EXEFileName],
         ds.last_startup_time [LastStartup]
    FROM
         sys.dm_server_services ds;


There are some other values from sys.dm_server_services as well, but I've just returned a few quick details on the service accounts.  See this for more details on this DMV:

    http://technet.microsoft.com/en-us/library/hh204542.aspx

Rename your SQL Server database files

Every so often, you will need to rename your database files.  Today, for example, I attempted to create a new database.  Very simple, have done it a million times before... but it failed!

  .Net SqlClient Data Provider: Msg 5170, Level 16, State 1, Line 1
  Cannot create file 'C:\MSSQL\DATA\DBA_Primary.mdf' because it already exists. Change 
  the file path or the file name, and retry the operation.
  .Net SqlClient Data Provider: Msg 1802, Level 16, State 4, Line 1
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It didn't fail because the DBA database already existed.  It failed because the .mdf filename in my statement was already there.  My bad.  I forgot that I had renamed the DBA database a while back.  Running sp_renamedb only renames the database, NOT the underlying files.  This is the code that I used to rename the files after I received the error above:

  /* Run this to return the filenames from the database in question. */ 
  USE DBName
  EXEC sp_helpfile

  /* Rename logical database filenames. */
  ALTER DATABASE DBName MODIFY FILE (NAME=N'DBA_Primary', NEWNAME=N'NewFileName_Primary')
  GO
  ALTER DATABASE DBName MODIFY FILE (NAME=N'DBA_Log', NEWNAME=N'NewFileName_Log')
  GO

  /* Rename physical database filenames -- this requires multiple steps:
      1. Put database into single-user mode
      2. Detach database
      3. Rename files
      4. Attach database
      5. Put into multi-user mode      */
 
  -- 1. Single user mode 
  ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  GO
  -- 2. Detach
  USE master
  GO
  EXEC master.dbo.sp_detach_db @dbname = N'DBName'
  GO

  -- 3. Rename the physical files
  -- Do this in Windows Explorer. Right click file, Rename

   -- 4. Attach database
  USE [master]
  GO
  CREATE DATABASE DBName ON
  ( FILENAME = N'C:\MSSQL\DATA\DBName_Primary.mdf' ),
  ( FILENAME = N'C:\MSSQL\DATA\DBName_Data.ndf'),
  ( FILENAME = N'C:\MSSQL\LOG\DBName_Log.ldf' )
  FOR ATTACH
  GO

  -- 5. Put back in multi user mode 
  ALTER DATABASE DBName SET MULTI_USER
  GO


All done! 

Friendly reminder, don't do this during the day, while you have users on the system.  ;-)