Benefit Of Moving Master && TempDB To Diff HD
Jul 20, 2005
I am interested to hear if people think it would be a good idea to move
the Master & TempDB to a different HD.
Here is my DB Server's set up:
1. Processor: (1) AMD XP 2800
2. 1st HD (IDE 0) is the system & boot drive
3. (3) SCSI HD make up a hardware RAID level 0 (striped without
parity)solution - these striped drives are just for my working DBs
4. (1) SCSI HD that's not doing anything.
I want to put the Master & TempDB on the SCSI HD that's not doing
anything. Would that be the best place for it for maximum performance or
should I put in the striped array. I am leaning more towards putting on
the SCSI HD that's not doing anything. What do you all think?
Ed
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
View 1 Replies
ADVERTISEMENT
Apr 16, 2004
I'm having difficulties copying a production DB to a new computer using backup files. The production computer had tempdb on the D: drive, the new computer is much smaller and only has a C: drive. I've successfully restored the Master DB backup but now the database will only start with the (-F) parameter. I know how to Alter the DB to move the tempdb, but I cann't get the DB to start while Tempdb is pointed to the D: drive
View 5 Replies
View Related
Mar 25, 2008
Hello,
My master database (MS SQL 2005) has simple recovery model, however, when performing diff backup of "all databases", I am getting the following error:
Executing the query "BACKUP DATABASE [master] TO DISK = N'X:\Database Backups\diff backups\master\master_backup_200803251235.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'master_backup_20080325123514', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I choose to back up only "All user databases", then it performs just fine. Why I cannot run diff backup of master?
View 5 Replies
View Related
Jan 29, 2008
I have a install of SQL 2005 that is about 5 months old. The tempdb.mdf and master.mdf files have ballooned to a huge size almost filling my hard drive. How do I reset them to a reasonable size?
View 14 Replies
View Related
Mar 20, 2002
Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?
Thanks!
View 2 Replies
View Related
Jun 18, 2002
Hi All,
I need to move the tempdb onto a different drive. Can you give me the sql statement to do so.
Thanks
Paul
View 1 Replies
View Related
Oct 17, 2001
Can someone tell me how to move my tempdb to a different drive?
Thanks,
Dianne
View 4 Replies
View Related
Feb 20, 2002
How do I move the location of the tempdb. I wish to place it on a separated
disk to to increase performance.
Parg
View 3 Replies
View Related
Aug 14, 2002
Hello ,
I want to move my tempdb database to another drive by moving the mdf and ldf files . Is it possible to shift the tempdb once it is created ?
I am shifting the database since there is no space on the current drive and the database might grow in the near furure .
Any ideas ???
Thanks .
View 1 Replies
View Related
Aug 22, 2002
I want to move my TEMPDB from C:MSSQL7DATA to another (less critical) drive in the event it grows too much/fast. I've reviewed the process from MS using ALTER DATABASE, etc., but wanted some feedback from the real world before I commit. Are there any issues I should be aware of? I know it must reside on a local drive on the server. Do I need to make any other adjustments in SQL after the move? Your help is much appreciated. - Rob
View 1 Replies
View Related
Apr 24, 2000
Hello:
We are supporting an mssql 6.5, sp 4 application. My associate recently
increased tempdb from 3 gb to 15 gb by adding a 12gb device on a second drive.
The original tempdb device was 3 gb on an h drive and the 12gb addition was added to the f drive limiting the disk space for the data dat file to be expanded. So now I would like to move that second tempdb device to a different drive to free up disk space on the f drive.
1) Can I move the second tempdb device?
2) Or should I try to shrink it and then expand it on a differetn drive?
3) Any other suggestions?
Any information you can provide will be greatly appreciated.
THanks.
David
View 1 Replies
View Related
Nov 10, 1999
Hi,
My tempdb size on the hard disk at the moment is 1GB.
We have recently put in an additional 1GB of RAM to add to our existing 1GB RAM on the SQL server m/c, making it 2GB now.
SQL server is configured to use 928MB of memory. So, I put the TEMPDB (1GB) on the spare (1GB) memory in RAM.
The problem is, it doesn't allow me this saying not enough memory available.
I have also set the WORK SET SIZE parm to 1, to reserve memory for SQL. Still, it will not allow me. The maximum value it allows me for TEMPDB in RAM is 384MB, beyond which the server doesn't start.
What could be the reason for not getting enough memory, though the 1GB of RAM was put in additionally just for this.
Please advise.
Thanks
Satish
View 1 Replies
View Related
Jul 23, 2005
Right now both of these reside on my C:, but templog is over 3 1/2 GB.1) What do I need to do to move both of these to another drive on thesame system?2) Is there an easy way to purge or compact templog, or just set a sizelimit for it?Thanks,Scott
View 1 Replies
View Related
Nov 3, 2003
When SQLserver2K was installed it placed master, model, msdb, tempdb data files in the installation location (i.e. C:Program Files....). This puts pressure on the C: drive, which also holds the page/swapfile. I want to move at least the tempdb location to the new 'Default data directory' and log directory we set after installation (i.e. E:MSSQLData).
How do I get tempdb to relocate to E: given that it gets recreated each time SQLserver starts?
TIA,
Al
View 1 Replies
View Related
Sep 30, 2014
Is it possible to encrypt Master database and tempdb? On executing below query result is showing temdb is encrypted.
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
View 1 Replies
View Related
Oct 30, 2007
We had an issue last night where tempdb ran out of space on one of our servers. Upon investigation I found that SSMS was reporting only one data file assigned to tempdb. I knew this couldn't be the case and dove into the system tables to figure out what is going on. I found what appears to be data corruption. There are a number of file records that appear in the master.sys.master_database_files that do not appear in the sys.database_files table in tempdb.
I tried to add files that it showed already existed and they of course errored out. I restarted SQL Server to get tempdb rebuilt and that did not work either.
Has anyone else ever ran into this? What was the resolution?
Here are some queries to demonstrate what I am seeing. Terrible formatting I know, but I don't know of a better way.
/*------------------------
select * from master.sys.master_files where database_id = 2
select * from tempdb.sys.database_files
------------------------*/
database_id file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
----------- ----------- ------------------------------------ ---- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
2 1 NULL 0 ROWS 1 tempdev E:MSSQL$S1MSSQL.2MSSQLDATA empdb.mdf 0 ONLINE 524288 1280000 128000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 2 NULL 1 LOG 0 templog E:MSSQL$S1MSSQL.2MSSQLDATA emplog.ldf 0 ONLINE 128000 268435456 64000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 3 01E3A12D-9CC1-44B6-B077-947C45BD5FDC 0 ROWS 1 tempdb_Data_01 K:MSSQL$S1Data empdb empdb_Data_01.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000011100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 4 4E91B5E8-1EE9-4DA1-985F-A37CB7262BC6 0 ROWS 1 tempdb_Data_02 K:MSSQL$S1Data empdb empdb_Data_02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000013400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 5 125B73CC-A6EB-464E-A8B8-50269EFD1311 0 ROWS 1 tempdb_Data_03 K:MSSQL$S1Data empdb empdb_Data_03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000015700001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 6 6C4CDA80-4AF0-42FE-BFCF-4E6AEF97956A 0 ROWS 1 tempdb_Data_04 K:MSSQL$S1Data empdb empdb_Data_04.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 50000000018000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 7 360F5EE6-5207-402E-BFFC-88F2B4532DD2 0 ROWS 1 tempdev_11 S:MSSQLDATATEMP empdev_11.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1347000000109400212 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 8 60F262B1-32AC-433C-95CE-8450BBFA2247 0 ROWS 1 tempdev_12 S:MSSQLDATATEMP empdev_12.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1348000000169600151 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 9 A18FA498-37A6-4A52-ABA6-C915FD50123E 0 ROWS 1 tempdev_13 S:MSSQLDATATEMP empdev_13.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1349000000001600060 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 10 2DC2BCCC-3DD8-45D3-B454-5DB88D0B6783 0 ROWS 1 tempdb_Data_05 K:MSSQL$S1Data empdb empdb_Data_05.ndf 0 ONLINE 524288 1024000 65536 0 0 0 0 0 50000000020400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 11 06A09B39-09C7-41C6-B11A-BEC1785039B0 0 ROWS 1 tempdb_Data_06 K:MSSQL$S1Data empdb empdb_Data_06.ndf 0 ONLINE 524288 1024000 65536 0 0 0 0 0 50000000022700001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 12 14F1019E-B3FE-4B7C-827B-880D3E0C430C 0 ROWS 1 tempdev2 I:MSSQLDATATEMP empdev2.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 103000000037600078 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 13 743D3EC0-8011-4ECC-8CA6-256C65B37FC5 0 ROWS 1 tempdev3 I:MSSQLDATATEMP empdev3.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 103000000043000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 14 968C0C01-95C9-4EA2-97B6-0DEFBBB8FEB5 0 ROWS 1 tempdev4 J:MSSQLDATATEMP empdev4.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 108000000039000001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 15 D4551A7F-3998-41E3-81B5-627168CBE77A 0 ROWS 1 tempdev5 J:MSSQLDATATEMP empdev5.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 108000000041400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 16 701E5614-E61C-46CB-8D23-F8726B6B1F4F 0 ROWS 1 tempdev6 S:MSSQLDATATEMP empdev6.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 114000000013600081 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 17 B4AB50DF-8004-4CBA-B97F-683BD8791FF4 0 ROWS 1 tempdev7 S:MSSQLDATATEMP empdev7.ndf 0 ONLINE 256000 640000 65536 0 0 0 0 0 114000000019200001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 18 C0A50693-6311-4F33-B20B-6C030CC4F65F 0 ROWS 1 tempdev_14 S:MSSQLDATATEMP empdev_14.ndf 0 ONLINE 512000 1280000 128000 0 0 0 0 0 1349000000279900070 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 19 800B86A7-142F-46C9-984F-8673CEFE2DDD 0 ROWS 1 tempdev_01 S:MSSQLDATATEMP empdev_01.ndf 0 ONLINE 512000 -1 128000 0 0 0 0 0 1350000000553600232 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 20 4A008D1B-89E1-4F07-AB5C-CADBFB6ABE44 0 ROWS 1 tempdev01 F:MSSQLDATA empdb empdev01.ndf 0 ONLINE 524288 -1 128 0 0 0 0 0 78000000008100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 21 B6B63467-4179-47FD-A226-58639B92F402 0 ROWS 1 tempdev03 T:MSSQLDATATEMP empdev03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 234000000253600164 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 22 2A075CBA-C600-47D9-B6B1-8231743EC5AF 0 ROWS 1 tempdev04 T:MSSQLDATATEMP empdev04.ndf 0 ONLINE 524288 -1 128 0 0 0 0 0 234000000262600001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 23 EDAFB1FB-3B3D-4F7E-B335-8056F2FA9EAD 0 ROWS 1 tempdev1 F:MSSQL$S1DATA empdb empdev1.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 453000006813300252 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 27 1BFE36F9-B621-4B7C-99C8-0C262ADFFEF9 0 ROWS 1 tempdev02 F:MSSQLDATA empdb empdev02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 430000003795400001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 29 7C8D9FDF-E2D9-4274-BC3D-B2EC060C4B39 0 ROWS 1 tempdev_02 S:MSSQLLOGTEMPDB empdev_02.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4030000039691100043 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 31 60973FF9-A35F-433C-9E58-D29F00E4D5A7 0 ROWS 1 tempdev_03 S:MSSQLLOGTEMPDB empdev_03.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048392400207 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 32 506E7E19-4D0A-47D8-A687-C9658500F367 0 ROWS 1 tempdev_04 S:MSSQLDATATEMP empdev_04.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048449300130 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 33 4DFD4A82-FD5C-4CEE-8939-C01C596B3714 0 ROWS 1 tempdev_05 S:MSSQLDATATEMP empdev_05.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048515100001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 34 B6A34F7D-7FBD-460F-B6FF-ACED575FE2D4 0 ROWS 1 tempdev _06 S:MSSQLDATATEMP empdev _06.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000048659000118 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 35 4B33C871-B52E-4514-8EBB-E856F151A96D 0 ROWS 1 tempdev_07 S:MSSQLDATATEMP empdev_07.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000049131800087 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 36 40575107-6C34-408A-BF04-DDF7D71935BE 0 ROWS 1 tempdev_08 S:MSSQLDATATEMP empdev_08.ndf 0 ONLINE 524288 -1 0 0 0 0 0 0 4033000049137600001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 37 C1DCC4CF-740A-4EB6-AF2E-7B105477251F 0 ROWS 1 tempdev_09 S:MSSQLDATATEMP empdev_09.ndf 0 ONLINE 524288 8192000 524288 0 0 0 0 0 4035000024325400202 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(32 row(s) affected)
file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
----------- ------------------------------------ ---- ------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
1 NULL 0 ROWS 1 tempdev E:MSSQL$S1MSSQL.2MSSQLDATA empdb.mdf 0 ONLINE 524288 1280000 128000 0 0 0 0 0 NULL NULL NULL NULL 95000000084000037 11815719-7EBA-453D-B93C-1CA083FADA76 2007-10-30 10:00:02.263 NULL NULL NULL NULL NULL
2 NULL 1 LOG 0 templog E:MSSQL$S1MSSQL.2MSSQLDATA emplog.ldf 0 ONLINE 128000 268435456 64000 0 0 0 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
(2 row(s) affected)
View 7 Replies
View Related
Mar 13, 2001
I have a situation where I would like to move the physical location of the Model, tempdb and MSDB database files. Does anyone know of a way to do this?
View 2 Replies
View Related
Jul 16, 2001
Is it possible to move (detach and attach) the physical data and log file (.mdf &.ldf) to another location?
View 1 Replies
View Related
Feb 27, 2006
Hello all ...
is there a standard procedure or document that explains how to:
Expand tempdb onto a faster drive ... making it larger
then ...
Remove the small portion of tempdb from my c: drive to reduce contention?
Thanks!
Doug
View 3 Replies
View Related
May 7, 2008
hi folks
I€™ve had a SQL 2000 server restored (using Veritas 9.1) to another server also running SQL 2000. I now have a collation problem.
When trying to add users to a database, I receive a "Error 446: Cannot resolve collation conflict for equal to operation" message.
I noted that tempdb and user tables collation don€™t match - so I was going to change the user tables. However, the master collation doesnt match tempdb, model or msdb.
Server is set to SQL_Latin1_General_CP1_CI_AS
Master - SQL_Latin1_General_CP1_CI_AS
tempdb, model, msdb - Latin1_General_CI_AS
I was under the impression that when tempdb was recreated it inherited the master database's collation? This doesn€™t seem to be happening.
any ideas?
much appreciated
N.
View 7 Replies
View Related
Jul 20, 2006
Hi Guys,
Since,You cannot mirror the master, msdb, tempdb, or model databases.What will happen if i create new login,change existing security profile and new jobs,change of existing job on princicpal db. how these will be mirrored to other server and in case failover, how it will treat.
Thaks
View 1 Replies
View Related
Nov 29, 2000
does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again
View 1 Replies
View Related
Oct 23, 2015
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. Â I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.Â
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed. Â
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Â
Also, what are peoples thoughts on percentage growth for tempDb? Â I've read that it's not recommend and yet it seems to be the norm.
View 4 Replies
View Related
May 25, 2001
Hi All,
Here's my situation: I need to move several application db's to a new server. The current server is SQL 7.0 SP2, the new server will be SQL 7.0 SP3.
In order to avoid having the problems of mismatched logins and db user definitions, I'd like to restore the current master over the master that's created at install on the new server.
I was doing some testing of this by trying to restore the 7.0 SP2 master onto a SQL 7.0 DE edition and had some problems. (Obviously, this is not the exact scenario that I need to take place -- I want to restore standard edition to standard edition.)
Is restoring the master db from different service packs under 7.0 going to pose a problem? Is restoring the master db the best way to get around the logins issue when moving db's?
Thanks in advance for all replies.
View 1 Replies
View Related
Mar 6, 2006
Does anyone know how to move master db to a differetn drive instead of default c: drive in a named instance environment?
Thanks.
Rick
View 10 Replies
View Related
Dec 14, 2007
Hi ,
I am trying to move the system database master database and mssqlresource database on SQL Server 2005.
I am going according to the doc's . And I am logging in as single user mode : NET START MSSQLSERVER /f /T3608 and when I try to login into sqlcmd or Management studio , I get the error:
SQL Error 18461 , can not login in more than one administrator server in single user mode.
For the managment studio , I close the object explorer as it is taken as one connection and click the new query with existing connection. But it throws me the same error. When I try to enter into sqlcmd , it throws me the same error.
What can I do here.. IS there anything I am missing.
Thanks
.
View 11 Replies
View Related
Sep 11, 2014
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
View 4 Replies
View Related
Mar 18, 2008
How to move master , model and msdb database to some other location. Can we overwrite the master, model and msdb files
View 8 Replies
View Related
Feb 4, 2008
I'm attempting to move my database from one server to the other using attach/detach method. I'm able to reattach everything just fine on the new server but I get the following error when trying to receive messages.
"Service Broker login attempt failed with error. Connection handshake failed. Error 15581 occurred while initializing the private key corresponding to the certificate....."
" Please create a master key in the database or open the master key in the session...."
I have a master key in the master database so I attempted the following:
open master key decryption by password = 'pwd'
alter master key add encryption by service master key
and I receive the following error on the alter master key command:
"An error occurred during decryption"
Any suggestions on how to recover the database to use the same database key? Do I need to regenerate the key?
Thanks for your assistance.
View 6 Replies
View Related
Feb 12, 1999
I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
View 2 Replies
View Related
Apr 11, 2007
Hi !
We're designing our data model, and have found that we have two groups of tables (about 10 tables in each group). The tables within each group are dependent, but the two groups are independent of eachother.
Now, our two choices are:
1. Put all tables into one database.
2. Put the two groups into two separate databases.
For simplicity, option 1 is the winner. However, my question is, will there be noticeable peformance benefits by using two databases? (In which case, option 2 will be the winner).
Thanks,
Martin
View 1 Replies
View Related
Jul 29, 2005
Just curious. The exec plan is the same for both qry's, and they both show the same estimated row counts @ the point of question in the exec plan. The exec times are roughly the same, any variances I'm attributing to db load from other things going on, since any benefits of one over the other are not consistent from execution to execution. So is there any benefit to filtering in the join conditions vs. the where clause? My thinking was that by filtering earlier in the qry (when joining) as opposed to "waiting" to do it in the where clause, the rest of the qry after the join would inherently be dealing w/a smaller result set for the rest of it's execution, thus improving performance. After the exec plan checking I did, I guess I was wrong. Seems that Sql Server is intelligent about such filtering when analyzing the entire qry, and building its execution accordingly. The execution plan for both qry's showed the same where clause argument for the tables being joined.
Filtering in where clause....
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
wherepc.removed = 0
andp.cancelled = 0
andp.deleted = 0
ands.closed = 0
ands.deleted = 0
andyear(getDate()) between year(P.startDate) and year(P.expDate)
Filtering in joins...
Code:
select...
FromtProject p with (noLock)
jointProjectCall pc with (noLock) on P.ID = pc.project_id
and pc.removed = 0
and p.cancelled = 0
and p.deleted = 0
and year(getDate()) between year(P.startDate) and year(P.expDate)
jointStore S with (noLock) on pc.store_id = s.id
jointZip Z with (noLock) on Z.zip5 = s.zip5
and s.closed = 0
and s.deleted = 0
jointManager M on M.ID = case ... end
leftjoin
(
selectprojectCall_RecNum as RecNum, sum(answer) as HoursUsed
fromtCall C
whereAnswer > 0 and question_id in (1, 2)
group by projectCall_Recnum
) as C on pc.recnum = c.recnum
View 1 Replies
View Related
