Every time a new database is created on my server,my log files and database files automatically default to the following to the C drive in the following path: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData. Is there a way to get them to default to different drives?
For example, right now I have my data files on our F Drive, which is a RAID 5 and my log files on our G Drive which is a RAID 1. Every time an out of the box application installs a database, it does so on the C Drive. I want data files to default to my F Drive and log files to default to my G drive? Is this possible?
When the SSISDB database is created, it appears to be using the "Full" recovery model by default. Because of the recovery model, the SSISDB transaction log needs to be regularly backed up or I risk running out of disk space.I would like to set the recovery model to "Simple", so that I do not need to worry about the transaction log consuming too much space. However, I am not sure what the consequences of that action are.features lost by switching the recovery model of the SSISDB database from "Full" to "Simple"?
I got a quick question. My database size has reached the limit of the drive, one option is to add a new database device on a different drive, then expand in across 2 drives. Anything I have to concern for a database on 2 database device on 2 logical drives (recoverability or ...?) compared to maybe another option:
reformat the whole disk and allocated more space to the original drive and recover (since in my case, all disk space have been formated) from backup....
I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.
Productnr Productdesc Language 1 product EN 1 produkt DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
How can I move the Model database to another location (i.e. from one drive / file location to another)?
I tried sp_detach_db (to be followed up with a sp_attach_db statement) but it rejected it because Model is a "system" database. I tried (and it failed with a "Modify file command failed" message): ALTER DATABASE model modify file (NAME='modeldev',FILENAME='f:MSSQL7DATAmodel.MDF')
I could not locate any help about moving model in books online (BOL). Any ideas?
How do I change the Database owner for a MODEL database? When I open the properties for a Model database, it shows "UNKNOWN" as the database owner, and I can't run the command 'sp_changedbowner 'sa' as it's not allowed to run on master, model, and tempdb. Any help?
I am receiving this message in my MOM Server: The system stored procedure sp_helpdb, which is used to gather information about the databases, has returned an error that may indicate that it cannot determine the db owner for the database [model]. Here are the details: sp_helpdb @dbname='model' on SQL Server Instance: INSTANCENAME. Error number: 515, Error Information: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.
So, I run this script in the Master DB: select name, suser_sname(sid) from master.dbo.sysdatabases where suser_sname(sid) is NULL
And I get the result that the db_owner is set to NULL!
I know that it is not possible to change the DB Owner of the model database but is there any workaround to solve this without reinstalling or similar solutions?
Hi, my 'model' database corrupted and my SQL Server 2005 Express can't be started.
I believe I have the SAME problem as his: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=455524&SiteID=1
but when I try the command start sqlservr.exe -c -T3608, an error message appeared:
"Your SQL Server installation is either corrupt of has been tampered with (Error getting instance ID from name). Please uninstal then re-run setup to correct this problem"
Well, the problem is that I have an ActiveBPEL database inside, and I really need a way without reinstalling it.
I am a newbie to SQL Server Admin. Today when I ran Dbcc newalloc on Model it is showing corruption on syobjects with a 2525 error. I am not sure how to fix this problem? Since this is a critical payroll application server, I need to solve it at the earliest.
If anybody has faced this problem earlier or knows how to solve, please respond to the following address: vsrikanth@ameren.com
I cannot think of any reason, in our environment, why I would recover the model database. Change framework has all databases coming from DEV & QA before landing on PROD. We have never used the model database as framework of new databases either.
So, if I discontinued backup of the database, what is my recovery method if it become corrupt? Since mine is not used, can I simply copy it from another server?
Last week we reset the Model datable to autogrow. Now I get an error when I try to go into it. (I discovered this since I can no longer go into the interface of the program). I wasn't here when the changes were made to the Database Model, but the change was made because the transaction log was filled up.
I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.
Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.
I do not have a backup of the model database and have had to rebuild SQL Server 2000 once already. All my databases except model and tempdb have the collation SQL_Latin1_General_CP1_CI_AS those two have Latin1_General_CI_AS.
During SQL rebuild I've restored over master and msdb and they work fine. Only came across problem with stored procedure and I'm lost as to how to alter model without a backup of it.
Any easy way to change model collation? The command listed in subject fails as it is a system table.
Is it as simple as going into single user mode and trying that command again?
I am using sql2012 se and we want to use couchbase to store some data as documents. So the database will be Hybrid(partly SQLServer and Partly couchbase). However the database is still in the design phase. What are the things that we should keep in mind when designing this database from a design perspective? Our database which was completely SQLServer based(RDBMS) was using GUIDs everywhere based off NEWID() and prime goal is to get rid of GUIDs for the most part.
We are thinking to create a small scale of the "Software On-Demand" model for Support Ticket Management System which means that the client can login to do a support tick and shared the application using the same URL.
Couple options though: 1) We design by table such has a SUPPORTS table with has a foreign key of the ClientID from CLIENTS table. This will cover that client can only can a specific data within the SUPPORTS table. 2) Alternatively, create a dynamic table which is created specifically for "Client B" for instance for SUPPORT table and it will be called such as ClientBSupport table. Something that can be common it will be shared such as like look-up table.
Couple consideration though: 1) Considered client wanting to backup this table. Obviosly it has too querying based on the client id to get the right data. 2) Security withing that table. Is the data needs to be incrypted ... I guess this is expensive excercise. 3) Possibly have a model that access to this table in querying directly etc etc. 4) If the Support table is getting larger and larger and it's not fair for small client accessing the small information which has equal to the one that has big records? Cause it's sharing table.
Have a database that's in "Simple" recovery mode whose .ldf has grown to 270GB. This database is a data warehouse so "full" is not required. I put it in simple mode a month ago and shrunk the log down and now it's filled up the disk.
What steps can I take to mitigate this in future? I've read that this is caused by long running transactions which fill the log for DR purposes. Should I put the database back into full mode and backup/truncate daily.
The auto-growth is set to 128MB which is very low.
I am trying to run a program called NVivo7, which - when I install - also requires installation of SQL Server. Because my C drive is small (and nearly full), I am trying to run NVivo7 off my D drive, though SQL seems to install on C. Is it possible, do you think, to use 2 different drives in this way, or do both the program and the Server need to be on the same one? If so, is there any way to get them both on D?
Been practicing DR strategies with a test SQL instance by following the scenarios listed here: [URL] ....
> Took a backup of the Model database > Stopped SQL Server > Deleted model database data & log file > Started SQL Server and it obviously wouldn't start because TempDB needs a model database present. > Started SQL instance with trace flags 3608 & 3609 > Connected to SQL instance using command prompt. > Issued restore command but was met with this error:
Shared Memory Provider: The pipe has been ended. Communication link failure
And found this in the SQL log..
2015-08-12 16:21:32.83 spid51 Starting up database 'tempdb'. 2015-08-12 16:21:36.88 spid51 Error: 3456, Severity: 21, State: 1. 2015-08-12 16:21:36.88 spid51 Could not redo log record (59:136:21), for transaction ID (0:0), on page (1:20), allocation unit 458752, database 'tempdb' (database ID 2). Page: LSN = (30:165:3), allocation unit = 458752, type = 1.
Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin
In MS Visual Studio, when creating a new Report Model Project, after defining a datasource to an Oracle database (and successfully testing it), and a simple datasource view (1 table), when I click "Run" in Report Model Wizard, I receive the following error: "ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }"
It does not appear that one can create Report Models from an Oracle database (since the SQL being used to query the Oracle database cannot be edited and contains syntax errors?). Is this the case?
I have one database which is multi tenant, every table has tenant id and every tenant is mapped with separate file group. Now can I achieve below :?
1. Can I take the backup of only one tenant and restored in separate database? 2. Can I takeout the data of one tenant quickly? 3. Can I undo the data of a particular tenant with 2 days older data without disturbing the other tenant ?
I used the code below to move the Model database and Model log file to new SAN drive locations. After running the commands I stopped the SQL server and moved the physical files to the correct location. Now I am unable to start SQL server, I get error 17204 Could not open file:Access is denied
I think the problem maybe that for FILENAME new_path I put "J:SQL ServerMSSQL.1MSSQLData" but did not include model.mdf in the file path.
How do I correct the path now that SQL service won't start? I have included the log file below.
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )
2007-09-11 13:19:31.82 Server Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 2007-09-11 13:19:31.82 Server (c) 2005 Microsoft Corporation. 2007-09-11 13:19:31.82 Server All rights reserved. 2007-09-11 13:19:31.82 Server Server process ID is 3264. 2007-09-11 13:19:31.82 Server Authentication mode is MIXED. 2007-09-11 13:19:31.82 Server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'. 2007-09-11 13:19:31.82 Server This instance of SQL Server last reported using a process ID of 2648 at 9/11/2007 1:10:02 PM (local) 9/11/2007 8:10:02 PM (UTC). This is an informational message only; no user action is required. 2007-09-11 13:19:31.82 Server Registry startup parameters: 2007-09-11 13:19:31.82 Server -d D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf 2007-09-11 13:19:31.82 Server -e D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG 2007-09-11 13:19:31.82 Server -l D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf 2007-09-11 13:19:31.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 2007-09-11 13:19:31.84 Server Detected 4 CPUs. This is an informational message; no user action is required. 2007-09-11 13:19:31.96 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required. 2007-09-11 13:19:31.98 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. 2007-09-11 13:19:32.00 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. 2007-09-11 13:19:32.00 Server Database mirroring has been enabled on this instance of SQL Server. 2007-09-11 13:19:32.00 spid5s Starting up database 'master'. 2007-09-11 13:19:32.09 spid5s SQL Trace ID 1 was started by login "sa". 2007-09-11 13:19:32.10 spid5s Starting up database 'mssqlsystemresource'. 2007-09-11 13:19:32.10 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required. 2007-09-11 13:19:32.26 spid5s Server name is 'SS02'. This is an informational message only. No user action is required. 2007-09-11 13:19:32.26 spid9s Starting up database 'model'. 2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1. 2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'J:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation. 2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1. 2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file J:SQL ServerMSSQL.1MSSQLData for file number 1. OS error: 5(Access is denied.). 2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101. 2007-09-11 13:19:32.26 spid9s Unable to open the physical file "J:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)". 2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1. 2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'I:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation. 2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1. 2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file I:SQL ServerMSSQL.1MSSQLData for file number 2. OS error: 5(Access is denied.). 2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101. 2007-09-11 13:19:32.26 spid9s Unable to open the physical file "I:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)". 2007-09-11 13:19:32.26 spid9s File activation failure. The physical file name "I:SQL ServerMSSQL.1MSSQLData" may be incorrect. 2007-09-11 13:19:32.26 spid9s Error: 945, Severity: 14, State: 2. 2007-09-11 13:19:32.26 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. 2007-09-11 13:19:32.26 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. 2007-09-11 13:19:32.26 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
