473,542 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

back up jobs, re-index question

We have a db that is accessed by users on the web 24/7. Problem is
when our maintenace job runs users are getting killed with this error:
Database Error: SQL Error #-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
canceled the distributed transaction.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 37000)
(NativeError: 1206)

I'm assuming that one of the steps in the maintenance job causes this
because it happens every night at the same time as the job
Here are the steps in our job:
1. differential backup
2. kill users
3. Alter database DBNAME set Recovery BULK_LOGGED
4. reindex all tables
5. Alter database DBNAME set Recovery FULL
6. truncate log
7. shrink log
8. full backup
9. resize database (if needed)

Question is do we need to kill users before a re-index, re-size or
backup occurs? if we don't will it just slow the process up? any
other step that would cause that error?
Ideally we need a different design for a web accessed solution but we
need a quick fix now
thanks in advance
Jun 27 '08 #1
2 3207
"esource" <da********@sha w.cawrote in message
news:08******** *************** ***********@k13 g2000hse.google groups.com...
We have a db that is accessed by users on the web 24/7. Problem is
when our maintenace job runs users are getting killed with this error:
Database Error: SQL Error #-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
canceled the distributed transaction.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 37000)
(NativeError: 1206)

I'm assuming that one of the steps in the maintenance job causes this
because it happens every night at the same time as the job
Here are the steps in our job:
1. differential backup
2. kill users
This is not necessary.
3. Alter database DBNAME set Recovery BULK_LOGGED
4. reindex all tables
5. Alter database DBNAME set Recovery FULL
6. truncate log
Why are you doing this step? If you really don't care about the log, set
the DB to simple.
7. shrink log
Don't do this at all. It's detrimental.
8. full backup
9. resize database (if needed)
Same thing, do't do this.

http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
Question is do we need to kill users before a re-index, re-size or
backup occurs? if we don't will it just slow the process up? any
other step that would cause that error?
It'll slow things down, but if you do it at night and intelligently, you
should be fine.

Ideally we need a different design for a web accessed solution but we
need a quick fix now
thanks in advance


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Jun 27 '08 #2
On Thu, 24 Apr 2008 07:53:25 -0700 (PDT), esource wrote:
>We have a db that is accessed by users on the web 24/7. Problem is
when our maintenace job runs users are getting killed with this error:
Database Error: SQL Error #-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction manager has
canceled the distributed transaction.
(Source: Microsoft OLE DB Provider for ODBC Drivers)
(SQL State: 37000)
(NativeError : 1206)

I'm assuming that one of the steps in the maintenance job causes this
because it happens every night at the same time as the job
Here are the steps in our job:
1. differential backup
2. kill users
3. Alter database DBNAME set Recovery BULK_LOGGED
4. reindex all tables
5. Alter database DBNAME set Recovery FULL
6. truncate log
7. shrink log
8. full backup
9. resize database (if needed)

Question is do we need to kill users before a re-index, re-size or
backup occurs? if we don't will it just slow the process up? any
other step that would cause that error?
Ideally we need a different design for a web accessed solution but we
need a quick fix now
thanks in advance
Hi esource,

And here are some other comments that do not address your question:

1: Why do you reindex all tables? Is just reorganizing or rebuilding the
most fragmented indexes not sufficient in your case?

2: Why do you shrink the log? It slows down performance later (as it has
to grow the log again) and will cause your log file to become
fragmented. See http://www.karaszi.com/SQLServer/info_dont_shrink.asp.

3: Same goes for "resizing" the database. Growing it before autogrow
kicks in is a good thing; shrinking it is not. Especially not right
after reindexing, since the shrinkprocess will cause your indexes to
become fragmented again...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jun 27 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
3298
by: Digital Puer | last post by:
I'm looking at some software development jobs whose listings require C++ experience. My history is that I have a strong C background, and a few years of C++ from undergrad classes. I've been working with Java since 1997 and consider myself a decent Java programmer. Now, if I apply for C++ jobs, do you think it's important that I know the...
12
2154
by: Jim H | last post by:
We ahve a .NET web page that we dynamically create a bunch of DataGrids and add them to a place holder. When the user hits submit we need to go through all of the DataGrids to get some data. We can't seem to find the grids on the post back, the PlaceHolder.Controls.Count is 0. How do we get that data back. The DataGrids have check boxes...
132
5604
by: Kevin | last post by:
I don't know if I should even start this topic but here goes. I'm an ex vb6 developer, now developing in C#. The reason why I started developing in C# is because the company that I worked for at the time standarised on C#. Many of my friends working in previous companies that I worked for are starting to move back to VB.Net. When I asked...
182
7366
by: Jim Hubbard | last post by:
http://www.eweek.com/article2/0,1759,1774642,00.asp
1
1829
by: rkbnair | last post by:
Using VB, is there any way to manipulate print jobs? I mean, combine print jobs, change the order of the jobs and so forth? Thanks
2
6660
by: Eamon | last post by:
Hi all, I'm trying to find a way to list all the jobs/schedules due to run between two dates but all I seem to be able to get is a list of jobs with their NextRunTime. Example: Say I had two jobs. The first job runs hourly on the hour and the second runs daily at 2pm. Now if it was now (26 Jan 2007 11:20am) and I want to find out what jobs...
0
1290
by: Koretex | last post by:
Hi, I am able to run all my DTS jobs manually, but when I try to run the shceduled jobs for those DTS, they all fail. It seems to be a security issue, but no matter who the owner is of job or dts, the jobs fail. This has started today as a complete reinstall of OS and SQL Server was needed after a hacker got in to the server and caused havoc....
5
2556
by: =?Utf-8?B?RkxEYXZlTQ==?= | last post by:
I'm developing an application that gets data from 100 sources (via telnet connections, but you can think stock quotes from a webservice if you like). I was planning on using the thread pool (25 at a time). I know I would start all 100 at once and as threads finish, a new thread would become available and the next one would start. However,...
16
2157
by: Mikhail Kovalev | last post by:
Was reading an article the other day (now offline for some reason) which included some "new lines of code"-statistics, this is different, but shows the same trend: http://www.simplyhired.com/a/jobtrends/trend/q-asp,+jsp,+php,+ruby,+python,+coldfusion,+perl http://www.indeed.com/jobtrends?q=perl%2Cpython%2Cphp%2Cruby What happened?
10
2505
by: Ranjana | last post by:
Hello I am PHP developer .Let me know if you have any contract base php jobs.
0
7392
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7721
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7670
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5867
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4880
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3380
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1798
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
943
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
620
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.