maintenance works for MS SQL server.

[Former Member]

Dear All,

i had a few quires related to SQL database, request you to spend your valuable time and answer as many as possible.

1*What are the maintenance works for MS SQL ?

2*How to shrink Database (only LOG )?

3*effects of shrinking database ?

4*which kind of backup is good to take, before doing upgrade activity in SAP ?

5* importance of Stored procedures, can we delete stored procedures?

6* Jobs that need to performed at regular interval times, by which the performance will be good.

7* this to be considered when SAP on SQL grows to large (database 300gb).

8* can we upgrade only DB. i.e.. SQL 2000 to SQL 2005?

9* Reorganization and index building in SQL.

please give your valuable inputs for these questions,

[Former Member · Accepted solution]

Hi raghav,

1*What are the maintenance works for MS SQL ?

You should shedule a transaction log backup (every few minutes) and a database backup (daily - full or differential) on a regular basis. Besides this you should also run a Check DB and an Update Statistics job (weekly or more often if you like).

2*How to shrink Database (only LOG )?

See Note 1002099 - How to shrink the logfile on MSSQL server 2000

3*effects of shrinking database ?

Have a look at this thread:

Read carefully what Clas explains there.

4*which kind of backup is good to take, before doing upgrade activity in SAP ?

A full backup plus all the transaction logs up to that point. The upgrade program asks you to take a backup at a certain point of time during the upgrade. Try a test run to find out.

5* importance of Stored procedures, can we delete stored procedures?

This note should answer your question: Note 333458 - Deleting obsolete stored procedures in SQL Server

6* Jobs that need to performed at regular interval times, by which the performance will be good.

See Q#1: Update Statistics

7* this to be considered when SAP on SQL grows to large (database 300gb).

Consider archiving your data (service.sap.com/ilm). Depending on the size of your organisation 300 GB is not really big. SQL Server can easily handle 300 GB of data but what about your disk system (SAN)?

8* can we upgrade only DB. i.e.. SQL 2000 to SQL 2005?

Have a look at this thread:

9* Reorganization and index building in SQL.

See Note 159316 - Reorganizing tables on SQL Server SQL 2000/2005

Regards,

Sven