Does anyone know where a good article pertaining to where you should locate your Data and Log files (in SQL Server 2005)?
I read an article several years ago stating that Log files should be on a seperate RAID 1 and Data on a seperate RAID 5.
Anyway, any help is appreciated.
Hi everybody,
On the time of installation SQL Server asking me where I wont to locate the DATA files and the PROGRAM files. It’s giving to me choice to put database AND log files on one disk and program files on separate. But what about to separate LOG and DATA files. I have RAID1 especially created on F: drive for LOG files and RAID 5 on E: for DATABASE files. When I have to separate that if not on the time of installation? How I can do that?
Thanks,
Miriam
In SQL2000, there's an option to change the location of the template folder. This allows me to create a customized set of templates on a network folder and have all the developers reference the centralized location. Can the same be done in SQL2005 and how would I go about doing so?
Thanks.
I have a situation from where I need to loop through different folders and files in these folders. After processing these files, I need to archive these folders to different location.
e.g., C:MainFolderMar01 ==> Multiple files in Mar01 folder
C:MainFolderMar02 ==> Multiple files in Mar02 folder
Does any one know the best way to do this in SSIS?
Thanks in advance.
BC
Hi All,
Can plz any one help me, acutally as i know sys.files table gives you the acutally physical location of the database fiels with name as well like this
C:sqldatax.mdf
but what i m looking is, is there any qury or script that will give me only the path of the data,log, index files like this
C:sqldata
Thanks and looking forward.
-MALIK
Hello there,
I've been told that it is good practice to keep mdf and ldf files in another location... We have it in place for all our user databases, however mdf and ldf files for our system dbs are still at the same location. I was wondering what is the right way of splitting those should be?
I have C,D,E drives on server. Data files will be on D and Log on E. My question is what is best practice for data and log files for system databases during sql server installation selection? Should they be on C drive along with SQL Server installation or D & E? If they should not be on C then what is the reason and what is benefit to move them on other drives.
View 9 Replies View RelatedI 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 }Â
How to load files with similar format , from two different locations into same database with same ssis.
Lets say
Location 1: C:LoadFilesCust1APP_123445.txt
Location 2: D:LoadFilescust2VDD_543121.txt
Currently we have one ssis which loads and process files from C:LoadFilesCust1 only. we have to modify the existing package it to load files from Location 2 (D:LoadFilescust2) as well. Also while loading, the ssis should assign a value to existing column CustID depending upon the file name. File names always start with APP_ in first location. VDD_ in second location
Assign CUSTID as 100 if file name starts with APP_
Assign CUSTID as 200if file name starts with VDD_
I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.
It ended up on C: and I needed it on D:.
So my questions are:
Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000?
Is there a way to do it during setup so I can avoid this in the future?
We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
Regards,
Sajesh
I have to copy files from a sharepoint or extranet location (basically https://.....) location to my local server using SSIS.
Any kind of early help would be really great.
Script to find the details of creation date and modified date of all files located in a path?
I wanna write few custom messages before I delete some files from a path.
I have Developed ETL Package Which Supplying the CSV File, if I run the package Next time if Same File name  is there I need to Rename the that File with Currentdatetime need to move in to Archive Folder. if that File is not exist in that location no need to move the file into Archive file.
View 4 Replies View RelatedCan multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
View 3 Replies View RelatedI've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
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 RelatedIn the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.
Any Suggestions?
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
It is very intersting to find out that when we are backuping a database we are not able to specify a shared folder .Why?Is it because security concern or it is idenfied as bug or is there any work around.Only thing we are able to specify is that it points to the local drive of the particular server. Help would be more appericated.
View 7 Replies View RelatedI am getting this error when i run my aspx project..Server Error in '/' Application.
Incorrect syntax near '.'.
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.SqlClient.SqlException: Incorrect syntax near '.'.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near '.'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.Sql is there anyway that i can get the line number ,file or any other information regarding location off error.. i got abt 3 aspx files..so i dont really know where to look for the error..
Hi Guys,
I'm actually a beginner in SQL Server and I got use to a database called "Cache" where the Data itself is stored separately from the tables (Table definition), and I'm wondering if its the same in SQL Server where the Data is stored in a deferent location then the Table definition, and if so, where it could be stored?
Thanks.
Can someone tell me what the default installation location
is for SQL 2000? Is it different for the default instance
compared to a named instance? What about the tools? It
seems to me I read something about the tools defaulting
to the program filesmicrosoft SQL Server directory.
I guess I'm more concerned about each instance and the
location of those files. I don't necessarily like the
idea of each instance installing to the program files
directory, if that's the default.
Can anyone help me out?
Thanks
Toni
Hello,
Can anyone tell me how to change the location of 'tempdb.mdf'
It locates in C drive but I want to move to D drive because D has much more space than C in my computer.
Thanks,
Mike
Hi,
currently, the default location for new db is c:msqldbdata and I want to change it to another location.
anyone know how?
Thanks.
We have three separate disks for our sql server.
Disk c : system
Disk d : data
Disk e : logs
A couple of months ago our IT manager moved the tempdb database from the c drive to the d and e drives because it had grown to 9Gb and the c drive had run out of space. When I discovered this, I shrank tempdb back to it's specified size of 1.9Gb and now I want to move it back to the c drive because I believe we will see better performance with the system + tempdb together than with the data/logs + tempdb together. Am I correct?
A couple of notes. Server is used exclusively by SQL server. We have three large databases (40Gb, 20Gb, 15Gb), three databases around 4Gb databases and around 15 smaller databases. Most of them are accessed exclusively from a web application.
Easy question, I hope We are setting up an active active sql cluster. The management have done everything the wrong way around and purchased the hardware already.
We are planning on having 2 databases 1 in each instance. We will place the transaction log files for each database on their own cluster "physical disk" resource. We only have 4 disks availalbe for the transaction log files. To make things a little more complicated I have been given no information on the expected transaction use other than they should be no larger than 50GB
Here are my options
1. 2 Mirrored raid arrays one for each sql instance
2. 1 Raid 10 array with 2 logical drives (so the transaction logs are sharing the same disk spindles)
What would you do? seperate the disk spindles or share them and go for fast disk performace.
Thanks in advanced
Hi all,
I hope someone can give me some tips on how to implement a nice solution.
I need a table to store distances between 2 locations.
First thought was to make a table like this
- id
- fromid
- toid
- distance
However distances can be used in both directions.
So a search to get distance between xx and yy would be: select distance where (fromid = xx and toid = yy) or (fromid=yy and toid=xx)
A unique index (fromid, toid) would make sure no double entries are made, but this still doesn't deny anyone from changing from and to and entering a distance in the other direction (from yy to xx)
Any suggestions for a cleaner db design ?
Ideally one where searches don't need the or, or where the db design can restrict people from entering a distance in the other direction ?
I could force something in business logic to sort locations alphabetically but that also seems like a dirty trick..
Aeneas.
Hi again,I am able to schedule a backup and default it to the server. However, isthere an option or a way to locate the backup somewhere else on the network?regards,gilbert
View 2 Replies View RelatedHi!
When I'm looking at the properties of my dbases I see there are at c:program filesms sql server etc..
But I want to put ALL my current and future databases to E:.
Can somebody tell me how to achieve that?
I have an installation of SQL2005 SP1 configured as follows:
Dell Poweredge 2650 with 4 GB RAM
c:, d: and e: are all on the local host and f: and g: drives are on a SAN. I have the system db's on the e: local disk and the Team Foundation Server databases and logfiles on the SAN disks. SQL2005 is installed to the E: disk.
We are migrating VSS to TFS and our times are very long for a simple 1 GB migration.
I need to know if I need the application databases and logfiles and system databases on the same disk because I see a lot of disk thrash and the tempdb is getting large, 3 GB today.
SQL architecture - should the system db's be on the same disk as the application db's?
Thanks