Moving Transaction Log File In SQL Server V7
Mar 2, 1999I'm in the process of tuning a SQL Server v7 box and have decided that the transaction log would be better placed on its own disk. How do I move a transaction log ?
View 1 Replies
ADVERTISEMENT
Moving Transaction Log File To Different Drive
Jan 19, 2001Hi,
I'm trying to move the transaction logs of my databases to a different drive (for fault tolerance). I can create a second transaction log file for each database via Enterprise Manager but I have 2 questions:
1) If two transaction log files exist for a database which one does it use ?
2) How do I force SQL to use the new transaction log file ? (so I can delete old)
Thanks,
Tim
Moving Transaction Log File To Another Disk
Apr 5, 2004Hi all,
I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;).
Now I have to shift the log files to a seperate disk.
I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline?
Anycomments welcome.
Thankyou.
regards,
harshal.
SQL Server Admin 2014 :: Restore Lost Transaction From Transaction Log File
Jun 10, 2015I 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 RelatedMoving A Text File To Sql Server
Feb 17, 2008I have a text file and moved the data to sql server using import and export data. These are step I am doing.
1. Open Microsoft Sql Server and select DTS Import Export Wizard
2. Using the wizard , I am selecting the Data Source as Text File and provided the file name.
3. In the next option, I am choosing Delimited , followed by Tab option.
4. Then, I am selecting the required Destination and the required Database also.
5. I am choosing the files specified and Run the file.
This method works perfectly.
However, I want a method, where I could avoid steps 1 thru 5 and provide the user a singe step. I mean just an execute of a stored procedure should do the steps 1 thru 5. Is that possible.
Thanks.
Sql Server Express Problem When Moving From VWD File Based To IIS
Dec 5, 2006Hi All,
We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test.
However, when we get to a page that tries to access our Sql Sever Express database, we get the following error:
An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Does anyone know how to overcome this problem? Any help appreciated.
Claude.
SQL Server 2008 :: Moving Tables To New File Group
Mar 16, 2015I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?
Moving Transaction Logs
Jun 15, 2000Is it possible to move .ldf's to another drive. For example I would like to move a database transaction log file from C:mssql7data to D:mssql7logs.
How can this be done?
TIA!
JJ
Moving Transaction Logs To Different Location
Oct 11, 2001Hello 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...
Can I Read Transaction Dumps (*.TRN) File In SQL Server 7.0
Jul 28, 2000our users believe that we lost some valid data, but no one knows who did it,
I thought I can find it from the transaction dumps I take every hour
so ,Can I read Transaction Dumps (*.TRN) file in SQL server 7.0 or Can I get this information through other means.
Thanks for your help
DP
File System Task-Moving File
Jan 19, 2008I have the following directories
\servershare
\serversharearchvie
I have a for each file loop and inside it a data flow that pulls from one of the flat files in the directory and then a file system task. If I choose the "Move File" option in the file system task to move the file to the archive directory, it fails with an access denied message. The access denied message occurs after a message saying file was successfully deleted. I am running this from BIDS right now and my local user can write delete etc in both the above directories. However, if I do a "copy file" in the file system task it seems to work. I think what is happening is it is deleting the file first and then trying to move it, but it no longer exists because it has been deleted--is this possible? Is this a bug of some sort?
For now I am going to workaround by putting in another file system task that deletes the files after they were copied and see how that goes, but would prefer just to do the "move" option.
Thanks,
Kayda
File System Task - Moving A File
Nov 7, 2006I have a File System Task that uses variables to resolve the destination and source paths of a document. When I select the 'copyfile' operation...the document is copied from the source to the destination without error.
However when I change the property from 'copyfile' to 'movefile' I get an error and the document is not moved.
The source and destination variables contain a valid file path name since the copy commmand is working as expected. However when I alter the properties of the File System task to move the document. I get the following error:
Could not find a part of the path 'G:CommonInformation SystemsDropFilesrtNRT_ConfirmationOrder Confirmation Report_11062006.xlsOrder Confirmation Report_11062006.xls
It seems a little nonsensical since the document file paths are valid when performing a copy. For some reason the error log is showing that that the file path is the document name "Order Confirmation Report_11062006.xls" and adding it twice to the the directory path called "'G:CommonInformation SystemsDropFilesrtNRT_Confirmation" as you can see in the above error message.
To replicate the 'move' action...I added an extra File System Task that deletes the document once the copy has been performed. I would like some insight into why this doesn't seem to work.
Thank you.
...cordell...
SQL Server 2008 :: Transaction Log File Drive Is Missing From SAN
Feb 17, 2015We had some SAN issues and we dont have Transaction Log files for some databases.. The drive which was holding this Tlog files were missing.. How to bring back databases.
View 1 Replies View RelatedSQL Server 2000 - Run Transaction Log Backup Using Windows CMD File
Mar 19, 2007How I can run a SQL Server Transaction Log Backup using a Windows CMD File. It's that posible? Anyone know the command or how do this?
We already have a application monitoring our SQL databases. Everytime the transaction log get full, it sends us an alert. Would be better if our application repair the problem itself, executing a command (Windows CMD File).
I'm not a expert using SQL databases or MS DOS Prompt commands.
Any help will be appreciate.
SQL Server 2008 :: Making Use Of A Large Transaction File To Delete Records?
Jun 5, 2015Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
MOVING DATA AND LOG FILE
May 25, 2001I am trying to move a data and log backup file from my C drive to another drive to free my C drive. Does anybody know the easiest way to do it? Thanks.
View 6 Replies View RelatedMoving SQL7 Log File
Oct 25, 1999To all,
How can I move the log file from drive C: to drive D:. I do not need to keep the log file contents and can start new.
Thanks,
Frank
Moving A File Using Activex In DTS
Jun 17, 2004I am attempting to export a records to an excel file using DTS and then use activex to rename the file to include the date it was run. To do this i just move the file.
However when the script is run, it fails saying that permission is denied on the "move" line. I have seen other reports saying that permission is denied when using the "CreateObject" however i have implemented the changes it says, ie/ adding the SQL Agent login using the DCOMcnfg utility and renaming the DefaultAccessPermission key in the registry, with no success. It also doesnt seem to fail on that line anyway.
I have also added all the groups and usernames with full control to the security of the folder the file is in to no avail either.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Main = DTSTaskExecResult_Success
Set fso = CreateObject("Scripting.FileSystemObject")
'
Set bFile = fso.GetFile("c:DTS reportsoutput.XLS")
aYear = year(now)
aMonth = month(now)
aDay = day(now)
filename = "output" &aYear & aMonth & aDay & ".xls"
bFile.move ("c:DTS Reports"&filename)
'fso.MoveFile "c:DTS Reportsoutput.xls", "c:DTS Reports"&filename
Set fso = Nothing
End Function
Does anyone know what kind of permissions the folder the file is in must have in order for this script to work? Any help would be greatly appreciated.
Thanks
Moving A Log File Location
May 14, 2008I'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
Moving The ASPNETDB.MDF File To Another Computer
Jul 22, 2006Hi all!I have some problems with asp.net windows authentication.Asp made the file ASPNETDB.MDF. But I dont have SQL Server on the machine where the application will be deployed...So I have to move this file to another server, but I couldnt find any information about ASPNETDB.MDF connection string or somenthing like this.Is it possible to move this file or I have to make the authentication on my own, with my own database? I hope there is a solution to this problem!Many thanks! Regards!
View 1 Replies View RelatedIndex Moving To A New File Group
May 7, 2008I have a requirement for moving all the non-clustered indexes to a new file group. Does anyone have a script for this?
Thanks.
------------------------
I think, therefore I am - Rene Descartes
Moving The Data File To Another Location
May 17, 2007Hi, 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 ' )"
Moving Table To New File Group
Oct 17, 2007Hi all
I have a table called ACTIVATION_CONSUMPTION which is in PRIMARY file group, in order to move this table to new file group [FG_ACTV], I have done the following
1. ALTER DATABASE [MYDB] ADD FILEGROUP [FG_ACTV] (i have not attached a file to this file group)
2. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ACTIVATION_CONSUMPTION]') AND name = N'ACTIVATION_CONSUMPTION_PK')
ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] DROP CONSTRAINT [ACTIVATION_CONSUMPTION_PK]
GO
3.ALTER TABLE [dbo].[ACTIVATION_CONSUMPTION] ADD CONSTRAINT [ACTIVATION_CONSUMPTION_PK] PRIMARY KEY CLUSTERED
(
[ACTIVATION_CONSUMPTION_ID] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [FG_ACTV]
The above command executed successfully and now the table is in new file group [FG_ACTV]
My question is how come the table was moved to new file group though it has no files attached to it?
Thanks in advance.
File Task Moving With Variables...
Apr 25, 2007I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.
Source Path & Filename:
@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]
Outputs:
\umrdwh2FTPCLAIMDAILY.DATA
Destination Path Now: (Filename not specified per another thread.)
@[User::WorkingFolder]
Original Destination Path: (Which did not work.)
@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]
I receive validation errors:
Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0
How do I overcome this without hard coding the path in the task?
SQL Conn. Problem After Moving .mdf File
Aug 11, 2007Hello ,
I am using SQLSExpress 2005 and VS2005.
Yesterday, i changed my sql connection string and i added ;
AttachDbFilename=|DataDirectory|\dbase.mdf
And i moved mdf file to project's debug directory by using cut-paste. I deleted dbase from databases section in SQL Managament Studio. And i attached dbase according to new path(project debug path).
After these, problem started to occur. If i open a table in SQLMS i cant start debugging my application, it throws
exception " Cannot open user default database .login failed. "
And when i start my application , i cant use SQLMS to see my tables or to run query.It says ; ...cannot be opened due to inaccesible file....
It seems like only 1 application can use my db at a time. But before i moved my database, i could both open tables in SQLMS and i could start running my application at the same time
Do u have any idea?
thankss
Moving Db Via Detach Then Copy And Paste 44 Gig Db File?
Aug 21, 2000Hi,
I have a 44 gig db that I moved to another server via the NT copy and paste method. The db has gotten a little larger, and now NT gets almost to the end, but can not complete -- either starts over completely and just does the last few minutes over and over.
Is there an NT copy and paste limit? Any ideas what the problem is? Is the db mixed up?
Thanks for any help.
Judith
Moving Table To A New File Group (SQL2K5)
May 19, 2008How to move a table to a new file group when the table DOES NOT HAVE A CLUSTERED INDEX.
I also don't want to , create a new table, move the data, drop the old table.
Is there a easier way?
------------------------
I think, therefore I am - Rene Descartes
File Groups: Moving A Large Table
May 8, 2007We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
Like I said a rough estimate is fine.
Many thanks,
Scott
Setup And Upgrade :: Moving Log File To Different Disk
Sep 16, 2015Do you have to move the entire content of the log folder to the new location?
View 4 Replies View RelatedSQL 2012 :: Free Space After Moving Tables From One File Group To Another
Aug 13, 2014Recently maintenance was done removing some tables from the original filegroup in one drive of our SQL Server 2012 Standard Edition 64bits to another created on a separate physical drive. I was expecting the full amount of data moved to the secondary filegroup to show up as unused on the primary filegroup but that doesn't seem to be the case. Do I have to do anything after the move to release that space, not to disk, but to the database as unused?
View 2 Replies View RelatedIntegration Services :: Moving File From One Folder To Another Using C# Script Task
Sep 11, 2015I have 2 source folder .I have created variable for both source folder like below
User::Source1 and User::Source2
and 1 destination folder variable like below
User::Destination
Now I have to search a file from both source folder which consist of *location_*.xml string in file name.I have to use C# Script task ,achieve above scenario as I required it on very urgent basis.
Recovery :: Moving DB Backups From File Share Witness AlwaysOn
May 31, 2015Is it alright to move the .bak and .trn backups which are automatically created in a File Share Witness when a DB is added to an Availability Group? I did that and there were error IDs 1069 occurring, and the AG was unable to be brought up whenever I run a load intensive batch job ...
View 5 Replies View RelatedIntegration Services :: SSIS Package Cannot Perform Moving File Operation
Oct 25, 2015I have an SSIS package, that move file from one folder (Download) to another folder (Working), where it will be processed and passed to (Processed) folder. The folder (Working) is created at run time and deleted after finishing process. I ran this package using SQL Server Agent (I created a sql job). My problem is that the package fails to move the file from Download to Working, Although it can move it to other folders (say I skipped Working and move it directly to the already-created folder "Processed").
I traced the problem and found the error "Access is denied", when run the package without Agent (double click). I provided the necessary permissions to all levels of folders to the user XX, which I made it the (SQL Server Agent Service Account) as well as the Job Owner. By this, the package executes successfully (again by double clicking it), but with Agent it FAILS.
Why Agent cannot move the file to the run-time-created folder (Working) ?