My question is recommendation for setting up maintenace plans that are safe and complete. What do you recommned I do for setting up a plan that backs up, and deletes old files older than 5 days. Should I do anything else and why?
thank you!
I made a backup maintenace plan in sql7.0, but it doesn´t work. The message included on the event logger is:
SQLServerAgent
SQL Server Scheduled Job
'DB Backup Job for DB Maintenance Plan 'Teste''
(0x219C5CB03C11D4119D8F0020188D7311) -
Status: Failed - Invoked on: 4/17/00 3:25:00 PM - Message:
The job failed. The Job was invoked by Schedule 12 (Schedule 1).
The last step to run was step 1 (Step 1).
And before this message there is another one that can be making something goes wrong:
SQLServerProfiler
Executable: C:WINNTsystem32mmc.exe
Description: Unable_To_Set_RegKey_Val
Source: ........srceventlog.c
Line: 315
Someone can help me telling me the reason that is generating this kind of error and doesn´t work. I have do the same think in others two server and there it works ok.
Thanks in regards
Sandra Lino
I have set SQL Database Maintenance plan in SQL Server 2000. it ws working fine but now it didnt work and fails everytime. can anyone advise me how to identify the reason why its not working.
View 5 Replies View RelatedI am trying to "Edit Recurring Job Schedule" in a new maintenance plane,however in start and end times there is no option or display for AM or PM toselect. I have seen on other systems an available AM/PM in the time window.I tries entering the time in 24 hour format, but that was not recognized.thank you
View 1 Replies View RelatedHelp!!!
Our Database Maintenance Plan has stopped backing up the database. I can manually backup the database but the plan does not work. We tried creating a new plan but the new plan is not backing up either. Any ideas would be appreciated. Thank you for your help!
Hello All,
Few days before, I had setup Maintanance plan for taking backup in 2005. It was running nice.But suddenly it stops automatically. It is not creating log file too.
Does anyone faced this type of problem ? Or does anybody knows the workaround for this.
Thanks in advance
--kneel
I am moving from one physical server to another. I have a full working maintenance plan backing up the databases on the production server.
Is there a way to "script" / move the maintenace plan from the productiuon server to the new one easily?
thanks for help!
www.mirrorgate.com
Hi again,I have setup a maintenance plan schedule, my question is; can I create anadditional plans located on another network location ?In my attempts the only locations displayed are those on the current server.thank you
View 1 Replies View Related
Does any body have any good recommendations for maintenace plans?
Here are few questions I have.
When should indexes be re-indexed?
What should be done first? Reorganize indexes or rebuild indexes?
How often backups should be done and what kind?
How often should database statistics be updated?
Do database statistics need need to be updated on system databases?
How often should a database integrity check be done?
How long should history be kept for?
What is a good order for tasks should be done?
Any input would be great
Eytan
Does any body have any good recommendations for maintenace plans?
Here are few questions I have.
When should indexes be re-indexed?
What should be done first? Reorganize indexes or rebuild indexes?
How often backups should be done and what kind?
How often should database statistics be updated?
Do database statistics need need to be updated on system databases?
How often should a database integrity check be done?
How long should history be kept for?
What is a good order for tasks should be done?
Any input would be great
Eytan
Is only one plan is kept for one query in plan cache?
i heard generally hash is created for a query and plan is search with this hash.
We have a debate in our team about embedded SQL vs. Stored Procs.
The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?
I can't find any definitive information on pros and cons between the two methods.
If there are no major performance issues, or gotchas, I guess it comes down to developer preference.
SP Pros:
- Great SQL support in VS.NET (dev, debug, integration)
- Seperation of database specific code from middle tier.
- Less lines of code in middle tier
- VS.NET support for .xsd dataset definitions.
- Logic closer to data for more demanding processes.
Embedded SQL Pros:
- Less artifacts for version control
- Better encapsulation of logic
Any info would be appreciated.
thanks
Kevin
I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
View 1 Replies View RelatedThe benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies View RelatedHello, everyone:
Does any one know the good T-SQL practice topic web site? It is better to include solutions. Thanks a lot.
ZYT
When setting up databases for end users, what's the best practice regarding who's the dbo for each individual database - the user itself or a sysadmin?
Does it really have any importance at all who the owner (as defined by 'dbo') is ?
(Terms)
TermID, Term
1----- Abc
2----- Arcico
3----- Tunic
and
(RelatedTerms)
TermID, RelatedTermID
1 ------ 3
1------ 2
2------ 4
I want to get the following results
1.- a list of all the terms that start with A%
2.- a list of all the related terms … that belong to terms that start with A%
For number 1 - I am doing a select on Terms table with where term like A%.
For number 2 – I am joining both tables and then once again doing a where term like A%.
Would it be more efficient to take the first results and put them in a table variable, and then just do a join with the second table RelatedTerms.TermID = Terms .TermID
The number of records that generally comeback are between 500 to 1000 records that
What would you consider is a better approach ? or maybe there is an even better way ?
Wasn't sure where to ask this question.
Was wondering what everyone is doing in regards to server/db protection?
Do you run your DB's on independent servers?
Do you run your DB's on clustered servers?
Do you run your DB's on redundant haardware w/ a 3rd party
application for bit-to-bit data replication?
Is anyone placing the DB's on a SAN's?
We're at a crossroads, and looking for a good direction to ensure the DB's are up.
Any insight welcome.
Thanks,
Kerry
hello
i'm a newbie for sql , but i want to learn sql on my own , is there any way that i can learn sql , do i have to download sample database from the internet, do i need to have my own server to play with. Hopefully someone show some lights on this.
regards
sutha
Please point me to a web resource from where I can study:1) writing complex queries such as those involving HAVING, mult-levelnested queries, GROUP BY, T-SQL functions2) Joins - a lot of practice3) Stored Procedures, transactions, cursors and triggers - I need someheavy-duty practiceWhere can I get some good practice of the above? Also, please recommenda good SQL Server/T-SQL book in the light of the above requirement.
View 3 Replies View RelatedFolks - had a look around Google and no surprises, but never found what i was looking for.
I want to see a real work best practice C# Stored Procedure for Sql 2005 (express is what i am using, but don't mind the Sql edition).
Almost everything i see is a "select * from table" which to be honest was my first stored proc many years ago - everything since has been fairly detailed.
I ask as i am sceptical, after years of trying to STOP building Sql queries in code (as it's hellish!) that the CLR technique really makes any kind of a diffence.
If someone has found that it HAS i'd love to hear about it. The thought of:
SqlCommand cmd = new SqlCommand ( "My Whole Stored Proc as Text" );
... doesn't appeal, never mind the potential for debugging syntactical issues and so on.
I was excited by this, until it became something i had to do in a real situation and then i got a little worried. Should i be?
create a table and name it Salary Information. Add an Employee Name and Salary column to the table. Create a column in the Employee table and name it Salary. Create a trigger that updates the Salary table with the employees's name and salary each time u insert data into the Salary column of the Employee table.
I tried but it didnt work!
Pls, help me!
Hi, my database is growing over 1Gb, and I only have one .mdf to keep them all. Should I use a secondary data file for my data? Can I do that now? Thanks.
View 5 Replies View RelatedGood Morning,
I work for a company that has sees alot of people come and go. The one thing I have noticed is that people use their admin accounts to log into SQL and create sp, views and databases.When the user leaves I am stuck with all these objects that are owned by somone no longer working for the company.
So my question to you guys is: What is the best practice to use in creating new objects?
Thanks for your guru-ness!
say i have a customer.aspx that allows a user to enter in customer data.
on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customerbut some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none"
Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?
Hi.
We have developed as quite simple ASP.Net webpage that fetches a number of information from a SQL 2005 database. We are having some problems though, becuase of a firewall that is beetween the webserver and the SQL server, and I think this is because of bad code from my part. I'm not that experiensed yet, so I'm sure that there is much to learn.
Usualy when I do a query against a SQL database, I do something like this:
Function GO_FormatRecordBy(ByVal intRecordBy As Integer) Dim dbQueryString As String Dim dbCommand As OleDbCommand Dim dbQueryResult As OleDbDataReader dbQueryString = "SELECT Name FROM tblRegistrators WHERE tblRegistratorsID = '" & intRecordBy & "'" dbCommand = New OleDbCommand(dbQueryString, dbConn) dbConn.Open() dbQueryResult = dbCommand.ExecuteReader(CommandBehavior.CloseConnection) dbQueryResult.Read() dbConn.Close() dbCommand = Nothing Return dbQueryResult("Name") End Function
Now, lets say that I have a DataList that I populate with Integer values, and I want to "resolve" the from another table, then i do a function like the one above. I guess that this means that I open and close quite alot of connections against the database server when I have a large tabel. Is there any better way of doing this? Chould one open a database connection globaly in lets say the ASA fil? Whould that be a better aproch?
When I added the CommandBehavior.CloseConnection to the ExecuteReader statment, I noticed that it was a bit faster, and I think there was fewer connections in the database, so maby there is more to the "closing connections" then I usualy do.
Any tips on this?
Best reagrds,Johan Christensson
Ok.
I recently started developing a web site for a client using storefront.net and ms sql server.
the db schema of storefront.net has autonumbers as the PKs for the products table (even though the products table contains an additional field for product_number.)
So here's my dilemma if you care to read:
I typically develop local, deploy remote (after testing). I have a local SQL server, and then the remote SQL server.
When I'm developing for this project, I'll insert data such as products to the products table (sometimes several times while i'm working out routines to import data to the products table.) this has the effect of creating a unique ID for each product based upon SQL auto-incrementing INTs.
This StoreFront.net (SF.NET) has another table that is a lookup table. For each part number, it has a corresponding categoryID number.
Now, if i have product_ID 1234, and I set the category ID to say 10 and get it working on my local box, every thing is fine.
Here's where the problem comes in: When I use DTS to transfer the database during remote deployment, each product is inserted into the remote DBs products table and gets a NEW product id. Same with the categories.
This has the effect of breaking the relationships. (SF.NET has no ref integrity nor relationships defined in the db.) let's say my product_id 1234 gets put into the remote copy, it'll get a new product_ID (PK). let's say it's now 5775. now my category ID will also get a new value. so my data is now not related.
I don't know how to handle this situation. The unique IDs generated on my local sql will nearly almost always be different from those generated on the remote db.
How do i handle this situatoin is my question? advice, guys?
Hi to everyone! I've to create a little auction system that runs on web. Before starting developing, I'll would like to be sure to use the best practice...The main aspect is to avoid conflicts on database updating with bids, i.e. if a user places his bid I've to be absolutely sure that his bid is the highest at the moment of updating database. If not, I have to refuse it...So I ask you: using transaction is the best way for assuring the non-conflicts? And may I have to be careful of some other aspect in ASP.NET pages? Or there's no problem of conflicts at page level?Thank you very much in advance for any suggestion, and If anyone has some other thing to say about possible problem on auctions I'll be glad to hear him!!!! ;-)
View 6 Replies View RelatedFollowing works fine in QA
master..xp_cmdshell 'osql -Usa -E -h -w250 -Q"set nocount on exec unallocated" -dTest -oc:est.txt' ,no_output
but does not from within a sp
err -
Server: Msg 170, Level 15, State 1, Procedure test, Line 2
Line 2: Incorrect syntax near 'master'.
Thank you.
Ivan
I have around 10 databases currently residing on different platforms which make-up for roughly a terrabyte of information. I would like to migrate all of these DBs over so that they are all managed under one instance of SQL server 2K. In my view this streamlines things a lot and reduces costs of licensing/hardware.
However, is managing all of these databases on one clustered instance of SQL 2k the best approach from a performace stand point? Would it be better to seperate each database onto its own machine? I am under the impression that given enough hardware (processors, RAM) using just one instance of SQL 2k enterprise should be enough to perform the mangement of this data. Is this correct? Is there an optimal model?
Money is always a concern but in this case, performance is the main objective. The size of the data managed will be growing significantly so the system should be scalable.
My background is as a developer so I may not have provided enough to give a good answer. Please ask questions if you need more detail. I am looking for suggestions on the best way to handle this.
Specifically I would like to know the preferred architecture as well as any suggested hardware.
Thanks in advance
I'm building a database that has maybe four unique tables Student,
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.
Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !
* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)
In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...
There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :
if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
------------------------------------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)
3) it could be done (maybe) using Functions in SQL SERVER...
There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!
I'll appreciate your help ...
Thanks in Advance . ! .
Need the following question addressed, as it keeps coming up in our development meetings and has been creating a divide. Pease voice your opinion.
To keep it simple, we have Table1 which identifies several questions that are revised on a regular basis. One of it's columns is called "Revision Status". Within revision Status, we would like to identify the possible status of a question such as:
New Questions;
Revised;
Resubmit;
Inactive;
Active;
...as well as several more.
I'm of the mind to have these in a seperate table identified with a unique ID... call it StatusTable.
Such as:
1 New Questions;
2 Revised;
3 Resubmit;
4 Inactive;
5 Active;
However others feel, just use the "Revision Status" column and simply use the numbers "WITHOUT" a table or description. The developer documentation will tell the developer which number equals the description. ie the following would be found in the Revision Status column.
1
2
3
4
5
My mind says the above is ilogical. I would rather join and say in my statement:
WHERE StatusTable.Status = 'Inactive'
Where the other way would be
Where [Revision Status] = 4
I hope i'm not being thick-headed.
Please advise.