Some of our databases have many transactions (a million or more) a day. I have read that every so often I need to rebuild indexes, update statictics for all tables (however that is done), and shrink the transaction logs.
I'm confused by all this. What are the daily recommended database maintennace steps steps for database "health" and how can they be done?
TIA,
barkingdog.
Hi,
What are current thoughts about who should own a Database?
I see 3 possibilities:
1. The DOMAINAdministrator (person wo starts up the Server at Bootup)
2. 'sa', or
3. a person/user closely tied to the database.
reasons for each?
Thanks for your opinions.
MichaelG
I was wondering what everyones preferred way to install a database in an automated fashion is.. IE:
You have a webapp. It sdriven by SQL Server. You need to prompt the user for a server, username, password, and database. Once you have those, you execute thge scripts against the DB.
I've been using osql.exe. but heres the situation. The installer may be run from a system, which does not have the sql server client tools installed. Which will be a problem.
So, given the situation that the machine the application is being installed on, does not have the client tools installed. How would YOU execute the provided SQL script against a remote server.
As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.
Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.
Regards,
Dominic Baines
Hello all,I have the following requirement (SQL 2000, SP2)1. Need to restore production database(A) to another database (B) onanother server (No direct connection)2. In the restored back-up(B) SP codes should not be visible (even tosa) or all SP's can be deleted .3. This needs to be carried out daily4. New tables may be added frequently to A5. This job should be scheduled6. I can overwrite the entire database (B)7. I can not encrypt SP in the original database (A)I thought of Snap-shot replication first. But when new tables areadded , I need to add new tables manually to publication .Can some one suggest most reliable and implementable method ?ThanksSrinivas
View 2 Replies View RelatedI am implementing an application that is looking into another System’s database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.
What are my choices?
Thanks,
Jason
I would like to use SQL Server's built in database snapshot feature. We have a database (e.g., ThatDatabase), and I would like to capture a snapshot of it at exactly midnight each morning. I would need the snapshot to have the same name each day (e.g., ThatDatabase12AM). My non-technical end users (statisticians with SAS) are going to use the snapshot a lot, but are not going to be able to handle it it having a different name every day.
Obviously, I could set up a job with a start time of 23:59:50 that drops the existing snapshot and then creates a new snapshot. This would have the problem that the snapshot name disappears for a few seconds every day. That would either a) kill user sessions, or b) block the snapshot drop and delay the new snapshot until after exactly 12:00 AM.
Hello -
I have a database server with over 300 databases. I want that MS-SQL Server should daily report me the sizes of SQL databases along with Transaction log files by sending me an email on my address.
How can I do that. Does someone have any script which can help me to do that.
Any help will be appreciated.
Kind Regards,
Rubal Jain
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
Hello..
I want to upgrade my website to asp.net 2.0 and I want to add a Daily news to website.and 10 to 15 news will be added daily , so after a year we have almost 4000 entry in the database.
For designing this DB what is your advice on how to store news in database?Is it better to create same tables for each year ? for exampletblNews_2005tblNews_2006
Or just to make one table and have all news stored in that ? its gonna be huge after years , isnt that make any problems?
ThanksRegards.
On our particular database server, we run the Rebuild Index Task (Using classic Maintenance Plan Designer) every night. Running the  script below, I saw that about 77 tables had an avg_fragmentation_in_percentage between 80% and 99% !!
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want
ORDER BY indexstats.avg_fragmentation_in_percent DESC
I dont understand why these tables are highly fragmented after a daily index rebuild! Unless the users are doing heavy inserts/updates/deletes during the day.
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy
ONÂ
PRIMARY
( NAME = Dummy_data,
  FILENAME = 'D:....DATADummy.mdf',
  SIZE = 250MB,
  FILEGROWTH = 25MB )
LOG ON
( NAME = Dummy_log,
  FILENAME = 'D:....DATADummy_log.ldf',
  SIZE = 50MB,
  FILEGROWTH = 5MB ) ;
GO
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
View 2 Replies View RelatedI am looking for opinions of setting up a database maintenance plan. I want to know if it is safe to trust the wizard and let it set up all of the jobs, or if it is better to write your own procedures to handle backups and maintenance as in 6.5. All sugestions and opinions are welcome. Thanks.
View 2 Replies View RelatedFor SQL server 7.0, is it necessary to schedule database maintenance plan on a regulare basis? I know it is necessary for SQL server 6.5.
Thanks.
Su Ge
SQLMaint is run once a week for the following a database on SQL 6.5. The following is the information for the database when I see it through the Enterprise Manager:
Data Size 650 MB
Data Space Available 0.00
Log Size 360
Log Space Available 359.99
The following is the syntax built by the DATABASE Maintenance Wizard:
SQLMAINT.EXE -D CATS -CkDB -CkAl -CkTxtAl -CkCat -UpdSts -RebldIdx 100 -Rpt E:MSSQLLOGCATS_maint.rpt
It runs once a week and takes about 40 mins and runs successfully. Last it run was 4/11/99 at 2:00 AM
The result set I get from sp_spaceused is as follows:
database_name database_size unallocated space
CATS 1010.00 MB 273.96 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
753710 KB 280360 KB 426494 KB 46856 KB
What I don’t understand is how come the data space available shows 0 in Enterprise Manager? Shouldn't SQLMAINT, which is run once a week, allow for correct information to be reported?
Could someone please explain.
Thanks
Shashu
Running SQL Server 2000 Enterprise Edition SP3. The database is alsoused by Microsoft Project Server 2002 and also has OLAP views, so thedatabase is being used to view/run cubes in the Analysis Manager.What is the best way of shrinking the database size and its log file too?Is there an automatic way to do this with a maintenance plan or i haveto manually run a SQL statement periodically?What are the best practices?I have a database that the size of it is 260megs and now the log file isover 800megs...Everytime i run the following:BACKUP LOG DBName WITH TRUNCATE ONLYDBCC SHRINKDATABASE (DBName, 10, TRUNCATEONLY)When running it, i get the following 2 records returned:DbId FieldId CurrentSize MinimumSize UsedPages EstimatedPages7 1 32304 128 28152281527 2 160 128 160128I don't know what the above 2 records mean. I am also concerned if i shouldbe running that statement or not.Also concerned as to why the Windows Server 2003 (Enterprise Edition)always creates a CRITICAL error in the Application event viewer with EventIDnumber 17055, source being MSSQLSERVER and the description of the event:18278:Database log truncated: Database: DBName.If it's a critical error message, then what i am doing is bad? Am i damagingmy database?Within like 7-10 days of running the above statement, the log file becomescloseto 1 GB again! How can i find out what causes the log file to grow that big?Is it beingcaused by running some OLAP views or what? The db has about 20 usersconnected to itusing an ASP web aplication as the front-end, and MSP Professional 2002 alsoto connectto the Project Server 2002 (which uses the same database as the aboveDBName).I would appreciate any help.Thank you very much
View 4 Replies View Related
I have been looking through the data files directory for my databases and noticed that the log (ldf) files of some of the databases are from 2GB to 4.5GB where as the data files are under 500MB. If I shrink the log files what affect does this have on the database? Should I be setting up regular shrink jobs on the log files?
Any help would be very much appreciated.
Hi there,
We are using a database maintenance plan to backup and reindex our db's. Up until the end of last month this was working perfectly - however now it has stopped deleting the old backups (even though we have checked 'Delete files over 1 day old').
Does anyone have any ideas as to why they are now being deleted - and how we can remove them automatically - has something been corruped? Would it be a case of creating a new maintenance plan?
thanks in advance,
Matt
SQL Server 7.0 with SP 1
Database maintenance job is failing
with error about being unable to delete old backup file.
I can manually delete the file with no problem.
This occurs randomly, maybe once a week.
Any ideas on what's wrong, and how to fix it?
I want to know database maintenance issue when there are many databases in a machine. The number of database will be 50~80. We'll use SQL 7.0. In Sql 7.0, I heard that there is not so much maintenance issue.
Wisepost Inc.
Chris Song
After looking through the archives at database maintenance steps used by others, I have decided on the following steps for each of my SQL Server 7.0 databases. I have set up a maintenance plan where they would execute in the following order for each database, if the preceding step is successful. If the preceding step fails, the job quits.
dbcc checkcatalog ('master')
dbcc checkdb ('master')
backup database master to master_dump with init, name = 'ASP Master dump'
dbcc dbreindex ('master', '', 70)
dbcc shrinkdb ('master', 10)
Now my questions...
1. The checkcatalog, checkdb, backup database, and shrinkdb commands appear in blue in SQL Server 7.0 to seemingly indicate they are commands. However, the dbreindex command is black. Does this mean that it is not recognized as a valid command? If so, why not? It is shown as a valid command in the Microsoft Knowledge Base.
2. The value of 70 in the dbreindex command was the value shown in sample code in the Microsoft Knowledge Base. However, the Knowledge Base does not give any guidance on whether this is a good value or not. How should I make a decision on what value to use? The value of 10% for the shrinkdb command is also just a sample value from Microsoft. How does one know what value to choose?
If anyone thinks this is a poor selection of maintenance steps, please let me know that also.
Thanks!
Joe Meyer
Hi, I'm new to SQL 7 (and fairly new to SQL Server), can anyone help with these basic questions on database maintenance plans generated by the wizard:
(1) Scheduling - can SQLServer handle say a REORG running at the same time as a backup against the same database? This should never happen, but what will happen if say a backup is due to start before a REORG has finished? To try & simulate this 'problem' I've run REORGs and backups at the same time & have yet to encounter errors I presume SQL locking handles this OK.
(2) Database integrity checks - (a) any comments on the wisdom of checking 'repair any minor problems'? Anyone had any problems with this? (b) While integrity checks are running do they take enforce a consistent view of the data? (I think this is probably the case as my reading of books online indicates that DBCC takes shared locks for the duration)
(3) Backups - does the 'VERIFY INTEGRITY' option have any impact on the live database? (My reading of RESTORE VERIFYONLY indicates it doesn't)
Thanks....
What is the recommended schedule for SQL Server maintenance plans that perform database integrity checks and optimizations?
Also, more specifically, how often should we be re-building our indexes?
Thanks for your help.
I have several Maintenance jobs that keep failing. They
are , Integrity Checks, Index rebuild, and Update
statistics. I keep getting a similar error on all three. I
have pasted it below. None of my other SQL 2000 servers
have any problems. I have tried changing the database
options in question. The error I keep getting is as
follows:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'DAPHNE' as 'CDCNorm' (trusted)
Starting maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:20:59 AM
[1] Database FSPRD84A: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed
because the following SET options have incorrect
settings: 'QUOTED_IDENTIFIER'.
** Execution Time: 0 hrs, 0 mins, 8 secs **
End of maintenance plan 'FSPRD84A Integrity Checks' on
4/21/2003 10:21:07 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Any suggestions?
Hi,
I'm researching on how to maintain RS SQL server database or what is the best way to maintain ReportServer and ReportServerTempDB? Should I go for the automatic shrink facility of SQL Server? Any suggestion? Thanks.
Hello, everyone:
I have a Database Maintenance Plan that back up some databases and delete old backup files that more than two day. Can any one have idea which action is first, backup or delete old files?
Thanks
ZYT
Hello everyone,
I'm new to DB Maint Plans, so let me apologize upfront. I've taken over a system from a DBA who is no longer working here, and he set up Maint Plans for all of the existing DBs. The plans show up in the Enterprise Manager under "Management->Database Maintenance Plans" like they should, but there are also entries in the "Management->SQL Server Agent->Jobs" area. When I set up a new DB Maint Plan for a new DB, it seems to be working fine, but I don't have any corresponding entries in Jobs. Did the other DBA set these up manually? Does anyone know why he might have done this? Is it needed? The jobs and job steps look like the following:
[DBName]Full:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "W:sqldataMSSQL$P001Backup" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "BAK"'
[DBName]Maint Integrity Checks:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -CkDB '
[DBName]Maint Optimizations:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 33C423D0-CC31-40BD-A357-7DCCAB1DC262 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '
Any help or insight would be greatly appreciated!
Thanks in advance,
Cat
Overview of my database: size - 45 GB, Full Recovery Mode, Log shipping ever 15 minutes; Full Backup nightly; Optimization and integrity checks on Sunday night.
The Database takes a transactional log every 15 minutes and ships it to our failover database. Usually these are small logs and are executed in matter of seconds.
On sunday night there is a problem. The optimization job (part of DB maintenance plan) runs for 3 hours and during this time the transactional log backup continues to runs and takes close to 4 hours of complete. When the tran log completes its close to a 30 GB file and this is causing problems with space and shipping. This also causes the failover server to be out of sync and at times we have to re setup log shipping.
Does anyone know if there is a way to get around this problem. Appreciate all help and comments.
I need to create sql 2005 standard maintenance plan for system and user databases and database maintenance plan should include Reorganize index task, full backup task, maintenance cleanup task(for backup files), history cleanup task.
What should be the tasks order ? please let me know....I have to complete this on 8 servers by 11/16/2007 10 AM PST
In sql 2000 database maintenance plan, any rebuild index task is there ??
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
View 6 Replies View RelatedI have in production, the database maintenance plan that runs the nightly backups. I want to restore this to a completly different server I am setting up for testing. What is the best way to restore a backup on one server (that was done using the maintenance plan) to another server? I tried just mapping a drive and then forcing a restore, however, I got some error message about an ID or something. Maybe I need to do it through query analyzer and there is an easier way to force it? thanks!
View 5 Replies View RelatedI've heard from several sources that there are issues using the maintenance wizard with 7.0 & 2000 databases. I'm looking for information to either support or refute this. Can anyone help?
View 2 Replies View Related