If possible how data will be organized in MDF and NDF files. Because we have a MDF file and 11 ndf files. when we are loading the data into one table, it is using all the files in equal ratio. Is there any address reference of ndf files in MDF file?
View 5 RepliesI am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCC command but it is taking too much time. is there any other option for deallocating the space.
View 7 Replies View RelatedI have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
I feel real bad asking this...but I have a state table...ordered by State ASC
and it mixes US and Canada, of course. I want to set up a table and a
stproc to produce a dropdownlist that looks like this (as I've seen in many
websites' state tables):
United States --
Alabama
Arizona
...
Canada --
Alberta
...
...and so on, so that the country is listed as a heading with no state code value
and the states each appear in asc order under the country heading. I don't know
how to do this. Does anyone have a good example of how to do this?
Thx much,
reid C.
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 RelatedHi,I have my create statments for tables, procedures, views, etc inindividual Transact-SQL script files (.sql).I wnat to write another script file that executes these scripts in thecorrect order to create the database.What is the syntax for executing script files from Transact-SQL?Thanks, Phil
View 1 Replies View RelatedI'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something
else End
exec master.dbo.xp_cmdshell
'dir C:Test'
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies View RelatedI am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
I have a job which copies .txt files 24 hours 7 days a week to c:TempSource
What I am planning to do is copy the files from one location to another say c:TempTarget
So I have written the Powell shell script and when i put that in the sql agent job i get below error ;
A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.
$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder }
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
View 21 Replies View RelatedI have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.
View 11 Replies View RelatedI am trying to trace few events using profiler Via T SQL like below, the below scripts works fine, but once the file reaches 1GB , it is not rolling over to another file and start trace again.I tried using URL...once it reaches max file size provided between the time interval..
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2015-08-18 16:00:00.000'------Stop Time 4 PM
set @maxfilesize = 1024 --1GB max File size
[code]...
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!!!
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies View RelatedHello 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 RelatedI 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.
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 RelatedHi,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 RelatedI am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to 255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
I need to create SQL to convert multiple rows data to single row for given subscriber#. Below is the example. In below example , I've 4 family members with same subscriber # and each members have separate rows, I want to combine member data for same subscriber in 1 row, so there would be a 1 row for each subscriber.
View 6 Replies View RelatedI 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
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
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
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!!! :)
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
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.
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.
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.
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.
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