Differential maintenance in plan sql 2000: setup

Hello!
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say
There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say

Differential maintenance in plan sql 2000: setup

Hello!
I'm trying to setup a differential maintenance plan to backup all
users databases on sql 2000 using t-sql (or not)
I can't figure out or find how to include "all user databases" like i
would using a maintenance plan. This is a cake to do in sql 2005 but
sql 2000.... well lets just say :(There are maint plans in 2000, which you reach from the Maintenance Plans folder in EM. A 2000 maint
plan do not include diff backups, though.
Right-licking a db and select Backup and from there selecting Schedule do *not* create a maint plan.
So if you want to include stuff like naming the backup file with time stamp and deletion of old
backups, you have to do it yourself. There are plenty of code out there to get your started, for
instance below (2005, but adaptable for 2000):
http://www.karaszi.com/SQLServer/util_backup_script_like_MP.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scottg" <scottgranado@.gmail.com> wrote in message
news:f9f653f3-f0c6-4cb8-ae3e-4694a380293f@.n58g2000hsf.googlegroups.com...
> Hello!
> I'm trying to setup a differential maintenance plan to backup all
> users databases on sql 2000 using t-sql (or not)
> I can't figure out or find how to include "all user databases" like i
> would using a maintenance plan. This is a cake to do in sql 2005 but
> sql 2000.... well lets just say :(

Differential Backups Produce 55GB Files

We have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.

I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.

Has anyone experienced this issue before?
What causes it?
How do we resolve it?

Thank you in advance for your help,
Greg
if the log backups occurs every 1 hr you dont need to schedule the differential backup.......moreover check if any process is running at the time of backup......generally if you schedule the backups frequently the log files shudnt grow as high in your case........|||

I'm not worries about the size of the log files. The logs are fine. This post regards the size of the differential backup file itself (55GB). We don't really need it, true, but its much easier to restore one diff backup over six logs in my humble opinion, but this is really beside the point.

|||Was there any unusual activity over the weekend? Someone doing a bunch of bulks loads or index rebuilds for instance?|||Are you appending your differential backups to the same file, or creating a new file for each differential backup?|||Yes. Over the weekend we usually do an index rebuild, and some heavy inserts. Our database is mirrored as well, so we're not able to actually do the heavy inserts as a true bulk insert.|||Each backup creates a new file. A secondary process on another machine copies the files over to another server.|||

most probably this is coz of the activities in the weekend. When u rebiult index it take extraspace to create the fresh index . the existing index are not overwritten beacuse if system needs the existing index to rollback if necessary. Differential backup take the backup of all the extents changed from last full backup. So what i would prefer is... take full backup after the weekend maintenance instead of Differential(if you have maintenance window).

Madhu

Differential Backups in Maintenance Plans

Using SQL Server2000, I don't see any way of setting up a maint plan to
include nightly differential backups. I would like to use a maint plan as it
allows you to select "All user DBs". Since we add databases quite a lot, I
like the dynamic aspect of maint plans.
Does anyone know of workaround or even a script that would allow me to
dynamically backup each user database? I've looked at sp_msforeachdb, but
can't seem to get it to work as I'm not too good at scripting.
Thanks
RonRon,
In SQL Server 2000, as you have discovered, there is no way to do
differential backups. Technet when discussing SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
describes how to set up differential backups one database at a time,
creating a schedule for each differential backup. By the way, please note
that _master_ cannot be backed up differentially.
You, of course, do not want to do that, but if you do it for one database it
will give you the working syntax. E.g.
BACKUP DATABASE MyDatabase TO
DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
Now, from that perhaps you can create a script from that. Here is one that
only PRINTs the command, but you can change this to EXECUTE it instead.
sp_msforeachdb @.command1='
DECLARE @.BuildStr NVARCHAR(500)
SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
'',''''),'':'',''''),''-'','''')
SET @.BuildStr = ''
Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
SET @.BuildStr = @.Buildstr + '''
WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
SET @.BuildStr = @.Buildstr + ''
NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
IF ''$''<>''master''
PRINT @.BuildStr'
,@.replacechar='$'
Of course, the backups need to be in the same location as other backups for
your maintenance plan deletion of old files to include these as well. Also,
remember that sp_msforeachdb is unsupported.
RLF
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> Using SQL Server2000, I don't see any way of setting up a maint plan to
> include nightly differential backups. I would like to use a maint plan as
> it
> allows you to select "All user DBs". Since we add databases quite a lot,
> I
> like the dynamic aspect of maint plans.
> Does anyone know of workaround or even a script that would allow me to
> dynamically backup each user database? I've looked at sp_msforeachdb, but
> can't seem to get it to work as I'm not too good at scripting.
> Thanks
> Ron|||Thank you Russell!
"Russell Fields" wrote:
> Ron,
> In SQL Server 2000, as you have discovered, there is no way to do
> differential backups. Technet when discussing SQL Server 2000
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ELYAG
> describes how to set up differential backups one database at a time,
> creating a schedule for each differential backup. By the way, please note
> that _master_ cannot be backed up differentially.
> You, of course, do not want to do that, but if you do it for one database it
> will give you the working syntax. E.g.
> BACKUP DATABASE MyDatabase TO
> DISK = N'\\BackupServer\MyDatabase_diff_200710230021.BAK'
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,
> NAME = N'MyDatabase backup', NOSKIP , STATS = 10, NOFORMAT
> Now, from that perhaps you can create a script from that. Here is one that
> only PRINTs the command, but you can change this to EXECUTE it instead.
> sp_msforeachdb @.command1='
> DECLARE @.BuildStr NVARCHAR(500)
> SET @.BuildStr = CONVERT(NVARCHAR(20),GETDATE(),120)
> SET @.BuildStr = REPLACE(REPLACE(REPLACE(@.BuildStr,''
> '',''''),'':'',''''),''-'','''')
> SET @.BuildStr = ''
> Backup Database $ TO DISK = N''''\\BackupServer\$_diff_''+@.BuildStr
> SET @.BuildStr = @.Buildstr + '''
> WITH NOINIT , NOUNLOAD , DIFFERENTIAL ,''
> SET @.BuildStr = @.Buildstr + ''
> NAME = N''''$ backup'''', NOSKIP , STATS = 10, NOFORMAT ''
> IF ''$''<>''master''
> PRINT @.BuildStr'
> ,@.replacechar='$'
> Of course, the backups need to be in the same location as other backups for
> your maintenance plan deletion of old files to include these as well. Also,
> remember that sp_msforeachdb is unsupported.
> RLF
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:44788552-025A-4A26-8387-F31F069AE7D2@.microsoft.com...
> > Using SQL Server2000, I don't see any way of setting up a maint plan to
> > include nightly differential backups. I would like to use a maint plan as
> > it
> > allows you to select "All user DBs". Since we add databases quite a lot,
> > I
> > like the dynamic aspect of maint plans.
> >
> > Does anyone know of workaround or even a script that would allow me to
> > dynamically backup each user database? I've looked at sp_msforeachdb, but
> > can't seem to get it to work as I'm not too good at scripting.
> >
> > Thanks
> >
> > Ron
>
>

differential backups

Hi,
Running SQL 7. We have a maintanence plan doing transaction log backups
every hour. Outside of the maintanence plan we have a job that does a
differential backup of the database. My question is, when the differential
backup occurs, is the transaction log truncated? For example if the last
transaction log backup was at 1pm, and a differential at 1:30pm, then
another transaction log backup at 2pm; will the 2pm transaction log backup
contain the transactions at 1:15pm?
Secondly, how can I modify the differential backup job below to:
a) name the filename of the diff backup file to be
<database_name>_diff_MMDDYYhhmmss.bak
b) have the script remove diff backup files older than 1 day.oops! here is the script I need to modify that I referred to below:
BACKUP DATABASE [myDB] TO DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH INIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'myDB latest diff backup', SKIP , STATS = 10,
DESCRIPTION = N'every 5 hour diff', NOFORMAT DECLARE @.i INT
select @.i = position from msdb..backupset where database_name='myDB'and
type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset
where database_name='myDB')
RESTORE VERIFYONLY FROM DISK =N'F:\hot_backups\myDB\myDB_db_latest_diff.BAK' WITH FILE = @.i
"aaz" <aaz@.webcapacity.com> wrote in message
news:uSP7mY$SDHA.3188@.tk2msftngp13.phx.gbl...
> Hi,
> Running SQL 7. We have a maintanence plan doing transaction log backups
> every hour. Outside of the maintanence plan we have a job that does a
> differential backup of the database. My question is, when the differential
> backup occurs, is the transaction log truncated? For example if the last
> transaction log backup was at 1pm, and a differential at 1:30pm, then
> another transaction log backup at 2pm; will the 2pm transaction log backup
> contain the transactions at 1:15pm?
> Secondly, how can I modify the differential backup job below to:
> a) name the filename of the diff backup file to be
> <database_name>_diff_MMDDYYhhmmss.bak
> b) have the script remove diff backup files older than 1 day.
>|||Azz
Running a differential backup does not truncate the
transaction log, it just records all the changes to the
database sine the last full backup.
Bear in mind you should not just do differential backups,
you should do a full backup as well as part of you backup
strategy. How often depends on the size of your database
and how dynamic the data is.
If you do not do full backups eventually your differential
will be as large and take as long as a full backup, and
you will still need a full backup to use it.
Regards
John|||yeah we are also doing fulls once a day, diffs 2x, then transaction logs
every hour. I just wanted to make sure that running the differential did not
truncate the transaction logs.
can anyone help with the second 1/2 of my question?
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> Azz
> Running a differential backup does not truncate the
> transaction log, it just records all the changes to the
> database sine the last full backup.
> Bear in mind you should not just do differential backups,
> you should do a full backup as well as part of you backup
> strategy. How often depends on the size of your database
> and how dynamic the data is.
> If you do not do full backups eventually your differential
> will be as large and take as long as a full backup, and
> you will still need a full backup to use it.
> Regards
> John|||Here is a sample to start from:
-- Separate file for each day of the week --
DECLARE @.DBName NVARCHAR(50), @.Device NVARCHAR(100), @.Name NVARCHAR(100)
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL
DROP TABLE #DBs
CREATE TABLE #DBs ([name] VARCHAR(50),[db_size] VARCHAR(20),
[Owner] VARCHAR(20),[DBID] INT, [Created] VARCHAR(14),
[Status] VARCHAR(1000), [Compatibility_Level] INT)
INSERT INTO #DBs EXEC sp_helpdb
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [Name]
FROM #DBs
WHERE [DBID] IN (5,6)
OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @.DBName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Device = N'C:\MVP\Backups\DD_' + @.DBName + '_Full_' +
CAST(DAY(GETDATE()) AS NVARCHAR(4)) +
CAST(MONTH(GETDATE()) AS NVARCHAR(4)) +
CAST(YEAR(GETDATE()) AS NVARCHAR(8)) + N'.BAK'
SET @.Name = @.DBName + N' Full Backup'
BACKUP DATABASE @.DBName TO DISK = @.Device WITH INIT , NOUNLOAD ,
NAME = @.Name, NOSKIP , STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @.Device WITH FILE = 1
FETCH NEXT FROM cur_DBs INTO @.DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
-- Removing Older Backup Files --
DECLARE @.Error INT, @.D DATETIME
SET @.D = CAST('20020801 15:00:00' AS DATETIME)
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD D:\Backups\*.trn'
SET @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
--SELECT * FROM #dirList
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.TRN'
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "D:\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec master..xp_cmdshell @.Delete
IF @.@.RowCount > 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
-- PRINT @.Delete
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly
SQL Server MVP
"aaz" <aaz@.webcapacity.com> wrote in message
news:Owh8jYHTDHA.2460@.TK2MSFTNGP10.phx.gbl...
> yeah we are also doing fulls once a day, diffs 2x, then transaction logs
> every hour. I just wanted to make sure that running the differential did
not
> truncate the transaction logs.
> can anyone help with the second 1/2 of my question?
> "John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
> news:093f01c34c3e$2afca6b0$a101280a@.phx.gbl...
> > Azz
> >
> > Running a differential backup does not truncate the
> > transaction log, it just records all the changes to the
> > database sine the last full backup.
> >
> > Bear in mind you should not just do differential backups,
> > you should do a full backup as well as part of you backup
> > strategy. How often depends on the size of your database
> > and how dynamic the data is.
> >
> > If you do not do full backups eventually your differential
> > will be as large and take as long as a full backup, and
> > you will still need a full backup to use it.
> >
> > Regards
> >
> > John
>

Differential backup with database maintenance plan

Hello,
I need to take daily differential backups of eight
databases. I was thinking of using a DB Maintenance Plan,
but the problem is it allows only full backups. Is there a
simple way of taking differential backups of multiple
db's? For example is it possible to modify the full backup
query of DB Maintenance Plan and use it as differential?
The query of DB Maintenance Plan full backup is like below:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
-CrBkSubDir -BkExt "BAK"'
ThanksMaint plans doesn't support diff backups. Db Maint does (www.dbmaint.com),
or write your own TSQL command and schedule them using Agent, quite simply
:-).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
news:2da501c3e183$8843dca0$a401280a@.phx.gbl...
> Hello,
> I need to take daily differential backups of eight
> databases. I was thinking of using a DB Maintenance Plan,
> but the problem is it allows only full backups. Is there a
> simple way of taking differential backups of multiple
> db's? For example is it possible to modify the full backup
> query of DB Maintenance Plan and use it as differential?
> The query of DB Maintenance Plan full backup is like below:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
> 43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
> BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
> -CrBkSubDir -BkExt "BAK"'
> Thanks|||Hi,
Here is a link to T-SQL code wrote by Uma Chandar - MVP.
Script to do Differential backups on weekdays, Full on
sundays & filenames with timestamp info
http://www.umachandar.com/technical/SQL70Scripts/Main30.htm
as Tibor said you can schedule this script by SQL Agent.
HTH
Regards
THIRUMAL REDDY MARAM
Sys Admin/ SQL Server DBA
>--Original Message--
>Maint plans doesn't support diff backups. Db Maint does
(www.dbmaint.com),
>or write your own TSQL command and schedule them using
Agent, quite simply
>:-).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
>news:2da501c3e183$8843dca0$a401280a@.phx.gbl...
>> Hello,
>> I need to take daily differential backups of eight
>> databases. I was thinking of using a DB Maintenance
Plan,
>> but the problem is it allows only full backups. Is
there a
>> simple way of taking differential backups of multiple
>> db's? For example is it possible to modify the full
backup
>> query of DB Maintenance Plan and use it as differential?
>> The query of DB Maintenance Plan full backup is like
below:
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
>> 43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
>> BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
>> -CrBkSubDir -BkExt "BAK"'
>> Thanks
>
>.
>|||Or you can simply create a job with this script:
BACKUP DATABASE [yourdb] TO DISK
= 'backupdrive\yourdb.bak' WITH INIT, NAME = N'yourdb
backup'
GO
BACKUP DATABASE [yourdb2] TO DISK
= 'backupdrive\yourdb.bak' WITH INIT, NAME = N'yourdb2
backup'
GO
--and so on and so forth--
>--Original Message--
>Hello,
>I need to take daily differential backups of eight
>databases. I was thinking of using a DB Maintenance Plan,
>but the problem is it allows only full backups. Is there
a
>simple way of taking differential backups of multiple
>db's? For example is it possible to modify the full
backup
>query of DB Maintenance Plan and use it as differential?
>The query of DB Maintenance Plan full backup is like
below:
>EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
>43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
>BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
>-CrBkSubDir -BkExt "BAK"'
>Thanks
>.
>

Differential backup with database maintenance plan

Hello,
I need to take daily differential backups of eight
databases. I was thinking of using a DB Maintenance Plan,
but the problem is it allows only full backups. Is there a
simple way of taking differential backups of multiple
db's? For example is it possible to modify the full backup
query of DB Maintenance Plan and use it as differential?
The query of DB Maintenance Plan full backup is like below:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
-CrBkSubDir -BkExt "BAK"'
ThanksMaint plans doesn't support diff backups. Db Maint does (www.dbmaint.com),
or write your own TSQL command and schedule them using Agent, quite simply
:-).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
news:2da501c3e183$8843dca0$a401280a@.phx.gbl...

quote:

---

> Hello,
> I need to take daily differential backups of eight
> databases. I was thinking of using a DB Maintenance Plan,
> but the problem is it allows only full backups. Is there a
> simple way of taking differential backups of multiple
> db's? For example is it possible to modify the full backup
> query of DB Maintenance Plan and use it as differential?
> The query of DB Maintenance Plan full backup is like below:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 8CD36FED-A3BC-
> 43D1-91E5-33A8C02A3FEA -WriteHistory -VrfyBackup -
> BkUpMedia DISK -BkUpDB "C:\DB\Backup\2004\full"
> -CrBkSubDir -BkExt "BAK"'
> Thanks

---

|||Hi,
Here is a link to T-SQL code wrote by Uma Chandar - MVP.
Script to do Differential backups on weekdays, Full on
sundays & filenames with timestamp info
http://www.umachandar.com/technical...ipts/Main30.htm
as Tibor said you can schedule this script by SQL Agent.
HTH
Regards
THIRUMAL REDDY MARAM
Sys Admin/ SQL Server DBA

quote:

---

>--Original Message--
>Maint plans doesn't support diff backups. Db Maint does

---

(www.dbmaint.com),

quote:

---

>or write your own TSQL command and schedule them using

---

Agent, quite simply

quote:

---

>:-).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

---

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

---

>
>"keremcan" <kbuyuktaskin@.yahoo.com> wrote in message
>news:2da501c3e183$8843dca0$a401280a@.phx.gbl...
Plan,[QUOTE]
there a[QUOTE]
backup[QUOTE]
below:[QUOTE]
>
>.
>

---

Differential Backup Restore Problem

I have a maintenance plan for a database Where I have a
full database backups on every sunday and a differential
backup every evening and transaction log backups every
hour (7:00 Am - 7:00 Pm).
I have a backup server where I restore my backups. After I
restore my full backup, I restore the differential backup
then the transaction log backups. I created a job to
restore my backups but it fails after the first
differential backup (Tuesday's differential backup). It
restore the first differential backup after the full
backup but not the second one or the others after the
first one. I only have transaction log backups in between.
When I restore the second differential backup manually, I
see that the first differential backup is selected in
the 'view contents' box, anotherwords, it is trying to
restore the first differential backup with the second diff
backup file.
Is there a way to restore diff. backups in a sequence '
Thanks for any info........Here's some info from BOL's, you need to specify 'with
file' and the number. If you have three, you would restore
increase the file number for each differential.
Hope that helps... here's the examples from BOL
--
This example restores a database, differential database,
and transaction log backup of the MyNwind database.
-- Assume the database is lost at this point. Now restore
the full
-- database. Specify the original full backup and
NORECOVERY.
-- NORECOVERY allows subsequent restore operations to
proceed.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY
GO
-- Now restore the differential database backup, the
second backup on
-- the MyNwind_1 backup device.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH FILE = 2,
NORECOVERY
GO
-- Now restore each transaction log backup created after
-- the differential database backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
>--Original Message--
>I have a maintenance plan for a database Where I have a
>full database backups on every sunday and a differential
>backup every evening and transaction log backups every
>hour (7:00 Am - 7:00 Pm).
>I have a backup server where I restore my backups. After
I
>restore my full backup, I restore the differential backup
>then the transaction log backups. I created a job to
>restore my backups but it fails after the first
>differential backup (Tuesday's differential backup). It
>restore the first differential backup after the full
>backup but not the second one or the others after the
>first one. I only have transaction log backups in between.
>When I restore the second differential backup manually, I
>see that the first differential backup is selected in
>the 'view contents' box, anotherwords, it is trying to
>restore the first differential backup with the second
diff
>backup file.
>Is there a way to restore diff. backups in a sequence '
>Thanks for any info........
>.
>|||Then, How can this be an automated process? I am using a
disk backup............
>--Original Message--
>Here's some info from BOL's, you need to specify 'with
>file' and the number. If you have three, you would restore
>increase the file number for each differential.
>Hope that helps... here's the examples from BOL
>--
>This example restores a database, differential database,
>and transaction log backup of the MyNwind database.
>-- Assume the database is lost at this point. Now restore
>the full
>-- database. Specify the original full backup and
>NORECOVERY.
>-- NORECOVERY allows subsequent restore operations to
>proceed.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH NORECOVERY
>GO
>-- Now restore the differential database backup, the
>second backup on
>-- the MyNwind_1 backup device.
>RESTORE DATABASE MyNwind
> FROM MyNwind_1
> WITH FILE = 2,
> NORECOVERY
>GO
>-- Now restore each transaction log backup created after
>-- the differential database backup.
>RESTORE LOG MyNwind
> FROM MyNwind_log1
> WITH NORECOVERY
>GO
>RESTORE LOG MyNwind
> FROM MyNwind_log2
> WITH RECOVERY
>GO
>
>>--Original Message--
>>I have a maintenance plan for a database Where I have a
>>full database backups on every sunday and a differential
>>backup every evening and transaction log backups every
>>hour (7:00 Am - 7:00 Pm).
>>I have a backup server where I restore my backups. After
>I
>>restore my full backup, I restore the differential
backup
>>then the transaction log backups. I created a job to
>>restore my backups but it fails after the first
>>differential backup (Tuesday's differential backup). It
>>restore the first differential backup after the full
>>backup but not the second one or the others after the
>>first one. I only have transaction log backups in
between.
>>When I restore the second differential backup manually,
I
>>see that the first differential backup is selected in
>>the 'view contents' box, anotherwords, it is trying to
>>restore the first differential backup with the second
>diff
>>backup file.
>>Is there a way to restore diff. backups in a sequence '
>>Thanks for any info........
>>.
>.
>|||Why would you want to restore diff backups in sequence? The only one needed is the last diff backup
and then all subsequent log backups. Or did I misunderstand your situation?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
> I have a maintenance plan for a database Where I have a
> full database backups on every sunday and a differential
> backup every evening and transaction log backups every
> hour (7:00 Am - 7:00 Pm).
> I have a backup server where I restore my backups. After I
> restore my full backup, I restore the differential backup
> then the transaction log backups. I created a job to
> restore my backups but it fails after the first
> differential backup (Tuesday's differential backup). It
> restore the first differential backup after the full
> backup but not the second one or the others after the
> first one. I only have transaction log backups in between.
> When I restore the second differential backup manually, I
> see that the first differential backup is selected in
> the 'view contents' box, anotherwords, it is trying to
> restore the first differential backup with the second diff
> backup file.
> Is there a way to restore diff. backups in a sequence '
> Thanks for any info........|||That is exacly what I want but not manually. I want to
automate this process..............
>--Original Message--
>Why would you want to restore diff backups in sequence?
The only one needed is the last diff backup
>and then all subsequent log backups. Or did I
misunderstand your situation?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
>> I have a maintenance plan for a database Where I have a
>> full database backups on every sunday and a differential
>> backup every evening and transaction log backups every
>> hour (7:00 Am - 7:00 Pm).
>> I have a backup server where I restore my backups.
After I
>> restore my full backup, I restore the differential
backup
>> then the transaction log backups. I created a job to
>> restore my backups but it fails after the first
>> differential backup (Tuesday's differential backup). It
>> restore the first differential backup after the full
>> backup but not the second one or the others after the
>> first one. I only have transaction log backups in
between.
>> When I restore the second differential backup manually,
I
>> see that the first differential backup is selected in
>> the 'view contents' box, anotherwords, it is trying to
>> restore the first differential backup with the second
diff
>> backup file.
>> Is there a way to restore diff. backups in a sequence '
>> Thanks for any info........
>
>.
>|||You can read the output from the RESTORE HEADERONLY command if you need to find out this information
from the backup device and if you have several backups on the same backup device. Or do as EM does,
read the info from the backup history tables in the MSDB database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:050001c3b9a6$0d5611c0$a001280a@.phx.gbl...
> That is exacly what I want but not manually. I want to
> automate this process..............
>
>
> >--Original Message--
> >Why would you want to restore diff backups in sequence?
> The only one needed is the last diff backup
> >and then all subsequent log backups. Or did I
> misunderstand your situation?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:03c501c3b918$2c88c3a0$a301280a@.phx.gbl...
> >> I have a maintenance plan for a database Where I have a
> >> full database backups on every sunday and a differential
> >> backup every evening and transaction log backups every
> >> hour (7:00 Am - 7:00 Pm).
> >>
> >> I have a backup server where I restore my backups.
> After I
> >> restore my full backup, I restore the differential
> backup
> >> then the transaction log backups. I created a job to
> >> restore my backups but it fails after the first
> >> differential backup (Tuesday's differential backup). It
> >> restore the first differential backup after the full
> >> backup but not the second one or the others after the
> >> first one. I only have transaction log backups in
> between.
> >>
> >> When I restore the second differential backup manually,
> I
> >> see that the first differential backup is selected in
> >> the 'view contents' box, anotherwords, it is trying to
> >> restore the first differential backup with the second
> diff
> >> backup file.
> >>
> >> Is there a way to restore diff. backups in a sequence '
> >>
> >> Thanks for any info........
> >
> >
> >.
> >

Differential backup question (Full restore followed by ONLY one differential restore ?)

HI,
Suppose this Backup Plan
Full DB backup on Sunday 23:00
Backup-log on monday 09:00
Backup-log on monday 12:00
Backup-log on monday 15:00
Differential backup on monday 22:00
Full DB backup on monday 23:00
Backup-log on tuesday 09:00
Backup-log on tuesday 12:00
Backup-log on tuesday 15:00
Differential backup on tuesday 22:00
Full DB backup on tuesday 23:00
Backup-log on wednesday 09:00
Backup-log on wednesday 12:00
Backup-log on wednesday 15:00
Is it possible to restore 2 consecutives Differential backups like this ?
Restore Full from Sunday 23:00 (suppose Full bkps on
monday and tuesday are damaged)
Restore Differential from monday 22:00
Restore Differential from tuesday 22:00
Restore transaction-log from wednesday 09:00
Restore transaction-log from wednesday 12:00
Restore transaction-log from wednesday 15:00
Thank you
Danny (I plan to create differential bkp because of existing non-logged
operations)A differential backup contains changes after the last database backup. In your case
you will need full backup of monday. If you have it then sequence would be:
Full DB backup on monday 23:00
Differential backup on tuesday 22:00
Restore transaction-log from wednesday 09:00
Restore transaction-log from wednesday 12:00
Restore transaction-log from wednesday 15:00
--
- Vishal

Differential Backup in Maintenance Plan?

Hello. Can you schedule a differential backup in a
Database Maint Plan? I didn't see any way to do that, so
my guess is this is a sqlwish type of thing? Unless
someone knows a trick? Thanks, BruceHello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||Hello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||ok Andy... Yep, a scheduled job would be fine. Just not
ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>--Original Message--
>I thought I just saw Tibor answer this question a few
minutes ago. Oh well,
>no there is not a way to do it in the MP. Create your
own scheduled job and
>do it there.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:006401c34641$7daeeba0$a501280a@.phx.gbl...
>> Hello. Can you schedule a differential backup in a
>> Database Maint Plan? I didn't see any way to do that,
so
>> my guess is this is a sqlwish type of thing? Unless
>> someone knows a trick? Thanks, Bruce
>
>.
>|||Why not put ALLLLL the backups in your own scheduled jobs then to be
consistent. You have much better control over things if you don't use the
wizard and there really isn't anything the wizard can do that you can't with
a few lines of code.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0b4001c34647$70000e20$a001280a@.phx.gbl...
> ok Andy... Yep, a scheduled job would be fine. Just not
> ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>
> >--Original Message--
> >I thought I just saw Tibor answer this question a few
> minutes ago. Oh well,
> >no there is not a way to do it in the MP. Create your
> own scheduled job and
> >do it there.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:006401c34641$7daeeba0$a501280a@.phx.gbl...
> >> Hello. Can you schedule a differential backup in a
> >> Database Maint Plan? I didn't see any way to do that,
> so
> >> my guess is this is a sqlwish type of thing? Unless
> >> someone knows a trick? Thanks, Bruce
> >
> >
> >.
> >|||Bruce
You can use the backup wizard to create a differential
backup, if you do not want to code it yourself, and keep
it consistent if you need to do more than one.
Regards
John

Differential Backup

How do i create differential backup maintenance plan ?
All examples i saw is using GUI and backup device manually.TIA
You will need to write your own script and job for
differential backups. The maintenance plans don't support
differential backups.
-Sue
On Tue, 10 Jan 2006 05:57:02 -0800, rupart
<rupart@.discussions.microsoft.com> wrote:

>How do i create differential backup maintenance plan ?
>All examples i saw is using GUI and backup device manually.TIA

Differential Backup

Is there away to create differential with SQL Server 2000
Enterprise Edition with the Database Maintenance Plan?
If not how would I create a differential backup job that I
create a full backup on Sunday and daily differentials and
transaction logs every 3 hours.
Database Name : NewOrleans_Sales
Backup Directory : K:\NewOrleans_Sales
Please help me resolve this backup issue.
Thank You,
Mark E.Hello Mark
Database Maintenance Plan does not provide facility to create differential
backups. If you would like to include differential backups in your disaster
recovery plan then you can configure a simple T-SQL job that can run a
BACKUP DATABASE command to perform the differential backup. You can still
use the Maintenance plan to create the complete and transaction log backups
as you desire.
The command text would be something like :
BACKUP DATABASE NewOrleans_Sales TO DISK = 'K:\NewOrleans_Sales\Diff.Bak'
WITH DIFFERENTIAL
Please note that the above command will create a file Diff.bak and will
continue to append to this same file each time a backup is performed. At
some point you should refresh this file, so as not to fill up the drive.
Please refer to BOOKS ONLINE topic on Backup for more information on this
command and other options that can be used.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Differential Backup

How do i create differential backup maintenance plan ?
All examples i saw is using GUI and backup device manually.TIAYou will need to write your own script and job for
differential backups. The maintenance plans don't support
differential backups.
-Sue
On Tue, 10 Jan 2006 05:57:02 -0800, rupart
<rupart@.discussions.microsoft.com> wrote:
>How do i create differential backup maintenance plan ?
>All examples i saw is using GUI and backup device manually.TIA

Differential Backup

How do i create differential backup maintenance plan ?
All examples i saw is using GUI and backup device manually.TIAYou will need to write your own script and job for
differential backups. The maintenance plans don't support
differential backups.
-Sue
On Tue, 10 Jan 2006 05:57:02 -0800, rupart
<rupart@.discussions.microsoft.com> wrote:

>How do i create differential backup maintenance plan ?
>All examples i saw is using GUI and backup device manually.TIA

Differential and Transaction Log BU Maintenance Plan SP2

I had a maintenance plan which does a full backup weekly, differential backup daily, and transaction log every hour. After installing SP2, the maintenance plan designer will not allow me to set up my differential. I select the database to backup in the designer, then when I go back, it didn't take and it tells me I need to specify which database. I found the following in the "Whats New" section of SP2.

The Backup Database maintenance plan task prohibits the ability to mistakenly set the option to create differential and transaction log backups for system databases.

So, have I been wrong all this time for doing Full, Differential, and Transaction log backups(even though that plan was recommended in a Sql Server 2005 book). Is it now an either or? (you either do differential or transaction log, not both). Because transaction logs take longer to restore, and you "HAVE" to do a transaction log backup or the log will grow to rediculous size, meaning you can only do differential on "Simple Mode" databases? Thanks,

Jason

Transaction log backup should control the virtual size of transaction log and that should give you more time for point-in-time recovery than the differential backups, in this case the SP2 readme is right on the subject.

Also you might try to keep the transaction log backup schedule frequently to take care of such slowness of restoring the tlog.

|||Alright, I'll take your word for it. No more Full, Differential, and Transaction Log backups. Just Full and Transaction Log. Thanks.

Different query plans for view and view definition statement

I compared view query plan with query plan if I run the same statement
from view definition and get different results. View plan is more
expensive and runs longer. View contains 4 inner joins, statistics
updated for all tables. Any ideas?which version?|||SQL Server 2000, Enterprise Edition with SP4|||ysfinks (ysfinks@.gmail.com) writes:
> I compared view query plan with query plan if I run the same statement
> from view definition and get different results. View plan is more
> expensive and runs longer. View contains 4 inner joins, statistics
> updated for all tables. Any ideas?

Since you didn't share anything close to a repro, I have little idea
of you what you are doing. Since a view essential is a macro, it should
not matter that much. Then again, I've been wrong before. Anyway, it
would help if you posted the view, and the two SELECT you run.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I narrowed down to one join. Same difference in query plans. Query cost
for 1 is 5.48%, for 2 is 94.52%
My view is:
create view dbo.sf_test as
SELECT
dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id

My queries are:
1.
SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
CompanyAccounts.root_account_id AS ECCRootID
FROM dbo.ManagedNodes WITH (NOLOCK)
INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
where ECCRootID=15427
2.
select NodeID, SubscriptionID, ECCRootID
from dbo.sf_test where eccrootid=15427|||ysfinks (ysfinks@.gmail.com) writes:
> I narrowed down to one join. Same difference in query plans. Query cost
> for 1 is 5.48%, for 2 is 94.52%
> My view is:
> create view dbo.sf_test as
> SELECT
> dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> My queries are:
> 1.
> SELECT dbo.ManagedNodes.NodeID, dbo.ManagedNodes.SubscriptionID,
> CompanyAccounts.root_account_id AS ECCRootID
> FROM dbo.ManagedNodes WITH (NOLOCK)
> INNER JOIN dbo.accounts CompanyAccounts WITH (NOLOCK)
> ON dbo.ManagedNodes.ECCRootID = CompanyAccounts.account_id
> where ECCRootID=15427
> 2.
> select NodeID, SubscriptionID, ECCRootID
> from dbo.sf_test where eccrootid=15427

I will have to admit that I don't have any good answers at this
point. But I still like to ask some questions, just to check:

Exactly how do you create the view? From Query Analyzer or Enterprise
Manager? If the latter, what happens, if you run a script in QA
where you first create the view, and then run the queries?

What happens if you take out the NOLOCK hints?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||so if i am understanding, if you do a select against a view, it takes a
very long time.

but if copy that exact same code into query analyzer or a stored
procedure, it goes MUCH faster.

and If I am understanding correctly the issue, there will be an index
on eccrootid.

And, if I am understanding, the view won't use the index on ECCrootid,
but everything else will.
Do I have the issue correctly? If so, yup, it does that in SS2000. You
can try compiler hints in the view to FORCE it to sue the index, but
that only works sometimes.

Best workaround is to move all your views to stored procedures, and
pass the eccrootid parameter to the sproc.

I reported this 5 years ago, adn even discussed it with Erland at that
time.

Views suck.

Regards,
Doug|||A VIEW is handled two ways in SQL. The text of the VIEW is "pasted"
into the query that uses it and then the parser and optimizer handle it
as if the query had been written with a derived table. The parser can
do a lot stuff at this point, so the original view text is "spread out
all over the place".

The second way is materialize the VIEW as a temporary table. The good
news is that this materialized table can be shared by multiple users,
so the overall processing time goes down, even if each user's plan is
not optimal for their query. This is a feature of larger SQL products
like Ingres, DB2 or Oracle.

Trust in the optimizer, Luke.|||if you are going ot have a materialized view, why not just bite the
bullet and have a denormalized table hanging around that gets updated
all the time.

the optimizer is fine for 90 percent of the time.|||View was created from Query Analyzer. If I remove nolock - same result.
Another fact - if I change condition value in where clause, for some
values it gives for the view the good query plan using index for
eccrootid.
For the query simulating the view - always good plan.|||ysfinks (ysfinks@.gmail.com) writes:
> View was created from Query Analyzer. If I remove nolock - same result.
> Another fact - if I change condition value in where clause, for some
> values it gives for the view the good query plan using index for
> eccrootid.
> For the query simulating the view - always good plan.

I will have admit that I am fairly stumped at this point. For this reason
I have consulted some other people offline. No promises, but keep watching
this space.

Nevertheless, you run the two queries bracketed by

set statistics profile on
set statistics profile off

If you can put that in a file as a attachment ot on web site, to avoid
that the output is mashsed in news transport, that would be great, but
anything goes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Different Execution Plan using COM+ of Query Analyzer.

Hi,
I have the following problem : one of our statements gives a different
execution plan if it is executed from a component in COM+, of directly
in Query Analyzer.
I checked this using the following scenario :
* Execute the business code and profile using SQL Profiler.
* I included the query plan in the trace.
* Copy/Paste the statement to Query Analyzer, to ensure that I have
exactly the same statement. Execute it, and compare the result from
SQL Profiler.
If I compare the two, he does a table scan coming from COM+, and an
Index S coming from Query Analyzer. The COM+ version takes a
minute, the Query Analyzer doesn't even last a second.
I have tried this scenario staring from a restart of the SQL Server
service, so I am sure that caching of the statement is not the cause.
If I restart and first execute from Query Analyzer, the COM+ version
still takes just as long.
Note that this is on a database where no one else is working on.
After adding an INDEX hint to the statement, both the query plans are
the same, and both execute quickly.
Of course I want to avoid having to add INDEX hints to statements, and
I want to be sure that if I request a Query Plan in Query Analyzer, I
get something that I can trust.
Any suggestions what might be the cause of this ?
JDAre you sending the statement from the client app or are you executing a
stored procedure?
AMB
"JanM" wrote:

> Hi,
> I have the following problem : one of our statements gives a different
> execution plan if it is executed from a component in COM+, of directly
> in Query Analyzer.
> I checked this using the following scenario :
> * Execute the business code and profile using SQL Profiler.
> * I included the query plan in the trace.
> * Copy/Paste the statement to Query Analyzer, to ensure that I have
> exactly the same statement. Execute it, and compare the result from
> SQL Profiler.
> If I compare the two, he does a table scan coming from COM+, and an
> Index S coming from Query Analyzer. The COM+ version takes a
> minute, the Query Analyzer doesn't even last a second.
> I have tried this scenario staring from a restart of the SQL Server
> service, so I am sure that caching of the statement is not the cause.
> If I restart and first execute from Query Analyzer, the COM+ version
> still takes just as long.
> Note that this is on a database where no one else is working on.
> After adding an INDEX hint to the statement, both the query plans are
> the same, and both execute quickly.
> Of course I want to avoid having to add INDEX hints to statements, and
> I want to be sure that if I request a Query Plan in Query Analyzer, I
> get something that I can trust.
> Any suggestions what might be the cause of this ?
> JD
>|||No stored procedure is used. All statements are executed from within
COM + transactions.
JanM
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<AA5F00BB-E11F-496F-8D69-DBE0BB3DAB5A@.microsoft.com>...
> Are you sending the statement from the client app or are you executing a
> stored procedure?
>
> AMB
>
> "JanM" wrote:
>

Different execution plan between stored proc and ad-hoc query

We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

cheers

James

If the system is heavily updated, the stats could get stale. Consider running [sp_updatestats 'resample'] or explicitly forcing an index (the latter is not really recommended unless you fully understand the consequences).

e.g.

select * from tb with (index(myindex))

|||

It's very quite possible to get two different plans. Take for example these two TSQL statements:

a)

declare @.x int

set @.x = 99

select * from t1 where col1 = @.x

b)

select * from t1 where col1=99

In a), the optimizer evalutes the whole batch, and cannot evalute the literal value of 99 since it's a parameter value set inside a batch. So it makes a guesstimate and optimizes for a given value which it thinks may be the most correct/optimal. Often times it is, often times it is not. In b), the statement is evaluated and immediately it knows the value of 99, so it can get a very accurate estimate, thus producing a more reliable plan.

The same holds true for stored procedures. If you create a stored procedure and pass in a parameter value used in your WHERE clause, the optimizer will most likely produce a far better estimate than if you created a procedure which declared variables, set them, and then used them in your WHERE clause. It's always beneficial in a proc to try not to declare variables, set them and then use them in a query. If you can pass them as a parameter, the query has a better chance of producing an optimal plan based on accurate estimates.

Where you can also get into trouble is if the plan is cached with a value that gave good estimates/performance at the time it was created, but then as time passes and changes occur to the tables, yes the stats can get out of date, and you'll need to either update stats or clear the cache to remove the stale stats/plan.

If you got lost with what i was trying to say above, forgive me, I'm sure it's documented in some whitepaper somewhere, but I know this is an issue as merge replication procs and triggers hit this kind of problem often.

|||

Thank you both for your comments. Greg, that makes sense and does explain why we may have been seeing the different plan.

This problem actually appeared again yesterday, although this time the ad-hoc query was using the same (flawed) plan as the proc. We have decided in this instance to use an index hint as there are really no circumstances where it should be doing a full scan. As OJ says, I know these are not really recommended but I think I'm happy for this to be one of the rare expections!

cheers

James

|||Which version of SQL Server are you using? If 2005 then you might opt for the "Optimize For" hint or "Option Recompile". When using an index hint, you run the risk of accidentally breaking code later if that index is dropped or changed to include different columns.