DB Design :: Dropping And Rebuilding A Database
Jun 26, 2015
With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:
A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)?
B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space?
C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?
View 16 Replies
ADVERTISEMENT
Jul 23, 2015
Currently we are facing some performance issue while accessing the archive data from the archive tables. the archive table is hugh and it contains around 100,000,000 records and this archive table is being used in few reports and in our commission cycles too. since we are facing performance issues we are rebuilding index once in a week on all the indexes on this archive table.
We have 1 clustered index and 5 non clustered indexes, every time when we rebuild all these indexes on this table it is taking more time, more often rebuilding the clustered index itself is taking approx. 1hr which is consuming more time. wanted to know is there any useful to rebuild clustered indexes or not, if yes then what would be the better way. if not then do we need to rebuild only non clustered indexes.
View 7 Replies
View Related
Sep 4, 2007
Hey all,
I'm a newbie to SQL, the organization i work for recently had a server experience a multiple hard-drive failure, corrupted the OS, etc etc. We called in a data recovery specialist who used an Ontrack tool to pull the SQL files we could off of the dead server and onto a different server. Question being how do i attempt a restore from disk, and also the Ontrack recovery software created a directory named "LOSTFILE" which contains multiple directories which contain a variety of different filetypes. Has anyone experienced a similar situation? Any ideas how i can get this resolved?
Ultimately i'd like to setup a new server and transfer the recovered files to it.
Any ideas or help is greatly appreciated,
Thanks!
View 7 Replies
View Related
Aug 23, 2007
I have a copy of SQL Server Express on a clients computer.
When i try & restart the services it fails with the following error:-
Error: 9003, Severity: 20, State1.
It looks like the master database has become corrupt & needs rebuilding.
I have found a utility rebuildm.exe that repairs the master db but unfortunately it is not supported in sql 2005.
Does anyone have any other suggestions on how to repair the master db.
Thanks.
View 7 Replies
View Related
Jul 7, 2015
I have come across a database system which isn't designed to work optimally. It is fairly large (~400GB) and performance of loading and querying is degrading (improper data types, fragmented indexes, non unique clustering key and other problems). So, I have quite a task in front of me, but I am up for the challenge. I figure this is not a unique situation, many of us would have come across this before. I have done this before too, but only for smaller databases, some of the operations here I expect to take a couple of hours or more to complete (depending on load/infrastructure speed etc, I know).
My plan is thus:
+ Take a full backup of the database
+ Set the recovery model of the DB to simple
+ Drop non clustered indexes
+ Drop clustered indexes
+ Remove PKs (wrong data types, too large!)
+ Narrow data types (add new column, update column in batches to old value, rename new column to old column)
+ Add PKs, which will create clustered indexes automatically based on PK ID
+ Create non clustered indexes
+ Run a SHRINKDB (normal operations I would never do this, but this is a special case, ensure log file is truncated to a logical size especially after all those table modifications...)
+ Set the recovery model of the DB to Full
+ Ensure everything works OK or better
View 9 Replies
View Related
Dec 21, 1998
Hi,
After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.
What should I do? Thanks.
View 1 Replies
View Related
Sep 7, 2007
Hi friends,
How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code
Hoping a early reply ....
Thanks :)
View 5 Replies
View Related
Jan 16, 2008
I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:
System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.
The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...
Setup: SQL 2005 SP2 2 Node cluster.
Thanks.
View 3 Replies
View Related
May 2, 2000
i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
thanks
View 1 Replies
View Related
Dec 23, 1999
Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?
View 3 Replies
View Related
Apr 26, 2006
Hi,
i have a problem dropping a database?
I have a WinForm and i can create DB, create the Tables and drop the DB via buttons.
When i start myProgram it's no problem to drop the Database.
Then i can create the database without problems.
The tables also without problems.
Now if i try to drop my whole DB, i get the error message:
SqlException Handle :System.Data.SqlClient.SqlException: Cannot drop database "XtmsDb" because it is currently in use.
The code for dropping db:
public void deleteProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = "Drop database XTmsDB";
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database dropped!!!");
}
catch (SqlException dropEx)
{
System.Console.WriteLine("SqlException Handle :{0}", dropEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
The code for creating db:
public void createProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = projectDBStrings.getDBCreateString();
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database created!!!");
}
catch (Exception)
{
System.Console.WriteLine("Could not establish Connection!");
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
The code for creating Tables:
public void createProjectTables()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=XtmsDb;Integrated Security=SSPI;";
ArrayList createList = projectDBStrings.fillProjectSchema();
SqlTransaction tx;
SqlCommand cmd = new SqlCommand("", conn);
IEnumerator createListEnum = createList.GetEnumerator();
try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
try
{
while (createListEnum.MoveNext())
{
sqlStr = (String)createListEnum.Current.ToString();
tx = conn.BeginTransaction();
cmd.CommandText = sqlStr;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
tx.Commit();
}
System.Console.WriteLine("Schema created!!!");
}
catch (SqlException deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
}
catch (SqlException connectionEx)
{
System.Console.WriteLine("SqlConnection Handle : {0}", connectionEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}
Who can help me?
View 14 Replies
View Related
Aug 3, 2000
Hello all,
I need to restore my database and I keep getting a database in use error. Can someone tell me the easiest way to drop all connections to a database and open it exclusively?
Also, I'm not sure where this connection is coming from, there is another database on the same machine that has user connected, can these connections be the problem?
Thanks
Kevin Kraus
View 3 Replies
View Related
Nov 2, 1999
I am trying to delete a database that currently subscribes for replication. The problem however, is that the publishing server no longer exists and I can't disable the subscription therefore I can't drop the database. When I try to disable the replication the server tries to communicate with the old server. Is there anyway to force the dropping of the database or to force the server to disable replication.
thanks in advance
Michael
View 2 Replies
View Related
Oct 2, 2015
I have managed to successfully connect to another SQL Server instance using a linked server.
I can then do my select statement like this: select * from [servernameinstancename].[databasename].[databaseowner].[tablename]
I just wanted to know how I can drop the database on the other instance?I have tried doing: DROP DATABASE [servernameinstancename].[databasename].
View 3 Replies
View Related
May 28, 2008
Does anyone have a script that will iterate through a database and drop all objects in order of dependencies? I need a way recreate all objects in database through SQL script in a development environment without having to recreate the actual database. Therefore I need a way to clear out the current database objects first without running into a problem with dependencies. This needs to be dynamic so that if a object is added, it is automatically included in the drop scripts.
View 5 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
Jan 9, 2008
Why in the hell doesn't SQL Server provide a facility for dropping anactive connection on an attached database in SQL Server Managementconsole? I can't detach an attached database because apparently thereis an active connection.I know you can use SSEUTIL but it seems like kluge for a poorlythought out function.Crazy
View 2 Replies
View Related
Jul 20, 2005
Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom
View 6 Replies
View Related
Oct 15, 2015
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml  in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
View 9 Replies
View Related
Feb 13, 2002
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View 2 Replies
View Related
Oct 29, 2015
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jul 23, 2005
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies
View Related
Aug 16, 2007
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies
View Related
Jan 14, 2002
Hi!
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.
Thanks!
View 1 Replies
View Related
Aug 15, 2001
Hi,
how long a rebuildm.exe takes?
TIA
View 1 Replies
View Related
Feb 13, 2001
Hi!
This is first time I'm using this forum.
I was using SQL Server forum on deja.com before.
Anyway here's the question I have.
We are planning to rebuild the server by reinstalling OS,SQL Server 7.0 and user application.
My question is what's the strategy for restoring sql databases?
Right now all data files plus backups located on this box.
Please advice.
Thank you
Lena
View 4 Replies
View Related
Sep 20, 2000
Do anyone know how to rebuild indexes on the maintenance plan??
I would like to automate rebuilding my indexes on the database about once every month.
I know you can manually do this by using DBCC DBREINDEX. This is to long and tedious.
Thanks in advance!
View 3 Replies
View Related
Dec 2, 1999
All,
I have scheduled dbreindex command to run on an clustered indexed on a very large table over night, so far this as failed to complete successfully. I think it may have something to do with the amount of space available in the device; the table is approx 238mb in size and the available disk space left in the device is 200 mb.
Does anyone know how much space is required to reindex a table/index this size and if it is required on the same device as the table or in tempdb.
Or even better, if anyone can explain the inter-workings of how the dbreindex command reorgs the table.
Thanks Mathew
View 1 Replies
View Related
Feb 23, 2004
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now that’s a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesn’t lock tables. I know DBCC INDEXDEFRAG doesn’t lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks all
View 3 Replies
View Related
Jan 14, 2002
(Oops, sorry I posted this on the SQL 7 discussion earlier).
----------------------
I was wondering what kind of locks (if any) SQL Server 2000 holds on tables while rebuilding clustered and non-clustered indexes.
Thanks!
View 1 Replies
View Related
Nov 26, 2004
Hi,
Maybe a silly question, but... Are DBCC DBREINDEX and sqlmaint -RebldIdx doing exactly the same thing ? I mean, I know they are both rebuilding index, with optionnally precising a new fillfactor, but are they processing this operation by the same way ? If not, which is best ?
Thanks
View 2 Replies
View Related
Feb 23, 2004
Hello all,
I need to diagnose a problem, this Sunday a regular Database Maintenance plan which is supposed to rebuild indexes took exactly 6 hours and 32 minutes. Now that’s a hell lot of time and during all that process users were denied access to those tables. This is a production server. I want to know what caused that plan to run for so long and how can I avoid this to happen again plus if it ever happens again how can I make sure that atleast it doesn’t lock tables. I know DBCC INDEXDEFRAG doesn’t lock tables but how can I make Database Maintenance plan to run DBCC INDEXDEFRAG instead of DBCC DBREINDEX but more importantly why it took 6 hours.
Thanks all
View 2 Replies
View Related
