Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

BAB11.1's SQL Database Running Out of Space in C-Drive

Status
Not open for further replies.

AlexChao

Technical User
May 16, 2003
44
SG
After converted the VLDB to MS SQL Server 2000, we heavn't experienced anymore database crash or corruption.

However, the SQL database now uses the most space in C-drive (12GB) and the remaining free space is only about 500MB.

1. What can I do to reduce the disk space usage of the MS SQL 2000 Server ?

2. What are my option when the free space in the C-drive runs out ?
 
If you have Prune Jobs you must to check index space under Sql Server. The VLDB database don't have space ocupation problem.

What is the process to migrate VLDB to Sql Server?
After migration all jobs run ok?
 
1 - Run a maintenance job:


2 - Don't let it get that far, apart from anything else it will cause your server to crash.

As for the previous comments - VLDB certainly DOES have space considerations the same as any database. To imply that it does not take up as much space as SQL is pretty naive at best.
 
I am aware of the tech note TEC353603. In fact, we have implemented it with SQL Database Optimization scheduled once-a-week, and Database Integrity check everyday. I have also used the SQL Utility to export the asdb database to D-drive of the backup server on a daily basis.

In-spite of the above, the database continues to grow such that I have only 500 MB of free space left in C-drive.

1. Would running the SQL maintenance plan shown in TEC353603 actually reduced the disk space usage ?

2. How to safely migrate the asdb database from C-drive to D-drive where there is plenty of free space ?

3. Any other helpful advise / tips would be very much appreciated.

Thank you.
 
1 Possibly - if you shrink the db and log

2 Do this in SQL
 
How to safely migrate the asdb database from C-drive to D-drive where there is plenty of free space ?


1. Backup Database
2. Stop BrightStor services and Drop database
3. Use restore command into sql with "with move" option
4. Start BrightStor services


Sample for you:

-- View Logical units into backup
restore filelistonly from disk = 'C:\Data\asdb.BAK'
go
-- Restore Database
restore database asdb
from disk = 'C:\Data\asdb.BAK'
with move 'asdb_Data' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\asdb_Data.MDF',
move 'asdb_Log' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\asdb_Log.LDF'

 
Hi n018737,


1. Backup Database

How to do it ?
a. use BABr11.1 to backup SQL database in C-drive ?
b. use SQL Utility to export ?
c. use Acronis to do partition backup ?


2. Stop BrightStor services and Drop database

I'm a newbie to SQL. By 'drop', you mean stopping
the SQL services ?


3. Use restore command into sql with "with move" option

Is the 'restore'and 'with move'found in the SQL
Utility ?


4. Start BrightStor services


I will check it out in the server. Will let you know how it went. Thanks so much !

 

1. Backup Database
How to do it ?
- Using SQL Enterprice Manager - Backup Database (Complete backup to c:\data\asdb.bak)

2. Stop BrightStor services and Drop database
How to do it ?
- Using SQL Enterprice Manager - Drop database

3. Use restore command into sql with "with move" option
Is the 'restore'and 'with move'found in the SQL Utility ?

- Open Query Analyzer, connect to master database and execute sql.

4. Start BrightStor services
I will check it out in the server. Will let you know how it went. Thanks so much !
- Depend of database size, if your database size is 1 GBytes in 10 minutes the service will be enable.
- You don't have to change params in BrightStor.
- Be sure that BrightStor user have database access permision.

Good luck!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top