Transaction Log File Location
Jul 18, 2007
version:SQL Server 2000.
db size: 25.6GB
trans log file: 32GB
so far: I have read the Forum FAQ on "clearing the transaction file" and some DBCC SHRINK in Books Online.
I am trying to set up maintenance plan or backup plan. File sizes are getting very large and performance is horrible since we have had no regular maintainence in the past (No DBA in house).
1. What is the best way to identify the location of the transaction log file associated with a particular database?
When I right-click on the database name in Enterprise Manager and select properties, I can see one location for the transaction log.
However, If I right-click on the database > All Tasks > Shrink Database > click Files and select Temp_Table_Log, I get a different location.
2. We delete then repopulate about 105000 records in one particular table each day. In addition, we do the same with about a hundred rows in several other tables daily.
-- Should I be doing Full Backups nightly?
-- I have the option set to "AutoShrink" on the db. Will this truncate and shrink the transaction log as well as shrink the db when I do a full backup?
Thanks,
Più pranzo libero!
View 1 Replies
ADVERTISEMENT
Sep 19, 2007
I asked this before and was told that it was an option on setup, but for the life of my I can't find it.
I have a requirement for all the transaction logs to go to a separate drive (L:), I would like to be able to specify this location as part of the install but I don't see the option. I have been successful doing so after the install but it is a bit annoying to have to do it that way when it seems like the install should have this option.
I am reaching out once more to see if anyone can tell me if I am missing something in the install process that will allow me to do this?
Thanks in advance.
Future guru in the making.
View 19 Replies
View Related
Sep 19, 2007
Hello,
I have a requirement to have all the transaction logs for our SQL Server databases go to a drive letter L: - I would like to do this during the install process but I don't see any way to specify in the setup. I am able to go in and do it after the install but it is a bit of a pain to do.
Anyone know how I can do this in the install itself?
View 4 Replies
View Related
Feb 19, 2002
I'm setting up a new server and would like the default location for the transaction logs to be on a different drive from the databases. Seems like the best way to do this would be to configure the Master database log location for the drive & directory where I want all trans logs stored. So far, I've been unable to accomplish this. I know I can specify the log location manually each time I create a database, but I'm hoping to configure a default. Is this possible to configure? And if so, how?
Environment is W2k with SQL7.
TIA, Sara
View 2 Replies
View Related
Oct 11, 2001
Hello SQLServer gurus,
We created the database with transaction log files and datafiles on the same drive. Now we want to change the transaction log file
location to a different drive. Please let me know the steps to follow. THanks...
View 5 Replies
View Related
Nov 7, 2007
Hi Experts,
Can we change the transaction log directory to a different location?
If so Can any help to know how it is possible?
View 18 Replies
View Related
Mar 14, 2007
I am finding that in order to have the Web Services Task work successfully the location of the WSDL file has to be on a local drive that SSIS is executing upon. Is the current intended behavior?
In my SSIS task I use a URL path to store information extracted from the Web Service. The information is stored on a different server than the one that SSIS is running upon. This works properly without error.
I have confirmed that SSIS has appropriate permissions to read/write to that directory on that server. When I attempt to reference the WSDL file (located in the same URL directory that I am saving the information) I get a web services error, 'The Web Services Name is empty, Verify that a valid web service name is available."
When I update the Web Service Task attribute to point to the WSDL file located on a local drive it works correctly. I have confirmed that both WSDL documents are exactly the same.
The behavior seems a little strange...so I must be missing something subtle.
...cordell...
View 2 Replies
View Related
Sep 2, 2007
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
View 3 Replies
View Related
Jun 3, 2008
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
View 1 Replies
View Related
Jul 11, 2002
Is the name of the most recent backup file for each database stored anywhere in SQL2K? I want execute a SQA job periodically that takes the BAK from database A and restores it over database B (using the T-SQL RESTORE DATABASE procedure), but I need to know the exact name of the .BAK file; i.e. I need to know the yyyymmddhhmm value at the end of that file.
TIA,
Al
View 1 Replies
View Related
May 14, 2008
I've got some users that created a database with the log file on a drive that doesn't have a lot of space. I'd like to truncate the log and move it to a different drive. I can truncate it, but is moving it as easy as changing the files properties through SSMS?
Thanks
View 2 Replies
View Related
Oct 13, 2015
I am requested to list out SQL Server setup file location but a bit confused with it, I can see two setup files in two different locations:
All Programms>Microsoft SQL Server 2008 R2>Configuration Tools>SQL Server Installation Center (64bit)>Right Click>Open File Location: Directing to LandingPage
C:Program FilesMicrosoft SQL Server100Setup BootstrapSQLServer2008R2x64
But here below it is found the setup file location:
E:SoftwareSW_DVD9_SQL_Svr_Enterprise_Edtn_2008_R2_English_MLF_X16-29
Here in case I have to install a new instance which one I have to select?
To repair the existing instance which setup file I have to select?
View 11 Replies
View Related
Dec 22, 2006
I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?
View 1 Replies
View Related
Dec 22, 2005
Is it possible to set the log file location during installation? I don't see an option under Advanced. Any suggestions are greatly appreciated. Thanks.
View 1 Replies
View Related
Jun 10, 2015
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies
View Related
May 2, 2005
Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directory
and file structure will be a folder with a client name (can be called
anything) and it will have files in it (these files will have the same
filenames as all the other directories. So I will have folder
JimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue with
files a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location to
a DTS package so I can import all the data from the text file into a
SQL server. The way the SQL server will be set up is that each
Folder from the FTP site will be a separate Database and each file will
1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the file
location to pull all those files and then importing them to the proper
database.
I'm not limiting the solution to DTS packages so if .NET can be
incorporated to make it easier then so be it. But keep in mind I
can have up to 200 folders with 12 - 20 text files ranging from
hundreds of rows of data to many thousands of rows. And the
package needs to be ran twice a day so time/performance is an
issue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
View 3 Replies
View Related
Dec 28, 2004
Hi
I want to change the default backup location of MSSQL. I know we can configure it while installing. But I want to change the default backup location of a particular database, running on a database server.
The default location is like.
Program FilesMicrosoft SQL ServerMssqlBackup
Thanks in advance,
Prasanna.
View 2 Replies
View Related
Apr 28, 2008
I'm writing a script for a pending migration (2k enterprise to 2k5x64 enterprise). I've got about 325 or so databases, spanned across 3 instances that I need to migrate. My plan is to do a mass scripted detach of all DBs of the old boxes, copy the files (on a new domain) and then reattach all of the mdf and ldf files.
I've been writing a sql script that will scan a "staging" directory for each of the MDF and LDF files, then programmatically run sp_attachdb. The problem is that the mdf and ldf files weren't always a 1 to 1 naming convention. My predecessors stuck all sorts of things in the file names that make doing a simple scripted search pretty difficult. The disk paths are going to be different from server to server (simpler), so I need to move the files
Is there a way (using sp_attachdb or otherwise) that I could somehow scan each of the MDF files to get the path of the LDF? I can already do that with a .bak file using the RESTORE FILELISTONLY command, but I'm not sure how to do it like this. I'm figuring there has to be a way though, as SSMS seems to know where the LDF is if you try to attach an MDF.
Usually, Google does well, but I haven't been able to find anything as of yet. Any ideas?
View 14 Replies
View Related
May 19, 2008
I want to move index file(69 gb) to different location.Right now data and index files are on the same drive(e), I am trying to move index files from e drive to f drive, so I will get 69 gb free on e drive . Can anyone please advise me what precautions I should take and advise best practice.
Thanks in advance
View 2 Replies
View Related
May 17, 2007
Hi, I am working on a new installation which I did not set up and realized was using the wrong partition of the server to store the data and log files, I have already created several databases, I want to use another partition for these databases without having to drop them and create them all over again.
In BOLine i saw this command but want to make sure its safe, hope somebody can comment on this or if I am missing something.thank you
proposed command:
"MODIFY FILE ( NAME = logical_file_name, FILENAME = 'new_path/os_file_name ' )"
View 7 Replies
View Related
Apr 10, 2008
hi,
Can you guys please give me the steps of how to create a destination file dynamically. What i mean is for example i want to get everything from a table and ssis should create a file distination according to today's date and save it in a specific folder.
thanks
View 7 Replies
View Related
Dec 14, 2006
I have developed an SSIS package which uses an XML configuration file for connection information. It runs fine both from Visual Studio and from an Agent job after I specify the location of the XML configuration file in the Agent job definition.
My question is regarding the target location of the XML configuration file after deployment. I do my SSIS development on a local workstation with SQL Server 2005 DE and Visual Studio 2005 installed on it. I deploy my SSIS packages to a remote SQL Server 2005 SE server. Both machines are running SP1. I double-click the package manifest from my local workstation and specifiy the remote server in the deployment utility. The package gets deployed to the remote server just fine but the XML configuration file ends up on my local workstations C:Program FilesMicrosoft SQL Server90DTSPackages path. I would have expected that the XML configuration file would have gone to the same path on the remote server, where the package itself was deployed. Obviously, when I run the deployment utility from the remote server, it stores the XML configuration file in the same path on the remote server. But this is not a workable production solution.
View 1 Replies
View Related
Jul 6, 2006
I've been working on package configurations here, mainly trying to figure out what works, and how. If I use an XML configuration, and and don't specify the path, ie just typing in test.dtsconfig, it creates the config file on my desktop. When I execute the packge in BIDS, it seems to be looking in that location. If however, I execute the package manually on the filesystem, test.dtsconfig must be in the same directory as the package (which is what I would expect). Why does this work differently in BIDS. Also, if the package is deployed to a SQL server, where would the configuration file need to be in this case? Thanks for the help.
View 1 Replies
View Related
Jul 31, 2006
Currently
File Connection Manager Editor with
Usage Type : Create File
and
File: eg C:DBALogSSISPackage1.log
The desired Goal:
Rather than hardcode the file directory for a log file
eg C:DBALogSSISPackage1.log
can the definition be parameterized.
For example \MachineNameDBALogSSISPackage1.log
where MachineName is the location where the package is phyiscally
located.
View 6 Replies
View Related
Oct 12, 2015
We have 1 tb of dbsize of 3 different db which include ,mdf and log and mdf file and also how some sql jobs ile daily and weeklybackup jobs .what is the best option to copy these db and sql jobs . Is there any tool tools available which can copy these file over the network .How much time will it take
1) If we go in for fullback and restore option ?
2) make the sql server db offfine and copy and paste these mdf ,ldf,log file to different location ?
3) what would happen to the will database login accounts , cross database ownership chaining ? etc
View 2 Replies
View Related
Oct 5, 2006
I have an SSIS package that I created that looks like the following:
Execute SQL Task [Determines the file name]
Data Flow Task
Source - Query [Grabs the data]
Destination [Saves data to file on network drive]
Send Mail Task [Tells user to get file from network]
When I run this package inside Microsoft
Visual Studio I get the results I want. It sends the file to the
network drive and because there are SSN's in the file, it doesn't
actually e-mail the file but puts it in a secure location for the
person to get the file.
When I schedule this in SQL Server 2005 I get the file saved to: c:windowssystem32dynamicfilename.csv
So somehow it is still able to determine what the file name should be, but instead of sending the file to the right destination when you run it through VS, it flops out and sends it to this wacky directory.
I've
spent too much time on this and was wondering if someone can point me
in the right direction without going to the extent of writing a script
like this suggests. thread1555-1265108
Thanks,
Keith
View 6 Replies
View Related
Feb 27, 2007
During installation of SQL Server 2000 I set the default location for data files as D:MySQLServer which resulted in the location D:MySQLServerMSSQL.
I then installed SQL Server 2005. I do not remember being given the option of specifying location for the Data Files. Then I read that the location for named instances is deteremined by the first installation of SQL Server. The location for the data files for SQL Server 2005 turned out to be MSSQL.1 but under C:Program Files.....
I want the default location for SQL Server 2005 to be under D:MySQLServer, something like D:MySQLServerMSSQL.1. How do I do I change the default location for the Data Files.
Jay
View 9 Replies
View Related
Jan 15, 2008
Hi,
I am using SQL Server configuration indirectly with the connection to it configured in an XML configuration file with the location of the XML file and the connections all being different between development and production. I deploy to the file system using the SSIS Deployment Manifest/Package Installation Wizard.
It seems that sometimes the path to the XML config file is updated in the deployed packages and sometimes it is not.
When the path is updated, all connection strings (as displayed in the Connection Managers section of the Execute Package Utility) are displaying the new values as well.
When it is not, it looks for the XML file in the development location and since it cannot find it all configurations still point to the development connections.
Does anyone know of any conditions when the location of the XML config does not get updated and when it doesn't?
Thanks,
Sven
View 6 Replies
View Related
Sep 26, 2007
I have a flat source file(.csv) which I am importing to SQL server. Now, if the source file is not available at the specified location, then the SSIS package should retry to execute n times( say 3 times) after certain time interval. The number of retries and the time interval should be configurable.
I have used Flat File connection manager for the source and OLEDB connection manager for the destination.
I am quite new to SSIS. Any help would be highly appreciated.
View 1 Replies
View Related
Sep 12, 2006
My parent package calls packages stored in the file system. While developing, I would like to call packages in the project bin directory. In production, I would like to call packages in a different development. Is this possible?
I can change the package connection string with an expression that refers to user variables PackageLocation1 or PackageLocation2. I would like to do this automatically. Is this something that should be done at deployment time? Or is there a run time value that I can check and conditionally use PackageLocation1 or PackageLocation2?
Development and deployment is done on the same server, so the same enivronment variable value would be used in an indirect configuration. Same thing applies to a file configuration.
Another question: Is it possible to set up a different Installation Folder for use during deployment? Every time I deploy, I have to navigate the folders, you can't even paste in the folder name.
View 4 Replies
View Related
Nov 9, 2015
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
View 3 Replies
View Related
Nov 15, 2007
Hello,
We have an existing OLD System in SQL Server 2000 DTS Packages.
The Whole application runs on DTS.
There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files.
Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves.
To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System.
But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data.
1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application.
2. Some wants to store it in a Table in the Same Database of this Application.
3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Best Regards,
Deepak
View 4 Replies
View Related
Oct 10, 2015
I am working on SQL Server 2014 and I want to know, Is it possible to change the location of SQL log file and will it affect to the database if it is possible to change its location?
View 4 Replies
View Related
