How to shrink the database using the command line

Issue

Microsoft SQL Express has a size limit. When a database reaches that limit, you must shrink the database.

Solution

1. Back up the database.
   1. Open the command prompt as an administrator.
   2. Run the following command:
      dbupdater "USE database_name; BACKUP DATABASE database_name TO DISK = 'backup_location\Symphony.bak'WITH FORMAT,MEDIANAME = 'database_name',NAME = 'FBdatabase_name';"
      Where database_name is the name of the Symphony database and backup_location is a valid file path for the backup database.
2. Shrink the database.
   1. Open the command prompt as an administrator.
   2. Run the following command:

      dbupdater "DBCC SHRINKFILE('database_name_log', 10)"

      Where database_name is the name of the Symphony database.
   3. Run the following command:
      dbupdater "DBCC SHRINKDATABASE (database_name)"
      Where database_name is the name of the Symphony database.
3. Purge the archive logs.
   1. Open the command prompt as an administrator.
   2. Run the following command:
      dbupdater "sp_DeleteEventLogArchive 'date'"
      Where date is the date after which to remove archive logs. Senstar suggests 30 or 90 days prior to the current date.
4. Truncate the eventlog and eventlogdata tables.
   1. Open the command prompt as an administrator.
   2. Run the following command:
      dbupdater "truncate table eventlogdata"
   3. Run the following command:
      dbupdater "truncate table eventlog"
   4. Run the following command:
      dbupdater "ALTER TABLE EventLogData DROP CONSTRAINT FK_EventLogData_EventLog;TRUNCATE TABLE EventLogData;TRUNCATE TABLE EventLog;ALTER TABLE EventLogData WITH CHECK ADD CONSTRAINT FK_EventLogData_EventLog FOREIGN KEY (EventID) REFERENCES EventLog (ID);"
5. Shrink the database.
   1. Open the command prompt as an administrator.
   2. Run the following command:
      dbupdater "DBCC SHRINKFILE('database_name_log', 10)"
      Where database_name is the name of the Symphony database.
   3. Run the following command:
      dbupdater "DBCC SHRINKDATABASE (database_name)"
      Where database_name is the name of the Symphony database.
6. Run the Symphony setup wizard.