We have an application that was built and testing using SQL Server Express. One of our clients is deploying it using SQL Server Standard and plans to put the data files and log files on separate disk volumes.
In allocating the available disks to the volumes, they are looking for a recommendation on how big the log file volume versus the data file volume should be. Over time there will several years worth of data in the data files. I assume the log files need to be at least big enough to log all the changes between back-ups. Are there any general rules of thumb? Or whitepapers that discuss the trade-offs?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I've got a SQL database running on Windows NT 4 Server (P400, 256 Ram, 8Gb IDE & 18Gb SCSI HDD) and quite a few of the queries are taking a long time to run, and are also using a lot of the processor time. This affects other users who are also trying to query the db. Has anyone got any recommendations for upgrading the hardware spec to improve the overall performance? I presume just add more RAM, and get a dual processor system?
I am a PHP programmer for a small startup. We are storing person records and our MS SQL Server 2000 database has grown to the point where we wish to paginate the data before returning it to my PHP scripts.
I was wondering if anyone has any recommendations on an optimal way to manage this given the following requirements.
- Data must return only X number of rows at a time (user configurable). - Must be able to search by several diffent criteria (name, date, birthday, location, ...)
Also, I was wondering if it is possible to return the total number of existant rows of data as the first row of a MSSQL procedure.
I have a SQL 2005 database containing the location of graphics files. I want to start learning how to write a C# application that will get a path from the DB and display the file. Any recommendations on sites where I can start learning how to do this?
Ok, let me start by saying that I already checked the FAQ. There was one link, but it just seemed to go to a review page with 5 books, with pretty specific themes. So I'm surprised that such a basic topic as book recommendations for SQL newbies wasn't covered.
In my case, I'm not a total newbie. I learned to write SQL queries for work on both Oracle and SQL Server, and I've gotten pretty good at all the basics. So I've got "SQL for Dummies" down cold, so to speak. Now I'm looking to take my query writing ability to the next level, which I guess would be the intermediate level. I'm also looking for books that are specific to just SQL Server, rather than the books about general querying on any DBMS. Speaking of which, just so you all know, "SAMS Teach Yourself SQL in 21 Days" is an Oracle book, no matter what it says on the cover.
It looks like the book "Inside Microsoft SQL Server 2005: T-SQL Querying" comes highly recommended, but I flipped through it on the shelf at a bookstore the other day, and I think it's over my head. It might be a good reference to have around, but I think I'd be totally lost trying to read it from cover to cover.
So does anyone have recommendations for books that go beyond "This is how to do a SELECT, and here's how to do a JOIN", but won't make my head explode?
I do software support for software that works with both Oracle and SQLServer, so I mostly just write queries to look at the data related tothe software. When I first started, I bought a couple of books to learnthe basics, intentionally going with generic books that would help withboth types of databases. I've got the basics down, and now I'm lookingfor a really detailed reference book that goes really indepth intoquerying for SQL Server specifically. Currently, we only work with the2000 version, but we'll be going to 2005 soon, so I think I'd rather gowith a book on 2005, although if you know one that covers thedifferences between them, that would be great. Any recommendations?Besides just writing queries, I'd also like to learn more about SQLServer in general. DBA software support, so I'd like to head mycareer in that direction. I was looking at Microsoft's newcertification path for SQL 2005. Given that my company won't pay tosend me for training, and I really don't want to have to put down$2200+ to pay for a class, I was thinking that I might be able to learnenough on my own to pass the first test and get the MCTS title. Iordered the Training Kit from Microsoft Press for that, which comeswith a 180 day trial of SQL Server 2005 to play with, along with a hugebook. Does anyone have any other recommendations for resources to helplearn this stuff?--Richard
Hello -Anyone have any thoughts on which API to use - ADO or ODBC?I have a fat client written in C++ using MFC ODBC classes to access a Jetdatabase.The app is going to be modified to write to a SQL Server central databasewith multiple users accessing their local copies of the database ( usingreplication technology on the clients side).Most of the performance benchmarks give an edge to ODBC over ADO whenwriting to an Access database. Anyone know of any benchmarks for a c/senvironment?I've seen references that ADO has some client side cursor features forfilters and sorting which are a benefit over ODBC. I'd also like to usethe asynchronous fetch that OLE DB provides and am not sure if this isimplemented in ODBC.Thanks for the help.Bruce
I've recently been tasked with doing some SQL 7.0 administration and was wondering if anyone could recommend a good book to get started. The bulk of my IT experience is in SMS, IIS and messaging so my database fundamentals are pretty weak.
As I see it, it's probably a three-step process to get me at least halfway comfortable - a first book to get some solid general database / SQL-language exposure, a second book that takes the knowledge to a more advanced level and finally a MS-specific book that covers the Microsoft implementation of a SQL server.
Any comments / suggestions would be much appreciated!
I've searched quite a bit, and have found several leads on schema, stored procedure, and database contents comparison scripts and tools.
I'm now looking for recommendations on which ones are best, easiest:
ObjCompare.exe sb_ABCompareDb.sql sp_db_comp.sql
There's a mythical script from Andrew Z <mumble> that Mike Hotek talks about...
There's a DBCompare on the Back Office Resource Kit 2 CD, which of course is not in the umpteen MSDN CDs :-(
There's some *other* command line dbcompare, or maybe db_compare.
There's a DBA Compare.
I need to be able to compare divergent schemas from two developers to integrate their changes, so need schema and stored procedures compared only, and would also like to have something to compare staging servers and production servers.
Leads on other choices also welcome. I'd be happy to summarize and post, if warranted.
I'm a solid SQL developer/dba and have some funds earmarked for training this year. I'd like to expand my dba skills...more specifically, I'd like to become a rock-solid enterprise level dba that has not only a solid foundation of skills but some innovative techniques for managing our corporate SQL servers.
I'm curious if anybody has recommendations on training or conferences that might help me dig into those skillsets for a couple of days.
I am looking for a book that discusses query tuning, index tuning, executionplans, etc. Can anyone recommend something?--BV.WebPorgmaster - www.IHeartMyPond.comWork at Home, Save the Environment - www.amothersdream.com
I'm trying to get a better idea of how I should set some of the SSCE connection properties. I will be deploying the application on a WinCE5 board with a 4G flash storage card and 512MB RAM. The application must run 24/7 with a medium amount of traffic. At any given point I expect up to 3 connections to the same database. I'd really like to keep performance high without risking any database corruption issues. As such, I need to make sure my connection properties are optimized.
I read from Joao's article that setting DBPROP_SSCE_MAXBUFFERSIZE = 1024 gave a considerable performance increase, while anything higher gave diminishing returns. Is this for a certain amount of RAM, or is it uncorrelated to RAM size?
What's the best practice for specifying DBPROP_SSCE_TEMPFILE_DIRECTORY and DBPROP_SSCE_TEMPFILE_MAX_SIZE? Is it possible in WinCE5 to directly put it in RAM? How big do these temp files get?
I've read of some bad experiences with autoshrink. Are there any reasons not to set DBPROP_SSCE_AUTO_SHRINK_THRESHOLD to 100 if the database will be compacted regularly?
Any other advice for the more obscure settings such as DBPROP_SSCE_FLUSH_INTERVAL or all the lock settings?
I am in the process of creating a user interface that will give the users a list of reports to choose from. I wanted to write an interface in ASP .NET with C# to pull information for different reports from the reporting services web service. I have successfully done this with C# Windows Form. The project requires it to be web-based.
Problem is, I am new to web forms and would like a recommendation for reference materials to help me program ASP .NET with C# and using the web service. I am still learning ASP .NET and C#, so I would like something fairly basic.
This really isn't a SQL Server specific question, but more tword SQL in general. I am pretty good at SQL, being able to perform joins on several tables at one time. I am looking for more challenges in SQL though as I want to learn more and to rise to the next level. Can anyone recommend some good resources to me? It would be challenging to me to learn how to do more complex queries involving three or more tables. Ralph
Dear friends I'm developping a multilanguage human muscle database and wanted to know your opinion about my design Table Muscle:-MuscleID(PK)-Name_DE...............german is standart-Description_DE Table Muscle_LANG (for appr. languages):-MuscleID(PK)-Name_EN-Name_FR-Name_ES-Description_EN-Description_FR-Description_ES Many thanks for your tips
I want to build a SQL testing environment in an active/active setup. Any recommendations on what I could use if I want to set up the most bare-bones system. I want to do it as cheaply as possible.
I'm just testing the database engine tuning advisor.
I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this.
The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.
Is it normal for DET to recommend that any index not referenced by the workload file be dropped?
I'm on the IS team of a medium-sized non-profit with internationalreach. We're trying to make some decisions regarding our Web serverand database server as we expand our web site to have more dynamiccontent. Currently the database server houses all data pertinent tothe organization (membership data, events, products, etc) in onedatabase (~2.2 GB) as well as the web site content in a separatedatabase (~40 MB). The web site pulls from both databases but hits thecontent database more often.In a nutshell, our database server appears to be struggling duringperformance testing of the new Web site. We are trying to determinewhether we simply need new hardware, or if there are things we can doto help MS SQL make better use of the resources we have. The hardwareis a COMPAQ ML370, 1266mhz Pentium III, 1gb RAM, RAID 5 with 3 HD(10,000rpm) and a COMPAQ Smart Array 5i SCSI controller. The OS isWindows 2000 (standard) running Microsoft SQL 2000, SP 3a. The Webserver is a 2.8ghz Pentium IV with 2.5gb RAM, RAID 5 with 3 HD (15,000rpm) running Windows 2000 standard and IIS 5.0. While stress testingour web site under a moderate load (simulating approximately 20simultaneous users), the database server processor tends to max outand stay that way for the duration of the test. Memory and disk accessappear to remain fairly stable -- there isn't a lot of paging goingon, and the disk queue doesn't escalate much if any. The Web servershows spikes in processor use, but appears to be coping well. However,under a heavy load, a sql-heavy page can take as long as 90 seconds toload! We've been assuming that the network is not the issue, as theservers are communicating over a gigibit backbone and while we'veidentified aspects of the ASP code that we can optimize, the databaseserver seems to be a large part of the problem.We've reviewed our SQL configuration settings, and they appear toalign with the best practices, which in our case are the defaultsettings for SQL 2000. We have rebuilt our indexes, and havedefragmented the hard disks on both the database and Web servers.This, along with changes to the structure of the Web pages themselves,has led to improvements, but the processor on the database serverseems to be groaning under the strain, and pages are still taking anunacceptable amount of time to load.What else should we be looking at? Are there steps we could take tominimize the load generated by client/server and Web-related traffic,or specific performance counters that would help us to identify theproblem? Do we just need to look at getting some new hardware? If newhardware is unavoidable, is there anyone running a similar environmentwho could suggest what minimum requirements we should be looking for?Any suggestions would be much appreciated!
I create data collection applications on Mobile Devices for field biologists. The biolgists need to sync thier field data with a local database upon returning from the field. I am planning on using sqlce as my mobile database and am trying to get a grasp of what my synchronization options are. Here are some of my questions.
1.) Is there any way to synchronize data between a SQLCE database on the mobile device and a SQLCE database on the desktop (third party or othewise)? It appears to me that the answer is no, which is a shame. Whats the use of having a free light weight database that you can use on a mobile device and as a backend on a desktop but not sync data between them?
2.) If my hunch is right, what are my sync options? Installing SQL Server on every field computer is not an option ($$$). If I'm bound to using SQL Server as the backend database, is it possible to have a single SQL Server database on a webserver that the field locations could sync to remotely?
Any feedback to get me started in the right direction would be greatly appreciated.
We are creating a company-wide table of ZipCodes, States, GPS info, etc. This table can be used by our development and production servers (many of them.) We could place the table on a given server and use linked servers to grant access to that table to the other servers. But is there a better way to handle this globally-useful table?
Barkingdog
P.S. Clearly, we don't want to have multiple copies of this table scattered around on various servers. That introduces synchronization issues.
We have a 4 processor 350 Hz NT 4.0 SQL server. Currently we have an application that is inserting rows one at a time, each row insert is a separate transaction. Currenty we are averaging 2500 rows a second with each row ( 56 bytes wide). The data and the log are on one string of Raid disk. We plan to get another controller and raid string to separate the data and the log onto separate controllers. The developer is modifying the application to insert the data in blocks. What is the impact to the transaction log? He seems to think that by inserting page blocks on rows there would be less data going into the transaction log. Why would this be so? Does anyone have any information on practical limits for inserts and log truncation with similar machine configurations. He would like to try to get around 150,000 rows a second. Has anyone accomplished inserts at this rate? What type of machine configuration?
I have a summary table with a 9 field composite primary key. Every 10 minutes, my system generates 2 files of 500,000 to 750,000 rows to be summarized into this table. I first Bulk insert those into a temp table, and then trigger an inner-join update query to do the updates, followed by a left-outer join to do the inserts. As the day goes on, millions of rows in my summary table, this process is too slow. Any ideas about causes/solutions???
I Have not been able to solve this problem from quiete a while now.
I am using sql server 2005.
I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
I have to perform a query on this table so that I can group the volumes for different months and different years.
We're planning to migrate our db to new and more disk drives, fasterRAID levels and more dedicated disk usage(e.g. placing the translog ondedicated disks). The db server runs on Win2003.Right now we're thinking about what file system to use on the newdrives. We opt for performance, but expect reliability as well.(Goeswithout saying, IMHO ;-))
I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.
We're having issues with Microsoft's 64 bit Oracle OLE-DB driver... when we escalated to Microsoft PSS they recommended that we go with Oracle's OLE-DB driver since Microsoft's hasn't been modified in a few years.
I've seen a lot of differing opinions on this topic (almost everyone says do not use Oracle's driver) and am wondering if there's a definitive answer here.
I just noticed that; although my server has 2 physical volumes my log files and DB are on the same one. How do I do it?It's SQL Server 2000 running on Windows 2000 Server.As a side note: Why does the database's Properties display in EM allow definition of multiple log files?Thank you!
Hello!Does anybody know whether mssql2000 and emc mirrorvew _certified_ forjoint work?(Mirrorview is a fc-based remote mirroring solution)I mean is it supported from the MS point of view to put mssqldatafiles on emc mirrorview volumes?For example Oracle corp. has "Oracle Compatible Remote MirroringTechnologies" certification.But what about MS?
