November 8, 2007 at 2:08 am
I havea database of 8GB. The underlying tables, at the moment, only take up 3GB. DBCC SHHRINKDATABASE(MyDB, 10) or DBCC SHRINKDATABASE(Mydb,TRUNCATEONLY) will truncate the log file but have no eefect on the database file.
Any Suggestions?
November 8, 2007 at 4:19 am
Hi,
Which physical file has freespace??
If its .mdf (datafile) then
DBCC shrinkfile('Logical MDF filename',3250)--Where the value should be greater than 3GB
If its .ldf (log file) then the below command
1.) Backup log dbname with truncate_only
2.) DBCC shrinkfile('Logical LDF filename',100)--Where the value should be greater than used value
How to avoid these situation in future?
1.) Change the recovery model to simple if its not needed in full recover model
2.) Refer the link http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
Regards..Vidhya Sagar
SQL-Articles
November 8, 2007 at 4:34 am
Hi Vidyha
I have solved it. What happened was that I created a database with an initial size of 3mb. I imported a lot of data and increased its' size to 8GB. I promptly ran out of space.
I then detached that database to delete its' log file. Obviously when I reattached it the Initial size was set to 8GB. You cannot SHRINKDATABASE below its' original size.
I just created a new database an exported the tables, deleted the database and recreated it.
Thanks for the hint about the recovery model, I will change it now.
Regards
David Halliday
November 9, 2007 at 3:13 am
Unless someone is very experienced in SQL Server, they should never delete a database log file unless advised to do so by Microsoft support.
Deleting a log file is a last-ditch attempt to get the database available again, after all other options have been tried and failed. Deleting the log file is a good way to corrupt the database. Running out of disk space for the log is NOT, repeat NOT, a reason to delete the log.
If a log file does fill the disk, add another log file to the database on another disk. Then resolve the problem that caused the log to grow. Consider putting the database into Simple recovery if you do not need to retain log backups. If you are in Simple mode and the log grows, then you just have to make enough disk space available.
If a log or data file has grown larger than it needs to be, it can be shrunk using DBCC SHRINKFILE - this can shrink a file to smaller than its original size. However, do not shrink a file if you expect it to grow again within 3 months. If you do repeatedly shrink and grow a file you will get NTFS fragmentation that will harm your performance and can only be cured by a disk defrag.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 9, 2007 at 6:15 am
I agree, removing log files is irresponsible and flies in the face of what a DBA stands for. And why the obsession to shrink files, do you actually know what happens to the internal structure of a mdf ( or ndf ) when you shrink it?
A database needs working space to perform optimally.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 13, 2007 at 1:20 am
Shuffle, shuffle, shuffle the pages... I wrote a post about this, after encountering way too many people shrinking databases for no good reason. (http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]) I've had an uphill battle with my colleagues about shrinking production databases.
Why does 100GB free on the disk look better than 10GB free on the disk and 90GB free inside the DB file? There's nothing else on the drive....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2007 at 4:00 am
Gail
Thank you very much for your comments. you are preaching to the converted.
This however is the very start of the development system and I only have 10 GB on the disk
Regards
David
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply