SQL Server Performance Forum – Threads Archive
how to write manual maintanance plan?
I am writing manual Maintanance planIs this will be okey?
Could someone help with optimization job? It includes: 1.Backup and shring transaction log(DAILY)
backup log + @db_name + ‘ to disk=”’[email protected] + ””
use @db_name
dbcc shrinkfile (‘ + @db_filename + ‘,’ + @shrink_value + ‘)’ 2.Backup database(DAILY)
BACKUP DATABASE @DB_Name TO [email protected] WITH INIT,RETAINDAYS=1 3.Optimisation database(daily) SQL wizard’s Maintainance Plan says:
-reorganize data and index pages
-change free space per page persantage to 10%
-remove unused space from database file
shrink db when it grows 50mb
amount of free space remains =10% 4.DBCC CHECKDB (‘dbname’, NOINDEX )
1) Update Statistics, critical tables every day, all database each week.
2) Reorganize data and index pages: include rebuild index for critical tables?.
3) Instead shirink db, I prefere a fix Mg to grow and not automatically. Ok with the rest. Luis Martin
I would go for back up of transaction log every 15 minutes using a job. This will reduce my recovery time (time needed incase I need to restore the database) and the chanced of loosing the data will be minimal. The transaction log size will also stay in limits. I would change the Retentiom period of backup to 2 or 3 days and create the backup on a new file everyday deleting the 2-3 day old file. This will help in error hanndling. As imagine u were trying to backup the data and ur server crached. This would mean, in the current scenario, you dn’t have even the last days backup (as it was initialized) at least on the machine. What is the size of the database? Check running all the operations on the database and see if they are completed during the allocated time window. Otherwise you can move some of the stuff to weekly job. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
900 database aprox. 100 MB each.
I think server will be dead if i schedule transaction log backp for 15 min
Does someone has script for optimization job?
I suggest you to create your own plan for backups on database and Tlog and rest of them can be utilised using MAINTENANCE PLAN (XP_SQLMAINT) this gives you option of running multiple choices for the plan. As the same referrd in this link http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp.
HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
thank you very much
This is exactly what i need !!!!!!!!!
i would like my backup files to be deleted everyday
it’s not happening with this statement:
Am i wrong? the @DB_Name will be always unique BACKUP DATABASE @DB_Name TO [email protected] WITH INIT,RETAINDAYS=1,NOSKIP
The backup media is not overwritten if any one of the following conditions is met: All backup sets on the media have not yet expired. For more information, see the EXPIREDATE and RETAINDAYS options.
The backup set name given in the BACKUP statement, if provided, does not match the name on the backup media. For more information, see the NAME clause.
Use the SKIP option to override these checks. For more information about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the Remarks section. Note If the backup media is password protected, SQL Server does not write to the media unless the media password is supplied. This check is not overridden by the SKIP option. Password-protected media may be overwritten only by reformatting it. For more information, see the FORMAT option. From SQL Server Books Online. Luis Martin
IT’S NOT WORKING!
there mustbe something related to name or medianame
Sorry, I use BACKUP DATABASE [Bark] TO DISK = N’F:BackupBark Backup01′, DISK = N’G:BackupBark Backup02′ WITH INIT , NOUNLOAD , NAME = N’Bark backup’, NOSKIP , STATS = 10, NOFORMAT and work fine. Luis Martin
here is my exact statement:
BACKUP DATABASE test TO disk=’c:\test est_db_200308071601.bak’ WITH INIT,RETAINDAYS=1,NOSKIP,NAME=’test_db_’,NOFORMAT I am lost and will go home
BTW, I assume the name of the file should be the same for the recyle to happen. If the file name is changind, it would simply create a new file with the name specified and won’t delete the old backup. If you want to maintain the file name convention, manually delete the file using xp_cmdshell ‘Delete …’. Else chnage the file name and always take backup on the same physical file. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
BOL refers :
Use the media retention option to provide a system-wide default for the length of time to retain each backup medium after it has been used for a database or transaction log backup. media retention helps protect backups from being overwritten until the specified number of days has elapsed. When you set media retention, you do not have to specify the length of time to retain system backups each time you perform a backup. The default is 0 days. If you use the backup medium before the set number of days has passed, Microsoft® SQL Server™ issues a warning message. SQL Server does not issue a warning unless you change the default. This option can be overridden by using the RETAINDAYS clause of the BACKUP statement. media retention is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change media retention only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
If the name of backkup file have to be the same what the reson then to even have Retaindays?
My idea is to keep in folder 1 ->>All bakups
db1_200301_011300.bak
db2_200301_011300.bak
db3_200301_011300.bak
next daydb1_200301_021300.bak
db2_200301_021300.bak
db3_200301_021300.bak
As sayta poited out the idea of having a retaindays parameter is to avoid accidental overwriting of database backup. So in case u take another backup (on the same device which menad the same file name) the old backup will be preserved. In your case since you are naming the files due to dates, you don’t need to set this option. The backup process will not delete the old files if the name has changed. So as I said before you’ll have to delete the backups manually. TIP: You can get the file names for the backups generated the previous day from the msdb..backupset table. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
this is what i was looking for:
CREATE procedure dbo.usp_fullBackups
As
/*Created by Patty Dunbar 6/3/03*/
/*Procedure to check recovery mode of database
and will backup all databases set to full recovery mode*/
declare @dbname varchar(50), @dbid smallint
declare @svr varchar(50), @cmd nvarchar(255) declare dbnamecur cursor for
select [name], dbid, @@servername as server from dbo.sysdatabases
where name not in (‘master’, ‘msdb’, ‘model’ )
AND CONVERT(sysname, DatabasePropertyEx(name,
‘Status’))<>’OFFLINE’
–AND CONVERT(sysname, DatabasePropertyEx(name, ‘Recovery’)) <>’Simple’ open dbnamecur
Fetch next from dbnamecur into
@dbname, @dbid, @svr While @@fetch_status = 0
Begin
–Path change 1, change -BkupDB path to location of full backups
select @cmd = ‘exec xp_sqlmaint ‘ + char(39) + ‘-S’ + char(32) + @svr + char(32) +
‘-D’ + char(32) + @dbname + char(32) + ‘ -BkUpDB "c: estBackupFullDatabase"
-BkUpMedia DISK -DelBkUps 1days -Rpt "c: estDB Maintenance Plan.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup ‘ + char(39)
–change retention date 1 next to -DelBkups i.e. 2days, 2weeks, etc. — Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
–Print ‘database ‘ + @dbname + ‘ has full recovery and has been backed up on ‘ + @svr Fetch next from dbnamecur into
@dbname, @dbid, @svr
END
close dbnamecur
deallocate dbnamecur
GO
]]>