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!
I am in the process of choosing between either SQL Workgroup or Standard Edition. I see the differences in features on the comparison table, but do not see any references to the differing capabilities in handling transactions.
Is there any differences between Workgroup and Standard in terms of handling transaction/data capabilities? i.e. Does Standard have the superior capability in handling X times more TPMs than Workgroup?
If not, am I correct to assume that this is totally determined by hardware configuration (# of CPUs, processor speed, HD speed, RAM) ?
If the data volume / transactions handling is solely determined by hardware configuration, and I know the # of transactions and amount of R/W per second, .......where would be a good reference to know what kind of hardware configuration I need (ideally, once I know the hardware configuration, I guess I would be able to determine I need Workgroup or Standard)
Thanks in advance,
benbry
Currently, my databases and their corresponding transaction logs are all on the same disk array. I finally was able to acquire a seperate disk, specifically to seperate the logs, in case of failure, etc.
Now, I need to figure out how to go about moving the tansaction logs off the current disk array and on to the new disk.
In Enterprise Manager, I brought up "Properties" for the first database, and went to the "Transaction Log" tab. Clicked on the "browse" or "..." button in the location field and got the following message:
"A transaction log file's physical name cannot be changed once the transaction log file has been created". :(
I have been looking through Books On Line, but have been unable to find anything helpful yet.
Can anyone help me figure out how to go about moving a transaction log's location? There has to be some way.
Even if it involves shutting down the server, altering system tables, etc. I need to get these moved.
Your help is appreciated. Thanks.
Hi all,I have a database with 20GB transaction log file.The recovery model of the database is Full.I need to move the T. log file to a new location with the minimaldowntime.I know I can do this by dettaching the DB, copying the T. Log andattaching it at the new location. This will take some time though ascopying the T. log file will take up some time.I thoght that I could maybe create a secondary T. log file and deletethe primary T. log file.What does it take so I'm able to delete the primary T. log file? Canyou please explain how to accomplish this?Also, if you can figure out of a better way, then please let me know.
View 3 Replies View RelatedHello guys and girls. I have installed SQL Server 2005 Standard Edition and I have specified that the databases should be created on the K: drive. This is okay but now I need to move all the transaction log files (.ldf) to the L: drive. I have already changed the default location for the log files to point to the L: drive and the new databases that were created after the installation have their transaction log file correctly in the L: drive but now I need to move transaction log files for the master, model, temp ... databases. How can this be done? And are there any gotchas?
View 11 Replies View RelatedWe 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???
RLiss
Hi Guys
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.
Hope I have made this quite clear.
Thanks
Mita
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
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?
View 1 Replies View RelatedWe 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?
Thanks in advance...
Hi All
I would like to know whether SQLServer can be installed in a raw volume or not..... Is there best practice guide for this. .
Regards,
Vijay
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I have a question on how to sum data by a certain date range. Here is the data I'm looking at. I have volume measured usually (but not always) every day. I want to sum the volume from the 2nd of the month to the first of the next month. I want to do this for every month. I have the columns of my data listed below. Can anyone help me with this? I've been trying to read up on it, but I'm not finding anything.
Entity Date Measured Volume
1 4/01/2008 5
1 4/02/2008 4
1 4/03/2008 6
1 4/04/2008 5
1 4/08/2008 7
1 4/12/2008 8
1 4/13/2008 5
1 4/14/2008 7
1 4/25/2008 8
1 4/30/2008 9
1 5/01/2008 6
1 5/02/2008 8
Thanks in advance for any help!
Hi Guys,
I am facing problems with concurrent access in SQL Server 2000,The scenario is that the DB contains one huge de-normalized table containing 40 million records.
The application frequently queries this table to populate other derived tables,the sql queries take a long time to return results.
So if one query is in execution the other user's query goes into a
wait mode.Please suggest how I can better this.
Or do I need to upgrade to 2005.
Regards,
Prashant
Hello ,i am a master student and i am making a seminar about high volume DB performance problems ,example : if i have a table with length of 1000000 record and this length is growing exponentially by the time,what the problems may i face in insertion ,deletion , search,in such table?? and what the problems in processing such DB in general
View 1 Replies View RelatedHi Good morning to all,
My day started with loading huge volume of data and my data flow task failed to do so.
My data flow has a flat file connected to a OLEDB target. This is a one to one mapping. My source file contains 50 lac records and it is of 500 MB in size.
I'm processing the data with all the default buffer settings. I have 4 CPUs in my server.
the system process DTSDebug.exe is utilizing more than 2GB page size. My average CPU usage being 70% when one of those CPU s is hitting 100% utilization.
I'm very new to SSIS. So, please provide me some info how do i set my buffers and do we have any PDF for performance and tuning in SSIS ?
Do we have any bulk load transformation in SSIS to load into DB2UDB ?
If so how do i get it installed?
Thanks in advance,
Suresh N
We are creating an enterprise application for fuel, and I am fighting with my DBA about the proper way to store volume and currency in the database. We have 2 main arguments. The first argument is whether we should store costs in the database in $ and convert in the presentation layer, or to store the amount and currency in the database. We sell product from the US in dollar but depending on the customer we may invoice in Euro. Our second argument is the same, execept with volume and UOM. We often purchase product by BBL but sell/transfer by gallon, or Ton.
please tell us the best practice for our dilemma.
Not really a question. Just looking for people with experience with SB in a highly transaction env. with passing a lot of messages. What kind of challenges have you ran into when you are processing the messages. I am currently writing a SB application for a large financial institution, and want to get some ideas of challenges that I might face when volume gets really high (couple of million transactions per day).
Thanks,
Tim
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
Hi,We need to use a free database for a project because of tight budget.Is MSDE ok for handling large volume of data and 70 - 80 users?My understanding is that MSDE is optimized for 5 concurrent users.Is MySQL better than MSDE?Thanks,Ben
View 1 Replies View RelatedHi,
I have been asked to design a solution for a client of mine who basically requires the daily analysis and reconciliation of the differences between 2 extremely large text files.
The files are not in an identical format but are both in some form of delimited format (one is CSV, the other is a little more complex). For the sake of this question, let's assume that I can effectively import each file into an MS SQL table.
Each file will have in excess of 100,000 rows each day (new data for each day).
Whilst I know that MS SQL does easily have the capacity to store the data, is there a recommended way to tackle the potential problems (I imagine that performance is important... they will be running the report every day)
Or is building the solution as simple as importing the data into 2 tables, and then querying the differences and outputting as a report using Crystal?
Any suggestions appreciated.
Thanks
Rael
Hi,
Is there any SQL Server functionstored procedure available to get drivesvolumes and mount points total size and free space information?
I don’t want to use clr approach for this.
Can you please provide any pointers related to this.
Thanks in advance,
-Kiran
Hello every body
i am doing a research about high volume database treatment (maybe a database with tera bytes volume) , so is there any optimization or specialization for queries deal with such database? !!
Hi, all experts here,
Thanks a lot for your kind attention.
I have a question on training large volume of datasets. In this case, the training will take a long while to complete, is there anything we can do to improve that? I know, we obviously cant split the training dataset into different smaller datasets. What we can do to improve that?
Hope my question is clear for your help.
Thank you very much in advance for your advices and help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi Everybody:
4 -5 years ago, I started my career as a translator translating the MetaTexis CAT (Computer Aided Translation Software).
It's amazing to see all the improvements that have been made until now, but recently I found some problems regarding
databases:
I heard that ACCESS databases volume is limited up to 2 GB and that SQL 2005 databases volume is limited up to 4 GB, but I think this information is wrong, or at least I was only able to import 10% of that amount.
Speaking in words, 2 GB doesn't represent a database with a volume of 125,000 segments/sentences (for ACCESS) and 4 GB a volume of 250,000 (for SQL 2005).
Concrete, my "mega.mxtm" database has "only" 359 MB and suddenly I refuses to import more sentences. Is that normal? (MICROSOFT SQL 2005)
Question: Is the new SQL 2008 also limited? Is there any way to "free" or increase the volume capacity?
Point 2: As I updated the SQL 2005 into 2008 I am not able to open the "old" "mega.mxtm" anymore... : (
Regards!
De Sena Viegas
Do anyone have an idea of how to split one report (Report subscribed for automatic delivery) into many file based on the volume of the data retrieved (records1-50 first file, 51-100 second file ect.,).
Say for example I have an employee and department table. The report is designed to provide a list of employees for a given department. If the department contains more than 50 employees then the report is exported individual file for every 50 employees.
Can anyone suggest a way to do this€¦
Regards,
Krishna
Hi all,
I've faced a problem with the below error when I load 1.5m data into oracle database.
The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers.
Please help. Thanks
I am looking to improve the performance of my sql server databases.
I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.
The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.
My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?
I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?
Hello,
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
Thanks,
KTMD
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.
Chris