Category Archives: Uncategorized

Changing Timezone from command line

It would be the case that you would need to change the time zone on the server but from the Time & Date option, it would be greyed out due to policy. There is a workaround for changing the timezone using the Command Prompt as Administrator.

Type tzutil /g to get the current time zone.

Type tzutil /l to list the available time zones.

Use tzutil /s "<timezone name>" to change the desired timezone.

 

How To: Automated shrink transaction logs in SQL

Making a scheduled automated shrink logs for the databases with specific space to shrink.

DECLARE @Step1 VARCHAR(200)
DECLARE @Name varchar(50)

DECLARE DBNames CURSOR
FOR
SELECT NAME FROM sysdatabases WHERE dbid > 4
OPEN DBNames

FETCH NEXT FROM DBNames INTO @Name WHILE (@@FETCH_STATUS <> -1)

BEGIN

SET @Step1 = 'USE ' + @Name + '' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(10)
SET @Step1 = @Step1 + 'DBCC SHRINKFILE (' +@Name + '_LOG, 500);' + CHAR(10)
SET @Step1 = @Step1 + 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(10)

EXEC (@Step1)

FETCH NEXT FROM DBNames INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames