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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem shrinking a database and Log

Status
Not open for further replies.

Andles

Programmer
Jul 16, 2001
6
NZ
Hi Folks

I have tried all the conventional means of shrinking a Database to reduce the database and Log size. Currently these are 102Mb and 38Mb respectively but use only about 20% of the file size for the DB and 5% for the Log.

The Truncate on Checkpoint option is turned on. There are NO open transactions as checked with:
DBCC OpenTran

I have tried:
DBCC ShrinkDatabse
DBCC ShrinkFile
Truncating the Log and doing a complete backup

Though these have work prefect for other databases on the SQL Server, nothing seems to work with this particular DB. Any suggestions are welcome

Thanks

AJ
 

I assume you are working in Microsoft SQL Server 7 or 2000. Detach the database, delete the log file and reattach the database.

Step 1:
exec sp_detach_db 'dbName'
Go

Step 2:
Delete the LDF file.

Step 3:
exec sp_attach_single_file_db 'dbName', 'e:\mssql\data\dbName_Data.MDF'
Go


I recommend the Microsoft SQL Server forum183 rather than the ANSI SQL forum for questions related to the Microsoft product. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top