Hi,
Just a quick one.
Is it advisable to have the data files and log files for a database in the same folder or different folders and what is the difference if any.
Thanks for the advice.
Stevfe
I know I've seen documentation on this but I can't find it at the time. What's the recommended file locations for a SQL install.. System and Data on a RAID drive and logs on a separate drive that's mirrored..? Oh and if anyone has links to this info let me know also.
Thx!
I am trying to split the log and data files between two drives on a newSQL-Server 2000 installation. I followed the instructions from the MSarticle 224071 (Moving SQL Server databases to a new location withDetach/Attach).Unfortunalty, when I try to move the Master database, as perinstructions, access to SQL-Sever is lost;the service will never startagain. I've tried this twice after re-installing with the same results.Anyone have any ideas as to what is going wrong?Tim
View 3 Replies View RelatedHere is what I am trying to accomplish. I need to move *.pdf files from a local directory into a local staging directory, then from the Staging directory FTP them up to the customers site. Then move the files from the staging directory to an archive directory.
I can do this fine as long as all values are static in SSIS, I need help to figure out hoe to do this using variables. The Directories to be used are to be supplied by the DB all that is give in the directory itself all files in the directory need to be moved.
Any help would be appreciated.
Thank you,
Mike
Michael Alawneh, DBA
Hi,
I have to create a datebase for an assignment and I am using MSSQL and need a little bit of help for something that I don't think can actually be done.
Basically I have an artist table. Now each artist may well have a website of their own where their biography can be found. In my artist table I have an artist_bio field where I want the URL of the artists bio to be placed and only the URL. So for example I would want www.iamanartist.com/mybio to be put into that field. So I was wondering if there was a constraint that could do this?
I don't think there is because URLS can be so complicated but if someone could tell me whether it is or isn't possible I would be really grateful.
I am also wanting to do something similar with the location of a hardcopy of their work. So I have a digital_images table with a location field where the loaction of their work will be on a harddrive. Is this possible or not as well?
Cheers for any help you can give,
Liam
Hi all,
I've have a simple question. After running the SSIS deployment utility to install a couple of SSIS packages into a SQL Server, is there a way of determining programmatically (at some later stage) where the dependency files (in my case a simple xml configuration file) for the SSIS packages were installed (if the user chose not to install it into the default location).
Thanks,
Johan
I have a package and added a configuration file to it to hold settings such as connection strings etc.
When I open the package up in the designer and check where the configuration file is from, it likes to specify it with a full path like this:
\myserverprojectspackageslah.xml
Where blah.xml is the configuration file and the package file is in the same exact directory.
This creates an issue when I use build and then try to install the package on a server. When installing, the server tries to read the config from \myserverprojectspackageslah.xml which it can not.
Conversely I've tried editing the location in the designer changing it to just be "blah.xml". Then after using the build option I am able to install the package successfully on the server (it can not find the xml file).
However when I then load the package later in the designer to try to edit or chagne it, the designer complains that the "configuration file could not be loaded" and it can't find it even though it is in the same directory that the package definition file is in.
What's going on with this and how do i solve it?
When I go to open a file in SSMS, the Open File dialog box appears. In the left side of that box, there is a panel containing shortcuts to various locations: Desktop, My Projects, and My Computer. I would like to add some shortcuts to folder that I use in this area, but I haven't been able to figure out how. how to do this?
View 9 Replies View RelatedWhen adding an SSIS step to a SQL Server Agent job, when selecting the location of a config file, the dialog lets you select from the database server you're working with. If selecting the location of the package itself (when the source is File System), the dialog lets you select from the machine where Management Studio is sitting instead of from the database server. Is that intentional? And if so, why? Should I just use a fully qualified file name for the package location rather than one using a drive letter?
View 3 Replies View Relatedhow can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies View RelatedHi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies View RelatedI've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
View 2 Replies View Related
hi all;
1. Excel file Source--> monthly Revenue details
2. Derived Colum Transoformations
3. Oledb Destination
its my flow in one of my packates (ETL job)
Excel file contains monthly revenue details, i wanna import the excel data to my database staging table, so i've created the package.
its working fine...
Problem
if we change the new data for the next month and running the package its not running;
the same file, same format, only we delete the contents, of the file except first row of the excel sheet,
and pasting the new data;
new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us)
i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized
Do you want to Fix this issue automatically with the available external column's meta data
Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to.
now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the package Execution wizard,
ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the asp.net webpage, that is executing the package in an On_click event)
The package is not running from the server, its due to the meta data change in the Excel file( i guess)
please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta data should not change when we have new updates in it;
otherwise suggest me some solutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kind of Data Profiling activity;
i know its some what crazy, but i need to maintain the system with permanent solution, instead of facing this meta data mismatch issue!!!
some what lenthy explanation--> its needed for my dear powerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries, i'll try my level best.
How to use Microsoft Data Link(Udl) type file to connect with SQL Server using SQL Native Client,If i use OLDEDB Connection i can make connection using UDL file with Database ,but how can i use a file for a connection using SQL Native Client.Is there any other data link file that can be used to connect with SQL server using SQL Native Client.
View 5 Replies View RelatedHi all,
In a foreachloop, I am inserting records into a flat file which is working fine. But the thing is that as the file grows, it takes longer for it to locate the EOF(End of File) of the flat file so as to insert the records.
I have around 70-100 lines written to the file at each loop and there are more than 20k records to be looped. wihich means that at the end I should be having 1400k - 20000k line in the text file.
One solution would be to insert the records at the start of the file itself so that it does not has to lookup the EOF each time before writting.
Another would be to generate separate files and then merge it.
Any idea how can this can be done?
Beside this I have to zip the file and then SFTP to a given address.
Any suggestion or help would be welcome.
Rdgs
David
I am running my package in sql server 2012, in which i am giving network path for flat file destination. And its working fine. But if i give m local path, its giving me error " cannot open data file" ...
Nothing is wrong with package.
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
View 7 Replies View Relatedone of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies View RelatedHow do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
good morning,
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Best regards.
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
Hi all,
I have a serious problem:
I created 1 database with 2 file group : 1 primary and 1 index.
- Primary file group includes 1 data file (*.mdf): store all tables
- Index file group includes 1 index file (*.ndf): store all indexes
Most of indexes are non-cluster indexes
After a short time using, data file is 2GB but index file is 12 GB.
I do not know what problem happened in my database.
I have some questions:
1/ How so I reduce size of index file ?
2/ How to know what is stored in index file?
3/ How to trace all impact to index file?
4/ How to limit size growing of index file?
Thanks in advanced
Thi Nguyen
I have a database whose log file size is 4 time greater then data file size, and its continuously growing day by day. Recently face limited disk related issue.
Is there any way to truncate log file???
What is impact on db if i truncate log file???
Is there any way to prevent this file continuously growing???
We have a large 'History' database that is currently about 4.5TB, with most of that in a datafile that is 4.2TB. We wanted to stop growth on the one large data file and have SQL Server allocate new data to the other data files, but this throws an error when we attempt to change the MAXSIZE settings:
ALTER failed for Database 'History'
MODIFY FILE failed. Specified size is less than or equal to current size.
The SQL Server is saying we can have a max size of 2TB, and anything over that is blocked. Since this is being blocked, the file continues to grow.
Is there any way to cap the growth of the 4.2TB file and not allow any more data to be written to it?
I have one of our production Accounting Databases starting from 2 GBnow grown into a 20 GB Database over the period of a few years...I have been getting timeouts when transactions are trying to updatedifferent tables in the database.. Most of the error I get are I/Orequests to the data file (Data file of the production dbAccounting_Data.MDF).I would like to implement the following to this Accounting database.I need to split the Data file into multiple files by placing some ofthe tables in different file groups. I have the server upgraded to beable to have different drives in different channels. I can place thesedata and log files in different drives so it will be less I/Oconflicts..I would like to have the following file groups..FileGroup 1 - which will have all database definitions (DDL).FileGroup 2 - I will have the AR Module tables under here..FileGroup 3 - I will have the GL module tables under here..FileGroup 4 - I will have the rest of the tables under hereFileGroup 5 - I will like to place the indexes under here....Also where will the associated transaction files go?I would like to get some help doing this. Is there any articles / helpavailable that I can refer to. Any suggestions / corrections/criticisms to what I have mentioned above is much appreciated...!Thanks in advance....
View 1 Replies View RelatedHi,
i'm trying to write this script that check my database file and log size(in MB) and insert them into a table.i need the following columns
dbid,dbname,compatability_level,recovery_model,db_size_in_MB,log_size_in_MB.
i try to write this a got stuck.
select sysdb.database_id,sysdb.name,sysdb.compatibility_level,
sysdb.recovery_model_desc,sysmaster.size from sys.databases sysdb,sys.master_files sysmaster
where sysdb.database_id = sysmaster.database_id
can anyone help me with this script?
THX
Hi all,
I want to create a database on a computer but datafile nad transaction log file located on the other computer.
please show me,
best regard.
Hello, i've installed DotNetNuke on my PC and used Visual Studio 2005 to create an SQL Server 2005 database using the "add new element" feature in the Solution Explorer, which works fine, but sometime when i log back in and bring up the DNN web page i will get some kind of database error, ie below:
The log scan number (49:361:1) passed to log scan in database
'C:DOCUMENTS AND SETTINGSBOBLOCAL SETTINGSTEMPVWDWEBCACHEIBM-E24B88DE7DD_DOTNETNUKEAPP_DATADATABASE.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database
'C:DOCUMENTS AND SETTINGSBOBLOCAL SETTINGSTEMPVWDWEBCACHEIBM-E24B88DE7DD_DOTNETNUKEAPP_DATADATABASE.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9003)
Can you please tell me if there is a way i can sync up the (.ldf) file with the (.mdf) file somehow as this error will sometimes happen when i havn't made any changes to the database, or at least i think DNN hasn't, maybe it does but it's not obvious.
Thanks very much for any suggestions or references!
BobK
Greetings,The following shows how the Properties of a database look like:Data Files:File Name: student_datLocation: e:dataMSSQLDatastudent.mdfSpace allocated (MB): 62'Automatically grow file' checkedFile growth: 'By percent 10%' checkedMaximum file size: 'Unrestricted file growth' checkedTransaction Log:File Name: student_logLocation: e:dataMSSQLDatastudent.ldfSpace allocated (MB): 52'Automatically grow file' checkedFile growth: 'By percent 10%' checkedMaximum file size: 'Unrestricted file growth' checkedThe physical files look like this:Name Size Type Modified------------------------------------------------student.ldf 52,416 KB Database File 2/11/2004 10:34PMstudent.mdf 63,424 KB Database File 2/11/2004 10:34PMMy question is now that it has been specified 'Unrestricted file growth'for both the data and the log file, why both haven't increased any insize since 2/11/2004? Actually, the modified timestamps of some otherdatabases files are also '2/11/2004 10:34PM'. That's weird.I found the following message that's relevent to the above timestamp inthe SQL Server Logs:=====2004-02-11 22:34:10.12 server SQL Server terminating because ofsystem shutdown.=====I'm pretty sure there have been a lot updates taking place on thisdatabase. We don't hear any complaints from the customers that theyhave had any problems (such as, no space left) with the databases.Did the SQL server write the data and log somewhere else?Any insight on what's going on would be appreciated.Bing
View 7 Replies View RelatedI need to import data from csv-file to sdf-database (SQL CE 2.0).
When I copy the csv-file to my mobile device, it thakes more than 1 hour. The sdf-File later has a size of 20MB.
If I create an sdf-File (SQL CE 3.0) on the desktop, it just thakes a few minutes.
Is it possible to create an sdf-File (SQL CE 2.0) on the desktop legally?
My clients don't want to by the SQL-Server, because it should be an inexpensive solution.
Or is there another way to create the database with more performance?
Thanks