DB Engine :: How To Change Location Of Secondary Data File

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


ADVERTISEMENT

DB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space

Oct 30, 2015

Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below

name    fileid    filename    filegroup    size    maxsize    growth    usage
XX    1    I:DataMSSQL.1MSSQLDataNew XX.mdf    PRIMARY    68140032 KB    Unlimited    0 KB    data only
XX_log    2    I:DataMSSQL.1MSSQLDataNew XX_log.LDF    NULL    1050112 KB    2147483648 KB    102400 KB    log only
XX_2    3    I:DataMSSQL.1MSSQLDataNew XX_2.ndf    PRIMARY    15458304 KB    Unlimited    0 KB    data only
XX_3    4    I:DataMSSQL.1MSSQLDataNew XX_3.ndf    PRIMARY    13186048 KB    Unlimited    0 KB    data only
XX_4    5    I:DataMSSQL.1MSSQLDataNew XX_4.ndf    PRIMARY    19570688 KB    Unlimited    204800 KB    data only
XX_5    6    I:DataMSSQL.1MSSQLDataNew XX_5.ndf    PRIMARY    19591168 KB    Unlimited    204800 KB    data only

2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.

What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.

What more data i need to ensure i submit an analysis to this.

View 5 Replies View Related

DB Engine :: How To Change Error Log Location

Nov 30, 2006

I have been trying to change the location of the SQL 2005 error log. It's logging to the default location at

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG

I want to move the log location to the E drive because it can get quite large. I already changed the setting in the SQL Server Error and Usage config tool, but this did not seem to make a difference.

View 5 Replies View Related

Setup And Upgrade :: Log File Location Change In Server

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

How To Remove Secondary Data File????

Jan 16, 2003

I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?

Thanks, Dave

View 3 Replies View Related

Can I Move To A Secondary Data File

Jun 22, 2007

I have a database that has been running well for a few years.
It has a single data file.
It has now become very large and is creaking and running slow sometimes.
Is it possible to now create a secondary data file or do i have other options?
Many Thanks

View 4 Replies View Related

SQL 2012 :: Log Shipping - Secondary Database Path Showing Wrong Location?

Mar 17, 2014

I've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.

Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).

This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.

SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files

Is this just cosmetic?

Here is an Example of how the Studio shows the incorrect path for the secondary.

Example:

(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,

Log shipped to

(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR

View 2 Replies View Related

Secondary Data File Extension Changes To Mdf After Restore

Apr 18, 2007

i have sql server 2000 db with two data file... primary data file has extension mdf and secondary file has extension ndf (as per microsoft recommendation)..



when i try to backup the db and restore thru the enterprise manager .. in the restore -> options window ... i see both the files has the same extension mdf.. and when the restore completed, the new database still has extension mdf for both the file..



why this behaviour?



* i even try to create a new test db with two files, still its the same behaviour.

View 10 Replies View Related

SQL Tools :: Adding Secondary Data File In AlwaysOn?

Jul 27, 2015

how to add the Secondary Data file to the Database that is a part of the Always on Availability on SQL Server 2012.

View 3 Replies View Related

Moving The Data File To Another Location

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

SQL Server 2005 Data File Location

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

DB Engine :: Table Triggers On Read-only Secondary Replica?

Jun 30, 2015

I was hoping for some information regarding table triggers. We have databases that are part of an AlwaysOn availability group on SQL Server 2012. Some of the tables have table triggers defined. There are both types of triggers, INSTEAD OF and AFTER INSERT, etc.

From my understanding of how secondary read-only replicas work, I am assuming that these triggers have no impact at all when the database is a read-only secondary replica and they only fire when the database is the primary read-write database.

However, after doing a search on the internet, I have not been able to find a definitive source of information.

View 3 Replies View Related

Writing Binary Data To Database Only To Refrence A File Location?

Sep 29, 2006

Hey everyone I've got this question that has me stuck for the last few days but its an important part of my website.....What I am trying to do is basicly have a user be able to upload a file, have that uploaded file plus some other info automaticly display on other parts of my site, and have a different user eventually be able to download that file....I have thought about allowing the file upload as a BLOB but still cannot find a proper way to execute this using VB, plus I have heard that this way of doing it is not reccommeneded cause databases were not designed to store large files like this, lots of articles recommened having the file upload to a Folder on your server then get the binary data for the file that can be placed in a database to refrence that particular file.....Well this also proves to be a lot harder then said here is what I got so far (written in C#) protected void UploadBtn_Click(object sender, EventArgs e)
{
if (FileUpLoad1.HasFile)
{

FileUpLoad1.SaveAs(@"C:Documents and SettingsAdamMy DocumentsVisual Studio 2005ProjectsWebsiteFiles" + FileUpLoad1.FileName);
Label1.Text = "File Uploaded: " + FileUpLoad1.FileName;
}
else
{
Label1.Text = "No File Uploaded.";
}
}
and here is the asp part of the code that goes with it<asp:FileUpLoad id="FileUpLoad1" runat="server" />

<asp:Button id="UploadBtn" Text="Upload File" OnClick="UploadBtn_Click" runat="server" Width="105px" />

<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
 Now from what I know is I need to get the binary of the file which I have read you can do with the Page.Request.Files statement but again not sure how I would impliment this.  Does anyone have any suggestions on which way I should take when dealing with this should I try and just use the BLOB method or use the binary refrence method? and if so how would I impliment this, heck even some good tutorials on the subject would be great... Thanks.....Adam

View 8 Replies View Related

DB Engine :: Migrating Clustered Server From One Location To Another

Oct 16, 2015

We currently have two virtual machines running on Windows Server 2003 together with SQL 2005. These servers are in a cluster and are using multiple physcial RAW disk mappings from shared storage (SAN) for their shared disks within a VMware ESXi 5.1 cluster.I need migrate these SQL servers over to new infrastructure, in a new physical location. I tried using VMware converter to import the active member server in the SQL cluster, but I am receiving VSS errors when running the conversion. I can see that shadow copy is disabled on all the disks on the server.

View 3 Replies View Related

DB Engine :: System Database Files Location

May 16, 2015

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 Related

DB Engine :: Collation Change And DDL Change In Same Script

Nov 18, 2015

We ran into weird/interesting issue with below details.

Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):

We are using SQLCMD to run DDL script on our product database in below order. That script has below content.

step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)

When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.

View 7 Replies View Related

DB Engine :: Database Default Location Is Not Showing System Drive

Oct 30, 2015

In SQL Server 2012 Instance properties ,database default location is not showing system drive E: which was my backup drive. How can I add it again?

View 8 Replies View Related

SQL2005 Log Shipping Roll Change Where Secondary Is Readonly Standby

Dec 13, 2006

Hi,

I am trying to implement a log shipping scenario in sql 2005 where the secondary server is in standby mode with the ability to roll change during failover.

With the help of BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2d7cc40a-47e8-4419-9b2b-7c69f700e806.htm) I can implement my scenario in Recovery mode, but not in standby mode. I use the following sql to put my primary in standby

BACKUP LOG [database]
TO DISK = @filename
WITH STANDBY = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupROLLBACK_UNDO_database.BAK'
GO

which works, but then my restore job fails on the last step. How can I put my primary db in standby mode in such a way that the log shipping restore will work?


Thanks

Andy

View 6 Replies View Related

Change TEMPDB Location

Dec 14, 2000

How do I move the tempdb to a different drive? It can't be dropped and it is automatically put on the same drive as the master during installation. I have moved a transaction log to a different drive, but I can't change the tempdb location. Please help.

View 2 Replies View Related

Change Location Sqlagent.out

Nov 6, 2007

Hello,

How can i change the location of the Sql Agent errorlog file, sqlagent.out
When i choose Properties from the Sql Agent page, i can see the location and filename, but i am not able to change it.
Is there some starup parm? And how can i apply it?

Any help is appreciated.

Best regards,

hvdtol

View 6 Replies View Related

Change Location Of Logging

Mar 21, 2007

What parameter should be changed where in order to send the logging to a separate directory.



THx

View 1 Replies View Related

DB Engine :: Can't Shrink MSDB Log File - Data Is 3GB But LDF Is 300 GB

Sep 26, 2015

On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+.  The data (.mdf) file is only 3GB.

Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."

I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)

I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.

View 7 Replies View Related

DB Engine :: Cannot Remove One Last Data File From TempDB

May 19, 2015

In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created.  I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.

I have also tried using the command:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');

Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.

I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.

I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.

View 2 Replies View Related

Change Default Location Where New DB's Are Created

Jul 19, 2007

Hello
The default location where db's are created is almost full.
Is there any way to change the default location where the .mdf and .ldf are created when I create a new db? I know I can do this when I create a new db and go select where I want it to be, but can I make it default to a different directory automatically so no human intervention is needed?
tia
r/P

View 1 Replies View Related

Change Location Of Template Files

Aug 8, 2006

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.

View 1 Replies View Related

DB Engine :: Splitting Data Of Single Large MDF File

Nov 13, 2015

We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.

Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?

SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) -  Enterprise Edition (64-bit)

View 10 Replies View Related

DB Engine :: 2008 R2 - Shrink Data File Size?

Nov 9, 2015

I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space.  This is because we have removed a large amount of old content from sharepoint.  The log file is fine.  I have 60GB left in my drive that host the database files.  I would like to shrink the datafile to get disk space back.  I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so).  

When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping.  So i have to kill the process. what i should do to reduce this data file.

why it keep using up all the free space in the drive when i try to shrink the data file?

View 10 Replies View Related

Change Location Of Tempdb And Model Databases

Aug 22, 2002

Is there a way to move my mdf and ldf files for the tempdb and the model databases? If yes, how? I tried ti detach but SQL Server will not let me.

View 1 Replies View Related

Change Reporting Services Location (2000)

Sep 26, 2006

hi,

our reporting services DB moved and I need to tell the report manager where to find it

isn't it in a config file somewhere, i can't find it?

do i have to reinstall?

View 5 Replies View Related

Change Logfile Location Without Taking Offline

Dec 7, 2007

If I can get around taking a few databases offline, I'd like to. I need to get logfiles on a lower-capacity drive.

Ideally, I wouldn't like to have multiple logfiles swimming around. But, I was thinking that if I created a second logfile, and then backed up the transaction log, I might be able to remove the first logfile?

The other thought I had, was creating a second logfile, and then turning off autogrow on the first log.

Maybe there is a better solution? Maybe neither of these will work?
Thoughts?

View 2 Replies View Related

Replication :: Change Snapshot Default Location

May 13, 2015

How to change the default snapshot folder (Merge Replication) location in SQL Server 2012 Standard Edition? Actually that field is grayed out.

View 3 Replies View Related

Change XML/XSD Source Location - Gives Lineage Errors!

Jun 26, 2007

Hi,



I have developed a SSIS package on my desktop and the package involves loading of XML data into a database. The XML does not have inline schema and I generated the XSD file from SSIS.



I used Derived Column and Data Conversion to load the data into the database.



Now, i want to migrate the package to a server. Now, when i change the path of the XML and XSD files, all the tasks show error such as



"Input column 'Last_Updated' (4433) has lineage id 3586 that was not previously used in data flow...."



Why is this so? I am using the same XML/XSD files after i moved to the server.



Please advice a fix for this?



Regards,

Vikram

View 6 Replies View Related

DB Engine :: How To Insert Excel File Data Into Temp Table

Jul 9, 2015

I have an Excel file with .csv extension . it has on sheet with name Sheet1.

Now, I'm trying to insert this Excel data into one #temp table. I tried with syntax:

----------------
Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; 

[Code] ...

But, I'm getting error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

If I'm executing for .xls file this statement is working finr and rows are inserted into #temp table. How to take excel file of .csv extension??

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved