Data/Transaction Log Files For Two Databases Have Same File Name
Jul 20, 2005
We have two db's. One live and one test.
When I right click on the live one in SQL Enterprise Manager and
select properties -> Data Files ->
File Name is LIVE.MDF
Location is F:DataLIVE.MDF
When I right click on the test one in SQL Enterprise Manager and
select properties -> Data Files ->
File Name is LIVE.MDF
Location is F:DataTEST.MDF
Same thing applies to Transaction log files too.
My concern is File Name is same in both the above cases even though
the location is different. What are the consequences of this.
Thanks for your help
GVV
View 1 Replies
ADVERTISEMENT
May 15, 2015
We have multiple databases on a single instance in an OLTP environment. I have my data files on a separate SAN LUN from my transaction log files (and a few NDFs split out onto additional LUNs). I was wondering if there is a performance benefit to putting each LDF file on its own LUN? Or at least my few busiest LDFs?
We are currently on 2012, but I'm having to put together specs for a 2014 installation and need to answer this question without having an environment in which I can benchmark different setups. I just want to hear whether or not others have done this (why or why not?).
View 3 Replies
View Related
Oct 26, 2015
The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
View 7 Replies
View Related
Sep 27, 2006
First of all i do not know whether this is the right form to ask the question Let me describe the scenario iam using Iam generating xml files at a particular place and sending them to a server xml1|--------------------->dataset1------------------------------>adapter1.update(dataset1)xml2|----------------------->dataset2----------------------------->adapter2.update(dataset2)xml3|----------------------->dataset3------------------------------>adapter3.update(dataset3) all the three updates should happen in only one transaction if any one of the update fails then the transaction should rollbackcan anyone tell me a way to do iti am desperately in search of any ways to do it can anybody help please
View 2 Replies
View Related
Apr 29, 2014
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.
View 3 Replies
View Related
Jul 20, 2005
Hello 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 Related
Mar 27, 2015
I am monitoring the data file growth of the databases in a table. Every week I review to see how much space is left on each database. I am thinking of writing a query where the current free space left is less than 20% of the file size, it sends out an alert to me, so that I can manually resize the file . Is it a good practice to resize the data file manually? If so I believe this need to be done at the time when the server is least busy since it can slow down the database. Also do I need to re-index the tables once the data file is resized?
View 5 Replies
View Related
Jun 14, 2007
Hi All,
Are there any MDS Descriptor Files associated with Databases Files? Are these associated with transaction logs? As per my information there are .mdf , .ndf and .ldf files.Could anyone please guide me ASAP,as there is an urgent requirement from one of the clients?
Thanks in Advance
Gaurav Matkar
View 5 Replies
View Related
Oct 1, 2004
I am wanting to reduce the amount of Virtual Log Files I have. In reading through the Online Book Documentation, I realize that I have forgotten to move the Transaction Log Files to a different drive. Now that the server is in production, I wanted to get some input about the best way of making this change.
Can I just change the directory the log files are being written to in the DB properties without having any adverse problems occurring?
View 2 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 7, 2014
A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.
I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.
I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.
One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.
View 2 Replies
View Related
Mar 13, 2008
In 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?
View 3 Replies
View Related
Jul 23, 2005
Is it possible to wrap a transaction across 2 database? For example:BEGIN TRANSACTION.... alter something in Database_1.... alter something in Database_2COMMIT TRANSACTIONor if there is any error:ROLLBACK TRANSACTION
View 3 Replies
View Related
Apr 9, 2007
Hi All,
Pandon me for asking such question, I am still a beginner to ASP.NET.
I have a project that require me to do single operation that is suppose to update two databases, wonder how do I maintain transaction between these two databases? Please advise, thank you!
View 3 Replies
View Related
Feb 7, 2005
I like to move my database from a directory to another (on different drives foir exemple).
Is there an easy way to do this kind of job ?
View 3 Replies
View Related
Jun 15, 2007
Hi, I've created a new filegroup, then added to it a new filename, then create a table pointing to this filegroup. So far everything is ok, but if I want to revert the process in this way:
1- Drop the table - OK
2- Drop the filegroup - OK
3- Drop the filename - ERROR: Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized.
When I query a catalog view with this query:
Select * From Sys.Database_Files
I get the file that I had recently deleted, it is offline but I can not delete it using the ALTER DATABASE instructions.
This is the code I use:
Use [TESTING ]
Go
-- Add a FileGroup to the Database
Alter Database TESTING Add FileGroup FG01
Go
-- Add a file to a FileGroup
Alter Database TESTING Add File (
NAME = TESTING_DATA01,
FILENAME = 'D:Sql ServerDataTesting_Data01.ndf',
SIZE = 1 MB,
MAXSIZE = 10 MB,
FILEGROWTH = 1 MB
) To FileGroup FG01
Go
-- Create the table using a specific FileGroup
Create Table TABLE1 (
Id Int Not Null,
FirstName VarChar(30) Not Null,
LastName VarChar(30) Not Null,
BirthDate SmallDateTime
)
On FG01
So far everything works ok, but in the next code there is an error:
-- Delete the table
Drop Table TABLE1
Go
-- Remove a FileGroup from the Database
Alter Database TESTING Remove FileGroup FG01
Go
-- Try to remove the file
Alter Database TESTING Remove File TESTING_DATA01
When I try to remove the file there is an error:
"Msg 5009, Level 16, State 9, Line 2
One or more files listed in the statement could not be found or could not be initialized"
View 2 Replies
View Related
Sep 8, 2006
Is there some sort of command that can tell me which mdf and ldf files are associated to which databases?
Thanks,
Ninel
View 1 Replies
View Related
Jun 26, 2006
Hi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
View 13 Replies
View Related
Feb 14, 2006
I recently was given control of an SQL server that stop working.
We had no past backups of the database and the only file we had was the .mdf
I decided to install a fresh version of SQL 2000 on another server and created a database using enterprise manager with the same name as my database called SWATraining
I then stop the sql server
the first thing that I notice is that the .mdf that enterprise manager created had _DATA at the end of the name. Thus I renamed the the orginal .mdf SWATraining_Data and
copied it to the
C:program filesmicrosoft sql servermssqldata
when I started SQL the database was greyed out and had (suspect)labeled
How can I recover the database when the only file I have is the .mdf file??
I even tried
sp_attach_single_file_db @dbname = 'SWATraining',
@physname = 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf'
but reason this error
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'C:Program FilesMicrosoft SQL ServerMSSQLDataSWATraining.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
View 4 Replies
View Related
Jun 23, 2006
I have an existing database with approx 500,000 rows and accessed by afew hundred users per day creating approx 1,000 new records per dayplus typical reporting - relatively low volume stuff for SQL Server.I'm about to add a process that will be importing data daily fromlegacy databases and summarizing it for reporting purposes, integratingit with the existing database. This volume of data will be considerablyhigher, perhaps 100,000+ rows per day, which will be deleted once ithas been summarized and the results written to some intermediatetables.Is there any concern about mixing different levels of volume within onedatabase? As I'll be creating lots of rows daily and then deleting themI was wondering about fragmentation, transaction logging etc. andwhether having this processing in a separate database from the mainapplication would be 'better'.
View 3 Replies
View Related
Jun 26, 2015
I am using alwayson on my SQL 2012 databases. I am using ola hallengren scripts for backing up databases. Full & diff db backups work fine, but the log is not getting backed up. The tran log backup job doesn't error out too. Trying to figure out what I may be missing?
View 10 Replies
View Related
Apr 7, 2008
I am trying to determine if it is possible to add transaction support for a SSIS package. Here are the basic tasks:
Read New/Updated records from SQL Server
Update changed records in Access
Insert new records into Access
Update new records in SQL Server with Access key assigned on insert in previous step
Reset dirty_flag
I would like to wrap a transaction around this. If the package fails in the task that updates sql server with the Access key, the next time it runs, it duplicates the new records in Access because it did not finish the process. Is it possible to have a transaction wrap this entire process so I can undo the inserts to Access if I fail any subsequent tasks?
Thanks,
Steve
View 1 Replies
View Related
Aug 28, 2000
How can I check if database transaction log file is full.
View 1 Replies
View Related
Dec 5, 2001
Hello,
I have a database with two data files as x.mdf, y.ndf and two transaction log files as l1.ldf, l2.ldf. I wanted to have only one log file for this database. What are possible options for me to make these two transaction log files into one transaction log file leaving data files as it is?
Thanks in Advance!
Kumar.
View 1 Replies
View Related
Oct 24, 2006
Hi All,
I am trying to understand the difference between the physical, logical and virtual logs. I found some info in BOL, but I am still a little bit confused.
What is the physical file? Is each transaction considered a physical file?
Each physical log file consists of a number of virtual log files.
Then what about the logical log? When does it come into play?
Thanks.
View 2 Replies
View Related
Jan 10, 2005
Hi,
I'm wondering if when adding another transaction log file, if the DB needs to be detached in order to remove that file. Or rather, what do you have to do in order to reduce the number of transaction log files..?
Cheers,
-Kilka
View 2 Replies
View Related
Jan 14, 2004
Hi,
I'm trying to list all database locations with SQL command, but getting lost here :(
So, anyone any idea how to get databasefile locations from server?
All I need is like: 'C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf' and others.
Thanks.
View 3 Replies
View Related
Jan 24, 2008
Hello,
I need to move all log files for my SQL 2005 databases to another drive. I don't wish to shrink the files, I need to move the logs to another drive spindle. I did find an article (Article ID: 224071) that describes moving both the database and logs using sp_detach and then sp_attach. What is the best way just to move the logs to another drive on the same server, and that keeps the databases in their original location?
Thanks.
View 3 Replies
View Related
Mar 18, 2008
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?
View 5 Replies
View Related
Jul 9, 2015
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
View 6 Replies
View Related
Sep 13, 2001
Hi,
I think we dont have option to read Transaction file in SQLserver Other than using Logexplorer. IS this Logexplorer working file to audit the sql server. We are planning to buy Logexplorer. Is it good product to buy.
Plese give some suggestions.
Thanks
keerthi
View 1 Replies
View Related
Aug 7, 2007
Hi,
In one of our DR Servers we have configured Custom Logshipping. In the folder where the .TRN files are getting copied there is a script to purge the files which are older than one day. Following is the code for the same.
@Echo Off
if exist filelist del filelist
date /t > rundate
for /f "tokens=2* delims= " %%i in (rundate) do set rundate=%%i
for %%i IN (*.trn) do echo >> filelist %%i %%~ti
for /f "tokens=1,2* delims= " %%i in (filelist) do if not %rundate% equ %%j del %%i
:pause
:Exit
Instead of removing the files older than 1 day, I need to keep 3 days transaction logs.
Being a novice I don't have much idea how to accomlish it. Can anybody help me with this?
Many thanks in advance,
Sandhya
View 1 Replies
View Related
Apr 4, 2007
i have a few tables using Sql Server 2005 Express. currently they are holding roughly 30-40k records in them. i have my log files set at restricted growth to 90 megs. while im not close to reaching that, i would like my tables to be able to scale up to possibly millions of records. based on that, i figure the transaction log file will prolly need to have a higher threshold (unrestricted growth). for those with experience, for tables that have millions of records, what are the average size log files i could expect.
is it a bad idea to just shrink the log file every night during off peak hours so that regardless of the amount of records i have, ill always start the day with a minimal log file?
do large log files have any effect on SQL performance?
View 3 Replies
View Related
