Tag Info

Hot answers tagged

25

On each server, you can query the sysjobs table in the msdb. For instance: SELECT job_id, [name] FROM msdb.dbo.sysjobs;


18

This is exactly how SQL Server is supposed to work. If you have other services on that machine and don't want SQL to consume all the available memory, you will need to set the maximum server memory. See SQL Server Memory Options on MSDN.


17

Assuming it's a user database, detach the database, move the .mdf / .ldf files to the new location and reattach the database. You might need to browse to the new location of the .ldf file when you choose your .mdf file in the reattach screen. To detach a database, right-click on it in management studio and choose Tasks | Detach. To reattach, right-click ...


13

You might be able to get the info out of the log using the undocumented ::fn_dblog function which interprets log records. I'm in the middle of teaching a disaster recovery class right now, but if you can wait 2-3 hours I'll post how to do it for you - should be able to get the username too without having to buy any tools (I used to spelunk around the log a ...


13

Upgrade to SQL Server 2005 Express, or even SQL 2008 Express. These are both free and are much more powerful than MySQL (not that there's anything wrong with MySQL!). SQL 2005 will even restore backups from SQL 2000, so it's trivially easy to port the database (2k8 may do this too but I've never tred it). Contrary to popular myths SQL 2005 and 2008 have no ...


13

Business mind is usually very different from yours. Why use something newer if it won't generate more revenue, but only expenses (migration, retraining etc.)?


8

The other posters are correct that this is by design, but you absolutely want to limit the max memory to a bit less than your server's RAM. Think about this sequence of events: SQL 2000 runs happily, consuming all of your server's RAM Someone RDPs in, or you have to pull up IE to download a patch, or your backups kick off, whatever SQL has to deallocate ...


8

Maybe it was Little Bobby Tables...


8

Yes, it runs just fine in VMware - hopefully it's a lightly loaded database server rather than a multi-terabyte monster! Ideally, you'd want a fresh install of Windows and SQL Server 2000, but failing that a P2V will work. Brent Ozar refers to it as taking care of dinosaurs: http://www.brentozar.com/archive/2008/08/sql-p2v-what-really-killed-the-dinosaurs/ ...


7

Shrinking as in regularly maintenance shrinks the DBs? Remove that immediately, as in right now, before you continue reading. Why you should not shrink your data files. As for your question, there are far too many unknowns. Do you need disaster recovery, including geo-DR? Do you need a warm standby location? What is your desired recovery interval in case of ...


7

Best reason I can think of is a variation of "it ain't broken" that goes like this: You have a business application that works with SQL 2000 but generates errors with SQL 2005. It may not be the right business priority for your organization at this time to open the hood on that application (which ain't broke) and make it work with a newer database.


7

The backup headers in MSSQL2008 are different than those in MSSQL2005, which is likely the source of your problem. Try exporting the DB instead of the backup-restore paradigm, or set up MSSQL2008 on your DEV server.


6

You can indeed script out passwords from sql server 2000 and import them into sql server 2008 so that they are recognised as upper case passwords. Here's some background reading that will be useful in understanding sql server password hashes, so you can following along with the example. SQL Server 2000 passwords were actually case insensitive as described ...


6

stop sql server rename data & log files of the db with the issues start sql server The db will be marked 'suspect' when you start, but at least it wont be trying to rollback. From there you can drop the database & do a restore


5

According to this Distributed Query Architecture page: "For each instance of SQL Server 2000, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL Server DisallowAdhocAccess property."


5

I've used this trick before to kickstart replication. Assuming that the sites have faster internet access then the link between them. Setup your replication, but use a local path that exists on both servers for the snapshot location. Run the snapshot. Compress the text files that are created (they will compress a lot). FTP the files to an outside FTP ...


5

A few thoughts here: 1) Don't do that. Seriously. Millions of tables would be a nightmare, and would likely cause far more problems than it solves. 2) If you really want to break the table up into multiple tables, you don't need to use that many. Depending on your hardware, I'd expect 50 million rows to be no problem, so you could divide up your data into ...


5

Jason, Yes, that is normal, and that is why many people (myself included) have decided to "roll-our-own" maintenance plans. Another quirk of MP's is that they will attempt to do a trx log dump for a database in SIMPLE recovery mode. You would think it is a simple check to make before attempting the database backup, right? Well, it is, but not inside of the ...


5

Unfortunately, no. You'll need to upgrade the default instance to SQL 2005, remove the 2005 named instance and reinstall SQL 2000 as a named instance. Or just wipe clean and install SQL 2005 first as the default and SQL 2000 as a named instance. Any reason you need to keep the SQL 2000 instance around?


5

Move your data and logs to a FusionIO drive, very very expensive but as far as I'm aware you can't get faster persistent storage right now. Oh and you should move to 2008R2 64-bit too of course, plus load up on memory.


5

Apart from the TechNet Magazine article I wrote that JGurtz referenced above, your problem is either fragmentation or statistics. If perf is slowing down gradually, its fragmentation. If perf suddenly drops off a cliff, its statistics getting out-of-whack and causing a query plan change. If its fragmentation, consider changing keys to something that ...


4

You can give this a try: Create a new database with the same name and same MDF and LDF files Stop sql server and rename the existing MDF to a new one and copy the original MDF to this location and delete the LDF files. Start SQL Server Now your database will be marked suspect Update the sysdatabases to update to Emergency mode. This will ...


4

I don't think there's anything you can do to change SQL server's behavior regarding password sensitivity. I think you first would want to be sure you've exhausted all efforts to locate the source code for those modules. Otherwise, Here are a couple of ideas: Use a hex editor to edit the binary executable files to change the password to the correct case. ...


4

SQL Server 2000 came with Data Transformation Services (DTS)1 which allows data to be moved from one database to another,2 potentially including transformations. This is designed to move bulk data, Query Analyzer isn't. 1 Replaced with SQL Server Intergration Services (SSIS) in newer versions. 2 Or anything with an ADO driver. I've used it to move data ...


4

Put a job step as the first step in the job to look to see if the DATEPART(dd, getdate()) = 22 and if it does issue an sp_stopjob to stop the job.


4

Index maintenance should be done based on existing fragmentation, not blindly. With a clustered IDENTITY column, you should not have much to worry about. SQL Fool's defrag script will help. 200 million rows is not that much and not yet worth partitioning IMHO because of the query overhead, many table names requiring dynamic SQL etc. Unless you have a tiny ...


4

It's not recommended (and not supported) to overwrite system database with those from another server instance, but it is technically possible. I much prefer to migrate the pieces myself. Install your new SQL 2000 instance clean on the new Win2003 server and then take things over to the clean instance, you'll be happier that way. Databases you have, either ...


4

As Mike Dimmick says - SQL 2000 won't recognise SQL 2005 backups (like Word 2000 wouldn't recognise 2007's DOCX files). Therefore you'll have to migrate your data in a lower common denominator format such as SQL scripts. Try the Database Publishing Wizard which is part of the SQL Server Hosting Toolkit which generates a single SQL file for both schema and ...



Only top voted, non community-wiki answers of a minimum length are eligible