Database Administration/Maintenance - Guidance Needed
May 16, 2006
I have been working SQL server for many years as a web developer, however in my current position we are lacking an expert in maintenance and admninistration as the company has recently switched over to SQL Server 2005.
I therefore need to develop a greater understanding of the best practices with regards to administration and am a bit lost as there are so many options which seem like they might match my requirements. As I have never looked beyond the basic administration/maintenance side of things before I have no idea which options to investigate.
Basically I want to clear any records older than 3 months old on a scheduled/automatic basis. There seem to be various options that could do this, however I want to know what the best option is, rather than waste a lot of time and risk problems by resorting to trial and error.
We are already backing up this data, however I am not sure if I should combine these tasks. It is highly unlikely that we will ever have to restore these older records, however we do need to store the data for legal reasons.
I dont expect anyone to tell me everything, as I am happy to investigate, I just need a nudge in the right direction.
Thanks
maw
View 3 Replies
ADVERTISEMENT
Sep 20, 2007
Hello Dear Forum! I am new to this web. I am now days working with .NET Tecs especailly ASP.NET. I have developed a small school system in which i used SQL MDF file. I want to know about its approach if they want to make it online. and one more question, is MDF file is best for more than 10,000 Students? Can i convert the MDF into normal table format like SQL Server 2005 , i am using SQL Server Express.
Take Care.Wating for reply.
Developer
View 4 Replies
View Related
Aug 8, 2007
I've got a lot of XML like this (simplified):
Code Snippet
... 8 MORE
... 9 MORE TIMES
I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:
Code Snippet
CREATE TABLE ELEMENT1 (
[ID] INT IDENTITY
)
CREATE TABLE ELEMENT2 (
[ID] INT IDENTITY,
[ELEMENT1_ID] INT
)
CREATE TABLE ELEMENT3 (
[ID] INT IDENTITY,
[ELEMENT2_ID] INT
)
With primary and foreign keys as you'd expect, and, of course, many more columns!
How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).
The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.
Any ideas or pointers to articles would be welcome.
View 14 Replies
View Related
Apr 25, 2008
Hello
I've been given the task of migrating a DTS package to SSIS (neither of which I am particularly familar with). The first job in the DTS package is to read a .ini file and set a bunch of variables. These variables are then used throughout the DTS package. After running the DTS package through the SSIS migration wizard this job turns into an execute script task and I can't see if it is still reading the .ini file. However, the only real purpose of this step is to allow different parameters to be passed in development, test, production etc. So I am thinking this whole step can be removed and effectively replaced with a package configuration (I'll probably use an XML file). My understanding is that by selecting the name/value pairs as appropriate in the XML package configuration file means this values will be passed in at runtime and achieve the same functionality. Is this the correct way to do this in SSIS, or do I still need the .ini file and variables?
Thanks for any advice on this issue.
Regards, John
View 7 Replies
View Related
Sep 12, 2007
HI
I know what sort of backup I want to accomplish with a given database, and I understand that the maintenance plans section is intelligent enough to give it to me, but I cant quite seem to configure it the way I want..
For the first week in the month I want to do a full backup every Tuesday at 7am and for the rest of the week, inc the following monday I want to append 10 min trans log backups to it. I then want to reinit and start a new backup file on the tuesday in the second week and do the same. the same thing for the next week and the next week until we get back to week one again.
so a 4 week schedule with each week dumping a full and trans log backups to its own backup set until we rotate back to week 1 again.
The schedule and the different files are the important bit. I assume I can create 4 backup objects named week1 to week4, create a job that references each one (with full and trans log backups) and then set the schedule for each job to occur weekly every 4 weeks. easy, yes?
Im just after alittle confirmation. whilst im not a numpty SQL is on a learning curve for me that I dont particulalrly want to be on... anyway.
Comments please.
Cheers
Alastair
Methodology.
"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
View 9 Replies
View Related
Oct 5, 2001
To All SQL Guru's
I have this simple problem. How can I make SQL Enterprise Manager list only one database that is accessible by client who registered the SQL Server on his / her Enterprise Manager. The user has been granted access to only one database. But every time I register the server in the client's SQL Server Enterpise Manager it lists all database and user database with it.
I just want it to show only one or two or as how many database that login has granted access to and eliminate the database from the SQL Server Enterprise Manager's database list.
Please Help
Thanks
View 3 Replies
View Related
Jun 26, 2005
Hi...
Does anybody know of an open source script I can download that will enable me to manage an SQL database? I basically need to access and modify the database from any machine that may not have SQL Enterprise Manager Installed. I was thinking of writing my own script to do this but I assumed that there is probably already one out there.
View 6 Replies
View Related
Jul 23, 2005
Hi,We are a systems research group at the Computer Science departmentatRutgers University, and are conducting a survey to understand detailsabout network, systems and database administration. We hope that thisinformation would help us recreate a realistic environment to helpresearch in 'systems management'.We request network, systems, and database administrators to take thissurvey. As an incentive, all surveys completed in their entirety willbeentered into a drawing of a number of $50 gift certificates (fromAmazon.com).We hope you have a few minutes to take the survey which is located at:http://vivo.cs.rutgers.edu/administration_survey.htmlResearch in our group:The goal of our research is to improve the overall availability andmaintainability of services. Since administrators form an integral partof these services, a key aspect of this work is to build environmentsand tools that ease the task of service administration. In particular,environments which would help administrators know how their actionsmight impact the real service (before performing them for real), webelieve would be useful in preventing inadvertent actions. This surveytries to understand the existing environments, what administrators docurrently to test the 'validity' of their actions, and the difficultiesthey face in doing so. The two specific systems we are looking at arenetworks and databases, as we believe these are important components ofmany services.If you have any questions regarding this survey or our work, feel freeto email us:Fabio Oliveira (fabiool at cs || rutgers || edu), orKiran Nagaraja (knagaraj at cs || rutgers || edu)Thanks for your time,Fabio OliveiraPhD student, Vivo Research Group (http://vivo.cs.rutgers.edu)Rutgers University
View 4 Replies
View Related
Sep 10, 2006
Hi All, I will shortly (hopefully) begin devleoping an ASP.Net web application to let users manage a remote sql server 2005 database (kind of like the php myAdmin tool for MySQL databases). Were not talking a server managament tool for DBAs, more an end user tool for managing personal databases (it seems that web hosting companies provide tools for MySQL, but if you use SQL Server, you're stuck unless you have management tools on your PC - and what about mac users?).Given that premise, what features should i attempt to include? Creating and modifying tables structures and data is an obvious inclusion, but what else? I also haven't yet decided whether to go about this using scripts or using SMO. I've read that SMO is backwards compatible across versions of SQL Server...how true is this? are there issues or is it seamless? Please post any comments or thoughts you have, and thanks for taking the time.
View 4 Replies
View Related
Apr 29, 2007
The Topics Covered In This Book Are: Understanding the OracleEnvironment; Understanding the Oracle Instance; Understanding theworking of Oracle Instance; Understanding the Oracle Database; Oracle9i Software Installation; Oracle 9i Database Design using DBCA;Enabling other computers to access Oracle Server; Oracle EnterpriseManger Oracle Backup & Recovery -Simple Technique; Oracle PerformanceTuning. Everything in this book is covered in a step by step mannerbyfirst building reader's concept using analogies, real world examplesand then taking him/her to the steps of design implementation. Thebook covers Oracle 9i Database Server. The concepts and most of thestuff discussed in this book are equally good for other Oracleversions including 10g. For advance user please check Oracle DatabaseAdministration Concepts & Implementation Made Simple, ISBN:0977073904.The link is provided below:http://www.lulu.com/takveen
View 1 Replies
View Related
Jul 6, 2007
Learn Oracle Database Administration in 10 Minutes. No kidding. Checkit yourself,http://www.takveen.comOnly good analogy makes complex concepts simple!
View 2 Replies
View Related
Jul 14, 2004
I have been trying to rescue my company's helpdesk database because the company moved premesis and we lost that particular server.
It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQLData directory without running a backup through Enterprise Manager.
Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!
thanks in advance for any help you can provide.
View 14 Replies
View Related
Jul 20, 2005
I have a HUGE project (at least for me) and need some guidance.I am trying to create a database for a local university movie clubthat allows users to input there basic personal information (name,address, telephone number) as well as movies in there collection. Themovies will be categorized by genre (comedy, romance, horror, etc.)and title. I want to be able to let the users add and remove moviesto their list of movies they own, I'll call it "MOVIES I OWN".The user will also need to be able to create a SECOND list of moviesthey would like to see, again they can choose by genre and title.They need to also be able to add and remove from this list also, i'llcall it "MOVIES I WANT TO SEE".The last part of the project will be to match the users of "movies iwant to see" with "movies I own" users. It will be displayed on thelocal university website when the user logs in and will alert the userto the match. If there is a match at a later time, maybe the user canbe emailed? Also if there is a match, perhaps the two movies can betaken off record after the user acknowledges the match. I would needit to be able to handle a small amount of users now logged in at thesame time , but would like for it to eventually handle several hundredusers logged on at the same time in the distant future with outperformance problems.I am not sure if sql 2000 is the best to get this done or perhapsoracle. I am currently trying this on mySQL with PHP but currently amlost in a forest of data. Any guidance suggestions will be greatlyappreciated. I am pretty new to this so please be kind...
View 3 Replies
View Related
Oct 9, 2007
Hi Guys
I'm new to this Forum and I downloaded SQL express advanced today, I'm more a network secuirty but I wanna save my company some money in create and quoting system and will work in the travel arena. However I'm not sure what querying program to use to pull quotes out and have options to email them as well. This would be the client side part, which records data.What program could I use to do this???
View 3 Replies
View Related
Sep 6, 2006
Hi,
We've decided to use SSIS for an ETL type project. We are going to convert some files to our special flat file format. Our special flat file format will not change, but we will need to convert about 40 different style flat files into this format. We will receive any of these files at random intervals. We will likely have to add more file formats to the 40 at some point, too.
I'd like to use a flexible design, but I just started with SSIS and its going to be hard to test all the possible combinations, so I hope I can find some guidance in this forum.
What would be a good approach to allow for the most reuse of work to support these various file formats?
View 4 Replies
View Related
Aug 24, 2007
I am the IS manager for a medium business. I am also the DBA and the programmer and help desk and trainer etc etc.
In 2003 I embarked on a project which went live in 2005. We contracted to have our order processing system rewritten into SQL. I went from a character based 4GL language called Progress running in a UNIX enviroment to a knock down, drag out, full blown microsoft solution. I have been operating this new system for two years making modest enhancements and improvements as my skills with microsoft are increasing and improving (retraining my brain to think objects)and hiring contractors to do the really cool fun stuff.
I have to learn Server 2003, SQL Server 2005, SQL Express, T-SQL, Replication, Reporting Services, SSIS, IIS, DTS, Visual Source Safe, Visual Studio, VB, C#, ADO.NET and the .NET Framework fits in here too although I don't really know where.
I hope you are starting to understand my feeling of being 'in over my head'. Right now everything is running perfect. I do backups, restore them and create new reports. Basic stuff.
On top of this our company has merged with a larger one and this bigger company does not have a microsoft solution. They have an AS400 home grown application that I do not want to learn. I will not go backwards!!!!! I will loose my job/position in the next couple of years. I want certifications (to get that interview).
I just don't have the skills to back up the certification (I don't want to be one of those exam cram people) I do a plethora of unrelated taks all day long. Not because I have to do them I choose to do them. I like both DBA and Programming.
So, if you were me, and could get certifications and have experience to back them up what would you do first?
View 4 Replies
View Related
May 2, 2007
Greetings All,
I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:
95752,95725,0001,0, ,N
11473,8704,0001,0, ,N
1279215,436612,0001,0, ,N
43595,43592,0038,0, ,N
and I need to get to here:
95752 ,95725 ,1 ,0 ," ","N ",0
11473 ,8704 ,1 ,0 ," ","N ",0
1279215 ,436612 ,1 ,0 ," ","N ",0
43595 ,43592 ,38 ,0 ," ","N ",0
Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.
Any feedback in regards to getting pointed in the right direction would be greatly appreciated...
View 5 Replies
View Related
Apr 10, 2007
im trying to convert a sql stored procedure to a ssis project as part of a learning experience, however, im not sure where to even begin. im looking to see if someone can help my find my footstep.
basically, the sproc looks through a table and pulls two fields into a cursor, there is a third field, the where clause is if say that field is greater than 1.
basically the first field it pulls is the name of another sproc to run, and the this field says if i want to execute that sproc or not. so i want to rewrite this sproc in ssis to go through the table, and execute each sproc name based off of the first table.
my initial ssis thought was to add a for each loop, but i couldnt figure out how to go through the table. anyone offer some help on how this could be done? thanks
View 3 Replies
View Related
Jul 12, 2007
I have a large (4 GB) xml file. I use the SSIS XML Source, generate an xsd and conversions/destinations and this does a great job of creating the multiple outputs including cross reference tables with autogenerated id's (I have no script task or component, so they must be auto). These load nicely into the six tables.
The guidance I seek is what should I do for the next xml source file as it will be an incremental data addition to the base file. I don't see any obvious way to keep the package from autogenerating id's that may conflict with the existing ids.
Am I missing the obvious? It may be, as I am enjoying SSIS, but I am not an expert at all. I can post pertinent items if needed.
Thank you for any guidance.
View 6 Replies
View Related
Mar 17, 2007
What some good reasons to select 64-bit rather than 32-bit for SQL Server 2005?
We are preparing to install a very large SQL Server 2005 cluster. Initially, the primary database is estimated at 1 Terabyte, with 5 more databases that will add up to another Terabyte. After a few years, the estimate jumps up to 20 Terabytes for the largest DB and another 20 for the other five, combined.
We've made the assumption that a database this large, with up to 1000 concurrent users, that this is a good candidate for a 64-bit installation. And that we're better off starting out with 64-bit, rather than migrating later.
Can someone provide some guidance?
View 1 Replies
View Related
Feb 14, 2000
I am looking for opinions of setting up a database maintenance plan. I want to know if it is safe to trust the wizard and let it set up all of the jobs, or if it is better to write your own procedures to handle backups and maintenance as in 6.5. All sugestions and opinions are welcome. Thanks.
View 2 Replies
View Related
Feb 2, 2000
For SQL server 7.0, is it necessary to schedule database maintenance plan on a regulare basis? I know it is necessary for SQL server 6.5.
Thanks.
Su Ge
View 1 Replies
View Related
Apr 12, 1999
SQLMaint is run once a week for the following a database on SQL 6.5. The following is the information for the database when I see it through the Enterprise Manager:
Data Size 650 MB
Data Space Available 0.00
Log Size 360
Log Space Available 359.99
The following is the syntax built by the DATABASE Maintenance Wizard:
SQLMAINT.EXE -D CATS -CkDB -CkAl -CkTxtAl -CkCat -UpdSts -RebldIdx 100 -Rpt E:MSSQLLOGCATS_maint.rpt
It runs once a week and takes about 40 mins and runs successfully. Last it run was 4/11/99 at 2:00 AM
The result set I get from sp_spaceused is as follows:
database_name database_size unallocated space
CATS 1010.00 MB 273.96 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
753710 KB 280360 KB 426494 KB 46856 KB
What I don’t understand is how come the data space available shows 0 in Enterprise Manager? Shouldn't SQLMAINT, which is run once a week, allow for correct information to be reported?
Could someone please explain.
Thanks
Shashu
View 2 Replies
View Related
Jul 20, 2005
Running SQL Server 2000 Enterprise Edition SP3. The database is alsoused by Microsoft Project Server 2002 and also has OLAP views, so thedatabase is being used to view/run cubes in the Analysis Manager.What is the best way of shrinking the database size and its log file too?Is there an automatic way to do this with a maintenance plan or i haveto manually run a SQL statement periodically?What are the best practices?I have a database that the size of it is 260megs and now the log file isover 800megs...Everytime i run the following:BACKUP LOG DBName WITH TRUNCATE ONLYDBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)When running it, i get the following 2 records returned:DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages7 1 32304 128 28152281527 2 160 128 160128I don't know what the above 2 records mean. I am also concerned if i shouldbe running that statement or not.Also concerned as to why the Windows Server 2003 (Enterprise Edition)always creates a CRITICAL error in the Application event viewer with EventIDnumber 17055, source being MSSQLSERVER and the description of the event:18278:Database log truncated: Database: DBName.If it's a critical error message, then what i am doing is bad? Am i damagingmy database?Within like 7-10 days of running the above statement, the log file becomescloseto 1 GB again! How can i find out what causes the log file to grow that big?Is it beingcaused by running some OLAP views or what? The db has about 20 usersconnected to itusing an ASP web aplication as the front-end, and MSP Professional 2002 alsoto connectto the Project Server 2002 (which uses the same database as the aboveDBName).I would appreciate any help.Thank you very much
View 4 Replies
View Related
Oct 15, 2007
I have been looking through the data files directory for my databases and noticed that the log (ldf) files of some of the databases are from 2GB to 4.5GB where as the data files are under 500MB. If I shrink the log files what affect does this have on the database? Should I be setting up regular shrink jobs on the log files?
Any help would be very much appreciated.
View 5 Replies
View Related
Apr 4, 2008
I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.
I have downloaded and installed the software, and would like to know how to connect and create a test database.
There will be a small test in the interview and the questions are:
1. SQL Management and Data Extraction
For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx
2. XSLT
For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.
3. Database Design
You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.
These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.
This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.
View 21 Replies
View Related
May 20, 2008
alter table consumers
add check (zipcode like( '[0-9][0-9][0-9][0-9][0-9]'))
the datatype is char(5)
error message:
The ALTER TABLE statement conflicted with the CHECK constraint "CK__Consumers__Zipco__4316F928".
Based on this message, i would think that there is another constraint, however the build script does not have any constraints. So i am somewhat confused.
View 6 Replies
View Related
Apr 5, 2008
I know I already have an open thread and this is naughty, but just digging my other into a hole.
As stated in my other thread I am going into interview next week for a junior developer role, and they want to know as a test what I can find out and achieve with SSMS.
I would really appreachiate it if someone could give me some of their time to answer a few questions and guild me though the very basics. Either via MSN or Skype or something, sometime this weekend, as I really want this job on wednesday!
Thank You!
View 3 Replies
View Related
May 30, 2006
Is there any guidance on using Xp_Cmdshell from the SSIS team at Microsoft? We're needing to remotely execute SSIS packages on the server where MS SQL Server 2005 is installed.
FYI, I've researched this heavily and know about the common ways--re: Using SQL Agent or the Microsoft.Dts.Runtime library in .NET with a web service etc.
View 10 Replies
View Related
Mar 12, 2002
Hi there,
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
thanks in advance,
Matt
View 1 Replies
View Related
Mar 26, 2001
SQL Server 7.0 with SP 1
Database maintenance job is failing
with error about being unable to delete old backup file.
I can manually delete the file with no problem.
This occurs randomly, maybe once a week.
Any ideas on what's wrong, and how to fix it?
View 1 Replies
View Related
Jul 28, 2000
I want to know database maintenance issue when there are many databases in a machine. The number of database will be 50~80. We'll use SQL 7.0. In Sql 7.0, I heard that there is not so much maintenance issue.
Wisepost Inc.
Chris Song
View 3 Replies
View Related
Feb 13, 2001
After looking through the archives at database maintenance steps used by others, I have decided on the following steps for each of my SQL Server 7.0 databases. I have set up a maintenance plan where they would execute in the following order for each database, if the preceding step is successful. If the preceding step fails, the job quits.
dbcc checkcatalog ('master')
dbcc checkdb ('master')
backup database master to master_dump with init, name = 'ASP Master dump'
dbcc dbreindex ('master', '', 70)
dbcc shrinkdb ('master', 10)
Now my questions...
1. The checkcatalog, checkdb, backup database, and shrinkdb commands appear in blue in SQL Server 7.0 to seemingly indicate they are commands. However, the dbreindex command is black. Does this mean that it is not recognized as a valid command? If so, why not? It is shown as a valid command in the Microsoft Knowledge Base.
2. The value of 70 in the dbreindex command was the value shown in sample code in the Microsoft Knowledge Base. However, the Knowledge Base does not give any guidance on whether this is a good value or not. How should I make a decision on what value to use? The value of 10% for the shrinkdb command is also just a sample value from Microsoft. How does one know what value to choose?
If anyone thinks this is a poor selection of maintenance steps, please let me know that also.
Thanks!
Joe Meyer
View 2 Replies
View Related
