I have a site hosted with another hosting company. My database is in MSSQL. I want to take a weekly backup of my database from the hosting server on to my backup device. How can I do this? Can I do it by using EM? Please advise.In article <4651AD67-5E8D-4719-BB13-AFE7305D777B@.microsoft.com>,
carrol_z@.yahoo.com said...
> I have a site hosted with another hosting company. My database is in
> MSSQL. I want to take a weekly backup of my database from the hosting
> server on to my backup device. How can I do this? Can I do it by using EM?
Only if your backup device is attached to the hosting company's server.
Otherwise you're going to have to back it up there and transfer it. Any
decent hosting company will have a backup and archival option.
Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts
Monday, March 19, 2012
Database automated part-archiving
Hi all,
i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .
the environment:
- MsSql Server 2000
- Suns App-Server Glasfish
- Java (J2EE)
- Hibernate
the requirements:
1.)
Some tables should be synchronized and redundant (the same data in both databases)
2.)
If the maindatabase is changing the schema the archivedatabse must do the same changes.
3.)
The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.
4.)
If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.
I don't find a way to realise this ...
can some one give me a hint?
... Sorry for my bad english, i hope i explained my problem good enough.
Thanks for ur answersyou have been sitting in class all semester and you have not a clue where to begin? Do you have any thoughts about how to do this?
This message board thing works a lot better if you have a solution in mind and you want our opinions. it does not work so well if you want someone to do your homework for you.
we have a team lead at my job that gets his\her work done by tasking it out to other people. everyone knows that person is worthless.
gee, its the middle of April. I guess you have 2 or 3 weeks to knock this out. Good luck.|||Offtopic:
yes u are right, there are always people who want that others do there work and people who ask questions (most of the time these people are comming to me), wait for answers and didn't do a littlebit one her own.
If u think i am worthless because i ask a question (for a not trival problem i think), it's fine but please don't spam the forum.
And no, i still have 3-4 month.
And yes, i have somethink in my mind, but i want to hear other opinions bevor i talk about it, so that it would not affect the answers.
Back to topic:
1 and 2)
Maybe i will try to make replication for the schema and the synchronized tables or read out the meta-data and use them to synchronize the schema and the tables.
3)
A simple thread can read the old datas from the maindatabase and write them in the archive database. But this is not ACID save. So, i musst find a save way to log the actions and if something went wrong i must undo the work.
4)
Catch every query and check if the result is valid, if not query the archivdatabase.
(But at witch point i should catch the query. At my company(yes i do my final year projekt in a company) we are using Hibernate and i don't know where i should catch the query, after hibernate i think its not possible, in hibernate ... i will not change the hibernate framework, befor hibernate it's not allowed because the application-code should not affect from my changes.
I am thank full for all ur opinions.|||I never said you are worthless and if you did not launch a personal attack on me I would have helped you out today because things are a little light on my end today.
I was simply helping you by telling you how to get a better response here and you did that, but since you do not appreciate my advice you can go some where else.
(_X_)|||Hmm, i didn't mean to launch an attack at u.
I am sorry if it looks like that.
The truth is, that i think that u launched an attack at me and didn't want to help me ;).
So, thanks for ur advice and a nice finishing time.|||Good lord...
In any case, you looking to automate Structure changes in SQL 2k?
Are you sure it's not SQL 2k5?
So you are asking for opinions, what have you come up with so far?|||Jeez, I would never automate cascading schematic changes between databases. There is an all-important phase called "testing" which is impossible to include in automation. Was this thesis your idea, or was it assigned to you?|||@.blindman:
It was assigned to me and wasn't my idear.
The reason for this solution is, that they have some tables that are realy large and grow every Day. To tune-up the access time they want to split the tables in old and new datas. But they want that the work after this is nearly like they have only one Database. So if they make schematic changes to the database (maindatabase) it should cascade to the other database (archivdatabase).
@.Brett Kaiser
2k5 is on the roadmap but at the moment it is still Sql-Server 2k.
If it is only possible with 2k5 than there is no other way then using 2k5.
I am still in the designing-phase.
I think i should try to use transaktion-replikation to synchronize the tables which should be synchronized (like Employees or some think like this) but i still don't know if the table schema will be replikated too and if i could only replikate selected tables, that is somethink i will glean today.
For the tables where the data shouldn't be synchronized (new data in the maindatabase, old data in the archivdatabase) i don't know how to cascade the schema from the tables. So i think i will write a program which willl read the meta-data form the tables (maindatabase) and update the tables in the archivdatabase. Then i have a delay between the struktur change in the main- and the update in the archivdatabase, but that shouldn't make a problem if i did this every time befor i move data from the maindb to the archivdb. In a thread i will move every 24hours (for example) data older then 2 years from the maindb to the archivdb but this is not transaktionsave (ACID) so i will log the status in a file (or maybe in the maindb) so that it will be possible to make somethink like a rollback if the transaktion fails (like a loose of power).
Thanks 4 ur help and opinions|||My buddy at work bought a zero-gravity pen last week. It was developed by NASA for astronauts to use in outer space. It contains a tiny CO2 cartridge that pressurizes the ink cylinder enabling it to write upside-down, or at any angle.
When faced with the same challenge the Russians used a pencil.
If you want to synchronize schematic changes between databases, then script all your modifications and run them on both databases. What you are endeavoring to do violates the KISS principle and my crystal ball tells me you are going to have more down time from process failures and corrupted data than it would take to to proper testing of modifications.
Thus sayeth the blindman.|||Thank u, i think i will do how u say and don't automate the schema synchronisation to "Keep It Simple, Stupid".
For the synchronisation of the datas i will write a program, that will compensate
the datas in the archivdb.
For the moving of the datas i will write a program, that will looking if the data in the maindb should move to the archivdb. The problem is, how to find all the datas which are depending on the moving data, because they all must move to. But i think i can find it out with the Meta-Informations from the tables ... that shouldn't be a problem.
Thank you for your help, opinion, and advice.|||It is absolutely appropriate to automate the update and transfer of the data. It is just modifications to the schema which you should not be automating.
i am a student doing my final year project and i have the duty to develop a automated part-archiving for our MsSql Server 2000 Database, but its not so simple :eek: .
the environment:
- MsSql Server 2000
- Suns App-Server Glasfish
- Java (J2EE)
- Hibernate
the requirements:
1.)
Some tables should be synchronized and redundant (the same data in both databases)
2.)
If the maindatabase is changing the schema the archivedatabse must do the same changes.
3.)
The datas in some tables (the ones that aren't synchronized) which are older then 2 Years should be moving (copy, paste) to the archivedatabase every 24 hours.
4.)
If there are is a access for a data which are not in the maindatabase, it is necassary to get it from the archivedatabase.
I don't find a way to realise this ...
can some one give me a hint?
... Sorry for my bad english, i hope i explained my problem good enough.
Thanks for ur answersyou have been sitting in class all semester and you have not a clue where to begin? Do you have any thoughts about how to do this?
This message board thing works a lot better if you have a solution in mind and you want our opinions. it does not work so well if you want someone to do your homework for you.
we have a team lead at my job that gets his\her work done by tasking it out to other people. everyone knows that person is worthless.
gee, its the middle of April. I guess you have 2 or 3 weeks to knock this out. Good luck.|||Offtopic:
yes u are right, there are always people who want that others do there work and people who ask questions (most of the time these people are comming to me), wait for answers and didn't do a littlebit one her own.
If u think i am worthless because i ask a question (for a not trival problem i think), it's fine but please don't spam the forum.
And no, i still have 3-4 month.
And yes, i have somethink in my mind, but i want to hear other opinions bevor i talk about it, so that it would not affect the answers.
Back to topic:
1 and 2)
Maybe i will try to make replication for the schema and the synchronized tables or read out the meta-data and use them to synchronize the schema and the tables.
3)
A simple thread can read the old datas from the maindatabase and write them in the archive database. But this is not ACID save. So, i musst find a save way to log the actions and if something went wrong i must undo the work.
4)
Catch every query and check if the result is valid, if not query the archivdatabase.
(But at witch point i should catch the query. At my company(yes i do my final year projekt in a company) we are using Hibernate and i don't know where i should catch the query, after hibernate i think its not possible, in hibernate ... i will not change the hibernate framework, befor hibernate it's not allowed because the application-code should not affect from my changes.
I am thank full for all ur opinions.|||I never said you are worthless and if you did not launch a personal attack on me I would have helped you out today because things are a little light on my end today.
I was simply helping you by telling you how to get a better response here and you did that, but since you do not appreciate my advice you can go some where else.
(_X_)|||Hmm, i didn't mean to launch an attack at u.
I am sorry if it looks like that.
The truth is, that i think that u launched an attack at me and didn't want to help me ;).
So, thanks for ur advice and a nice finishing time.|||Good lord...
In any case, you looking to automate Structure changes in SQL 2k?
Are you sure it's not SQL 2k5?
So you are asking for opinions, what have you come up with so far?|||Jeez, I would never automate cascading schematic changes between databases. There is an all-important phase called "testing" which is impossible to include in automation. Was this thesis your idea, or was it assigned to you?|||@.blindman:
It was assigned to me and wasn't my idear.
The reason for this solution is, that they have some tables that are realy large and grow every Day. To tune-up the access time they want to split the tables in old and new datas. But they want that the work after this is nearly like they have only one Database. So if they make schematic changes to the database (maindatabase) it should cascade to the other database (archivdatabase).
@.Brett Kaiser
2k5 is on the roadmap but at the moment it is still Sql-Server 2k.
If it is only possible with 2k5 than there is no other way then using 2k5.
I am still in the designing-phase.
I think i should try to use transaktion-replikation to synchronize the tables which should be synchronized (like Employees or some think like this) but i still don't know if the table schema will be replikated too and if i could only replikate selected tables, that is somethink i will glean today.
For the tables where the data shouldn't be synchronized (new data in the maindatabase, old data in the archivdatabase) i don't know how to cascade the schema from the tables. So i think i will write a program which willl read the meta-data form the tables (maindatabase) and update the tables in the archivdatabase. Then i have a delay between the struktur change in the main- and the update in the archivdatabase, but that shouldn't make a problem if i did this every time befor i move data from the maindb to the archivdb. In a thread i will move every 24hours (for example) data older then 2 years from the maindb to the archivdb but this is not transaktionsave (ACID) so i will log the status in a file (or maybe in the maindb) so that it will be possible to make somethink like a rollback if the transaktion fails (like a loose of power).
Thanks 4 ur help and opinions|||My buddy at work bought a zero-gravity pen last week. It was developed by NASA for astronauts to use in outer space. It contains a tiny CO2 cartridge that pressurizes the ink cylinder enabling it to write upside-down, or at any angle.
When faced with the same challenge the Russians used a pencil.
If you want to synchronize schematic changes between databases, then script all your modifications and run them on both databases. What you are endeavoring to do violates the KISS principle and my crystal ball tells me you are going to have more down time from process failures and corrupted data than it would take to to proper testing of modifications.
Thus sayeth the blindman.|||Thank u, i think i will do how u say and don't automate the schema synchronisation to "Keep It Simple, Stupid".
For the synchronisation of the datas i will write a program, that will compensate
the datas in the archivdb.
For the moving of the datas i will write a program, that will looking if the data in the maindb should move to the archivdb. The problem is, how to find all the datas which are depending on the moving data, because they all must move to. But i think i can find it out with the Meta-Informations from the tables ... that shouldn't be a problem.
Thank you for your help, opinion, and advice.|||It is absolutely appropriate to automate the update and transfer of the data. It is just modifications to the schema which you should not be automating.
database audit
Does MSSQL 2000 and above have an auditing feature? We have a requirement for tracking activity and history on a particular table. I realize this can be accomplished by a simple trigger but I was wondering if MSSQL had a feature that would accomplish this.
joeTriggers can record inserts, updates, and deletes, but not reads (selects).
You either need to log this activity through stored procedures used to access the data, or turn on SQL Profiler.|||Nope, you need a trigger
http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx
joeTriggers can record inserts, updates, and deletes, but not reads (selects).
You either need to log this activity through stored procedures used to access the data, or turn on SQL Profiler.|||Nope, you need a trigger
http://weblogs.sqlteam.com/brettk/archive/2004/10/20/2242.aspx
Sunday, March 11, 2012
database and tlog backups - MSSQL 2000 & MSSQL 2005
hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!
You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>
|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)
|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.
|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!
|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!
|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!
You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!
|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>
|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)
|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.
|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!
|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!
|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>
database and tlog backups - MSSQL 2000 & MSSQL 2005
hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
Performance_Tuning_Waits_Queues.doc" target="_blank">http://download.microsoft.com/downl...aits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
Performance_Tuning_Waits_Queues.doc" target="_blank">http://download.microsoft.com/downl...aits_Queues.doc
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>
database and tlog backups - MSSQL 2000 & MSSQL 2005
hi all, as we have big problems with sql timeouts and deadlocks during
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>> hi all, as we have big problems with sql timeouts and deadlocks during
>> the backup of the databases and transaction log, i am searching for a
>> possibiliy to give the backup procedure a lower priority, so that the
>> sql requests falling into that period don't fail anymore. is there a
>> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
>> another/better solution to avoid such problems! thx in advance!
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>> Dear Andrew,
>> The nighly backup contains only around 50% of the data because
>> recurrung clean-up jobs are running just before the backup. While the
>> daily backup is just a local one, the nightly one is a remote one.
>> However, I'll pick up your recommendation to backup the tlog more
>> frequently and to apply the integrity check only for the nightly
>> backup. Hope that helps. Thanks so far!
>
the backup of the databases and transaction log, i am searching for a
possibiliy to give the backup procedure a lower priority, so that the
sql requests falling into that period don't fail anymore. is there a
way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
another/better solution to avoid such problems! thx in advance!You can't assign priorities in any sql server version prior to 2008.
What you should do is attempt to find out WHY you are getting timeouts and
address that issue. Without further information, my first guess would be
I/O delays because you are backing up to the same drive system as the data
resides on. If this is the case you could try backing up to another disk
subsystem. NAS devices or iSCSI SANs are very effective for this and quite
inexpensive these days. Heck, for smallish dbs even an external USB 2 drive
enclosure could be beneficial.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I agree with Kevin in that it sounds like you have I/O issues to begin with.
You must also already have deadlock issues and the backups just make it
worse. I also agree with Kevin in that it sounds like you are backing up to
the same physical drive as the data or log files are on and this should be
changed immediately. A well configured system should only see a few % in
overhead when backing up. This depends on many factors but the process
itself is not that intensive that it should cause issues like you report
unless there are issues already.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
> hi all, as we have big problems with sql timeouts and deadlocks during
> the backup of the databases and transaction log, i am searching for a
> possibiliy to give the backup procedure a lower priority, so that the
> sql requests falling into that period don't fail anymore. is there a
> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
> another/better solution to avoid such problems! thx in advance!|||I should have mentioned that you can evaluate I/O performance using
performance monitor (various physical disk counters including avg disk queue
length, avg sec/read avg sec/write). You can also evaluate wait stats using
dbcc sqlperf(waitstats) in 2000 or the sys.dm_os_wait_stats DMV in 2005.
Microsoft has very good documentation on what the waits mean. See BOL and
search for "sql server wait stats" or get this doc for 2005:
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kdt7a10ahahec@.corp.supernews.com...
> You can't assign priorities in any sql server version prior to 2008.
> What you should do is attempt to find out WHY you are getting timeouts and
> address that issue. Without further information, my first guess would be
> I/O delays because you are backing up to the same drive system as the data
> resides on. If this is the case you could try backing up to another disk
> subsystem. NAS devices or iSCSI SANs are very effective for this and
> quite inexpensive these days. Heck, for smallish dbs even an external USB
> 2 drive enclosure could be beneficial.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:01e39c0a-4f3e-4741-97dc-823957f4abfb@.x69g2000hsx.googlegroups.com...
>> hi all, as we have big problems with sql timeouts and deadlocks during
>> the backup of the databases and transaction log, i am searching for a
>> possibiliy to give the backup procedure a lower priority, so that the
>> sql requests falling into that period don't fail anymore. is there a
>> way to do so both on MSSQL 2000 & MSSQL 2005? or does anyone know
>> another/better solution to avoid such problems! thx in advance!
>|||Dear Kelly,
The strange thing is, that the opposite is the case. We are running 2
backups each day, a nightly one to the same hard drive and a daily one
to a different drive. The backup on the same drive causing much less
problems - however this backup also contains less data than the one
during the day. We already spent a lot of time in query optimization
and are aware that the database I/O sometimes run on the physical
limits, so we don't expect significant improvements by investing more
time on improving the queries and the indexes. That's why we hoped for
somekind of "on idle" backup option to start the backup on low prio.
Maybe you can give some advice on the sequence of the backup itself?
The sequence we use is to check db integrity first, then backup
database and finally save tlog. I wonder if the correct sequence in
sense of a full db backup must be to first save the tlog and then the
database? (In regards to performance / Timeouts this probably doesn't
matter at all I guess?)|||See in-line:
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:039df945-a971-4257-9eb8-64d119c0bc97@.r60g2000hsc.googlegroups.com...
> Dear Kelly,
> The strange thing is, that the opposite is the case. We are running 2
> backups each day, a nightly one to the same hard drive and a daily one
> to a different drive. The backup on the same drive causing much less
> problems - however this backup also contains less data than the one
> during the day.
What are these backups? Are they both FULL backups? Why would one have less
data than the other one? Why would you backup to the same disk as the data
in the first place? If the drive fails you loose the data and the backups.
Backing up across the network can be slower than backing up to disk even if
it is on the same drive.
> We already spent a lot of time in query optimization
> and are aware that the database I/O sometimes run on the physical
> limits, so we don't expect significant improvements by investing more
> time on improving the queries and the indexes.
If the database and queries are alreay highly tuned and you still run into
I/O issues you simply don't have the proper configuration to do the job.
There is no getting around that and there are no magical cures. You need to
upgrade the hardware.
>That's why we hoped for
> somekind of "on idle" backup option to start the backup on low prio.
> Maybe you can give some advice on the sequence of the backup itself?
> The sequence we use is to check db integrity first, then backup
> database and finally save tlog. I wonder if the correct sequence in
> sense of a full db backup must be to first save the tlog and then the
> database? (In regards to performance / Timeouts this probably doesn't
> matter at all I guess?)
I bet it is more the Integrity checks that do the damage more than the
backups. Do you only backup the log once a day? Log backups should be
independent of the FULL backups and done frequently. On average they are run
every five to fifteen minutes. Doing a log backup directly before or after a
FULL backup is pretty much useless.|||Dear Andrew,
The nighly backup contains only around 50% of the data because
recurrung clean-up jobs are running just before the backup. While the
daily backup is just a local one, the nightly one is a remote one.
However, I'll pick up your recommendation to backup the tlog more
frequently and to apply the integrity check only for the nightly
backup. Hope that helps. Thanks so far!|||Well that explains some of it. If you delete half the rows in the db the
transaction log backup will be huge. You might want to see if you can run
the cleanup jobs more often to spread the load over the entire day and keep
the logs to a smaller size.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"morebeer" <stefan.beer@.webergy.de> wrote in message
news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
> Dear Andrew,
> The nighly backup contains only around 50% of the data because
> recurrung clean-up jobs are running just before the backup. While the
> daily backup is just a local one, the nightly one is a remote one.
> However, I'll pick up your recommendation to backup the tlog more
> frequently and to apply the integrity check only for the nightly
> backup. Hope that helps. Thanks so far!|||Or possibly interleave tlog backups within the cleanup job itself. Delete
some fixed number of rows, backup tran, repeat until no rows to delete.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ummehUQMIHA.748@.TK2MSFTNGP04.phx.gbl...
> Well that explains some of it. If you delete half the rows in the db the
> transaction log backup will be huge. You might want to see if you can run
> the cleanup jobs more often to spread the load over the entire day and
> keep the logs to a smaller size.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "morebeer" <stefan.beer@.webergy.de> wrote in message
> news:4c78e828-4c57-4c70-9262-486e1622ae5d@.e1g2000hsh.googlegroups.com...
>> Dear Andrew,
>> The nighly backup contains only around 50% of the data because
>> recurrung clean-up jobs are running just before the backup. While the
>> daily backup is just a local one, the nightly one is a remote one.
>> However, I'll pick up your recommendation to backup the tlog more
>> frequently and to apply the integrity check only for the nightly
>> backup. Hope that helps. Thanks so far!
>
Wednesday, March 7, 2012
Databade Suspect
Hi ,
I need help , As I have mssql server with the database . Now I can not
access to this Database because the data have the suspect . How can I
Resetting the Suspect ?
Thanks and regards ,
ChuongTrang
UPDATE master..sysdatabases SET status = status^256 WHERE name = <dbname>
If the database still goes back into suspect mode, and you can't fix the
original problem, and you have no recent backup, then you can get
information out of the database by putting it into emergency mode. If you
do this, extract the data/objects out with BCP/Transfer Manager and then
rebuild the database. Note that the data may be corrupt or transactionally
inconsistent.
Issue the following command to put the database into emergency mode (you'll
need to allow updates first)
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Trang" <trangtran86@.hotmail.com> wrote in message
news:Oz9Ua24dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I need help , As I have mssql server with the database . Now I can not
> access to this Database because the data have the suspect . How can I
> Resetting the Suspect ?
> Thanks and regards ,
> Chuong
>
I need help , As I have mssql server with the database . Now I can not
access to this Database because the data have the suspect . How can I
Resetting the Suspect ?
Thanks and regards ,
ChuongTrang
UPDATE master..sysdatabases SET status = status^256 WHERE name = <dbname>
If the database still goes back into suspect mode, and you can't fix the
original problem, and you have no recent backup, then you can get
information out of the database by putting it into emergency mode. If you
do this, extract the data/objects out with BCP/Transfer Manager and then
rebuild the database. Note that the data may be corrupt or transactionally
inconsistent.
Issue the following command to put the database into emergency mode (you'll
need to allow updates first)
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Trang" <trangtran86@.hotmail.com> wrote in message
news:Oz9Ua24dDHA.3660@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I need help , As I have mssql server with the database . Now I can not
> access to this Database because the data have the suspect . How can I
> Resetting the Suspect ?
> Thanks and regards ,
> Chuong
>
Friday, February 24, 2012
Data update
Hello all,
I'm new in this newsgroup and I apologise if my question has already done.
Let me knwo if FAQ is avaible somewhere.
I use msSQL server to manage data for a B2B portal.
Orginal data comes from as400 db2.
I need to update msSQL data from db2 source.
I used MetaDataService executed by SQL server Agent, but dosn't work as
excepted 'cause I need to delete all row in a table and then copy all new
rows, this works fine but what happen if an user is quering data while sql
agent starts?
I need to make data update very often so I need other solution.
The update procedure must follow these rules:
1- check diffrents between a table in DB2(source) and the same table on
msSQL server
2- update my msSQL table if some diffrents was found.
I'm a beginner with msSQL server, any advices are welcomed, I need a startin
point.
please help.
thank you all
kindly regards
AlbertoIt all depends on your timing. If it's only for a few seconds, the
risk may be worthwhile in comparison to the development time. If it's
longer, I'd suggest adding in a 'work' table to do the import into (if
this is what takes the time) and then moving the date 'en-mass' from
there. This may give you the improvement you need.
I have a similar situation where I have to generate a lot of data from
various sources and replace it in a live system after truncating the
existing data. I do the generation work in a seperate table and then
move it over afterwards. The final part takes a couple of seconds to
do. You could always add in something for these few seconds to
introduce a deliberate delay to the user if the table isn't populated
(maybe hold a flag somewhere in a 1 row table ?) and then release it
when you have finished.
Ryan
"Alberto" <alberto@.nomail.it> wrote in message news:<cof0ge$9eq$1@.newsreader.mailgate.org>...
> Hello all,
> I'm new in this newsgroup and I apologise if my question has already done.
> Let me knwo if FAQ is avaible somewhere.
> I use msSQL server to manage data for a B2B portal.
> Orginal data comes from as400 db2.
> I need to update msSQL data from db2 source.
> I used MetaDataService executed by SQL server Agent, but dosn't work as
> excepted 'cause I need to delete all row in a table and then copy all new
> rows, this works fine but what happen if an user is quering data while sql
> agent starts?
> I need to make data update very often so I need other solution.
> The update procedure must follow these rules:
> 1- check diffrents between a table in DB2(source) and the same table on
> msSQL server
> 2- update my msSQL table if some diffrents was found.
> I'm a beginner with msSQL server, any advices are welcomed, I need a startin
> point.
> please help.
> thank you all
> kindly regards
> Alberto
Data Type that accept . for MSSQL
Hi,
I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?
I'm using MSSQL Server 2000.
Please advice.varchar, nvarchar..?|||Thanks.
Tuesday, February 14, 2012
Data Transformation Services
How can I make a DTS in MSSQL Server Management Studio Express?
I know how to do that in SQL Server Enterprise Manager.
Is this restricted in the express?
Thanks.
SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
|||I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"
Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?
Data Transformation Services
How can I make a DTS in MSSQL Server Management Studio Express?
I know how to do that in SQL Server Enterprise Manager.
Is this restricted in the express?
Thanks.
SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
|||I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"
Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?
Subscribe to:
Posts (Atom)