Quote:
|
Originally Posted by varad01
Hi,
I replied for this some days back, but I dont know why the info. is missing here???
Here is the simple and Quick Solution. But the database will not be available for awhile.
Use detach and attachdb
1. First detach the database by using
Example : EXEC sp_detach_db 'pubs', 'true'
2. Delete the log file (or) To be safe ,Rename the log file to some name
3. Attach the database without log file.
EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
Make sure the paths are correct.
When you attach without log file, the SQL Server will automatically create a log file with small in size , ithink it will be 1mb.
Try it . I will appreciate a response.
Have fun.
Varad01
|
Not a recommended approach ...
Shrinking the Log file is ideally done by taking a backup and then shrinking the file. Using a approach similar to this on your prod server might even cost you your job in case anything goes wrong
Ideal way to go about shrinking your log file
Code:
Use Master
dbcc sqlperf(logspace)
GO
The above code will give you log file size and the percentage of space actually used in the file. Ideally, you should be able to reduce any log file by the amount of space = 100-[Log Space Used%] * [Log Size (MB)]
SQL Server needs Log data only upto the point of the last log backup, the rest of the space is marked as reusable.
In case the recovery of your database is important to you, you may either take a full backup or a log backup.
You might want to take a look at the recovery model of the database, and the recovery model you actually need. In case, Transaction log backups are not being taken and all you are doing are full backups , you can set the recovery model to simple and this will free up log space in the file which you can then shrink.
Use
Code:
Use DBName
exec sp_helpdb DBName
To shrink the log file, you can then use
Code:
Use DBName
GO
DBCC SHRINKFILE(Filename, SizeInMB);
GO
Where filename is the name of the file (Not the actual filepath)
dBforums
>
*_log.ldf became giant...
09-16-02, 11:22
Thanks a Lot!!!
Linear Mode
