Log file Shrinking (Single User Reqd?)

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  March 7, 2007 at 12:16 pm

  #172444

  Hello Experts

  I am new to SQL Server; Can i carry out the shrinking using Right Click or by command when the database is NOT in Single-User mode?

  I will take a database back up and log file backup prior to this.

  Thanks

• Patrick Folan

  Mr or Mrs. 500

  Points: 512

  More actions

  March 8, 2007 at 2:50 am

  #693744

  Hi

  Yes

  You can use this from SQL Query Analyzer...

  use master

  BACKUP LOG YOUR_DATABASE_NAME WITH TRUNCATE_ONLY

  use YOUR_DATABASE_NAME

  dbcc shrinkfile (YOUR_LOG_NAME, 0)

  Its a good idea to take a backup first...

• gk-411903

  Hall of Fame

  Points: 3801

  More actions

  March 8, 2007 at 12:10 pm

  #693905

  Why are you saying "use master" prior to BACKUP LOG YOUR_DATABASE_NAME WITH TRUNCATE_ONLY? I don't understand this part.. what is the role of the master database here? Last time i tried the following set of commands

  - use my_db

  - backup log my_db with truncate_only

  - dbcc shrinkfile(my_log_file_id_number,size_to_shrink_in_MB)

  Did i miss something?

  Thanks