Two Simple Ways to Find Last SQL Server Restart

The first command below will tell you the last time the SQL Server service was started. The second will tell you the last time the server itself was restarted.

-- Format will be:  2013-01-19 14:13:53.243 
select sqlserver_start_time from sys.dm_os_sys_info

-- Look for the row like:  Statistics since 1/18/2013 12:27:01 PM 
exec xp_cmdshell 'net statistics server'

Script to Create Multiple, Same-Sized Tempdb Files

This script will take a tempdb database with one database file, and create a set of equally-sized files. You can set how much total space you want the files to use, and how many files to have (in total).

From BOL, as a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs. Make each data file the same size; this allows for optimal proportional-fill performance.

"A more pragmatic approach however, is to have a 1:1 mapping between files and logical CPUs up to eight, and then add files if you continue to see allocation contention or if you're looking to push the I/O subsystem harder. The performance benefit from adding files diminishes each time, and in our experience, eight is the sweet spot, especially if you're implementing this as a pro-active measure." (Configuration Best Practices for SQL Server Tempdb--Multiple Files)

These calculations assume that currently the tempdb database has only one datafile. Also, the log file for tempdb is assumed to be on a different drive.

USE master
SET NOCOUNT ON

-- Enter your values here.  
RAISERROR('Comment out this line once you have entered your values here!', 20, 1) WITH NOWAIT, LOG

DECLARE @TempdbTotalSizeGB INT               SET @TempdbTotalSizeGB = 266
DECLARE @NumberOfDataFiles INT               SET @NumberOfDataFiles =   8
DECLARE @TempdbFolder      NVARCHAR(260)     SET @TempdbFolder      = 'E:\MSSQL_TEMPDB\'

-- Check that there is only one tempdb datafile.  
DECLARE @DataFileNum INT

SELECT @DataFileNum = COUNT(*)
  FROM msdb.sys.master_files
 WHERE database_id = DB_ID('tempdb')
   AND type_desc = 'ROWS'

IF @DataFileNum <> 1
    RAISERROR('Tempdb does not have exactly one file - cannot use this script', 20, 1) WITH NOWAIT, LOG

-- Check that the path exists. 
DECLARE @t TABLE (FileExists BIT, FileIsDirectory BIT, ParentDirectoryExists BIT)
INSERT @t EXEC master..xp_fileexist @TempdbFolder

IF NOT EXISTS (SELECT * FROM @t WHERE FileIsDirectory = 1)
    RAISERROR('Folder "%s" does not exist.', 20, 1, @TempdbFolder) WITH NOWAIT, LOG

-- Calculate the size of each tempdb datafile. We're leaving 10% free for extra space. YMMV. 
DECLARE @DataFileSizeGB INT
SET @DataFileSizeGB = ((9 * @TempdbTotalSizeGB) / 10) / @NumberOfDatafiles

-- First, modify the existing tempdb datafile's size. 
DECLARE @CrLf NVARCHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(4000)

SET @Sql = 'ALTER DATABASE tempdb '                                                                       + @CrLf
         + '   MODIFY FILE (NAME = ''tempdev'', '                                                         + @CrLf
         + '                SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                     + @CrLf
         + '                FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB)' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

-- Now create the additional datafiles for tempdb. 
SET @DataFileNum = 1

WHILE @DataFileNum < @NumberOfDatafiles
BEGIN
    SET @Sql = 'ALTER DATABASE tempdb '                                                                                 + @CrLf
             + '  ADD FILE (NAME = ''tempdev' + CAST(@DataFileNum AS NVARCHAR(50)) + ''', '                             + @CrLf
             + '            FILENAME = ''' + @TempdbFolder + 'tempdb' + CAST(@DataFileNum AS NVARCHAR(50)) + '.mdf'', ' + @CrLf
             + '            SIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB, '                                   + @CrLf
             + '            FILEGROWTH = 0KB, MAXSIZE = ' + CAST(@DataFileSizeGB AS NVARCHAR(50)) + 'GB) '              + @CrLf

    RAISERROR(@Sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @Sql
    SET @DataFileNum = @DataFileNum + 1
END

-- Finally, set the logfile's autogrowth for the tempdb database. The Microsoft  
-- default of 1% is not best practice. 
DECLARE @LogFileSizeGB INT
SET @LogFileSizeGB = 1

SET @Sql = 'ALTER DATABASE tempdb '                                                                            + @CrLf
         + '   MODIFY FILE (NAME = ''templog'', FILEGROWTH = ' + CAST(@LogFileSizeGB AS NVARCHAR(50)) + 'GB) ' + @CrLf

RAISERROR(@Sql, 10, 1) WITH NOWAIT
EXEC sp_executesql @Sql

GO

Run DBCC CHECKDB Against All Databases on Server

This script runs DBCC CHECKDB against every database on the server. It starts off with the simplest and fastest commands, and works up the the most stringent test. Note that TABLOCK keyword is commented out - you can use it at your own discretion. The commands that actually repair the database can't be run accidentally, and the version that can allow data loss - aptly named REPAIR_ALLOW_DATA_LOSS - is left as an exercise for the reader. (In other words, I'm not giving you that particular big, sharp knife!)

-- Run DBCC CHECKDB commands against all databases, including system. 
-- Read-only checks are run automatically. For safety's sake, to run 
-- the "repair" checks, you have to highlight that section and run it. 
-- SQL Server 2005+ 

set nocount on
declare @Sql nvarchar(4000)


--------------------------------------- 
--           READ-ONLY CHECKS           
--------------------------------------- 

-- Do quickest check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* ?? TABLOCK, */ PHYSICAL_ONLY, ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now check things beside what PHYSICAL_ONLY does: the integrity of the 
-- physical structure of the page and record headers, and the consistency 
-- between the pages' object ID and index ID and the allocation structures. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now also check the indexes. This is the most thorough check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'') WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

go


--------------------------------------- 
--           READ-WRITE CHECKS          
--                                      
-- "Use the REPAIR options only as a    
-- last resort. To repair errors, we    
-- recommend restoring from a backup."  
--------------------------------------- 

raiserror('    *** THESE ARE READ-WRITE CHECKS - BE SURE YOU KNOW WHAT YOU ARE DOING!', 16, 1) with nowait

set nocount on
declare @Sql nvarchar(4000)

-- Performs both minor, quick repairs, such as repairing extra keys in 
-- nonclustered indexes, and time-consuming repairs such as rebuilding 
-- indexes. These repairs can be performed without risk of data loss. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', REPAIR_REBUILD) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- If you want to use REPAIR_ALLOW_DATA_LOSS, you'll have to write your own command! 

go