db error: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

DB Backup problem

Hello,
I have one particular SQL 7 database that is rather large, over 100
gigs. Most of this is in one table which is full of blobs. This database
is mission critical stuff and is backed up every night. However, as of
recent this backup is taking almost 15 hours to complete sometimes and when
it's running the DB can't be accessed which is causing problems. Can anyone
suggest a solution to keeping this backup time to a minimum?
--
-Scott ElgramThere are several things that you can do.. None of them are particularly
pretty.
One suggestion that comes to mind:
1. Create a new table for a 1-to-1 relationship with your big table and
move your BLOBs off the big table and into the new table.
2. Put that new table in it's own filegroup
3. Back up that filegroup separately.
Another suggestion..
If the BLOBs are not often modified or added, you may wish to enable
t-logging of your BLOB objects. Then the blobs are part of the transaction
log. You don't have to do a complete DB backup every night.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have one particular SQL 7 database that is rather large, over 100
> gigs. Most of this is in one table which is full of blobs. This database
> is mission critical stuff and is backed up every night. However, as of
> recent this backup is taking almost 15 hours to complete sometimes and
when
> it's running the DB can't be accessed which is causing problems. Can
anyone
> suggest a solution to keeping this backup time to a minimum?
> --
> -Scott Elgram
>|||Are you certain that it really is backup that is blocking? Have you verified using sp_who2 etc?
Backup shouldn't block, perhaps you in the same job is doing some shrink or defrag?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have one particular SQL 7 database that is rather large, over 100
> gigs. Most of this is in one table which is full of blobs. This database
> is mission critical stuff and is backed up every night. However, as of
> recent this backup is taking almost 15 hours to complete sometimes and when
> it's running the DB can't be accessed which is causing problems. Can anyone
> suggest a solution to keeping this backup time to a minimum?
> --
> -Scott Elgram
>|||Tibor Karaszi,
I looked into that and the Maintenance Plan does the following Monday -
Friday;
5:00pm - Optimization:
- Reorganize data and index pages, Change free space per page to 1%.
- Remove unused space from database files, shrink when it grows
beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
space.
5:30pm - Integrity
- Check database integrity, include indexes, attempt to repair any
minor problems.
6:00pm - Transaction Log Backup
- Backup the transaction log of the database as part of the
maintenance plan
- Verify the integrity of the backup upon completion
7:00pm - Complete Backup
- back up the database as part of the maintenance plan
- verify the integrity of the backup upon completion
After reviewing this, which I did not create ;) , Would in help my cause
if I were to move the Optimization to some other plan that runs once a week
on perhaps a weekend when backups are not running? If backing up should not
lock the DB then could it be the Optimization that is taking 15 hours and
locking it?
-Scott
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> Are you certain that it really is backup that is blocking? Have you
verified using sp_who2 etc?
> Backup shouldn't block, perhaps you in the same job is doing some shrink
or defrag?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > I have one particular SQL 7 database that is rather large, over 100
> > gigs. Most of this is in one table which is full of blobs. This
database
> > is mission critical stuff and is backed up every night. However, as of
> > recent this backup is taking almost 15 hours to complete sometimes and
when
> > it's running the DB can't be accessed which is causing problems. Can
anyone
> > suggest a solution to keeping this backup time to a minimum?
> >
> > --
> > -Scott Elgram
> >
> >
>|||Rick Sawtell,
The information added to the BLOB table is daily....sometimes several
hundred megs are added each day. Does that leave me with only this second
option? Also, Someone by the name Tibor Karaszi has suggested that Backups
should not "lock" databases or tables and this lock might be caused by some
shrink or defrag occurring in the same job. Could Optimization in the
backup maintenance plan be the culprit and running for 15 hours, not the
actual backup?
-Scott
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:Osb3YGgtEHA.3572@.tk2msftngp13.phx.gbl...
> There are several things that you can do.. None of them are particularly
> pretty.
> One suggestion that comes to mind:
> 1. Create a new table for a 1-to-1 relationship with your big table and
> move your BLOBs off the big table and into the new table.
> 2. Put that new table in it's own filegroup
> 3. Back up that filegroup separately.
> Another suggestion..
> If the BLOBs are not often modified or added, you may wish to enable
> t-logging of your BLOB objects. Then the blobs are part of the
transaction
> log. You don't have to do a complete DB backup every night.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> > I have one particular SQL 7 database that is rather large, over 100
> > gigs. Most of this is in one table which is full of blobs. This
database
> > is mission critical stuff and is backed up every night. However, as of
> > recent this backup is taking almost 15 hours to complete sometimes and
> when
> > it's running the DB can't be accessed which is causing problems. Can
> anyone
> > suggest a solution to keeping this backup time to a minimum?
> >
> > --
> > -Scott Elgram
> >
> >
>|||"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uTP9RmgtEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Rick Sawtell,
> The information added to the BLOB table is daily....sometimes several
> hundred megs are added each day. Does that leave me with only this second
> option? Also, Someone by the name Tibor Karaszi has suggested that
Backups
> should not "lock" databases or tables and this lock might be caused by
some
> shrink or defrag occurring in the same job. Could Optimization in the
> backup maintenance plan be the culprit and running for 15 hours, not the
> actual backup?
> -Scott
A couple of hundred megs of changes are still better than 100 gigs of
nightly backups.
That said, Tibor is correct. Backups don't generally lock the tables. You
probably have something going on in the DBMaint that is locking rows and
slowing your backup process. An index defrag or similar.
Rick|||"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following
Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to
1%.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems.
> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
> After reviewing this, which I did not create ;) , Would in help my
cause
> if I were to move the Optimization to some other plan that runs once a
week
> on perhaps a weekend when backups are not running? If backing up should
not
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
>
Yes! You are only picking up 1% or so.. If you let it go until the
weekends, you should see very little performance degradation.
To be sure, run a baseline on the system as it operates currently. Then
make your changes and check it again. Compare to the baseline.
Rick|||Comments Inline
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following
Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to
1%.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
>
Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
SHOWCONTIG. There is a script that will allow you to defrag your tables
when they actually need it rather than all at once. Do not shrink the data
files. You will just have to grow them again and take the performance hit
when they auto-grow. And yes, it is the optimization step that is likely
locking the tables.
> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems
Big no-no. Go ahead and run the checks, but don't attempt to repair. All
you will do is mask the early stages of a problem until it gets too big for
DBCC to fix.
> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
Yes.
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
Yes.
> After reviewing this, which I did not create ;) , Would in help my
cause
> if I were to move the Optimization to some other plan that runs once a
week
> on perhaps a weekend when backups are not running? If backing up should
not
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
> -Scott
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > Are you certain that it really is backup that is blocking? Have you
> verified using sp_who2 etc?
> > Backup shouldn't block, perhaps you in the same job is doing some shrink
> or defrag?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > > I have one particular SQL 7 database that is rather large, over 100
> > > gigs. Most of this is in one table which is full of blobs. This
> database
> > > is mission critical stuff and is backed up every night. However, as
of
> > > recent this backup is taking almost 15 hours to complete sometimes and
> when
> > > it's running the DB can't be accessed which is causing problems. Can
> anyone
> > > suggest a solution to keeping this backup time to a minimum?
> > >
> > > --
> > > -Scott Elgram
> > >
> > >
> >
> >
>|||I fully agree with Geoff's points.
To read more specifically about shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And more about defragmentation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi,
> I looked into that and the Maintenance Plan does the following Monday -
> Friday;
> 5:00pm - Optimization:
> - Reorganize data and index pages, Change free space per page to 1%.
> - Remove unused space from database files, shrink when it grows
> beyond 50 MB, Amount of free space to remain after shrink: 1% of the data
> space.
> 5:30pm - Integrity
> - Check database integrity, include indexes, attempt to repair any
> minor problems.
> 6:00pm - Transaction Log Backup
> - Backup the transaction log of the database as part of the
> maintenance plan
> - Verify the integrity of the backup upon completion
> 7:00pm - Complete Backup
> - back up the database as part of the maintenance plan
> - verify the integrity of the backup upon completion
> After reviewing this, which I did not create ;) , Would in help my cause
> if I were to move the Optimization to some other plan that runs once a week
> on perhaps a weekend when backups are not running? If backing up should not
> lock the DB then could it be the Optimization that is taking 15 hours and
> locking it?
> -Scott
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
>> Are you certain that it really is backup that is blocking? Have you
> verified using sp_who2 etc?
>> Backup shouldn't block, perhaps you in the same job is doing some shrink
> or defrag?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> > I have one particular SQL 7 database that is rather large, over 100
>> > gigs. Most of this is in one table which is full of blobs. This
> database
>> > is mission critical stuff and is backed up every night. However, as of
>> > recent this backup is taking almost 15 hours to complete sometimes and
> when
>> > it's running the DB can't be accessed which is causing problems. Can
> anyone
>> > suggest a solution to keeping this backup time to a minimum?
>> >
>> > --
>> > -Scott Elgram
>> >
>> >
>>
>|||Geoff N. Hiten,
What about checking the "Reorganize data and index pages, Reorganize
with the original amount of free space" and "Update the statistics used by
the query optimizer, Percentage of database to sample 10%" to run on a
weekend? Those seem like good things to have done but because of the cost on
performance might be better run during a time of little or no usage like the
weekend. Or is it just a bad idea over all to set Optimization in the
maintenance Plan?
-Scott
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23CT8aygtEHA.1464@.TK2MSFTNGP15.phx.gbl...
> Comments Inline
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> > Tibor Karaszi,
> > I looked into that and the Maintenance Plan does the following
> Monday -
> > Friday;
> >
> > 5:00pm - Optimization:
> > - Reorganize data and index pages, Change free space per page to
> 1%.
> > - Remove unused space from database files, shrink when it grows
> > beyond 50 MB, Amount of free space to remain after shrink: 1% of the
data
> > space.
> >
> Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
> SHOWCONTIG. There is a script that will allow you to defrag your tables
> when they actually need it rather than all at once. Do not shrink the
data
> files. You will just have to grow them again and take the performance hit
> when they auto-grow. And yes, it is the optimization step that is likely
> locking the tables.
>
> > 5:30pm - Integrity
> > - Check database integrity, include indexes, attempt to repair
any
> > minor problems
> Big no-no. Go ahead and run the checks, but don't attempt to repair. All
> you will do is mask the early stages of a problem until it gets too big
for
> DBCC to fix.
> >
> > 6:00pm - Transaction Log Backup
> > - Backup the transaction log of the database as part of the
> > maintenance plan
> > - Verify the integrity of the backup upon completion
> Yes.
> >
> > 7:00pm - Complete Backup
> > - back up the database as part of the maintenance plan
> > - verify the integrity of the backup upon completion
> Yes.
> >
> > After reviewing this, which I did not create ;) , Would in help my
> cause
> > if I were to move the Optimization to some other plan that runs once a
> week
> > on perhaps a weekend when backups are not running? If backing up should
> not
> > lock the DB then could it be the Optimization that is taking 15 hours
and
> > locking it?
> >
> > -Scott
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > > Are you certain that it really is backup that is blocking? Have you
> > verified using sp_who2 etc?
> > > Backup shouldn't block, perhaps you in the same job is doing some
shrink
> > or defrag?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > > news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > > I have one particular SQL 7 database that is rather large, over
100
> > > > gigs. Most of this is in one table which is full of blobs. This
> > database
> > > > is mission critical stuff and is backed up every night. However, as
> of
> > > > recent this backup is taking almost 15 hours to complete sometimes
and
> > when
> > > > it's running the DB can't be accessed which is causing problems.
Can
> > anyone
> > > > suggest a solution to keeping this backup time to a minimum?
> > > >
> > > > --
> > > > -Scott Elgram
> > > >
> > > >
> > >
> > >
> >
> >
>|||Some people don't like maintenance plans at all and prefer to do things
using scripts and SQL agent jobs. The Reorganize Data and Index pages uses
DBCC DBREINDEX under the covers. I prefer a combination of DBCC DBREINDEX
and DBCC INDEXDEFRAG driven by DBCC SHOWCONTIG, all running at low activity
times in SQL agent jobs.
As for free space, you need to pro-actively manage that. Don't try and
shrink active databases unless there is more than 30% empty space. You will
fill in the space soon enough and auto-grow has a huge performance penalty.
Finally, the Statistics update. I prefer to set up SQL agent jobs to
recalculate statistics at a known time. Sample rates depend on your data.
If you have a fairly normal or even data distribution, then a small
percentage like 10% is ok. If you have some unusual distributions (I.E.
many entries are unique and a few entries have hundreds or thousands of
matches, then you will likely need a higher percentage. If you end up with
bad query plans, then you will definitely have to increase the scan
percentage.
Maintenance plans are easy but not as flexible as writing your own code.
There are good examples in BOL and out on the web.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> Geoff N. Hiten,
> What about checking the "Reorganize data and index pages, Reorganize
> with the original amount of free space" and "Update the statistics used by
> the query optimizer, Percentage of database to sample 10%" to run on a
> weekend? Those seem like good things to have done but because of the cost
on
> performance might be better run during a time of little or no usage like
the
> weekend. Or is it just a bad idea over all to set Optimization in the
> maintenance Plan?
> -Scott
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:%23CT8aygtEHA.1464@.TK2MSFTNGP15.phx.gbl...
> > Comments Inline
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> > > Tibor Karaszi,
> > > I looked into that and the Maintenance Plan does the following
> > Monday -
> > > Friday;
> > >
> > > 5:00pm - Optimization:
> > > - Reorganize data and index pages, Change free space per page
to
> > 1%.
> > > - Remove unused space from database files, shrink when it
grows
> > > beyond 50 MB, Amount of free space to remain after shrink: 1% of the
> data
> > > space.
> > >
> > Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
> > SHOWCONTIG. There is a script that will allow you to defrag your tables
> > when they actually need it rather than all at once. Do not shrink the
> data
> > files. You will just have to grow them again and take the performance
hit
> > when they auto-grow. And yes, it is the optimization step that is
likely
> > locking the tables.
> >
> >
> > > 5:30pm - Integrity
> > > - Check database integrity, include indexes, attempt to repair
> any
> > > minor problems
> > Big no-no. Go ahead and run the checks, but don't attempt to repair.
All
> > you will do is mask the early stages of a problem until it gets too big
> for
> > DBCC to fix.
> >
> > >
> > > 6:00pm - Transaction Log Backup
> > > - Backup the transaction log of the database as part of the
> > > maintenance plan
> > > - Verify the integrity of the backup upon completion
> > Yes.
> > >
> > > 7:00pm - Complete Backup
> > > - back up the database as part of the maintenance plan
> > > - verify the integrity of the backup upon completion
> > Yes.
> > >
> > > After reviewing this, which I did not create ;) , Would in help my
> > cause
> > > if I were to move the Optimization to some other plan that runs once a
> > week
> > > on perhaps a weekend when backups are not running? If backing up
should
> > not
> > > lock the DB then could it be the Optimization that is taking 15 hours
> and
> > > locking it?
> > >
> > > -Scott
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> > > message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > > > Are you certain that it really is backup that is blocking? Have you
> > > verified using sp_who2 etc?
> > > > Backup shouldn't block, perhaps you in the same job is doing some
> shrink
> > > or defrag?
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > > > news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > > > > Hello,
> > > > > I have one particular SQL 7 database that is rather large, over
> 100
> > > > > gigs. Most of this is in one table which is full of blobs. This
> > > database
> > > > > is mission critical stuff and is backed up every night. However,
as
> > of
> > > > > recent this backup is taking almost 15 hours to complete sometimes
> and
> > > when
> > > > > it's running the DB can't be accessed which is causing problems.
> Can
> > > anyone
> > > > > suggest a solution to keeping this backup time to a minimum?
> > > > >
> > > > > --
> > > > > -Scott Elgram
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you read the defrag article I referred to? It has *lots* of good info!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott Elgram" <SElgram@.verifpoint.com> wrote in message
news:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> Geoff N. Hiten,
> What about checking the "Reorganize data and index pages, Reorganize
> with the original amount of free space" and "Update the statistics used by
> the query optimizer, Percentage of database to sample 10%" to run on a
> weekend? Those seem like good things to have done but because of the cost on
> performance might be better run during a time of little or no usage like the
> weekend. Or is it just a bad idea over all to set Optimization in the
> maintenance Plan?
> -Scott
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:%23CT8aygtEHA.1464@.TK2MSFTNGP15.phx.gbl...
>> Comments Inline
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
>> > Tibor Karaszi,
>> > I looked into that and the Maintenance Plan does the following
>> Monday -
>> > Friday;
>> >
>> > 5:00pm - Optimization:
>> > - Reorganize data and index pages, Change free space per page to
>> 1%.
>> > - Remove unused space from database files, shrink when it grows
>> > beyond 50 MB, Amount of free space to remain after shrink: 1% of the
> data
>> > space.
>> >
>> Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
>> SHOWCONTIG. There is a script that will allow you to defrag your tables
>> when they actually need it rather than all at once. Do not shrink the
> data
>> files. You will just have to grow them again and take the performance hit
>> when they auto-grow. And yes, it is the optimization step that is likely
>> locking the tables.
>>
>> > 5:30pm - Integrity
>> > - Check database integrity, include indexes, attempt to repair
> any
>> > minor problems
>> Big no-no. Go ahead and run the checks, but don't attempt to repair. All
>> you will do is mask the early stages of a problem until it gets too big
> for
>> DBCC to fix.
>> >
>> > 6:00pm - Transaction Log Backup
>> > - Backup the transaction log of the database as part of the
>> > maintenance plan
>> > - Verify the integrity of the backup upon completion
>> Yes.
>> >
>> > 7:00pm - Complete Backup
>> > - back up the database as part of the maintenance plan
>> > - verify the integrity of the backup upon completion
>> Yes.
>> >
>> > After reviewing this, which I did not create ;) , Would in help my
>> cause
>> > if I were to move the Optimization to some other plan that runs once a
>> week
>> > on perhaps a weekend when backups are not running? If backing up should
>> not
>> > lock the DB then could it be the Optimization that is taking 15 hours
> and
>> > locking it?
>> >
>> > -Scott
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> > message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
>> > > Are you certain that it really is backup that is blocking? Have you
>> > verified using sp_who2 etc?
>> > > Backup shouldn't block, perhaps you in the same job is doing some
> shrink
>> > or defrag?
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
>> > > news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
>> > > > Hello,
>> > > > I have one particular SQL 7 database that is rather large, over
> 100
>> > > > gigs. Most of this is in one table which is full of blobs. This
>> > database
>> > > > is mission critical stuff and is backed up every night. However, as
>> of
>> > > > recent this backup is taking almost 15 hours to complete sometimes
> and
>> > when
>> > > > it's running the DB can't be accessed which is causing problems.
> Can
>> > anyone
>> > > > suggest a solution to keeping this backup time to a minimum?
>> > > >
>> > > > --
>> > > > -Scott Elgram
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Thanks alot guys,
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eJaSOihtEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Some people don't like maintenance plans at all and prefer to do things
> using scripts and SQL agent jobs. The Reorganize Data and Index pages
uses
> DBCC DBREINDEX under the covers. I prefer a combination of DBCC DBREINDEX
> and DBCC INDEXDEFRAG driven by DBCC SHOWCONTIG, all running at low
activity
> times in SQL agent jobs.
> As for free space, you need to pro-actively manage that. Don't try and
> shrink active databases unless there is more than 30% empty space. You
will
> fill in the space soon enough and auto-grow has a huge performance
penalty.
> Finally, the Statistics update. I prefer to set up SQL agent jobs to
> recalculate statistics at a known time. Sample rates depend on your data.
> If you have a fairly normal or even data distribution, then a small
> percentage like 10% is ok. If you have some unusual distributions (I.E.
> many entries are unique and a few entries have hundreds or thousands of
> matches, then you will likely need a higher percentage. If you end up
with
> bad query plans, then you will definitely have to increase the scan
> percentage.
> Maintenance plans are easy but not as flexible as writing your own code.
> There are good examples in BOL and out on the web.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> news:u6$30YhtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> > Geoff N. Hiten,
> > What about checking the "Reorganize data and index pages, Reorganize
> > with the original amount of free space" and "Update the statistics used
by
> > the query optimizer, Percentage of database to sample 10%" to run on a
> > weekend? Those seem like good things to have done but because of the
cost
> on
> > performance might be better run during a time of little or no usage like
> the
> > weekend. Or is it just a bad idea over all to set Optimization in the
> > maintenance Plan?
> >
> > -Scott
> >
> > "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> > news:%23CT8aygtEHA.1464@.TK2MSFTNGP15.phx.gbl...
> > > Comments Inline
> > >
> > > --
> > > Geoff N. Hiten
> > > Microsoft SQL Server MVP
> > > Senior Database Administrator
> > > Careerbuilder.com
> > >
> > > I support the Professional Association for SQL Server
> > > www.sqlpass.org
> > >
> > > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > > news:uuQrgjgtEHA.2316@.TK2MSFTNGP12.phx.gbl...
> > > > Tibor Karaszi,
> > > > I looked into that and the Maintenance Plan does the following
> > > Monday -
> > > > Friday;
> > > >
> > > > 5:00pm - Optimization:
> > > > - Reorganize data and index pages, Change free space per
page
> to
> > > 1%.
> > > > - Remove unused space from database files, shrink when it
> grows
> > > > beyond 50 MB, Amount of free space to remain after shrink: 1% of the
> > data
> > > > space.
> > > >
> > > Ditch the Maintenance Plan Optimizations. Look in BOL under DBCC
> > > SHOWCONTIG. There is a script that will allow you to defrag your
tables
> > > when they actually need it rather than all at once. Do not shrink the
> > data
> > > files. You will just have to grow them again and take the performance
> hit
> > > when they auto-grow. And yes, it is the optimization step that is
> likely
> > > locking the tables.
> > >
> > >
> > > > 5:30pm - Integrity
> > > > - Check database integrity, include indexes, attempt to
repair
> > any
> > > > minor problems
> > > Big no-no. Go ahead and run the checks, but don't attempt to repair.
> All
> > > you will do is mask the early stages of a problem until it gets too
big
> > for
> > > DBCC to fix.
> > >
> > > >
> > > > 6:00pm - Transaction Log Backup
> > > > - Backup the transaction log of the database as part of the
> > > > maintenance plan
> > > > - Verify the integrity of the backup upon completion
> > > Yes.
> > > >
> > > > 7:00pm - Complete Backup
> > > > - back up the database as part of the maintenance plan
> > > > - verify the integrity of the backup upon completion
> > > Yes.
> > > >
> > > > After reviewing this, which I did not create ;) , Would in help
my
> > > cause
> > > > if I were to move the Optimization to some other plan that runs once
a
> > > week
> > > > on perhaps a weekend when backups are not running? If backing up
> should
> > > not
> > > > lock the DB then could it be the Optimization that is taking 15
hours
> > and
> > > > locking it?
> > > >
> > > > -Scott
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > > in
> > > > message news:O%23pUFIgtEHA.2536@.TK2MSFTNGP11.phx.gbl...
> > > > > Are you certain that it really is backup that is blocking? Have
you
> > > > verified using sp_who2 etc?
> > > > > Backup shouldn't block, perhaps you in the same job is doing some
> > shrink
> > > > or defrag?
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Scott Elgram" <SElgram@.verifpoint.com> wrote in message
> > > > > news:%23EiS8cftEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > > > > > Hello,
> > > > > > I have one particular SQL 7 database that is rather large,
over
> > 100
> > > > > > gigs. Most of this is in one table which is full of blobs.
This
> > > > database
> > > > > > is mission critical stuff and is backed up every night.
However,
> as
> > > of
> > > > > > recent this backup is taking almost 15 hours to complete
sometimes
> > and
> > > > when
> > > > > > it's running the DB can't be accessed which is causing problems.
> > Can
> > > > anyone
> > > > > > suggest a solution to keeping this backup time to a minimum?
> > > > > >
> > > > > > --
> > > > > > -Scott Elgram
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

DB Backup problem

Tuesday, March 27, 2012

DB - DDL - how much time each operation takes - a doc needed

Is there a doc describing how much time each DDL can take,
e.g. (n is the number of records in the table):
add nullable column = 0(1)
add column with default falue = o(n)
etc'...

Tal Olier
otal@.mercury.co.ilI haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?|||Originally posted by Paul Young
I haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?

No it hasn't, I am looking for something like:

# Operation DB Type Example Time
1 Rename a table Oracle Alter table x rename .. o(1)
MS-SQL Sp_rename o(1)
2 Rename a index Oracle Alter index x rename.. o(1)
MS-SQL Sp_rename t.x.. o(1)
3 Add column Oracle Alter table x add y NULL o(1)
Oracle Alter table x add y default (10)/default(null) o(n)
MS-SQL Alter table x add o(1)

Days between Two Dates.

Hi all,

I have a table in which I have two fields in my DB.

FromDate and ToDate.

Both are stored as Varchar(MAX).

I would like to have an SP which gives me the Days in Between them.

Regards,

Naveen.

try this:

selectdatediff(day,convert (datetime ,'07/05/2007'),convert (datetime ,'07/06/2007'))as [day diff]

|||

Hi addie,

what I want is an SP.

Regards,

Naveen

|||

ifexists (select *fromsysobjectswhere id =object_id ('sp_test'))drop proceduresp_testgocreate proceduresp_test@.start_datevarchar(20),@.end_datevarchar(20)asselectdatediff(day,convert (datetime , @.start_date),convert (datetime , @.end_date))as [day diff]

dayly table update

hello,
i must dayly update a table in my database with the values of a CSV file
(~300000 entries)
example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
....
245433 router 126,33
Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.
How can this be done?
thanksOne recommendation could be
1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted
a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
WHERE SURR.Col1= BCP.Col1 )
b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT Col1, Col2
FROM ot_su_daily_csv SURR(nolock)
ORDER BY Col1|||thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc 699,44
the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz 699,44 (product description is modified)
245433 router 126,33 -> ok will be detected and updated
....
how to make a correct update in this situation ...
thanks
Xavier|||On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

>thanks for the recommendation - it works well if only each day new values i
n
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc 699,44
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz 699,44 (product description is modified)
>245433 router 126,33 -> ok will be detected and updated
>....
>how to make a correct update in this situation ...
>thanks
>Xavier
Hi Xavier,
Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:
UPDATE t
SET Descr = s.Descr,
Price = s.Price,
.. (other columns)
FROM TheTable AS t
INNER JOIN StagingTable AS s
ON s.KeyColumn = theTable.keyColumn
WHERE t.Descr <> s.Descr
OR t.Price <> s.Price
OR ... (other columns)
INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT KeyColumn, Descr, Price, ... (other columns)
FROM Stagins AS s
WHERE NOT EXISTS
(SELECT *
FROM TheTable AS t
WHERE t.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks,
Xavier
"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> Hi Xavier,
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
> UPDATE t
> SET Descr = s.Descr,
> Price = s.Price,
> ... (other columns)
> FROM TheTable AS t
> INNER JOIN StagingTable AS s
> ON s.KeyColumn = theTable.keyColumn
> WHERE t.Descr <> s.Descr
> OR t.Price <> s.Price
> OR ... (other columns)
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT KeyColumn, Descr, Price, ... (other columns)
> FROM Stagins AS s
> WHERE NOT EXISTS
> (SELECT *
> FROM TheTable AS t
> WHERE t.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Sunday, March 25, 2012

Day of the week

I have a table whcih contains order Id (orderid_c), and order date
(orderdate_d).
Is there anywhere I can program to count the number of order from Monday to
the day the report is run, for example, when I run the report on Wednesday,
the report will cover from Monday to Wednesday and when I run the report on
Thursday, the report will cover from Monday to Thursday. I will have to run
the report several time during the business hour.
Thanks,set datefirst 1
select count(orderid_c) from table
where datepart(wk,orderdate_d) = datepart(wk,getdate())
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:9FCC02A9-29B8-48B1-B888-091BBC502CFD@.microsoft.com...
> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday
to
> the day the report is run, for example, when I run the report on
Wednesday,
> the report will cover from Monday to Wednesday and when I run the report
on
> Thursday, the report will cover from Monday to Thursday. I will have to
run
> the report several time during the business hour.
>
> Thanks,
>|||sp_who will tell you who and what database
"qjlee" wrote:

> I have a table whcih contains order Id (orderid_c), and order date
> (orderdate_d).
> Is there anywhere I can program to count the number of order from Monday t
o
> the day the report is run, for example, when I run the report on Wednesday
,
> the report will cover from Monday to Wednesday and when I run the report o
n
> Thursday, the report will cover from Monday to Thursday. I will have to r
un
> the report several time during the business hour.
>
> Thanks,
>|||On Thu, 18 Aug 2005 10:31:01 -0700, qjlee wrote:

>I have a table whcih contains order Id (orderid_c), and order date
>(orderdate_d).
>Is there anywhere I can program to count the number of order from Monday to
>the day the report is run, for example, when I run the report on Wednesday,
>the report will cover from Monday to Wednesday and when I run the report on
>Thursday, the report will cover from Monday to Thursday. I will have to ru
n
>the report several time during the business hour.
Hi qjlee,
Here's how to select data between "last monday" and "now":
SELECT ...
FROM ...
WHERE TheDate >= DATEADD(day, DATEDIFF(day, '20050103',
CURRENT_TIMESTAMP) / 7 * 7, '20050103')
AND TheDate <= CURRENT_TIMESTAMP
AMD ...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Thursday, March 22, 2012

DateTimestamp

Hi,
I currently have a column in a table that is a default of getdate()). My
question : Is there a way to capture just the date without the time appended
?
For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexes
on this datetimestamp field? Would a clustered index on this field help?
Almost all searching is done by the date field, whether it is searched by da
y
or a date range...Any better way than what is currently being done to help
with speed ? Currently we have a clustered index on this column. The column
currently contains approximately 12 million rows...
Thanks for your input,
WarrenChange the defualt to Cast (getdate() as Integer)
-- Datetimes as stored internally as a decimal value 4 bytes for the
integer portion, and another 4 bytes for the fractional portion. The 4 byte
s
for integer portion are the date, and the other 4 bytes are the time... So i
f
you cast the value to an integer, you truncate the time...
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Store same time for all rows.
Example:
use tempdb
go
create table dbo.t (
colA int not null identity unique,
colB datetime default (convert(char(8), getdate(), 112))
)
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
select * from t
drop table t
go
If you are planning to do a lot of selects filtering this column based on a
range of dates, then having a clustered index by this column will be helpful
.
AMB
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||There is no way to capture a date without the time portion; best thing to do
is probably write a function that sets each portion of the time to zero - us
e
DATEDIFF and DATEADD functions to subtract hours, minutes, seconds, and
milliseconds from the date.
With SQL 2000 32-bit datetimes are stored as 2 4-byte integers, so you can
convert the datetime to a binary(8), take the first 4 bytes and concat
0x00000000 to that and convert back to datetime to get zero hours, mins,
secs, & ms; but this method would not necessarily work on anything but SQL 2
k
32-bit.
As for a clustered index, only testing will tell...
KH
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||If you're really interested in speed, don;t use DateTime column use an
integer or smallint.. and store the integer you get from Cast (getDate()
as Integer) directly. This reduces the size of the data in the column frm
8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width o
f
the index, you will dramatically increase the number of entries you will be
able to store on each IO Page of the index, and increase the performance
using the index.
NOTE: If you use smallints, then you need to subtract 32768 from the valu
you get from Cast(getdate() as Integer) before stuffing it into the smallint
field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
UNSIGNED 2-byte integer..
SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 65536
representing 6 June 2079... whereas smallint goes from -32768 to +32767...
Using Regular datetime is no problem, because it uses SIGNED 4-byte Integer,
with values from
January 1, 1753 through December 31, (this way value 0 still represents 1
Jan 1900... )
If your queries against this table often use date as a range of dates...
then a clustered index on this column will increase performance
draamatically.
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Thanks for all the replys...
I found out that the client application inserts the date stamp in the row of
the database. The colun is a dattime field, so it's not the getdate()) that
I thought it was..
Any more thoughts?
Thanks,
Warren
"CBretana" wrote:
> If you're really interested in speed, don;t use DateTime column use an
> integer or smallint.. and store the integer you get from Cast (getDate(
)
> as Integer) directly. This reduces the size of the data in the column fr
m
> 8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width
of
> the index, you will dramatically increase the number of entries you will b
e
> able to store on each IO Page of the index, and increase the performance
> using the index.
> NOTE: If you use smallints, then you need to subtract 32768 from the valu
> you get from Cast(getdate() as Integer) before stuffing it into the smalli
nt
> field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
> UNSIGNED 2-byte integer..
> SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 6553
6
> representing 6 June 2079... whereas smallint goes from -32768 to +32767...
> Using Regular datetime is no problem, because it uses SIGNED 4-byte Intege
r,
> with values from
> January 1, 1753 through December 31, (this way value 0 still represents 1
> Jan 1900... )
> If your queries against this table often use date as a range of dates...
> then a clustered index on this column will increase performance
> draamatically.
> "Warren" wrote:
>|||Then you need to
a) change the way the client write the data to strip off the time portion,
How is client writing to Database? using CLient constructed SQL,
Through a Stored Proc, etc.?
b) write an Insert/ update trigger on the database table that strips off the
time portion whenver client tries t owrite time into the table
In either case, To speed things up, same comments I made earlier about
datatypes still apply...
"Warren" wrote:
> Thanks for all the replys...
> I found out that the client application inserts the date stamp in the row
of
> the database. The colun is a dattime field, so it's not the getdate()) th
at
> I thought it was..
> Any more thoughts?
> Thanks,
> Warren
> "CBretana" wrote:
>sql

datetime update

I would like to change all rows in a table that have the date range between
June 1, 2005 to June 30, 2005 to the month of May. I haven't been able to
figure out the SET code that would update the rows to the the month of May
instead of June. The day and year have to remain the same. Any suggestions?
Thanking you in advance.
SherryUPDATE YourTable
SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
DATCOL END)
WHERE Datcol between '20050601' AND 20050630
HTH, Jens Suessmeyer.
"Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>I would like to change all rows in a table that have the date range between
> June 1, 2005 to June 30, 2005 to the month of May. I haven't been able
> to
> figure out the SET code that would update the rows to the the month of May
> instead of June. The day and year have to remain the same. Any
> suggestions?
> Thanking you in advance.
> Sherry|||Thank so much. Worked perfectly.
"Jens Sü?meyer" wrote:

> UPDATE YourTable
> SET DatCol = (CASE MONTH(DATCOL) WHEN 6 THEN DATEADD(mm,-1,DATCOL) ELSE
> DATCOL END)
> WHERE Datcol between '20050601' AND 20050630
> HTH, Jens Suessmeyer.
> "Sherry" <Sherry@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:2233DF06-C29A-4DDD-A4DF-993C9AAEC42A@.microsoft.com...
>
>sql

DateTime unable to save in datetime field of SQL database

Hi all, having a little problem with saving dates to sql database

I've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up

Error "

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

I've tried researching it but not been able to find something similar.

Heres the code:

DateTime createOn = DateTime.Now;

string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')";

Any help would be much appreciated

If you are using SQL Server, change the statement to

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',GetDate())

If you are using Access then use this:

INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" +name + "','" + description + "','" + userName + "',Date())

|||

Sorry, my fault i should have said, i'm coding in c sharp, heres the expanded function

void AddToQuizIndex(String userName,String quizName,String description,String question_xml)

{

DateTime createOn = DateTime.Now;

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"','" +createOn+"','" + question_xml +"')";this.ActionSQLStatement(sSQLStatement);

}

|||

C# makes no difference. GetDate() in SQL Server will automatically apply the equivalent of C# datetime.now. But your database won't complain. Try it.

string sSQLStatement ="INSERT INTO QuizIndex (Name, Description,Creator,CreatedOn,Data) values ('" + quizName +"','" + description +"','" + userName +"',GetDate(),'" + question_xml +"')";

Really, you should be using parameters rather than compiling dynamic SQL statements, but that's another topic.

|||

nice one, first time i tried it i didn't put ' ' round the GetDate()

Thanks very much for the replyMikesdotnetting, you really helped me out.

Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

Wednesday, March 21, 2012

Datetime Selection II

I posted this earlier and got an unusable answer, so I'm trying again...
I have a table with a transaction date of datatype datetime. I need to
select all where where the transaction date is 2 days ago. If I use
"where transaction date = (getdate( ) -2)" the selection fails because of, I
think, the time portion of each field. How can I compare two datetime
datatypes based only on the date portion?
Thank you!
Stan Gosselin
On Mon, 6 Feb 2006 14:46:25 -0800, Stan wrote:

>I posted this earlier and got an unusable answer, so I'm trying again...
>I have a table with a transaction date of datatype datetime. I need to
>select all where where the transaction date is 2 days ago. If I use
>"where transaction date = (getdate( ) -2)" the selection fails because of, I
>think, the time portion of each field. How can I compare two datetime
>datatypes based only on the date portion?
>Thank you!
Hi Stan,
I posted a reply to your original post, half an hour after you sent this
copy of the message (but before I saw it).
Was it useful?
Hugo Kornelis, SQL Server MVP

DateTime Question?

Hi All!

I have a SP that does a search in a table. I have a datetime field. But I would like to only search by date, not by datetime. I noticed it works only if the time is 12:00am. But if it's different, the date won't show. I do need to display the date and time.

here is my SP

PROCEDURE [dbo].[SearchHist]

-- Add the parameters for the stored procedure here

@.FName nvarchar(50),

@.LName nvarchar(50),

@.DOB nvarchar(50),

@.Acct nvarchar(50),

@.Login nvarchar(50),

@.Status nvarchar (50),

@.Rmark nvarchar(255),

@.Room nvarchar (50),

@.Age nvarchar(50),

@.Sdate datetime,

@.Type nvarchar(50),

@.Misc bit

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

If LEN(@.FName)>0 OR LEN(@.LName)>0 OR LEN(@.DOB)>0 OR LEN(@.Acct)>0 OR

LEN (@.Rmark)>0 OR LEN (@.Room)>0 OR LEN (@.Age)>0 OR LEN(@.Sdate)>0

OR LEN (@.Type)>0 OR LEN (@.Misc)>0 OR (@.Login)>0

-- Insert statements for procedure here

SELECT Archive_Orders.First_Name, Archive_Orders.Last_Name, Archive_Orders.Account_Number, Archive_Orders.Remarks, Locations.Loct_Desc,

Archive_Orders.Rm_Desc, Archive_Orders.Age, Archive_Orders.Service_Date_Time, Archive_Orders.Type, Archive_Orders.Order_ID,

Order_Status.Status, Archive_Orders.Misc

FROM Archive_Orders INNER JOIN

Locations ON Archive_Orders.Location_ID = Locations.Location_ID INNER JOIN

Order_Status ON Archive_Orders.Status_ID = Order_Status.Status_ID

WHERE (Archive_Orders.First_Name = @.FName) OR

(Archive_Orders.Last_Name = @.Lname) OR

(Archive_Orders.DOB = @.DOB) OR

(Archive_Orders.Account_Number = @.Acct) OR

(Order_Status.Status = @.Status) OR

(Archive_Orders.Remarks = @.Rmark) OR

(Archive_Orders.Rm_Desc = @.Room) OR

(Archive_Orders.Age = @.Age) OR

(Archive_Orders.Service_Date_Time = @.SDate) OR

(Archive_Orders.Type = @.Type) OR

(Archive_Orders.Misc = @.Misc) OR

(Archive_Orders.Login = @.Login)

END

TIA!

Rudy

If you're looking to return Orders for a particular day, i tend to use DATEDIFF as its quick and simple.

WHERE DATEDIFF(D, Archive_Orders.Service_Date_Time, @.SDate) = 0

HTH!

|||

There are multiple approaches to searching dates. You could for every row strip off the time value, using a construct like for example:

Code Snippet

convert( datetime, convert(varchar(10), your_date_col, 120), 120)

and then check for equality with the value you are looking for. This has a big disadvantage though when searching historical data which tend to contain many records: you're asking sql server to first convert every value in the table and then evaluate all of those converted values against your selection criterium, i.e. it will be slow.

The better approach is to select all datetime values within the range from your_date 0:00 and before your_date + 1 0:00. Thus your where clause will look something like:

Code Snippet

where your_datetime_col >= @.your_search_date

and your_datetime_col < dateadd( day, 1, @.your_search_date)

This way SQL can more easily answer your query.

You may also want to ensure in your sp (depending on your requirements) that the @.your_search_date value does not have a time component in it by adding something like this before your select statement:

Code Snippet

select @.your_search_date = convert( datetime, convert(varchar(10), @.your_search_date, 120), 120)

|||

Of the methods mentioned, I like this one (mentioned by rrozema) better:

Code Snippet

where your_datetime_col >= @.your_search_date

and your_datetime_col < dateadd( day, 1, @.your_search_date)

with this method your search can take advantage of a date index -- provided that one is available -- and use a seek. The other methods are likely to scan.

|||

Here's how I do a Date search for a SINGLE day. Let's assume you're looking for all records where occurs any time on August 9, 2007 (i.e. '8/9/2007 00:00:00' to '8/9/2007 23:59:59.999')

Code Snippet

declare @.TargetDay int

set @.TargetDay = datediff(d,0,'09 August 2007')

select *

from <MyTable> where DateDiff(d,0, <MyDate>) = @.TargetDay

|||

@.rusag2: Your solution will most likely do a scan too. i.e. If there are many of rows in the table, this is going to be very slow as SQL can not take advantage of indexes (given that any are available).

|||

Absolutely agreed. If the column is indexed, you might take advantage of that like this:

Code Snippet

declare @.dtStart datetime, @.dtEnd datetime

set @.dtStart = '09 Aug 2007'

set @.dtEnd = dateadd(d,1,@.dtSTart)

set @.dtEnd = dateadd(ms, -1, @.dtEnd)

select * from <MyTable> where <DateField> between @.dtStart and @.dtEnd

|||

There are a couple of other potholes that you need to be aware of. My understanding here is that we are in the context of a stored procedure and that the date is a parameter of the stored procedure. In the context of a stored procedure, a where clause such as this:

Code Snippet

where your_datetime_col >= @.your_search_date
and your_datetime_col < dateadd( day, 1, @.your_search_date)

has an advantage over a where clause in which you load values into a second variable like this:

Code Snippet

declare @.dtStart datetime, @.dtEnd datetime
set @.dtStart = '09 Aug 2007'
set @.dtEnd = dateadd(d,1,@.dtSTart)
set @.dtEnd = dateadd(ms, -1, @.dtEnd)
select * from where between @.dtStart and @.dtEnd

because the first where clause can take advantage of parameter sniffing and has a better chance of an index seek than the second query.

Also, the upper bound of the first where clause uses a "strictly less than" comparator whereas BETWEEN uses a "less than or equal to" comparator for the upper bound. To compensate you need to STILL include the less than operator like:

Code Snippet

select * from <MyTable> where <DateField> between @.dtStart and @.dtEnd

and <DateField> < @.dtEnd

|||

Thank you all!!

Great suggestions!!

(Archive_Orders.Service_Date_Time >= @.SDate

and Archive_Orders.Service_Date_Time < dateadd(day, 1, @.SDate))

This one worked very nicely. Thanks rrozema!!

Rudy

Thursday, March 29, 2012

DB Backup problem

DB Backup problem

DB Backup problem

Tuesday, March 27, 2012

DB - DDL - how much time each operation takes - a doc needed

Days between Two Dates.

dayly table update

Sunday, March 25, 2012

Day of the week

Thursday, March 22, 2012

DateTimestamp

datetime update

DateTime unable to save in datetime field of SQL database

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."

Datetime to time conversion with default date

Wednesday, March 21, 2012

Datetime Selection II

DateTime Question?

db error

Blog Archive

About Me

Thursday, March 29, 2012

Tuesday, March 27, 2012

Sunday, March 25, 2012

Thursday, March 22, 2012

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."

Wednesday, March 21, 2012

Blog Archive

About Me

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated."