 |
|
|
thanks
|
|
|
|
|
|
|
|
|
|
Thank you for this piece of instruction. It worked for me.
Please include print icon on this web page so that the instructions can be printed out without the ADs (advertisements and un-needed images).
Thank you.
Muda
|
|
|
|
|
|
|
Extremely bad and dangerous advice...
|
|
|
|
|
|
|
Can you please describe why it is dangerous. What are the drawbacks.
Thanks
|
|
|
|
|
|
|
Point I specifies how to MANUALLY shrink the log after an SSIS bulk load. Errrmm... why not invoke it from SSIS itself???
Point II is essentially a way to delete recovery information, but not explaining the risks and why would anyone do it in the first place - a much easier and CLEANER way would be to switch to Simple Recovery Mode. Also better performance IF business case allows that recovery scenario.
Log files are created in Full mode for a purpose and there's a point to Full recovery mode - this answer and the whole article is not the right place to discuss that, most basic SQL 2005 documentation covers that topic properly.
In essence the whole article says "If you are running out of space on a HD because you have a Full Recovery Mode on the database for transactional integrity and rollback functionality - just delete it (the log), risking potential data loss, wasting the workload resources used to create that log... and then leave the database in Full recovery mode so you can panic and manually delete recovery information all over again".
What makes it strange is that I can see SQL gurus much better than me voicing very similar concerns for YEARS now and the article just hangs there to catch out an unwary SQL Admin looking for a quick answer because his HD is full.
VERY bad article and advice.
What's even more scary is that the author advertises himself as the "Microsoft Certified SQL Server Developer and DBA", as much as I doubt that claim it makes this BAD article more damaging as someone may believe this is a specialist advice.
|
|
|
|
|
|
|
I agree too - VERY bad article and advice - Attaching/Detaching and Deleting
Simple advice without Attaching/Detaching and Deleting as follow
Step 1: Run following script
----------------------------
BACKUP LOG 'myDatabase' WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ('myDatabase' , 0, TRUNCATEONLY)
GO
OR-->
-----
Step 2: Right click the 'myDatabase'
Select Task --> Shrink --> Files
Under File type --> select log
Release unused space button should be checked
click OK
Right click the 'myDatabase'
Select Properties
Select Files
Check the size of the log files - it is reduced!
Step 3: If you don't need log to be backed up then change the recovery model to simple as follow
Right click the database 'myDatabase'
Select Properties
Select Options
Change Recovery model to Simple
Click OK
Many Thanks
|
|
|
|
|
|
|
Dear All,
I use SQL Server 2005.
I created a Folder that name "Company Folder"
declare @Path varchar(50)
set @Path = 'mkDir D:\Company Folder'
exec master.dbo.xp_cmdshell @Path
But it is issue with space that it creates only "Company".
Have any solve with this issue ?
Does SQL Server support with space ?
Thanks for your help and feetback.
VB.Net
|
|
|
|
|
|
|
in a query window:
USE <DB_NAME>
BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<LOG_NAME>, 1)
where <DB_NAME> is your database name
and <LOG_NAME> is the log name from sys.database_files table, name column (and not the physical filename !)
you can find your the name here:
SELECT * FROM sys.database_files
|
|
|
|
|
|
|
Overall not good advice... But perfect for my situation where
- databases are throw-away, scrubbed, production copies in debug/support environment
- hardware resource are limited, and I need the storage (zero dollar budget for test/dev environment)
|
|
|
|
|
|
|
|
|
|
Truncating your transaction log is at best a bandaid fix to a bigger problem. You are ripping the transactional heart out of your databases, and eliminating any possibility of point in time recovery. You might as well switch your database to SIMPLE recovery at this point.
What do I know, I am just a MCITP DBA and DEV for 2005 and 2008, a SQL Server MVP, and Sr. Database Administrator for a multi-billion dollar a year company. If you want to know more on the subject I'd recommend you see what other real professionals in the industry have to say about it by reading fell SQL Server MVP Aaron Bertrands blog post:
Oh, the horror! Please stop telling people they should shrink their log files!
|
|
|
|
|
|
|
Thank you! It is very helpful.
|
|
|
|
|
|
|
This is horrible information, see my comment above. You should not be doing this kind of thing to your database.
|
|
|
|
|
|
|
| This is not so horrible, sometimes even a DBA will need to shrink the log file for reasons you might not be aware of. (2B)||(!2B)
|
|
|
|
|
|
|
Thank U.
The second way is very easy and helpful
|
|
|
|
|
|
|
BAD ADVICE!! See MSFT comment!!
|
|
|
|
|
|
|
I was looking for some solution to replace the log file which you have given in the second method. Thanks a lot...
Abhishek M
|
|
|
|
|
|
|
hi,
In sqlserver 9.0(2005) i got this issue when i execute DBCC SHRINKDATABASE(database_name) command, i' ve got the follow message:
"DBCC SHRINKDATABASE: File ID 1 of database ID 9 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator."
any idea??
thanks in advance..
William
|
|
|
|
|
|
|
|
|
|
Paul Randal explained that this is a bad idea. At best it forces you to take your database offline. Worst case is that you invalidate your entire database and have to restore from backup.
The right way is to select Simple Recovery and then make your log files an appropriate size.
|
|
|
|
|
|
|
Hi All
Having hit this very issue I appreciate everyone's contribution that makes this overall article really good, especially with references to MS technical articles on the subject. Verifying with reliable sources is very important.
If you are here for the first time you should read all these posts to be safe.
This worked fine for me, but note you should do a backup first if the database is critical:
USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
Source was from Dislektik in this thread and origin is here: http://msdn2.microsoft.com/en-us/library/ms189493.aspx[]
Special thanks to the author and Dislektik and thanks to the other posters too....
You can contact me at coder@danhog.com or howartthou@danhog.com.
Regards
Super Coder!
|
|
|
|
|
|
|
hi this was very helpful...I lost data couple of times after deleting the log files...so thanks for tip
|
|
|
|
|
|
|
hello,
what about this statement
DBCC SHRINKFILE (YourDatabase_log, 10)
this should shrink log file of your database without need of detaching database.
Look at:
http://bloggingabout.net/blogs/dennis/archive/2007/02/28/shrink-your-sql-2005-transaction-logfiles.aspx
http://support.microsoft.com/kb/907511
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
Hth.
Lubos Pazdera
lubos.pazdera@gmail.com
|
|
|
|
|
|
|
Hi i used this method but it gives an error
Cannot shrink file '2' in database 'mydatabase' to 1280 pages as it only contains 480 pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
please tell me what i do
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin