Swapping Out Drives On Our Production SQL Server This Weekend
Jul 20, 2005
All,
To make a long story short, we are swapping out the "knock-off" drives
that the NA purchased on E-Bay in one of our production SQL Servers
(SQL Server 2000 Enterprise) this weekend for brand new ones (Compaq
15K RPM 32GB drives). We are currently experiencing ASR almost on a
daily basis and it is really causing a disription in service. SO, The
Network Admin ahs made this decison to replace these drives in attempt
to solve this. These new drives will be imaged with ALL of the current
data on the "knock-off' drives and will be plugged back in to this
database server and brought back up .This Server also happens to
currently be a subscriber in Merge Replication as well. Besides
stopping replication to this subscriber is there any other tasks that
I need to do or concerns that I need to be knowledgable about or look
for when we bring this database server backup on line this weekend?
Thanks Travis. :)
Is there a way to permanently change the order of the columns in Job Activity Monitor?
I'd like to move Duration to the right of Step Name, but this only lasts so long as I have JAM open. Once I close it and re-open, JAM goes back to its default column order. Google gives me nothing but the temporary "drag and drop" method that I already know about.
If I was asked "How many days passed" the query is (below) and the answer is 33. But, then the curve ball is "can you subtract out the weekends?" and I said....well, I said I think so, but not sure how. So far, I have had no luck. Any advise?
I was wondering could someone help me. i have two dates a start_date and an end_date i need to work out if a weekend falls with in the dates. can this be done or is there an easy way of working it out
I mapped a drive on to my SQL Server box. It points to another server from the same domain. When I try to backup or restore a database, I can't see this mapped drive through my SQL Server. Even if I type the entire path, SQL Server wouldn't take it. I don't have a clue about why it is not working. Can anyone throw some light on this. Your help is grately appreciated.
Hello! I hope that I have posted in the right spot. We are looking for advise. I know that there is no right or wrong answer. "It depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical drives are being used. These 3 physical drives are 1 container with Raid 5. This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is SAN.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples of what they are doing or provide suggestions?
I'm aware that it's best practice to separate mdf and ldf files onto separate drives.
However, I see a lot of servers where the underlying disk array is the same for drives on the server.
Is there still any performance benefit to separating mdf and ldf files in this situation?
For example, a single virtual server running SQL Server, with multiple drives attached.All of the drives are connected to a shared storage via iSCSI.There drives C:, D:, E: etc are all actually sharing the same underlying disks.
Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.
I am looking to create a query to select data to accomodate the weekend. For example, If today is Monday, then select Friday's records. This only has to be done for Monday's. For each additional day there would be a date difference of just 1 not 3. I hard code the date diff when Monday rolls around. how to make that specific so the CT.workeddate = what I have in my Where clause.
I want to find the first weekend day, second weekend day, third weekend day and fourth weekend day per month using sql query. This is getting from recurring appointment. If weekdays = 65 means it accepts only saturday and sunday. So I want first, second, third and fourth weekend days for a month using query in sql server 2008...
I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
I am converting a legacy ASP.NET 1.x site to an ASP.NET 2 one. In the former site, sets of data connections are stored in an web.config file allowing swapping between testing and production servers based on a global variable value at the start of the app. As I now try to use sqldatasource, I find the control has its connection string embedded in the html page and stored as a unique variable in the web.config. How can I dynamically swap the new sqldatasource's connectionstring the same way data connections are made in the former site? Please advise. Thanks.
I have a table that has a column for each month and I want to use a view to convert each row into 12 rows with a one month column. The month column will have 1 - 12 in it depending on the month. I need to convert it this way to push it into an Essbase cube. I know I can use the union operator to do this, but I would rather not read the table 12 times. Is there a way to do this just reading through the table once? My Hyperion Essbase book gives an Oracle example for swapping rows and columns by using a decode function. Is there a similar function in SQL Server?
I need to build a *.sql script that will remove a database (let's callit "DB1") and replace it with a brand new empty database (let's call it"DB2").Caveat: I don't want to be left with database "DB1" having it's filesconfusingly named "DB2.mdf" and "DB2_log.ldf". These two files shouldalso be renamed to "DB1.mdf" and "DB1_log.ldf" so that outsidecustomers are not left confused. In addition, I need to be able torestore the original DB1 if anything goes wrong during, or even after,the entire process.Let's assume every customer's *.mdf's and *.ldf's will always reside inC:Program FilesMicrosoft SQL ServerMSSQLdata folder.I've researched sp_attach_db, but this looks more appropriate formoving databases. This isn't what I want to do.Thank you in advance.
Is it possible on a matrix report for the rows and columns to be swapped around after the report has been built? E.g, can the rows and columns be swapped around by the user in the preview page?
Hello!We have a maintenance job on MSSQL server scheduled for the weekend.some times we arrive at the beginning of a week and see that a user lefthis screen open on a SQL record and the job is still waiting blocked bythat user. Please give me an idea how to overcome this.Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment) ,( ((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field). I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string: select product_code, CASE can_image_en ?? When (can_image_en LIKE '%.jpg') then can_image_en Else 'no_image_EN.jpg' End as can_image_en, none of these do the trick either (some are bad syntax obviously): ? When (can_image_en LIKE '%.jpg') then can_image_en ? When LIKE '.jpg' then can_image_en ? When '%.jpg' then can_image_en ? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en When '%.jpg%' then can_image_en Else 'no_image_EN.jpg'
As part of a migration of data to a new SAN I have hit a bit of a snag in the migration. In summary what will happen is user database data files will be moved from one LUN (say drive F:) to a new LUN (say drive G:). Once all the data is migrated, plan is to remove dependency of that drive from SQL server and remove the drive and delete the LUN. So far, so good.
However one of the LUNs (drive D:) destined to be deleted also hosts the instance default directories, i.e. everything under MSSQL11.MSSQLSERVER (Data, Backups, FTData, JOBS, etc). BOL has articles on how to migrate system databases, including tempdb. But there is no guidance that I could find on how to relocate other folders. There are forums where users have listed registry changes, etc that can achieve this but these are steps I am unwilling to take on a production server.
So my plan is: 1) Add new drive to cluster (drive E:), sufficiently large enough to host instance default folders 2) Shutdown SQL server 3) Copy all default folders to new drive 4) Swap drive letters so that new drive is now D: 5) Start SQL server and if everything works, delete the original drive (which is now drive E:).
I have been trying to load data from AS400 to DB2 (windows) using ADO.NET connection in Data reader source and OLEDB Destination (IBM Oledb provider )
The files, I€™m trying to load, have number of rows more then 15 million.
On execution of the package I get Out of Memory Error (see below)
My Destination Box is 4GB+ RAM and 4 CPU Box.
There seems to be some Buffer and Swapping related issue which I€™m not able to figure out. It says that System is unable to allocate memory
Please help me on the same.
Thanks in Advance
Amit S
SSIS package "ABCDE 1.dtsx" starting.
Information: 0x4004300A at ABCDE 2003 to 2004, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE 2003 to 2004, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE 2003 to 2004, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE 2003 to 2004, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at ABCDE 2003 to 2004, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at ABCDE 2003 to 2004, OLE DB Destination [12]: An OLE DB error has occurred. Error code: 0x8007000E.
An OLE DB record is available. Source: "Microsoft Cursor Engine" Hresult: 0x8007000E Description: "Out of memory.".
Error: 0xC0047022 at ABCDE 2003 to 2004, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (12) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at ABCDE 2003 to 2004, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.
Error: 0xC02090F5 at ABCDE 2003 to 2004, DataReader Source [61]: The component "DataReader Source" (61) was unable to process the data.
Error: 0xC0047038 at ABCDE 2003 to 2004, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (61) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at ABCDE 2003 to 2004, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at ABCDE 2003 to 2004, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at ABCDE 2003 to 2004, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at ABCDE 2003 to 2004, DTS.Pipeline: "component "OLE DB Destination" (12)" wrote 289188 rows.
Task failed: ABCDE 2003 to 2004
Warning: 0x80019002 at ABCDE 1: The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Executing ExecutePackageTask: C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005ProjectsIntegration Services Project1Integration Services Project1ABCDE 2.dtsx
Information: 0x4004300A at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Execute phase is beginning.
Information: 0x4004800D at ABCDE 2005_04 to 2005_11, DTS.Pipeline: The buffer manager failed a memory allocation call for 10484320 bytes, but was unable to swap out any buffers to relieve memory pressure. 3 buffers were considered and 3 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
Error: 0xC0047012 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: A buffer failed while allocating 10484320 bytes.
Error: 0xC0047011 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: The system reports 63 percent memory load. There are 4294660096 bytes of physical memory with 1548783616 bytes free. There are 2147352576 bytes of virtual memory with 227577856 bytes free. The paging file has 6268805120 bytes with 3607072768 bytes free.
Error: 0xC02090F5 at ABCDE 2005_04 to 2005_11, DataReader Source [61]: The component "DataReader Source" (61) was unable to process the data.
Error: 0xC0047038 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (61) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at ABCDE 2005_04 to 2005_11, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at ABCDE 2005_04 to 2005_11, DTS.Pipeline: "component "OLE DB Destination" (12)" wrote 0 rows.
Task failed: ABCDE 2005_04 to 2005_11
Warning: 0x80019002 at ABCDE: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Executing ExecutePackageTask: C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005ProjectsIntegration Services Project1Integration Services Project1ABCDE 3.dtsx
Information: 0x4004300A at ABCDE 2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE 2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at ABCDE 2005_11 to 2006_04, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at ABCDE 2005_11 to 2006_04, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at ABCDE 2005_11 to 2006_04, DTS.Pipeline: Pre-Execute phase is beginning.
Hello - I have a SQL 2000 server which has a D: drive that contains all of my databases (system and user). I am running out of space on this volume and need to migrate the contents of this volume to a larger one. My initial plan was to introduce a new volume to the server (say a K: drive). Backup all databases (of course), and then stop all SQL services. Copy all data from D: to K:. Once data is copied, swap drive letter names (D: to I: and then K: to D. Then restart SQL services. SQL should not know any better since everything was on the D: drive when it went down, and everythiing is still on the D: drive when it came back up, correct?
The other option mentioned is to detatch the databases, copy the data and then reattach them in their new locations. I understand this method, but it seems more involved (and riskier) than just renaming the drives. Does anyone have an opinion regarding these two migration methods? Thanks for your help.
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
I currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
I have a brand new database server with system databases. I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
1.First issue The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues. Can you SQL guru give me best suggestion and how should i proceed.
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
