| Author |
Topic |
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
 Posted - 2006-11-28 : 08:11:27
|
| Hi all,Got a problem with a system from a company we took over.The disk - there is only one - is full, has no backup. I've determined that there's one datafile taking up 278Gig. About a week ago one of the tables within had 118 million rows, of which I deleted nearly half, down to 60million, but there's now something like 100Gig of unused space WITHIN the datafile that isn't getting yielded to the operating system. I keep my databases in better order, so I've no experience in compacting them, but I'll tell you what I've tried.I've hit it with a shrinkfile, to no appreciable effect, and tried shrinkdatabase, but this keeps getting "could not obtain exclusive lock" errors, or something like that. It would have been nice to backup with no truncate, but there's not enough space to backup to.I've ordered new disks, but these are taking an age to arrive.Thankfully, this isn't a user system (it's actually something of an ANTI-user system), but this issue will reflect badly if left to fester much longer.Any of you guys have any experience with this? It's a 2k system.Thanks in advance,Jaybee. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 08:13:12
|
| Remove all network cables, restart sql service and put it in simple mode.now backup log.then shrink and truncate file with EM.Peter LarssonHelsingborg, Sweden |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-11-28 : 08:25:27
|
| Remember to re-attach network cables afterwards!!!...and take the DB out of single-user mode. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-11-28 : 09:11:00
|
| You may find that you have to pull the information out of the database into another new one, rather than trying to shrink the existing file. Shrinking the file needs space to work in, and I've found it to be unreliable with large files like this.-------Moo. :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-28 : 09:41:10
|
| Bit off the wall but:If you've deleted a whole load of stuff REINDEX may help. REINDEX normally gets a bad rap for moving stuff into newly created space, but if you have acres of slack it should move it into that - which will be nearer the start of the disk, and should enable Shrink to just cutback the filesize, without having to "move data to start of file"I'd feel a whole lot more comfortable if you had a backup before you started experimenting though ...Kristen |
|
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-11-28 : 09:57:03
|
quote:
Originally posted by Peso
Remove all network cables, restart sql service and put it in simple mode.now backup log.then shrink and truncate file with EM.Peter LarssonHelsingborg, Sweden
Hi Peter,By 'Simple mode', presumably you mean 'Minimal' (There may also be a 'Simple' mode, for all I remember!!)Where can I backup up the log to? The disk is chock full!Cheers,Jaybee. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 10:03:06
|
I think there is an option for log backupBACKUP LOG { database_name | @database_name_var } { [ WITH { NO_LOG | TRUNCATE_ONLY } ] }quote:
NO_LOG | TRUNCATE_ONLYRemoves the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
Peter LarssonHelsingborg, Sweden |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-11-28 : 10:12:04
|
| Backup log with truncate only would release off the tran log space, but you may have issues with it if you have actually zero space.You'd still have to shrink afterwards.-------Moo. :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 10:16:11
|
| I thought this could free up necessaty space to reindex and later shrink both logfile and datafile.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-28 : 11:44:26
|
| "By 'Simple mode', presumably you mean 'Minimal' (There may also be a 'Simple' mode, for all I remember!!)"Peter was referring to the Recovery Model of the database. (i.e. set it to SIMPLE instead of FULL)"I thought this could free up necessaty space to reindex and later shrink both logfile and datafile."Worthwhile deleting any temporary files on the server that you can before you start - just to create a little elbow-room. Pull the network cable first though, you don't want that puppy grabbing the precious few spare bytes that you manage to create.Can Jaybee just:Shut down SQL serverRename MDF file to, say, NEWDATABASE.MDFRename LDF file to, say, DONOTUSE.LDFRestart SQL ServerDrop the ORIGINAL DATABASE (not marked as Suspect, Offline, or somesuch)Do a Single File Attach to reattach the NEWDATABASE.MDF??Of course this would be highly risky without a backup.Kristen |
|
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-11-29 : 16:15:58
|
| Ok, so the recommended course of action (I have about 30Gb spare) is:0.5) Restart SQL Server1) Set recovery mode to 'Simple'2) Backup Log with Truncate -- (I've already done this, down to a few Mb)3) DBCC Reindex -- I'm almost certain there are NO indexes on the table though!!!4) DBCC Shrinkfile -- I presume I should tick the "Move pages to beginning of whatever" option?Kristen - I have no idea how the front-end application will react if I tinker around with changed datafile id's, that's a minefield I don't want to step into, for all I know the app might reference a DB id that is changed somewhere. Thanks,Jaybee. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-29 : 16:34:16
|
| Let us know he result when you're done.PS. Don't forget about 0.25) Remove network connection by pulling network cable.And for calification. You don't have to restart whole server machine, just sql server service.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 02:03:24
|
| "if I tinker around with changed datafile id's"Sorry, should have added that after messing with the ATTACH stuff you could then rename the "new" database back to the original name."3) DBCC Reindex -- I'm almost certain there are NO indexes on the table though!!!""Got a problem with a system from a company we took over"You've got more problems than just the size of the database then!!If the tables have a Primary Key then they have an index."I presume I should tick the "Move pages to beginning of whatever" option?"Yes. And when its done be prepared to have another go. If you go back into Shrink (in Enterprise Manager) and there is still loads of Free Space then try again to see if that gets removed with another go.Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-11-30 : 06:05:16
|
| ....I'm almost certain there are NO indexes on the table though!!!you've got another problem as well....one in all probability called "sh*tpoor performance".better hope there are no "cursors" in the codebase as well!!! |
|
|
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
Posted - 2006-12-01 : 08:46:18
|
| All compounded by the fact that I can't FAARRRRRRKING read....Though I deleted half the rows, the actual data within the db is 265Gb...and that's the space taken up on the disk. And there IS no space within the db, because the other 60 million rows ARE 265Gb.Know WHY I thought there was 180Gb of data in the 265Gb DBF? Because I read the DAMNED output on the file space statement, and on the index it was 18MB...and I misread it as 180Gb. And believe it or not, the original 60 million rows I deleted were largely empty. Whoddathunk it...I think I need to logoff, put my coat on, get as far away from any other screen as possible, and spend the rest of the day in the pub...and you can all come by way of making up...:)MASSIVE apologies for all the wasted effort, people!!!"Barman, a cold Kronenbourg for me and whatever my esteemed partners in crime are having..." |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-12-01 : 09:31:38
|
| ....I think I need to logoff, put my coat on, get as far away from any other screen as possible, and spend the rest of the day in the pub...and you can all come by way of making up...Perfect solution on a Cold and Windy day.....enjoy it!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-01 : 09:48:46
|
| Its POETS today, anyway ... |
|
|
|
|
Out of disk space