@echo off
rem ---------------------------------------------------------------------------
rem Mark's comment for TSQL script
rem ---------------------------------------------------------------------------
rem Bare bones database maintenance
rem Note: I have included a shrink database function
rem Note: Database backup is not included as this is a separate file that Lisa's team already has
rem Note: currently hardcoded for VPMSER - I will code a mode flexible version later
rem What this does:
rem   1. Remove all current users' running sessions from the VPMSER database
rem   2. Switch to single-user mode
rem   3. Check database integrity and fix simple issues (If error messages show here - there may be further effort required)
rem   4. Switch to VPMSER database
rem   5. Ensure system indexes are upto date
rem   6. Rebuild all indexes with 80% fill rate
rem   7. update statistics and adjust amounts based on current rows in each table
rem   8. Shrink the database (note: this is an optional step - too much of this could lead to file fragmentation, but it's useful to do sometimes)
rem   9. switch back to multiuser mode
rem  10. backup can now be performed - all users are allowed in
rem
rem ---------------------------------------------------------------------------
rem Comment for this batch file
rem ---------------------------------------------------------------------------
rem It only use trusted connection at the moment
rem ---------------------------------------------------------------------------

rem Extract commandline parameters
if "%1" == "" goto Syntax
if "%2" == "" goto Syntax

SET SQLSERVER=%1
SET DBNAME=%2
SET CSETSTATEMENTS="SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON SET CONCAT_NULL_YIELDS_NULL ON "
SET LOGFILEOUTTEMP="SpeedUpSqlDb_TMP.log"
SET LOGFILEOUTPUT="SpeedUpSqlDb.log"

echo.
echo Speeding up %DBNAME% on %SQLSERVER%
echo ** Testing connection to %DBNAME%
SET TSQL="select MAX(VersionNo) as CurrentVersion from ServerVersion"
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto Error

echo ** OK
echo.

rem Datetime stamp when started
setlocal ENABLEEXTENSIONS
call :GetDate y m d
call :GetTime h n s t
echo/Started at: %d%/%m%/%y% %h%:%n%:%s%



echo ** Remove all current users' running sessions
SET TSQL="declare @spid smallint, @s varchar(20); declare clist cursor for select spid from master..sysprocesses sp INNER JOIN master..sysdatabases sd ON sp.dbid=sd.dbid WHERE sd.name='%DBNAME%'  and spid>50; open clist; fetch next from clist into @spid; while @@fetch_status = 0 BEGIN SET @S='KILL '+CAST(@spid as varchar(10)); EXEC (@S); fetch next from clist INTO @spid; END close clist; deallocate clist;"
osql -S %SQLSERVER% -E -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto Error

echo ** Switch to single user mode
SET TSQL="ALTER DATABASE %DBNAME% SET SINGLE_USER WITH NO_WAIT"

rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto Error

echo ** Check database integrity
SET TSQL="%CSETSTATEMENTS:~1,-1% DBCC CHECKDB('%DBNAME%',REPAIR_REBUILD) WITH NO_INFOMSGS"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto ErrorInSingleUserMode

rem Reoganize index/Rebuild index
rem Microsoft recommends that indexes containing over 30% fragmentation be rebuilt, while indexes having less than 30% fragmentation be reorganized (take the easiest approach and rebuild)

echo ** Ensure system indexes are up to date
SET TSQL="%CSETSTATEMENTS:~1,-1% DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto ErrorInSingleUserMode

echo ** Reindex all tables
SET TSQL="%CSETSTATEMENTS:~1,-1% exec sp_MSforeachtable 'dbcc dbreindex (''?'', '''', 80) WITH NO_INFOMSGS'"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto ErrorInSingleUserMode

echo ** Updating statistics for all tables
SET TSQL="%CSETSTATEMENTS:~1,-1% exec sp_MSforeachtable 'UPDATE STATISTICS ? WITH RESAMPLE'"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto ErrorInSingleUserMode

echo ** Shrink database
SET TSQL="%CSETSTATEMENTS:~1,-1% DBCC SHRINKDATABASE('%DBNAME%',0)"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTPUT% -b
if errorlevel 1 goto ErrorInSingleUserMode

GOTO MultiUserMode
:ErrorInSingleUserMode
SET ELEVEL=1
::TYPE %LOGFILEOUTTEMP% > %LOGFILEOUTPUT%
echo ++ Restoring Multi-user mode for error - check file content

:MultiUserMode
echo ** Switch back to multi-user mode
SET TSQL="ALTER DATABASE %DBNAME% SET MULTI_USER WITH NO_WAIT"
rem echo %TSQL%
osql -S %SQLSERVER% -E -d %DBNAME% -Q %TSQL% -o %LOGFILEOUTTEMP% -b
IF "%ELEVEL%"=="1" goto Error

if errorlevel 1 goto Error



rem Datetime stamp when finsished
setlocal ENABLEEXTENSIONS
call :GetDate y m d
call :GetTime h n s t
echo/Finished successfully at: %d%/%m%/%y% %h%:%n%:%s%
echo.
echo.
pause "Press any key to finish"
goto End


:Syntax
echo.
echo SpeedUpSqlDb.bat [SERVER_NAME] [DATABASE_NAME]
echo where:
echo - [SERVER_NAME] is the SQL SEVER instance name
echo - [DATABASE_NAME] is the database name
echo.
goto End

:Error
echo.
echo ** Log file content
type %LOGFILEOUTPUT%
echo.
echo.
goto End

:End
goto :EOF

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:GetDate yy mm dd
::
:: By:   Ritchie Lawrence, 2002-06-15. Version 1.0
::
:: Func: Loads local system date components into args 1 to 3.
::       For NT4/2000/XP/2003.
::
:: Args: %1 var to receive year, 4 digits (by ref)
::       %2 var to receive month, 2 digits, 01 to 12 (by ref)
::       %3 Var to receive day of month, 2 digits, 01 to 31 (by ref)
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
setlocal ENABLEEXTENSIONS
set t=2&if "%date%z" LSS "A" set t=1
for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('echo/^|date') do (
  for /f "tokens=%t%-4 delims=.-/ " %%d in ('date/t') do (
    set %%a=%%d&set %%b=%%e&set %%c=%%f))
endlocal&set %1=%yy%&set %2=%mm%&set %3=%dd%&goto :EOF
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:GetTime hh nn ss tt
::
:: By:   Ritchie Lawrence, updated 2002-10-30. Version 1.2
::
:: Func: Loads local system time components into args 1 to 4.
::       For NT4/2000/XP/2003
::
:: Args: %1 Var to receive hours, 2 digits, 00 to 23 (by ref)
::       %2 Var to receive minutes, 2 digits, 00 to 59 (by ref)
::       %3 Var to receive seconds, 2 digits, 00 to 59 (by ref)
::       %4 Var to receive centiseconds, 2 digits, 00 to 99 (by ref)
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
setlocal ENABLEEXTENSIONS
for /f "tokens=5-8 delims=:. " %%a in ('echo/^|time') do (
  set hh=%%a&set nn=%%b&set ss=%%c&set cs=%%d)
if 1%hh% LSS 20 set hh=0%hh%
endlocal&set %1=%hh%&set %2=%nn%&set %3=%ss%&set %4=%cs%&goto :EOF
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::