0

I know. It's a stupid question, or at least a dangerous one, but my supervisors are having to restart DP jobs every time I run a LOG truncation job that reduces the size of our LDF file from what was 300GIG down to 5GIG.

This is because the "truncation job" detaches the database, changes it to SIMPLE mode, does its thing and then changes it back to FULL mode.

To save them having to remember or be reminded that my log truncation job has completed, they've asked me if I can force the entire windows server, not just the SQL Server and Agent, but the entire server to be rebooted.

Is that possible from within an T-SQL job?

Please don't get me started on why, I just need to know how.

Thanks

UPDATE

Here is the transaction job I am using, once a month to keep the log file small.

USE finprod

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE finprod

SET RECOVERY SIMPLE WITH NO_WAIT;

GO

-- Shrink the truncated log file to 5 gig

DBCC SHRINKFILE(finprod_log, 5024);  --file_name is the logical name of the file to be shrink

GO

-- Reset the database recovery model.

ALTER DATABASE finprod

SET RECOVERY FULL WITH NO_WAIT;

GO

-- now run a full backup, otherwise the next TxnLog backup will fail
EXEC msdb.dbo.sp_start_job N'FINPROD - Backups.Full Backup';
Fandango68
  • 103
  • 5
  • 1
    None of what you've stated makes sense to me in relation to a log truncate, which makes me think you're really talking about a DBCC SHRINKFILE. Is that the case? – joeqwerty Apr 09 '18 at 01:43
  • 1
    You can use a cmd script with your T-SQL job and reboot (shutdown -t 0 -r). Use start command with "/wait" switch for the T-SQL and after that "shutdown -t 0 -r" for example. – mariaczi Apr 09 '18 at 06:51
  • @joeqwerty no. I do not believe in SHRINKFILE. I've posted the transaction script in the question – Fandango68 Apr 10 '18 at 00:07
  • I don't understand. DBCC SHRINKFILE is right there in your script. – joeqwerty Apr 10 '18 at 00:35
  • @joeqwerty - dogh! So it is! hehehe... ok. Is there a better way please? – Fandango68 Apr 10 '18 at 00:50
  • @joeqwerty: the problem is, I have no choice but to use SHRINKFILE. I've mapped a plan to do regular TXN log backups on a daily basis, but the LDF file does not shrink. The above method (stolen I admit), was the only thing that worked. – Fandango68 Apr 10 '18 at 00:51
  • A transaction log backup on it's own won't shrink the physical log file. It's generally considered bad practice to perform a DBCC SHRINKFILE operation. Do you use the transaction log backups as part of your recovery plan? – joeqwerty Apr 10 '18 at 01:01
  • @joeqwerty yes. So what does shrink the logfile without doing the above? – Fandango68 Apr 10 '18 at 01:23

1 Answers1

0

I know this is an old question but...

Sounds to me like the log is not getting backed up. Remember, the only time log is backed up is by BACKUP LOG.

See https://www.sqlskills.com/blogs/paul/new-script-is-that-database-really-in-the-full-recovery-mode/.

Roy Latham
  • 101
  • 1