Showing posts with label run. Show all posts
Showing posts with label run. Show all posts

Tuesday, March 27, 2012

Dimwit MDX count problem

I have run into a small problem. I have a dimension that identifies certain types of bills. I also have a measure that reports how many bills have been issued. There are several types of bills. But 1 of these is an adjustment bill. This is like a correction to an existing bill. These are represented as "A" bills in the DB

I need to count the number of unique bills, so that's a count of all the bills other than "A" bills.

I've looked at calculated members but am struggling with MDX syntax.

Anyone have any pointers or good articles I should read?

It should be something like this:`

with member [Measures].[Num]

as 'sum(except([Bills].[Level Name].members, [Bills].[ A ]), [Measures].[NumBills])'

Hope this helps,

Santi

Thursday, March 22, 2012

Difficulty Installing SQL Reporting Services on SBS 2003 Premium

I am trying to run reporting services on a SBS 2003 Premium server and have
been runninng into a lot of issues.
I have installed the SQL Reporting Services Components on the server and the
Visual Studio .Net components on my workstation. The goal behind installing
the components in the specified locations include: report authoring on my
workstation and finished reports available to all via the server.
The issues I have encountered include:
1) SQL Reporting Services activation error due to a conflict with
Sharepoint. I have removed Sharepoint from the server as outlined in KB
829114. After completion of the Sharepoint removal, I reinstalled SQL
Reporting Services and Reporting Services SP2 and was again informed that
activation was not possible due to a conflict with Sharepoint. How do I
work around this problem?
2) When accessing SQL Reporting Services from Visual Studio .Net on my
workstation, I can open an existing project but I can't view content within
the data sources tab. When trying to do so I get an error that states: "A
connection cannot be made to the database. Set and test the connection
string. Can't generate the SSPI context. How do I resolve this problem?
3) When creating a new SQL Reporting Services project using the Report
Wizard, the Shared Data Source field is blank and unavailable. Likewise,
attempts to access shared data sources using the Server Explorer window
result in the system prompting for a login when I try to select a listed in
the tree under my server. I am unable to login using our available network
or SQL User IDs.
My server is running SBS 2003 SP1 which includes SQL 2000 SP4. My
workstation is a member of the domain for which the server is the PDC.
Is my topology realistic and can my problems resolved or should I relocate
all components to the server? Even if located on the server, I will still
encounter the activation problem and need a work around.
Thanks for your help!Hello RS,
A few weeks ago I wanted to order SQL Server Reporting Services on a CD vom
Microsoft Direct. At that occasion, they told me that Reporting Services
simply are not supported on SBS. Seems like MS wants you to by the full SQL
Server product if you want to use RS. But please re-check that with your
contacts.
Greetings,
Stefan Falk|||Hi Stefan,
Sehr interesant. I initially posted my issue in the SBS forum and was
instructed by Microsoft to post in the reporting services forum. No mention
was made of non-compatability and I have noticed from the SBS forum that
other SBS users are running reporting services. You should be able to get a
copy of the software from one of the US Microsoft resellers.
Take care,
RS
"Stefan Falk" wrote:
> Hello RS,
> A few weeks ago I wanted to order SQL Server Reporting Services on a CD vom
> Microsoft Direct. At that occasion, they told me that Reporting Services
> simply are not supported on SBS. Seems like MS wants you to by the full SQL
> Server product if you want to use RS. But please re-check that with your
> contacts.
> Greetings,
> Stefan Falk
>
>

Sunday, March 11, 2012

Differential Backups

I have set up differential backups for six of my critical databases to run every two hours, between certain times of the day. I have set them up bt creating the job in TSQL using append, but I want to set an option to delete/remove entries after 2 days like you can in Enterprise Manager. (In Enterprise Manager I can ony set up full backups) How can I accomplish this
Thanks
TinaI think you need to add the following in the parameter:
-DelBkUps 2DAYS
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Differential Backups

I have set up differential backups for six of my critical databases to run e
very two hours, between certain times of the day. I have set them up bt crea
ting the job in TSQL using append, but I want to set an option to delete/rem
ove entries after 2 days li
ke you can in Enterprise Manager. (In Enterprise Manager I can ony set up f
ull backups) How can I accomplish this?
Thanks,
TinaI think you need to add the following in the parameter:
-DelBkUps 2DAYS
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Differential Backup Size and Transaction Log Backup Size !

Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn

DIFFERENTIAL BACKUP ERROR

Hi ,
I am running a job for taking a differential back up of a Merge replicated
database.
in week days the differential backup will run and on sunday the normal
backup will overwrite the differential back up.again from monday differential
back up will start.
the backup job is running correctly.when i try to restore the differential
back up it's working upto 4th day.for 5th and 6 th its not working and giving
an error.
here i am listing my backup job code
declare @.Weekday INT
SET @.Weekday=0
SET @.Weekday=datepart(weekday,getdate())
IF @.Weekday<>1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
IF @.Weekday=1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
Please help.i am unable to identify the problem.
thanks,
reddy.
reddy
What is an error that you've got?
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> Hi ,
> I am running a job for taking a differential back up of a Merge replicated
> database.
> in week days the differential backup will run and on sunday the normal
> backup will overwrite the differential back up.again from monday
differential
> back up will start.
> the backup job is running correctly.when i try to restore the differential
> back up it's working upto 4th day.for 5th and 6 th its not working and
giving
> an error.
> here i am listing my backup job code
> declare @.Weekday INT
> SET @.Weekday=0
> SET @.Weekday=datepart(weekday,getdate())
> IF @.Weekday<>1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> IF @.Weekday=1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> Please help.i am unable to identify the problem.
> thanks,
> reddy.
>
>
>
|||The error is
can not apply the backup on device 'c:\mssql\test.dat' to database test
restore database is terminating abnormally.
the backup job aim is from monday to saturday differential backup, on sunday
normal backup will overwrite all the differential backups.i am able to
restore differential backups till thursday.after that any backup i am unable
to restore.i am getting the above error.
your help is appreciated.
thanks
reddy.
"Uri Dimant" wrote:

> reddy
> What is an error that you've got?
> "reddy" <reddy@.discussions.microsoft.com> wrote in message
> news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> differential
> giving
>
>

DIFFERENTIAL BACKUP ERROR

Hi ,
I am running a job for taking a differential back up of a Merge replicated
database.
in week days the differential backup will run and on sunday the normal
backup will overwrite the differential back up.again from monday differentia
l
back up will start.
the backup job is running correctly.when i try to restore the differential
back up it's working upto 4th day.for 5th and 6 th its not working and givin
g
an error.
here i am listing my backup job code
declare @.Weekday INT
SET @.Weekday=0
SET @.Weekday=datepart(weekday,getdate())
IF @.Weekday<>1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
IF @.Weekday=1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
Please help.i am unable to identify the problem.
thanks,
reddy.reddy
What is an error that you've got?
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> Hi ,
> I am running a job for taking a differential back up of a Merge replicated
> database.
> in week days the differential backup will run and on sunday the normal
> backup will overwrite the differential back up.again from monday
differential
> back up will start.
> the backup job is running correctly.when i try to restore the differential
> back up it's working upto 4th day.for 5th and 6 th its not working and
giving
> an error.
> here i am listing my backup job code
> declare @.Weekday INT
> SET @.Weekday=0
> SET @.Weekday=datepart(weekday,getdate())
> IF @.Weekday<>1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> IF @.Weekday=1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> Please help.i am unable to identify the problem.
> thanks,
> reddy.
>
>
>|||The error is
can not apply the backup on device 'c:\mssql\test.dat' to database test
restore database is terminating abnormally.
the backup job aim is from monday to saturday differential backup, on sunday
normal backup will overwrite all the differential backups.i am able to
restore differential backups till thursday.after that any backup i am unable
to restore.i am getting the above error.
your help is appreciated.
thanks
reddy.
"Uri Dimant" wrote:

> reddy
> What is an error that you've got?
> "reddy" <reddy@.discussions.microsoft.com> wrote in message
> news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> differential
> giving
>
>

DIFFERENTIAL BACKUP ERROR

Hi ,
I am running a job for taking a differential back up of a Merge replicated
database.
in week days the differential backup will run and on sunday the normal
backup will overwrite the differential back up.again from monday differential
back up will start.
the backup job is running correctly.when i try to restore the differential
back up it's working upto 4th day.for 5th and 6 th its not working and giving
an error.
here i am listing my backup job code
declare @.Weekday INT
SET @.Weekday=0
SET @.Weekday=datepart(weekday,getdate())
IF @.Weekday<>1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
IF @.Weekday=1
BEGIN
BACKUP DATABASE DIFFBACKUP
TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
STATS = 10, NOFORMAT
END
Please help.i am unable to identify the problem.
thanks,
reddy.reddy
What is an error that you've got?
"reddy" <reddy@.discussions.microsoft.com> wrote in message
news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> Hi ,
> I am running a job for taking a differential back up of a Merge replicated
> database.
> in week days the differential backup will run and on sunday the normal
> backup will overwrite the differential back up.again from monday
differential
> back up will start.
> the backup job is running correctly.when i try to restore the differential
> back up it's working upto 4th day.for 5th and 6 th its not working and
giving
> an error.
> here i am listing my backup job code
> declare @.Weekday INT
> SET @.Weekday=0
> SET @.Weekday=datepart(weekday,getdate())
> IF @.Weekday<>1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> IF @.Weekday=1
> BEGIN
> BACKUP DATABASE DIFFBACKUP
> TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
> NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> STATS = 10, NOFORMAT
> END
> Please help.i am unable to identify the problem.
> thanks,
> reddy.
>
>
>|||The error is
can not apply the backup on device 'c:\mssql\test.dat' to database test
restore database is terminating abnormally.
the backup job aim is from monday to saturday differential backup, on sunday
normal backup will overwrite all the differential backups.i am able to
restore differential backups till thursday.after that any backup i am unable
to restore.i am getting the above error.
your help is appreciated.
thanks
reddy.
"Uri Dimant" wrote:
> reddy
> What is an error that you've got?
> "reddy" <reddy@.discussions.microsoft.com> wrote in message
> news:057BCACB-136F-42A4-82C4-3613A94B222B@.microsoft.com...
> > Hi ,
> >
> > I am running a job for taking a differential back up of a Merge replicated
> > database.
> > in week days the differential backup will run and on sunday the normal
> > backup will overwrite the differential back up.again from monday
> differential
> > back up will start.
> > the backup job is running correctly.when i try to restore the differential
> > back up it's working upto 4th day.for 5th and 6 th its not working and
> giving
> > an error.
> > here i am listing my backup job code
> >
> > declare @.Weekday INT
> > SET @.Weekday=0
> > SET @.Weekday=datepart(weekday,getdate())
> >
> > IF @.Weekday<>1
> > BEGIN
> > BACKUP DATABASE DIFFBACKUP
> > TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH DIFFERENTIAL ,
> > NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> > STATS = 10, NOFORMAT
> > END
> > IF @.Weekday=1
> > BEGIN
> > BACKUP DATABASE DIFFBACKUP
> > TO DISK ='F:\BackUp\DIFFBACKUPBACKUP.DAT' WITH INIT ,
> > NOUNLOAD , NAME = N'DIFFBACKUPBACKUP', NOSKIP ,
> > STATS = 10, NOFORMAT
> > END
> >
> > Please help.i am unable to identify the problem.
> >
> > thanks,
> > reddy.
> >
> >
> >
> >
> >
>
>

Friday, March 9, 2012

Differential Backup

Hello,
I am trying to do a differential backup but I have run
into a couple of problems.
I am doing this through the EM by right clicking the db
then selecting backup.
Firstly it doesn't actually seem to work. For a 7mb
database, the first backup is 7mb (which I agree with),
the second backup is also 7mb, rather than a smaller amout
with the difference.
Secondly I would like it to have different files for each
differential backup, currently it writes to one file, so
it keeps increasing from 7, 14, 21 mb.
Can anyone give me some pointers ?
Thanks
PeterPeter
1) Were there some activites in the database since last full backup
2) You will have to build the backup file by yourself .
SELECT @.FileName = 'D:\DatabaseName' + @.Date+'.bak'
BACKUP DATABASE DatabaseName
TO DISK = @.FileName
WITH DIFFERENTIAL
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:151e01c3fbbc$37c5d000$a401280a@.phx.gbl...
> Hello,
> I am trying to do a differential backup but I have run
> into a couple of problems.
> I am doing this through the EM by right clicking the db
> then selecting backup.
> Firstly it doesn't actually seem to work. For a 7mb
> database, the first backup is 7mb (which I agree with),
> the second backup is also 7mb, rather than a smaller amout
> with the difference.
> Secondly I would like it to have different files for each
> differential backup, currently it writes to one file, so
> it keeps increasing from 7, 14, 21 mb.
> Can anyone give me some pointers ?
> Thanks
> Peter|||Peter,
I don't think that you will find that diff backup will save storage for such
small databases. At least, that is my experience. Also, a diff backup
contains all modifications since the *latest database backup*! this means
that it will only be bigger and bigger until you do the next database
backup.
IMO, diff backup is not there to replace transaction log backup. It is there
for more advanced configurations to *complement* transaction log backups. Is
there a reason why you don't want to use log backups? There are a number of
nice things that log backups will give you that diff backup wont.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:151e01c3fbbc$37c5d000$a401280a@.phx.gbl...
> Hello,
> I am trying to do a differential backup but I have run
> into a couple of problems.
> I am doing this through the EM by right clicking the db
> then selecting backup.
> Firstly it doesn't actually seem to work. For a 7mb
> database, the first backup is 7mb (which I agree with),
> the second backup is also 7mb, rather than a smaller amout
> with the difference.
> Secondly I would like it to have different files for each
> differential backup, currently it writes to one file, so
> it keeps increasing from 7, 14, 21 mb.
> Can anyone give me some pointers ?
> Thanks
> Peter|||Thank you both for your help on this. My apologies for not
writing back earlier but I have been off work with the flu.
Anyway I will give these sugestions a try.
Thanks
Peter
>--Original Message--
>Hello,
>I am trying to do a differential backup but I have run
>into a couple of problems.
>I am doing this through the EM by right clicking the db
>then selecting backup.
>Firstly it doesn't actually seem to work. For a 7mb
>database, the first backup is 7mb (which I agree with),
>the second backup is also 7mb, rather than a smaller
amout
>with the difference.
>Secondly I would like it to have different files for each
>differential backup, currently it writes to one file, so
>it keeps increasing from 7, 14, 21 mb.
>Can anyone give me some pointers ?
>Thanks
>Peter
>.
>

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
Steve
Why are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>
|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent[vbcol=seagreen]
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore
>
|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>
|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>
|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last
>
|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
restore[vbcol=seagreen]
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WIT
H NO_LOG?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transactio
n
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> you
> kicked
> it
> recent
> restore
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
am[vbcol=seagreen]
Are[vbcol=seagreen]
last[vbcol=seagreen]
>|||Shrink doesn't break the log backup sequence. This assumes, of course that E
M doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> 8am,
> in
> am
> Are
> last
>

Differential and Transaction backup restore.

Differential and Transaction backup restore.
I have scheduled differential backup to run at 3PM and the transactional
backup to run at 3pm. In the event of data restore work, should I restore
the data from differential as well as transactional backups.
My questions, is it required to run data restore from the 3Pm transactional
backup?
Thanks,
SteveWhy are you doing a Differential and a Log backup at the same time? Are you
only issuing one Log backup during the entire day? Depending on which kicked
in first or finished first and if there was any activity during that time it
could be either one or both. Transaction logs keep a running chain of all
transactions since the first FULL backup assuming you never truncated the
log outside of a log backup. The Diff has all the changes since the last
FULL backup. You always apply the last FULL backup and then the most recent
Diff if you have one. Then any logs since the last Diff. Since it is
imposable to tell the real order from this post it is hard to say if
restoring the log would actually do anything or not in regards to
transactions that the Diff may not have caught. If you attempt to restore
the log and it is out of sequence it will warn you. I suggest you stagger
your backups and ensure you have a proper schedule of Log backups to cover
your needs.
--
Andrew J. Kelly SQL MVP
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> Differential and Transaction backup restore.
>
> I have scheduled differential backup to run at 3PM and the transactional
> backup to run at 3pm. In the event of data restore work, should I restore
> the data from differential as well as transactional backups.
> My questions, is it required to run data restore from the 3Pm
> transactional
> backup?
> Thanks,
> Steve
>|||I will rethink bout my backup scheduling. Thanks for the suggestions.
By the way, I have taken one complete backup at 8 am, some one at 9.00 am
truncated the log file. SQL Server crashes at 10am, then all my transaction
log files are not restorable since 9.00 onwards. Is it correct?
Steve
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why are you doing a Differential and a Log backup at the same time? Are
you
> only issuing one Log backup during the entire day? Depending on which
kicked
> in first or finished first and if there was any activity during that time
it
> could be either one or both. Transaction logs keep a running chain of all
> transactions since the first FULL backup assuming you never truncated the
> log outside of a log backup. The Diff has all the changes since the last
> FULL backup. You always apply the last FULL backup and then the most
recent
> Diff if you have one. Then any logs since the last Diff. Since it is
> imposable to tell the real order from this post it is hard to say if
> restoring the log would actually do anything or not in regards to
> transactions that the Diff may not have caught. If you attempt to restore
> the log and it is out of sequence it will warn you. I suggest you stagger
> your backups and ensure you have a proper schedule of Log backups to cover
> your needs.
> --
> Andrew J. Kelly SQL MVP
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> > Differential and Transaction backup restore.
> >
> >
> >
> > I have scheduled differential backup to run at 3PM and the transactional
> > backup to run at 3pm. In the event of data restore work, should I
restore
> > the data from differential as well as transactional backups.
> >
> > My questions, is it required to run data restore from the 3Pm
> > transactional
> > backup?
> >
> > Thanks,
> >
> > Steve
> >
> >
>|||What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG WITH NO_LOG?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>I will rethink bout my backup scheduling. Thanks for the suggestions.
>
> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
> truncated the log file. SQL Server crashes at 10am, then all my transaction
> log files are not restorable since 9.00 onwards. Is it correct?
> Steve
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
> you
>> only issuing one Log backup during the entire day? Depending on which
> kicked
>> in first or finished first and if there was any activity during that time
> it
>> could be either one or both. Transaction logs keep a running chain of all
>> transactions since the first FULL backup assuming you never truncated the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to restore
>> the log and it is out of sequence it will warn you. I suggest you stagger
>> your backups and ensure you have a proper schedule of Log backups to cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the transactional
>> > backup to run at 3pm. In the event of data restore work, should I
> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>|||If someone actually truncates the log at 9am, you can only restore til 8am,
as you suspect.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> WITH NO_LOG?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>>I will rethink bout my backup scheduling. Thanks for the suggestions.
>>
>> By the way, I have taken one complete backup at 8 am, some one at 9.00 am
>> truncated the log file. SQL Server crashes at 10am, then all my
>> transaction
>> log files are not restorable since 9.00 onwards. Is it correct?
>> Steve
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> Why are you doing a Differential and a Log backup at the same time? Are
>> you
>> only issuing one Log backup during the entire day? Depending on which
>> kicked
>> in first or finished first and if there was any activity during that
>> time
>> it
>> could be either one or both. Transaction logs keep a running chain of
>> all
>> transactions since the first FULL backup assuming you never truncated
>> the
>> log outside of a log backup. The Diff has all the changes since the last
>> FULL backup. You always apply the last FULL backup and then the most
>> recent
>> Diff if you have one. Then any logs since the last Diff. Since it is
>> imposable to tell the real order from this post it is hard to say if
>> restoring the log would actually do anything or not in regards to
>> transactions that the Diff may not have caught. If you attempt to
>> restore
>> the log and it is out of sequence it will warn you. I suggest you
>> stagger
>> your backups and ensure you have a proper schedule of Log backups to
>> cover
>> your needs.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> > Differential and Transaction backup restore.
>> >
>> >
>> >
>> > I have scheduled differential backup to run at 3PM and the
>> > transactional
>> > backup to run at 3pm. In the event of data restore work, should I
>> restore
>> > the data from differential as well as transactional backups.
>> >
>> > My questions, is it required to run data restore from the 3Pm
>> > transactional
>> > backup?
>> >
>> > Thanks,
>> >
>> > Steve
>> >
>> >
>>
>>
>|||When I say truncate the log file means...
In Enterprise Manager, Right click the database, All Tasks... Shrink
Database... and Shrink the log file here.
Thanks,
Martin
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
> If someone actually truncates the log at 9am, you can only restore til
8am,
> as you suspect.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
> > WITH NO_LOG?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
> >>
> >>
> >>
> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
am
> >> truncated the log file. SQL Server crashes at 10am, then all my
> >> transaction
> >> log files are not restorable since 9.00 onwards. Is it correct?
> >>
> >> Steve
> >>
> >>
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
> >> Why are you doing a Differential and a Log backup at the same time?
Are
> >> you
> >> only issuing one Log backup during the entire day? Depending on which
> >> kicked
> >> in first or finished first and if there was any activity during that
> >> time
> >> it
> >> could be either one or both. Transaction logs keep a running chain of
> >> all
> >> transactions since the first FULL backup assuming you never truncated
> >> the
> >> log outside of a log backup. The Diff has all the changes since the
last
> >> FULL backup. You always apply the last FULL backup and then the most
> >> recent
> >> Diff if you have one. Then any logs since the last Diff. Since it is
> >> imposable to tell the real order from this post it is hard to say if
> >> restoring the log would actually do anything or not in regards to
> >> transactions that the Diff may not have caught. If you attempt to
> >> restore
> >> the log and it is out of sequence it will warn you. I suggest you
> >> stagger
> >> your backups and ensure you have a proper schedule of Log backups to
> >> cover
> >> your needs.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
> >> > Differential and Transaction backup restore.
> >> >
> >> >
> >> >
> >> > I have scheduled differential backup to run at 3PM and the
> >> > transactional
> >> > backup to run at 3pm. In the event of data restore work, should I
> >> restore
> >> > the data from differential as well as transactional backups.
> >> >
> >> > My questions, is it required to run data restore from the 3Pm
> >> > transactional
> >> > backup?
> >> >
> >> > Thanks,
> >> >
> >> > Steve
> >> >
> >> >
> >>
> >>
> >>
> >>
> >
> >
>|||Shrink doesn't break the log backup sequence. This assumes, of course that EM doesn't do anything
strange (run profiler trace to see). But there are disadvantages to shrink:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
news:eMWe3pgZFHA.1384@.TK2MSFTNGP09.phx.gbl...
> When I say truncate the log file means...
> In Enterprise Manager, Right click the database, All Tasks... Shrink
> Database... and Shrink the log file here.
> Thanks,
> Martin
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%23pfQ76EZFHA.1088@.TK2MSFTNGP14.phx.gbl...
>> If someone actually truncates the log at 9am, you can only restore til
> 8am,
>> as you suspect.
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> message news:Oo5wCJ2YFHA.3164@.TK2MSFTNGP09.phx.gbl...
>> > What exactly do you mean by "truncated the log file"? Shrink? BACKUP LOG
>> > WITH NO_LOG?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> >
>> >
>> > "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> > news:OLwVbSzYFHA.3624@.tk2msftngp13.phx.gbl...
>> >>I will rethink bout my backup scheduling. Thanks for the suggestions.
>> >>
>> >>
>> >>
>> >> By the way, I have taken one complete backup at 8 am, some one at 9.00
> am
>> >> truncated the log file. SQL Server crashes at 10am, then all my
>> >> transaction
>> >> log files are not restorable since 9.00 onwards. Is it correct?
>> >>
>> >> Steve
>> >>
>> >>
>> >>
>> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> news:uhkbT4yYFHA.3620@.TK2MSFTNGP09.phx.gbl...
>> >> Why are you doing a Differential and a Log backup at the same time?
> Are
>> >> you
>> >> only issuing one Log backup during the entire day? Depending on which
>> >> kicked
>> >> in first or finished first and if there was any activity during that
>> >> time
>> >> it
>> >> could be either one or both. Transaction logs keep a running chain of
>> >> all
>> >> transactions since the first FULL backup assuming you never truncated
>> >> the
>> >> log outside of a log backup. The Diff has all the changes since the
> last
>> >> FULL backup. You always apply the last FULL backup and then the most
>> >> recent
>> >> Diff if you have one. Then any logs since the last Diff. Since it is
>> >> imposable to tell the real order from this post it is hard to say if
>> >> restoring the log would actually do anything or not in regards to
>> >> transactions that the Diff may not have caught. If you attempt to
>> >> restore
>> >> the log and it is out of sequence it will warn you. I suggest you
>> >> stagger
>> >> your backups and ensure you have a proper schedule of Log backups to
>> >> cover
>> >> your needs.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Steve Martin" <SteveM_Sanjose@.hotmail.com> wrote in message
>> >> news:u73ESQyYFHA.3920@.TK2MSFTNGP10.phx.gbl...
>> >> > Differential and Transaction backup restore.
>> >> >
>> >> >
>> >> >
>> >> > I have scheduled differential backup to run at 3PM and the
>> >> > transactional
>> >> > backup to run at 3pm. In the event of data restore work, should I
>> >> restore
>> >> > the data from differential as well as transactional backups.
>> >> >
>> >> > My questions, is it required to run data restore from the 3Pm
>> >> > transactional
>> >> > backup?
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Steve
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.
Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegr oups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>
|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database
is identical, to the
point that one is a backup or attach of the other? If not, things like stati
stics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers/databases

Hi!
I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.
Stored procedure:
BEGIN TRANSACTION
--step 1
TRUNCATE myTable
--step 2
INSERT INTO myTable VALUES ('myValues')
--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns
COMMIT TRANSACTION
The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:
select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <> 0
order by waittime desc
select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <> 0)
select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <> 0)
order by waittime desc
When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).
What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?
I am at a loss! Any help would be greatly appreciated.Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegroups.com...
> Hi!
> I have a stored procedure that takes 22 minutes to run in one
> environment, that only takes 1 sec or so to run in another
> environment. Here is the exact situation:
> Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
> exactly the same, and the tables and index structures are exactly the
> same. Implicit transactions are turned off on both databases.
> Stored procedure:
> BEGIN TRANSACTION
> --step 1
> TRUNCATE myTable
> --step 2
> INSERT INTO myTable VALUES ('myValues')
> --step 3
> UPDATE a
> SET rating=AVG(someValues)
> FROM myTable a
> JOIN otherTable b
> ON a.column1=b.column1
> GROUP BY someColumns
> COMMIT TRANSACTION
> The update statement on the problem server is the only step that takes
> forever. While it is running, I don't see anything that could be
> blocking the statement. I used the following queries to determine if
> there was another process blocking it:
> select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
> (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where blocked <> 0
> order by waittime desc
> select dbid, name from sysdatabases where dbid in (select dbid from
> master.dbo.sysprocesses where blocked <> 0)
> select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
> waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
> substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
> memusage
> from master.dbo.sysprocesses where spid in (select blocked from
> master.dbo.sysprocesses where blocked <> 0)
> order by waittime desc
> When I change the UPDATE statement to a SELECT, it still takes longer
> than it does on the test server (1 min 35 sec vs. several
> milliseconds).
> What could be causing the UPDATE to take forever on one server/
> database, and run without a problem on another?
> I am at a loss! Any help would be greatly appreciated.
>|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!

Different UPDATE behaviors across servers

Hi!

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:

Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.

Stored procedure:

BEGIN TRANSACTION

--step 1
TRUNCATE myTable

--step 2
INSERT INTO myTable VALUES ('myValues')

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns

COMMIT TRANSACTION

The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:

select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <0
order by waittime desc

select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <0)

select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <0)
order by waittime desc

When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).

What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?

I am at a loss! Any help would be greatly appreciated.Dmitri (nienna.gaia@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
>
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.


Apparently the query plans are different. This could be because there
are differences in statistics between the databases. Fragmentation
could also matter. I would recommend that you run DBCC DBREINDEX on
the tables in both environments. If you are lucky, the query runs
quickly in both databases. If you are less lucky, the query will now
run slowly in both databases.

If the machines has a different number of processors, this could also
matter. Maybe one machine is a single-CPU machine, whereas the other
is an 8-way box, so there is a parallel plan on server and a non-parallel
plan on the other. Parallel plans are sometimes really amazing -
either amazingly fast or amazingly slow.

Quote:

Originally Posted by

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns


Not that it matters for the discussion since I don't see the table
definition and the indexes, but this syntax is not legal.

--
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

Wednesday, March 7, 2012

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
--
Message posted via http://www.sqlmonster.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:
> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
--
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned
in result #1?
Message posted via http://www.droptable.comPRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via droptable.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returne
d in result #1?
> --
> Message posted via http://www.droptable.com
>|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP
--

Different return formats

I have a table (call it table_a) that has a column with a datetime format.
1. When I run the following I get the following result:
select min(createdate) from table_a
RESULT: 2003-09-15 15:58:19.273
2. When I run the following I get the following result:
declare @.createdate datetime
select @.createdate = min(createdate) from table_a
print @.createdate
RESULT: Sep 15 2003 3:58PM
How can I get the variable @.createdate to hold the identical value returned in result #1?
Message posted via http://www.sqlmonster.com
PRINT does not "return" a value it just prints to the output screen. Change
that to "SELECT @.Createdate" instead
"Robert Richards via SQLMonster.com" wrote:

> I have a table (call it table_a) that has a column with a datetime format.
> 1. When I run the following I get the following result:
> select min(createdate) from table_a
> RESULT: 2003-09-15 15:58:19.273
> 2. When I run the following I get the following result:
> declare @.createdate datetime
> select @.createdate = min(createdate) from table_a
> print @.createdate
> RESULT: Sep 15 2003 3:58PM
> How can I get the variable @.createdate to hold the identical value returned in result #1?
> --
> Message posted via http://www.sqlmonster.com
>
|||PRINT performs an implict conversion to VARCHAR for its arguments. You can
use CONVERT to specify a format of something other than the default:
PRINT CONVERT(VARCHAR,@.createdate,121)
or you can just return the value as DATETIME, using SELECT, and format it
client-side.
David Portas
SQL Server MVP

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.