Tag Info

New answers tagged


There is no way round the restriction with truncate as other answers have indicated, but you can of course use delete without disabling the constraints: delete from parent; Note that 'delete' and 'truncate' have different side-effects


In addition to Tom's and Shanky's answers, if your database contains LOB/BLOB data the DBCC SHRINKFILE may not work. If that is the case, then you have two options, depending on whether you can take the database offline or not. If you can take the database offline, then you will need to copy the data out and copy it back in to remove the empty space. You ...


This is normal behavior when you truncate table and which involves removal of more than 128 extents as Per Books Online When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both ...


If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically. Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data. What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file ...


but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen). The recovery mode is FULL (SQL Server 2005). I guess previous users were taking file system backup which in my opinion is not best practice when you have important app running. You can never get point in time ...


Ball park - it will have to read 60Gb out of the log file, write 60Gb to the backup file, do some metadata operations for the log truncation itself and (optionally) move what's left of the log file if you shrink the ldf. For your average disk subsystem I'd expect you would be done and dusted in couple of hours, tops.


Well, if you are relying on the physical file backups, that is okay, you can take a transaction log backup and then shrink the 'log' file to a small amount and change the recovery model to simple because you are not planning to take log backups. However, to make the approach more sound, schedule the full and regular T-log backups so that you can restore ...

Top 50 recent answers are included