December 28, 2004 at 2:07 pm
I normally Shrink my Databases by right clicking on the Database, selecting All Tasks, selecting Shrink Database and specifying 10 % for the 'Maximum free space in files after shrinking' Option and selecting the 'Move pages to beginning of file before shrinking' Option. I am looking for a way to schedule the Shrink Database Command with the 'Maximum free space in files after shrinking' Option and 'Move pages to beginning of file before shrinking' Option. Is there any way to do this?
Thanks in advance, Kevin
December 28, 2004 at 2:11 pm
Hello
Reference Books Online: DBCC SHRINKDATABASE. You can schedule a SQL Server Agent job to execute this command on your databases.
December 29, 2004 at 12:13 am
Here's the script to create a job that shrinks both the data file and the log file on TempDB. You can adapt it to suit your database.
Note that it basically uses a script from the MS site: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
for shrinking the transaction log and then uses a DBCC SHRINKFILE to shrink the data file. I run jobs like this on all my big databases about once per week.
Also note you will have to change the trusted login "YourDomain\YourLogin" to fit your own user login.
/****************** BEGIN SQL ****************/
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Transaction Log Shrink and Xtra Maint for TempDB')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Transaction Log Shrink and Xtra Maint for TempDB'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Transaction Log Shrink and Xtra Maint for TempDB'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Transaction Log Shrink and Xtra Maint for TempDB', @owner_login_name = N'YourDomain\YourLogin', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @notify_email_operator_name = N'FirstName LastName'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Shring_tempdb_trx_log', @command = N'/*=========================================================================
Title: Shrink SQL Server Transaction Log
Script SQL_Server_Trx_Log_Shrink.sql
Purpose: INF: How to Shrink the SQL Server 7.0 Transaction Log
Create/Update History:
5/13/2004 10:47:40 AM - My Name: Created;
Notes:
5/13/2004 10:47:40 My Name: Taken from MS site:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** check out the database file names:
-- sp_helpdb tempdb
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE tempdb -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = ''templog'', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 2, -- Limit on time allowed to wrap log.
@NewSize = 50 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT ''Original Size of '' + db_name() + '' LOG is '' +
CONVERT(VARCHAR(30),@OriginalSize) + '' 8K pages or '' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ''MB''
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ''BACKUP LOG [''+ db_name() + ''] WITH TRUNCATE_ONLY''
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES (''Fill Log'') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT ''Final Size of '' + db_name() + '' LOG is '' +
CONVERT(VARCHAR(30),size) + '' 8K pages or '' +
CONVERT(VARCHAR(30),(size*8/1024)) + ''MB''
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT ''*** Perform a full database backup ***''
SET NOCOUNT OFF', @database_name = N'tempdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'shrink_tempdb_datafile', @command = N'DBCC SHRINKFILE (TEMPDEV, 200)', @database_name = N'tempdb', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'every_Sat_800PM', @enabled = 1, @freq_type = 8, @active_start_date = 20041202, @active_start_time = 210000, @freq_interval = 72, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
/****************** END SQL **************/
G. Milner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply