Separation Of Log And Data Files
Sep 15, 2004
I'm kind of new to sql server (but experienced in Oracle) and I've got a couple of questions I wanted to bounce off you guys.
I'm implementing a SQL server cluster right now (2 node on Win2K3, shared EMC DASD for databases). We're at the very preliminary phase of this. I did an install and had my resource group set up with all of my disks on it. When prompted for the data file drive, I gave it one, but it put all the tlogs for the 'out of the box' database on that same drive as the data files (i.e. master, model, tempdb, etc.). The doc is a little vague in some of these areas (i.e. it says separate logs and data files on different disks, but then never actually tells you how to do that).
Now, I know how to specify the default paths for data and transaction logs for any NEW database I create and that's not a problem. However, my question is, how do I 'move' the tlogs from the databases created during the install? I've tried a detach, move tlog to separate physical drive and then reattach the db, but whenever I do this, SQL server wants to create a new tlog for the db on the same old drive as the datafile. I also can't delete the original tlog from a particular database even after I've created an additional tlog on another disk.
Any help is much appreciated. I'm more or less looking for the strategy any of you might take to set up this initial phase.
View 7 Replies
ADVERTISEMENT
Jan 23, 2008
We have an environment/application where we have separate databases for different clients. Each database is pretty much a copy of the other - i.e. same tables, same stored procs, and a few reference tables whose data content is the same across all the dbs - except for that client's data in the user tables.
The issue we have is one of maintenance and growth. We wanted separated DBs so the amount of data in one would not result in performance issues for another client among other things (security and geographical separation for example). However, now that we are adding more clients the deployment team has to apply changes (schema chagnes, stored proc modifications) to each and every database. Does SQL (2005 SP2 in our case) support a concept of shared resources like schema, stored procs, user functions, and even a few tables but separate out the data for other tables? That way the schema, reference data changes have to done in one place...
Thanks.
View 1 Replies
View Related
Jul 18, 2007
I just want to verify my understanding of schemas...
Using separate schemas to segregate data is only a logical mechanism. Separate schemas do not physically segregate data onto different groups of pages or files. Partitioning is the mechanism for physical segregation of data.
Is that everyones understanding as well?
View 1 Replies
View Related
May 11, 2004
Hi all
Do we have provision of separating sa and sso role in SQL server as we have in sybase? ( In such a case, sa shouldn't have any control on creating/modifying users/logins)
Thx
Wilson
View 7 Replies
View Related
Mar 26, 2008
I am new to SQL and, unfortunately, actually do my work in Access 2003, but I have a question.
This is an example of what the data I am working with looks like:
AALADIN
AA-TACH EH65X, EH65V (V-Twin)
AA-TACH w/Robin 20 & 20.5 h.p. OHV
AA-TACH w/Wisc.-Robin EY21
ABI CONTRACTOR w/Honda 20 h.p. (V-Twin)
The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
The
View 7 Replies
View Related
May 13, 2008
Afternoon.
I have a client who is in the market for a dedicated SQL box. It's a smaller company of 50 users. The primary task of this machine is a backend for Sharepoint 2007. At first I'm stuck using MSSQL 2000 for a few months until the budget can be approved to upgrade them to 2005.
I intend to spec this machine out for 2005, the question I have at the moment pertains to the HD configurations.
How would you recommend rigging that up?
Mirror for system, Mirror for the data store, mirror for transaction/temp files?
Put everything on a Raid-5 and forget about it?
I also have a question about Quad Core processors. How does MS sql's licensing work on that?
View 1 Replies
View Related
Mar 16, 2007
I guess I just don't get the reasoning behind the new SqlDataSource control. Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET? In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display? It doesn't make sense. Why would anyone do this? And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?
View 4 Replies
View Related
Oct 23, 2007
Dear all,
I have a table like:
State -- Customer
TN -- AAA
TN -- AAA1
TN -- AAA2
Delhi -- BBB
Delhi -- BBB1
Delhi -- BBB2
Mumbai -- CCC
Mumbai -- CCC1
Mumbai -- CCC2
Maharashtra -- DDD
Maharashtra -- DDD1
Maharashtra -- DDD2
I want to show the output in a single query like:
State -- Customers
TN -- AAA, AAA1, AAA2
Delhi -- BBB, BBB1, BBB2
Mumbai -- CCC, CCC1, CCC2
Maharastra -- DDD, DDD1, DDD2
How do i do this in a single query
Can Pivot query will help in this situation. Please explain with a sample query
Thanks
gopalan@sofist.com
okugops@hotmail.com
View 4 Replies
View Related
Nov 8, 2015
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
View 2 Replies
View Related
Jan 9, 2015
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies
View Related
Sep 17, 2004
Hi!
I'm using replication with two database on SQL 2000,when begin, the log files size is 50mb and the data files size is 150mb. But now the log files size is 2Gb and the data files size is 4Gb. I would like to decrease the log files and the data files ??? How do i do this???
(I using Truncate and shrink doesn't change )
Thanks!!!
View 2 Replies
View Related
Jul 20, 2005
Hello all. Before my arrival at my current employer, our consultantsphysically set up our MSSQL 7 server as follows:drive c: contains the mssql enginedrive d: contains the transaction logdrive e: contains the data filesNo filegroups were set up and the data files consist of only 1 largephysical file. Currently, our data file is >10GB. When I was trained onthe physical aspects of sqlserver, I was told to never create physical files[color=blue]> 2048MB each. If I did, I could expect inefficient physical storage of[/color]data and slower performance (due to the OS).Our server has 2 RAID-5 arrays. Drive c: and e: are located on the firstarray and drive d: on the second. We're running Windows 4.0 NT Server SP6with NTFS.Can someone comment on the use of 1 single large data file vs. more smallerdata files?
View 2 Replies
View Related
Aug 7, 2015
I have an MVC asp.net application that stores many records in a table on sql server, in its own system. used the system for 2 months, worked fine accessing, changing data.
Now that other users are logging in? there is cross coupling going on. one user gets the data from another users sql search.
In the mvc app it had used the get async method to read the ID record from the db, i set that to synchronous. no effect; the user makes their own login id but that does nt matter either.
View 8 Replies
View Related
Mar 10, 2008
i am really in need of help. i have a text file consiting of some data.i want to update my database from that text file periodically say 12 hours.the text file is being updated by another server program in every 12 hours can any one help me in this case? i am lost for this scenario?? help me please.....
View 1 Replies
View Related
Dec 10, 2005
Hi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies
View Related
Sep 17, 2001
I have an sql 6.5 server that has had it OS (nt4.0) reinstalled on c:.
sql was installed on d: and all of the dat files are still there, i need to recover data from one of these, i tried install on a new machine and creating a device/db with same name and then over writing the file with the old one but enterprise manager shows it as suspect and will not let me work on it.
Any assistance would be appreciated.
Dominic
View 1 Replies
View Related
Nov 13, 2007
Hello
I have a database with two data files configured under primary filegroup. Both the files are set to grow automatically till the disk space is full and both the data files are in the same drive on same physical disk.
When the transactions are happening in the database,how the data is getting distributed on both data files?
Will the transactions is inserted in to the primary data file first and once that is full it will start occupying the secondary data file?. How this distribution is happening?
Thanks in advance
Regards
Arvind
View 1 Replies
View Related
Mar 14, 2007
Hello all,
I am installing SQL Server 2005 on our server and have researched with no success on how to redirect the Data Files and Log files to a different drive, e.g. D: drive. In SQL Server 2000 you specified where you wanted to place the LOG files and Data files upon Setup.
I have looked everywhere in SQL Server 2005 install and cannot find where I can tell it to place the Data and Log files. Please point me in the right direction.
Your help is greatly appreciated,
Dan
View 3 Replies
View Related
Aug 14, 2006
hi
just wondering if any expert out there can answer my question.
i got a database separate into 3 datafiles in 3 different drives.
i will name it A, B, C.
A datafile size 30G, B datafile size 15G, C datafile size 15G.
and the drive for datafile A is about full. so is there anyway i can more some of data from A datafile to other data file? or since A+B+C =60G can i make it all 20G for each one of them by any command? thanks!!! :)
View 4 Replies
View Related
Jun 26, 2001
Hello Group,
I have a SQL Server 2000 database that has never been backed up. The SQL Server database can't be logged into due to an SSL Security Error, thus I can't get to the backup utilities within Enterprise Manager.
What data files do I need to backup manually and what steps do I have to take to backup these files to a tape and rebuild the server?
Thanks
Kevin Kraus
View 3 Replies
View Related
Jun 8, 2004
Hello,
My first post, I don't know much about MS SQL.
I have the .mdf and .ldf files from an MS SQL 2000 server. I don't have MS SQL server running on my home machine. I'd like to be able to extract the data from the .mdf file to something I could use on my home machine. XML would be good, CSV would be ok too, maybe even some way to import into MySQL. Any help would be appreciated, also if I've some how missed the forum faq, please point me to it.
View 8 Replies
View Related
Jan 25, 2005
We have a large Database (91 GB) that is currently in one large data file. Now that we have muliple disk arrays I can split that up on I would like to have a couple data files. My question is, what is the best way to split this up? Should I keep one primary file group and just create another file, or should I create a file group for indexes and put those on that? This database is used for reporting only so it doesn't really have any writes being done on it.
Thanks much.
View 10 Replies
View Related
Jun 15, 2006
We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.
View 2 Replies
View Related
Feb 13, 2007
How can I combine two data files (i.e .mdf and .ndf). We had two data file in our old server due to disk limitation. In the new server we don't have such limitation so we like to consolidate the two file in one. What is the best way to accomplish that?
Thanks
View 1 Replies
View Related
Mar 30, 2004
Hi,
How do you defragement SQL database data files? Running the DBCC DBREINDEX , DBCC INDEXDEFRAG, does that minimize fragmentations of the tables and the indexes files or is it just the index files?
Thanks in advance
a
View 6 Replies
View Related
Nov 16, 2007
Hi all
I'm a newbie with SQl Server But somewhere i read that SQL server Data can be Recovered through *.LDF files
now i want to know if i open a table in Enterprise-Manager and delete some records through DELETE statement or
manually, is it possible to recover those deleted records or not? if so how?
Thanks in advance.
Regards.
View 4 Replies
View Related
Dec 4, 2007
Hi All
i am bit confused about how data and log files grow in databases. suppose i turn off the auto grow and restrict the maxsize upto some limit but size of data/log file is less then maxsize at some stage because what i understand is size of data/log file keep changing depends upon the activities going on the database. in future if data/log file need to grow can it grow upto the maxsize without turing on the auto grow.
regards
View 4 Replies
View Related
Feb 29, 2008
Can someone help me out in telling me what a data cube is and if you have any examples I can look at?
We are in the process of using data cubes through SQL. What we are looking for is to be able to have summary of data, but if we wanted to click on a grouping we can go to the details of the data.
I just wanted to know if there was any infomation/websites I can go and look at.
Thanks,
Wendy
View 2 Replies
View Related
Sep 7, 2005
Hi guys,I have been trying to write data from a database to XML files andalthough i have been successful in doing that, the file(s) that are madeare not of the form that I would want.I am using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to generate the files where strFileName is the name of the file. Thefiles when viewed with XML Editor are seen as lines of data instead ofthe regularly generated XML where each row is one below the other. Whenviewed with WordPad/Notepad i see elements />< betweeen the dataand somehow I think this has to do something with the problem.Can someone help me with this?? Any help or suggestion would be greatlyappreciated.Thanks.*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Jul 20, 2005
hello,my problem is how to move data files drom drive c: to drive d: ??--MarcinS
View 1 Replies
View Related
Oct 18, 2007
Hi,
My first post.
Problem:
I have 2 tables EmployeeA(Eng) and EmployeeB(Spanish) kept in seperate mdb's. I want to add the records into Sql Server 2005 table called StateEmployee.
Procedure:
1. Loop through 2 folders..one containing table EmployeeA in mdb and other containing tbl EmployeeB in diff mdb's.
2. Pick a file from EmployeeA and EmployeeB, both at the same time.
3. Count the total no of rows in both files. If equal proceed.
4. Compare the 'employeeid' of one row of employeeA to the employeeid of EmployeeB.
5. If employee id matches, load both the rows in Sql server else file it to the error table.
6. Loop through all rows simultaneously till end of row.
7. Go to next mdb.
How do i go about this step by step. I am fairly new to SSIS. I asked my other friends too but they have complex answers which i couldnt follow. Hope someone gives an 'easy to understand' solution with sample.
thanks.
View 1 Replies
View Related
Mar 24, 2007
I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.
View 5 Replies
View Related
Apr 18, 2001
SQL Server 7.0 doesn't seem to support data files for bulk insert that have quoted text fields.
e.g.
" 1","Farmer","Joe","AAA","Smith John","",20001001,
I've tried using the format file to strip out the quotes. But, this doesn't seem to work.
My format file looks like this:
4.2
9
1 SQLCHAR 0 0 """ 0 dummy1
2 SQLCHAR 0 9 "","" 2 EmployeeID
3 SQLCHAR 0 35 "","" 3 LastName
4 SQLCHAR 0 35 "","" 4 FirstName
5 SQLCHAR 0 10 "","" 5 Category
6 SQLCHAR 0 35 "","" 6 Supervisor
7 SQLCHAR 0 5 ""," 7 OpCode
8 SQLCHAR 0 8 "," 8 HireDate
9 SQLCHAR 0 8 "
" 9 TermDate
Any idea on how I can bulk insert a data file where some of the fields are qutoed.
View 2 Replies
View Related