I need to remove log segment of one database from current position to another drive. The current log segment is used by two databases. This was created by mistake. Now we need to fix the problem and create another log segment for the other database. If we keep the log setment as is, we will have problems deleting one of the databases in the future.
Thanks for your help,
Ana.
I want to duplicate the environment of another existing server where tempdb
resides on it's own device (200mb in size). I can create the devices (data & log). What do
I have to do to physically move tempdb to the new devices I create? I've seen such
articles on Swynk.com, but today the links are not working. Thanks very much!
Mark Blackburn o `"._ _ M onterey
mark@mbari.org o / _ |||;._/ ) B ay
Science at its Best! ~ _/@ @ /// ( ~ A quarium
(831) 775-1880 ( (`__, ,`| R esearch
http://www.mbari.org/ '.\_/ |\_.' I nstitute
Database Administrator
MBARI Personal Web Page: http://www.mbari.org/~mark/
Is it possible to relocate the master database device to another drive?
View 3 Replies View RelatedI have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
I have a Device of 6GB and a Transaction logof device of 1.5Gb , the database of the associated device had gone suspect.so i dropped the
database , now i a m trying to delete the device it says cannot delete as its in use by some database , i have checked all the
Dbs butits not associted with anything what shold i do????
Regards
I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
Thanks
Scott
I am using SQL Server 2012 Standard Edition. I have a requirement to instantaneously move data from 3 tables that are dependent on each other in a database to another database to same tables with same structure and dependencies.
I can set up replication to manage this. However the data once moved over has to be deleted from the source database immediately after the move. Hence replication is ruled out. Also data is continuously being insert into source database into those 3 tables.
I want to create a sql agent job that handles the data move and delete process and shcedule it to run once every minute. What is a best strategy to handle this without causing deadlocks in the source database?Below is the ddl and all objects in source database match the destination database the only difference is destination has 100 tables and source has only 3 tables which are shown below
CREATE TABLE [dbo].[StackPosition](
[StackPositionId] [uniqueidentifier] NOT NULL,
[AccountTriggerId] [uniqueidentifier] NOT NULL,
[StackPositionStatusId] [int] NOT NULL,
[QueuedAt] [datetime] NOT NULL,
[LastUpdatedAt] [datetime] NULL,
[Code] ....
I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1
May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS
Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2
ABCDE123 SER1
XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
Thank you.
I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.
find ddl below:
GO
CREATE TABLE [dbo].[GetPerson](
[GetPersonId] [uniqueidentifier] NOT NULL,
[LinedActivityPersonId] [uniqueidentifier] NOT NULL,
[CTName] [nvarchar](100) NULL,
[SNum] [nvarchar](50) NULL,
[PHPrimary] [nvarchar](50) NULL,
[code]....
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies View RelatedI dump my databases to backup devices each night.
However would like to purge old backups - say older than a week - from the device.
Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?
I have seen the RETAINDAYS claus but it applies to tape backups only?
Thanks
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
I need a stored procedure that can drop and recreate any table in a DB.
How could I find out all information pertinent to a table and able to generate the create statement dynamically in the preocedure then drop it and recreate the table.
Thank you and please help.
I want to drop table and then recreate. It's referenced by many table and I dont want to drop all constraints referencing to it. Is there any feature like "switch off/on constraints" in MSSQL?(6.5)
View 1 Replies View RelatedDoes anyone know how to recreate a database from a log file? Here is the situation:
I had a server go bad. The drive configuration was as follows
Server OS on separate RAID controller - RAID Level 1
SQL Data on separate RAID controller - RAID Level 5
Transaction Logs on Separate Raid Controller - RAID Level 1
I lost the RAID 5 array and can't get it back and don't have a backup. I need to know if/how I can recreate the database from the log file. The log file has been set to grow and has never been purged so I am hoping that I can recreate the database from scratch. Is this possible?
Dear All,
after searching continuously for removing the suspect mode of MSDB database, i've found a great article in net.but i dont understand how to proceed. please explain me the steps....
1.Detach the damaged msdb. You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. I did this by shutting down the server, navigating to the directory 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn' and doing the following: start sqlservr.exe -c -T3608
2.Move or rename the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData' directory)
3.Run the instmsdb.sql script in the 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall' directory
4.Shutdown and restart the server without the 3608 trace flag
Vinod
Even you learn 1%, Learn it with 100% confidence.
I want to create a clean copy of my DB now that it is done. So it can be moved to another Server. It has some sample data in some tables that I would like to keep and some in other table that I don't. How can I do this?
View 1 Replies View RelatedHi!
I have lost the repl_publisher login.
Is it possible to recreate it in any way?
What about the password?
Thanks
Håkan
I need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.
Due to a server move of our SQL databases something 'strange' happend: one of the login accounts disappeared.
When I try to recreate it, SQL gives me back an error stating the account already exists. Read something about loginaccounts becoming orphaned but don't know how to solve it. The login account is a Windows (AD) account and not a SQL account (both requiring different methods of fixing?). How can I recreate just this single account?
Any help would be greatly appreciated!!!
P.
Hello
I am trying to recreate a MySQL database on a new domain. The original database was exported into about 40 .SQL text files. I am importing these .SQL text files into a new database using phpmyadmin. When I attempt to import the first of these files, I get a "fatal error". It says the upload time has exceeded the 300 second limit. How do I set up the server so there is no upload time limit? Or how would you approach a solution to this problem? There are about 40 of these text files and each file varies in size between 5 and 15mb. Is it possible to split individual files in half or 3 parts and import that way? Any help would be greatly appreciated. Thanks!!
Can anyone help with how to recreate a pull subscription replication job after I had to recreate the msdb database when it was marked as suspect. I still have the subscription but cannot seem to recreate a pull replication job
View 1 Replies View RelatedHi!I need to recreate the system tables (sysobjects, syscolumns,sysindexes, sysforeignkeys, sysconstraints, sysreferences, sysindexkeysat least) in another SQL server.You may say "Thats easy! Backup and restore the database!" and I wouldanswer "I can't, the database size is above 50GB and I just can't do itevery time I need to recreate the info".So, ¿Do you know any simple way to do these?I don't need the data on the user tables and I'll log-in always as dbo(using trusted connection), perhaps that helps.Perhaps the simplest way is to do a bcp to backup and restore the databut.... you never know what would happen. This is why I write ask youguy, probably you know much more than me.Thanks in advance
View 9 Replies View RelatedI have a slow query. It is indexed and I was wondering if there were any issues with using 'dbcc dbreindex' on each of the 12 replicas, to maybe make it run more efficiently? And is this something I could/should do nightly?
View 1 Replies View Relatedi have a used database publishing wizard to create a sql fiel of a databse i wish to transfer to server running sql server management studio express on a server 2005 ,
what do i need to do to recreate my databse from the sql file.
many thanks
A customer on SQL7 has a 40GB tempdb (which is usually 2-5GB) which has filled the drive. ShrinkDb/ShrinkFile do nothing as the db has only 2GB of free space. How can I truncate the data or blitz tempdb and create a new (2-5GB) one (Stopping the server, deleteing the .mdf, starting the server creates a new tempdb with the same size as the old one!)
View 2 Replies View RelatedHi!
What is the easiest way to recreate jobs running on one server on another server?
Thank you
Praveena
I have come across a user that has error 2525 errors on sysobjects tables throughout their SQL 6.5 system. The ids match and I can identify the index. But as they are system tables I cannot drop or rebuild the indexes.
Any ideas please?
The errors have been occuring for some time, so it is not an option to restore. I would like to avoid having to transfer data to and from another SQL Server.
Many Thanks
I am looking for a script to recreate scheduled jobs on new SQL Servers that I build. I am hoping to find one that will pull information from the system tables for the name of the server and the names of the databases so that it does not have to be extensively modified for each new server.
View 4 Replies View RelatedI have different versions of essentially the same database on two different computers. From Computer B I need to copy one table AND its data to Computer A.
I've read several articles and have tried several things but can't figure out how to do it. Incidentally, I'm running SQL Server Express 2005 on both machines.
Robert Werner
Vancouver, BC
I'm having a heck of a time trying to upload data to an excel spreadsheet. This works perfectly in sql 2000 but I've been having problems with 2005
SSIS package "Package1.dtsx" starting.
Error: 0xC002F210 at Drop table(s) SQL Task, Execute SQL Task: Executing the query "drop table `GRE`
" failed with the following error: "Table 'xxx' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop table(s) SQL Task
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the query "CREATE TABLE `xxx` (
`TEST_REC_NBR` Decimal(29,0),
`PROCESS_DT_GRE` LongText
)
" failed with the following error: "Invalid precision for decimal data type.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "Package1.dtsx" finished: Failure.
hi,
how can i drop views and recreate again?
thinks ...