I have a SQL server with multiple instances on it and would like to move one of them to a drive with more storage.
I have SQL 2010 on a server with 2 partitions.
The database is located on the C: drive (original build) but the drive isn't partitioned to handle a db of the size that this one will grow to. I would like to move the full DB instance to another partition.
Does anyone know if SQL7 will work with Storage Area Networks(SAN's)? I've read that SQL2000 implements something called a Virtual Interface System Area Network (VI SAN) that allows communication with devices connected via a SAN.
My site is installing a SAN and I need to know if SQL7 can utilize those resources (Storage,etc) and how reliable if so.
Randy
I have a Windows 2003 server with SQL Server 2005 installed. Theserver is on small drive and we would like to upgrade to much largerharddrives. I've been hearing of problems using Ghost to get an imageand placing the image onto the new drive. I think this is more of aWindows 2003 problem, but this server is for nothing but the SQLServer databases. Does anyone have a clear method of moving thisserver to the larger drives?TIA.
View 3 Replies View RelatedI'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
Hello,
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get.
" The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
connstr= "Data Source=Storage CardItems.sdf;Max Database Size=500;"
Any help regarding this would be appreciated.
Thank you
.
Hi,
i want to be alerted when any of my databases is more the xxxGB.
i know one method to do that and it is with the alerts in the SQL agent Performance condition ,but with this alert i needs to created alert monitor for every DB.
do you know a better way to achieve that ?
THX
I have written code to combine, delete redundant data in my system. The table structure remains the same, except I changed some INTs to TINYINTs.
When I do sp_spaceused, it tells me that number of rows is smaller(which is correct), but the datasize, and index_size is significantly larger AFTER the deletions.
I tried using shrink, but that doesn't seem to change anything.
When I right-click the database, and choose PROPERTIES, it also confirms that the database got significantly larger.
I am confused about how deleting data and changing to TINYINTs could make my database bigger. What would cause this?
I have replication setup between our main site and a remote one, and have recently noticed that the database at the remote site's .MDF file is about 3 times as large as the main site's. This doesn't seem to make sense since essentially all of our data is replicated between the two servers. Can anyone suggest why this might be happening and what is safe to do to shrink the remote file?
TIA
Ron L
Hi,We have troubles when we try to use the 'dbuse' calls with databaseslarger than 28 characters, looks like the dbuse truncates the nameafter it.Any ideas ???
View 4 Replies View RelatedWe have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Hello,
I spent a lot of time yesterday reading all the threads and other information I can regarding this topic, and I thought I had it figured out but I don't think I quite understand it, so I am posting here for a little clarification.
I read the following links to begin with:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1
http://blogs.msdn.com/lcris/
I set up encryption on SQL Server instance, then backed up and restored a database to another instance on another machine. From what I have read, I thought all I needed to do was the following:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
I then tried to read encrypted data by doing the following:
OPEN SYMMETRIC KEY <key>
DECRYPTION BY CERTIFICATE <cert>
SELECT CONVERT(varchar, DecryptByKey(<column>)) FROM <table>
CLOSE SYMMETRIC KEY <key>
But I get the following error on when opening the symmetric key:
"An error occurred during encryption".
I tried executing these statements in various order to see if i was running them out of order but still the same error.
Obviously I have overlooked something. Can someone please shed some light on what I have missed?
Greatly appreciated...
Scott
after moving the resource database files with this command:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_mastermssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_mastermssqlsystemresource.ldf');
GO
I am getting this error trying to do anything in the instance while I have it started in minimal mode (/f /t3608)
(the instance WILL NOT COME UP UNLESS I USE THE /F /T3608 from a command promt.. please .. any suggestions??????????????????????
Also.. I checked and the primary file is NOT READ-ONLY.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
File activation failure. The physical file name "E:MSSQLKOCSQLDEV01Datafilesmssqlsystemresource .ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 945, Level 14, State 2, Line 1
Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I think one of my system database files is corrupted. I did an alter database... to move my Model and MSDB and now I can only start the instance with /f /t3608
I can connect to the instance but I can't do anything I get an error that one of the database files does not match it's primary file.. I don't know if it's MSDB or MODEL but I pretty much can't do anything.
I made a backup of model and msdb before I started.. how do I go about restoring them?
Hi,
View 17 Replies View RelatedHi,
I would be very grateful if someone could help me. I have very little SQL knowledge and would like a push in the right direction:
I have a application that receives GPS lattitude and longitude values. I was originally using file base system where I would load all the points from the file into memory and do the calculations in the software but I now would like to use SQL Server Express.
I have created the tables:
[Table 1] GPS Points:
id int Primary key
latitude real
longitude real
desc nvarchar(128)
[Table 2] GPS Locations (comprises of one or more GPS Points from [1] above) to form a boundary:
GPS Locations database structure is:
locationId int primary key
gpspoint1 int
gpspoint2 int
gpspoint3 int
gpspoint4 int
desc nvarchar(128)
Table 2 contains up to 4 gps points ids from table 1 and GpsPoints1-4 can be null.
I have created the tables and I have inserted GPS values into both tables.
My problem:
I am trying to compare the current Lat/Lon (which are passed into the SQL fuction as two real datatype value) and I want to return all Table 2 locationId's where the current Lat/Lon are withing GPSPoints1-4 area.
gpspoint1, gpspoint2, gpspoint3, gpspoint4 reference an id from Table 1. I am not sure how I can do this?
I have implemetned a SQL function that returns all points from Table 1 that are within the predefined radius of 1.5 miles from the current Lat/Lon values passed in.
select desc,latitude,longitude, acos(SIN( PI()* @LatDec /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* @LatDec /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* @LonDec /180)
)* 3963.191 AS distance
FROM GpsPoints
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* @LatDec /180)*SIN(PI() * latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
) < = Radius
ORDER BY 3963.191 * ACOS(
(SIN(PI()* @LatDec /180)*SIN(PI()*latitude/180)) +
(COS(PI()* @LatDec /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* @LonDec /180))
)
where 3963.191 is earths radius in miles,
radius is 1.5,
@LatDec is the current latitude and
@LonDec is the current longitude
Uses a lot of Trig and (for me) is quite complicated. This works very well and is very accurate. It also only uses a single table. I want to be able to now look for the current position within a boundary of 3 or more positions from Table 1 and this is where I am struggling?
Also, any suggestions on how I could do this better would be very much appreciated?
I look forward to your reply and help.
Thank you in advanced.
Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????
I want to send mails from SQL Server 2005 Management Studio Express for which I referred to the article at http://www.databasejournal.com/features/mssql/article.php/3626056. I could successfully do the steps from step 1 to step 6 listed at the above URL. In fact, when I sent a test mail (as cited in step 6), SQL Server displays the message Mail queued though the mail didn't get delivered. The mail server address I am using is smtp.mail.yahoo.co.uk.
But when I expand the Management node in the Management Studio Express GUI, I don't find Database Mail as a child node. In fact, there are only 2 child nodes under the Management node - SQL Server Logs & Activity Monitor.
Why isn't Database Mail listed under the Management node? What am I missing here?
Also when I open Surface Area Configuration & then click Surface Area Configuration for Services and Connections, the following error gets generated:
Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. (SQLSAC)
Additional Information:
The type initializer 'System.Management.ManagementPath' threw an exception. (System.Management)
Unable to cast COM object of type 'System.Management.WbemDefPath' to interface type 'System.Management.IWbemPath'. The operation failed because the QueryInterface call on the COM component for the interface with IID '(38C15AF2-736C-477E-9E51-238AF8667DCC)' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (System.Management)
When I click Surface Area Configuration for Features, it generates the error cited above but from the first bulleted list.
How do I overcome this error? I confirmed that Windows Management Instrumentation is running by opening Services from Control Panel ---> Administrative Tools.
I am on WinXP Pro SP2. Please note that I also have SQL Server 7.0 installed in my machine which is a standalone m/c.
Thanks
How to implement distinct storage tiers on SQL Remote BLOB Storage (RBS)?
I want to use this SQL Feature to move files(images, videos, pdf files) from a database to a distinct database dedicated to RBS. Then I want to have several storage tiers, where objects will be saved and moved according access frequency. Old data will be arquived in cheap storage, but it must be always accessible if needed.
Description:
- 1st and main tier: new and frequently accessed objects stored in high performance storage;
- 2nd tier: automatically move older or less accessed objects to an inexpensive and different storage tier;
- in all cases, all objects must be accessible to all users, but accessing to archived objects(2nd tier) will be much slower;
The operation system is windows xp, and before i installed the SQL SERVER2005 the SQL SERVER2000 had installed.
the course of installing the SQL SERVER2005 has no error at all.
but i can not find the Database Engine under MSSQLSERVER from surface area configuration
==============
thank you in advance for any help
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedI have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View Relatedhi,
I have a database whose mdb and ldb files are located in C drive.
I need to change these files to D drive.When i go to the database properties
and try to change the location it does not allow me.It has given me a message(The database physical file cannot be changed once it is created).
I can drop the table and recreate it from a BackUp but is there a better way
to do this.
Thank you,
Whats the limitations of data storage in sqlserver DB. How will be the perforamcne if i have database which will get a data of 400 GB per year and all the data should be there in the table the whole year and then it can be archived.
View 2 Replies View RelatedHi all.
I develop a app on PPC 4.20 using SQLServer 3.1. I developed a o/r mapper whitch auto-creates the Database and Tables using attribute-decorated objects. That all works fine - as long as my Database is in \MyDocuments .
Trying to store the Database in \Storage Card\test.sdf, whitch is absolut neccessary because it will become to big for storing onto device, I get error 25113, "A not supportes operation to host was called" (Translated from German)
When I trie to copy a Database to the SD-Card, inserts are not commited.
Is it not supported to store Databasefiles on a Flashcard?
My development-environment is .net 2.0 sp2, device-emulator v2, Windows XP, vs2005. In the emulator, i have linked a windows-based directory as storagecard.
What am I doing wrong??
class SQLManager
{
private static SQLManager instance = null;
private SqlCeEngine m_cEngine = null;
private SqlCeConnection m_cConn = null;
private SQLManager()
{
m_cEngine = new SqlCeEngine(
"Data Source = \Storage Card\test.sdf");
bool bCreated = false;
if (!File.Exists("\Storage Card\test.sdf"))
{
bCreated = true;
m_cEngine.CreateDatabase();
}
m_cConn = new SqlCeConnection(
"Data Source = \Storage Card\test.sdf");
m_cConn.Open();
if (bCreated)
DoInitialDatabaseSetup();
}
....
}
I have ran into a problem making a database where i have two versions of a book , one in russian and the other in English. The english part worked but when i input the data for Russian it gets replaced each time with question marks. Is there something i have to enable for the SQL database for it to be able to store Russian Text, or is it a DataType i have to set???
THANKS IN ADVANCE
Here are my planning details:
RAID 1:
2 disks RAID 1 for OS and BINS
2 disks RAID 1 for Backups.
RAID 10
4 disks RAID 10 for data
4 disks RAID 10 for log
4 disks RAID 10 for TEMPDB
Should i put the master and other system database on RAID 10 along with TempDB.
Or what else a guru fellow will advise in above setup.
thanks,
I am a Windows developer for the IBM Tivoli Storage Manager Server (TSMS) product.
Our product installation is built with InstallShield and uses the Windows Installer.
On a new installation of Windows 2003 x64 Storage Server R2, at a customer's site, the TSMS product fails to install.
The install of the OS has version 3.01.400.3959 of the Windows Installer and I see no newer version that installs.
Part of our product is 32 bit (console) and another part is x64 (server).
When installing I can see that the install's default is being redirected/reset to C:Program Files (x86)TivoliTSM after it is explicitly set by a custom action to ..Program Files.. . I further observe that our custom actions to write 64 bit registry entries are being refused.
REGSAM samMask = KEY_ALL_ACCESS;
if ( regIsWow64Process () ) samMask = samMask | KEY_WOW64_64KEY;
lStatus = RegCreateKeyEx( hLocalConnectKeyRoot,
szSubkey,
0L,
NULL,
REG_OPTION_NON_VOLATILE,
samMask,
NULL,
hKey,
&dw ) ;
The above fails to create the key.
We have tried four versions of our TSMS spanning many changes but the install acts the same.
This does not happen on any other Windows OS we test on but we do not test on Windows 2003 Storage Server R2 being that it is an OEM product. We did test on Windows server 2003 R2 x64 and do not see this problem.
Do you have any suggestions on how to tackle this problem?
I have full installation traces but can only see that the registry work is being refused. I can't see why.
With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?
For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.
My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?
Thanks in advance for your help!
PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.
I have a SQL Server database. The data from a table is populated in the table and can do a regular display query on a record without issue.
Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.
I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?
The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.
Your help is much needed and appreciated.
Thanks.