Sql Server 2000 Table Maintenance
Jul 23, 2005
sql server 2000
I am currently maintaining a table that contains 30 Million+ records,
30 columns, and 11 indexes and will double within the next six mouths.
The application that accesses this table, mainly for read only
purposes, runs without any problems. We have begun using Crystal
reports and are now having problems. When we create reports that
accesses the large table our server has significant performance dip.
The application begins to time out and the reports take a very long
time, even with simple selects on indexed field.
I have began looking into partitioning the large table on its key field
and creating a partition view. But from what I have read this will
only help if we key on the partitioned field. And all other searches
will actually take a little longer.
Archiving old data is not an option. All the data is being used
Any suggestions will be appreciated. Thanks in advance.
Rick
View 1 Replies
ADVERTISEMENT
Jan 1, 2007
Hi Dear Friends
I have the sql server2000 that I have installed it on the windows server 2000
My server has 4 GB of RAM but i now can use only 2 GB of RAM.
How can i adjust the server for using total of RAM.
Best Regards Ahmadreza.
View 2 Replies
View Related
Dec 14, 2006
Can someone please explain what the tempdb database is responsible for in SQL Server 2000. The database and log file has grown extremely large and I cannot backup this table for some reason. Thanks.
View 1 Replies
View Related
May 4, 2006
Could someone here clarify the difference between replication and mirroring?
What I want to do is to be able to make a back-up of my database to a different computer, what are the things I need to do? Is it replication or mirroring?
TIA
View 1 Replies
View Related
Jul 2, 2014
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
View 5 Replies
View Related
May 6, 2001
Hello,
I am just getting started with SQL 2000 Server, and we have our database online, and starting transfer all the data from ACCESS to SQL 2000.
Need to know what type of maintenance I need to do to keep the data clean on SQL.
Any help would be appreciated.
View 2 Replies
View Related
May 4, 2007
Calling all those that use Maintenance Plans.
There are some perculiar goings on happening on my servers. Plans across servers have been doing funny things like dumping back ups in different folders, some jobs have been hanging, so on and so forth.
And I think it's occuring after modifying SQL Server 2000 plans with SSMS. It's so intermitent, it's hard to put my finger on exactly when it's happening. It may have not even been reported yet. Fixing the anomolies are achieved by recreating the jobs (a matter of unticking and ticking the boxes in the Maintenance Plans).
In SQL 2000, if you create more than 1 schedule on a job that was itself created using a maintenance plan, SQL Server returns a message along the lines of "Dude, do this and weird things might happen". The message doesn't say what exactly, just that it can no longer guarentee the integrity of the plan. And indeed, weird things does happen, I tested it once.
I'm guessing this is a similar problem, but has not yet been pinned down. Has anyone come across this when using SSMS to maintain 2000 plans.
At this stage, I would like to point out that this is not a slagging off Maintenance Plans thread... I'm looking in the direction of anyone who's name starts with the letter Kristen or Tara
Drew
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."
View 1 Replies
View Related
Jul 20, 2005
I want to know if there is a "best-practice" for setting up DatabaseMaintenance Plans in SQL Server 7 or 2000. To be more specific, I wantto know the order in which I complete the tasks. Do I completeoptimization first, then integrity checks, then translog backup, thenfull backup??? OR is there a better order which should be used?Should I ALWAYS backup the transaction Log before I complete a fulldatabase backup, and if so, why??If someone can help, it would be great.....
View 2 Replies
View Related
Jun 24, 2007
I was once told that I was not to use the GUI to setup a maintenance plan. What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap.
Has anyone ever heard of this or is this an old wise tale told by only one person?
I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!
View 1 Replies
View Related
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Mar 9, 2006
Hi,
I'm working with SQL 2000 and am just learning about Maintenance Plans (MP). They seem convenient, but after some time, I'm wondering if they're the best approach long-term. Here are my experiences.
Using the MP Wizard, I created a plan with tasks from all the dialogs:
- Optimize database
- Check integrity
- Backup database
- Backup transaction log
- Write a report
I was puzzled to find 4 jobs were created, each with just 1 step, and staggered starting times. I expected to find 1 job with 4 steps. So, brimming with confidence, I did just that. I combined all 4 into 1 job, deleted the 3 other MP created jobs, and checked for any job-specific details in the code. However now when I open the MP, I get this pop-up:
"One or more of the jobs created for this plan has had additional steps added to it. It is not recommended that jobs created by the maintenance plan be modified in any way."
Okay, fair warning. Yet it appears the job and all steps run successfully, both on demand, and on a schedule. So now I'm wondering if jobs always need a MP. If I don't mind working with xp_sqlmaint syntax, it appears the only thing I'm giving up is the MP history. But I expect job history and '-WriteHistory' will minimize that loss.
I searched BOL, this Forum, and Google, and found a couple articles. One author preferred the ease of the Wizard, another preferred the control and added features of T-SQL, but both created a MP in their examples. So I'm hoping some experienced DBAs can advise.
If I create a job with multiple steps, and no MP, are there important things I give up or problems I create?
Is this approach a bad idea in SQL 2005?
At this stage, I don't need replication or other advanced features. Just simple database maintenance.
Thank you,
- Martin
View 1 Replies
View Related
Nov 15, 2007
Hi All
I€™m having a problem setting up maintenance plans on one of our SQL 2005 servers but first here€™s a bit of back ground.
The server is a Virtual machine running win2k3R2 SP2 server std and SQL 2k5, A supplier arrived to install and app on the server when discovering there app didn€™t work with 2k5 they seem to have set the 2 dbs on the server into 2k compatibility mode and set the entire SQL install to think its a 2000 install. (I€™m not sure how this is done all I can see is the wrong version number in management studio) The problem this is giving me it that the folder in management studio for Database Maintenance Plans is no longer under the management folder instead it under a sub folder called Legacy (which is how management studio seems to deal with all registered 2000 instances) and as such a can no longer right click and create new database maintenance plans is there any way around this or will I have to setup my backup job manually?
Ohh and have the supplier shot at dawn
Thanks All
Jon
View 6 Replies
View Related
Sep 21, 2007
We have some to-disk backups scheduled on our 2000 Enterprise machine - nightly fulls, hourly logs - that go to a network share located on another machine. They were originally stored directly on the same machine as SQL Server, but we changed them to a remote destination within the past few weeks. This works okay, but despite having the maintenance plan set to remove files older than 2 days, old files don't seem to be removed. Understandably, this gets to be a problem when the backup disk becomes filled.
Is there any obvious reason why this option wouldn't work against a network share? I've checked the directory permissions, and the SQL Agent domain account should have no trouble deleting the files.
View 1 Replies
View Related
Mar 29, 2004
Hello,
I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?
The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.
Any suggestions?????
View 1 Replies
View Related
Nov 28, 2000
I am running an overnight job for table Maintenance - this job execs a SP. It has been working okay for a while and is suddenly failing (last two weeks) with Error 515 cannot insert nulls into column. I realise that it is possible that a change has been made but cannot for the life of me ascertain what it is. I have checked server config, database properties and user options and it all appears to be the same as a server that is running this job okay. Please help!!
View 1 Replies
View Related
Jul 8, 2007
I have been asked to look at some performance isssues with an application that utilises a 800GB table. This table is huge and contains 4 int columns and 1 decimal column. The table has a clustered index that covers 4 of the int columns and is heavily fragmented and it has not been maintained for a long time. The system has limited free space to even attempt rebuilding the index. Does anyone have any experience of running a the Alter Index Reorganize command on such a large table? Any information on what storage would be required to attempt this, how long would this take?
View 4 Replies
View Related
Jun 4, 2008
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
View 2 Replies
View Related
Mar 5, 2008
Hi all!
I want to perform a DB cleanup automatically using Maintenance Plan
Basically I want to DROP all TABLES that starts with this : 'utt_'
Help please.
:beer:
View 2 Replies
View Related
Aug 17, 2007
Hello,
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
Thanks.
View 1 Replies
View Related
May 8, 2006
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
View 5 Replies
View Related
Nov 14, 2001
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
View 2 Replies
View Related
Nov 14, 2001
How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?
I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.
View 2 Replies
View Related
Aug 4, 2006
Hi,
I am DB programmer, How can i get Table Constraints to XML in SQL-Server 2000 with out data.
View 1 Replies
View Related
Jan 13, 2005
I want to make an exact copy of a table in SQL Server 2000.
If I right click on the table I can select copy, but paste does not show up?
View 7 Replies
View Related
Mar 8, 2004
Hello friends,
Despiste what it is mentioned in B.O.L., in the Table SYSPROCESSES, the column KPID has almost always the value 0 !!!
If anyone knows how I can find the right link between a SPID and the corresponding KPID, I shoud be very happy to know to.
It concerns SQL SERVER 2000 under WINDOWS 2000 SP3.
Thanks in advance for any help about that ...
View 1 Replies
View Related
Jul 20, 2005
Is there any way to set up a User in SQL Server so they DO NOT have toqualify a table name with the owner name in a Select statement?
View 3 Replies
View Related
Aug 30, 2006
I've got a situation in whcih I have to import PDF and MP3 files into SQL Server 2000 table.
Thanks,
YL
View 3 Replies
View Related
Jul 28, 2007
Unfortunately a table is Deleted by me from my database.
Now How I can Recover that
I have no data and structure of that table Now.
It Was Very IMP. Table .
So Plz Help.......
View 8 Replies
View Related
Aug 4, 2007
I have created the T1 table which will accept US records and T2 table for any other country.
CREATE TABLE T1(I INT unique ,name varchar(10) CHECK(name = 'US') PRIMARY KEY(I,name))
CREATE TABLE T2(I INT unique ,name varchar(10) ,CHECK(name NOT IN ('US')) PRIMARY KEY(I,name))
Then Created the partitioned View using below script
CREATE VIEW V AS
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
I am able to insert the US records using view
insert into V values (1,'us')
Problem:
I am not able to insert the non US records like UK and JN...
It is thorwing the exception like partition column not find.
eg : insert into V values (1,'uk')
PLease help me to insert the non US records into the View
View 5 Replies
View Related
Jan 4, 2006
Hi
anyone please help!
I have created the database driven web application with asp.net and sql server 2000. now I want keep track three operation(insert, updata and delete) that have been made on tables in a SQL Server 2000 database. what i did is:
1, create a audit table with columns: auditTable, actions, actionUser, actionTime
2, create three trigger(insert, update and delect respectivily) for every table
my problem is that i can not get right user name. I use form authentication and i stored user login information in the database. every time, no matter who is logining to the web application, the action user is always SA. I user user-name() function to get userName(actionUser).
Please anyone can help me to get current login user name, or tell the best way to track operations on a table.
Thanks
jili
View 9 Replies
View Related
Nov 29, 2004
I have looked on google and haven't found a query (as of yet) to perform this function.
Essentially I am using VB.NET with Excel and have a mapping between a worksheet and a table in my database. I wrote an import function to pull the data out of excel and put it into SQL Server but I want to try catching errors before i do that.
What is the SQL query to get column sizes from a table. Meaning in a table I have column1 that is allowed a size of int(5). How do i retrieve that information from a query opposed to just looking at it in SQL Server EM??
any help would be appreciated
thanks
View 5 Replies
View Related
Jan 24, 2005
Hi,
Can anybody give me some information on Table data types of SQL2K.
How and where can it be used. Does it make the queries faster, in case there are many users using ?
Please could anybody give me these details, to make me decide whether I can use it or not ?
Thanx and Regards,
Sushma
View 1 Replies
View Related
Feb 9, 2004
Does any one have any examples of how to declare and use these properly?
MSDN doesn't seem to give any example for a beginner.
View 3 Replies
View Related
