July 20, 2011 at 2:30 pm
I'm afraid this situation is a bit uncommon, but hopefully someone will be able to shed some light...
I recently removed some very large tables from my database, and as I don't plan to replace them within the DB anytime soon, thought I should use DBCC SHRINKDATABASE to reclaim that space to be available outside if the database.
It's a very large database, so I'm not surprised that the DBCC SHRINKDATABASE command is still executing (about 5 hours later). However, what did surprise me is that my available space on my C: drive dropped significantly during this process so far.
I've had no other processes running during this time, so my question is - what would have been written to the C: drive that would take up that much space? Is this likely to be temporary files or something like that associated with the transaction log? (I'm currently using the "FULL" Recovery Model for this database, which I as I understand it logs EVERYTHING... so I presume that matters.)
Can someone confirm/correct my assumptions on what might have been written to the C: drive during this process? Also - I am hoping this writing is temporary (otherwise it will negate any positive effects from the DBCC SHRINKDATABASE command). Can anyone confirm/correct that assumption? And perhaps give some guidance as to how much additional writing to the C: drive may take place in the time remaining for the DBCC SHRINKDATABASE command?
Thanks in advance for the help!
July 20, 2011 at 3:03 pm
How are your drives set up? Are they single drives? Raided? How are your SQL DB files placed on the drives?
It's difficult to say what's going on here without knowing how you've split up you data/log files and/or where your system db's are sitting. If you're got LDF files and your tempdb files ditting on the C:\ drive then that explains why you're seeing significant space being used up on that drive.
Because the DB's are in FULL mode you may want to check the growth of your LDF files, If you're concerned about that you can run something like this to check them, and see if there are any open transactions:
SELECT
db.[name] AS [Database Name] ,
db.recovery_model_desc AS [Recovery Model] ,
db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
ls.cntr_value AS [Log Size (KB)] ,
lu.cntr_value AS [Log Used (KB)] ,
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM master.sys.databases AS db
INNER JOIN master.sys.dm_os_performance_counters AS lu ON
db.name = lu.instance_name
INNER JOIN master.sys.dm_os_performance_counters AS lsON
db.name = ls.instance_name
WHERE db.state = 0
AND lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE 'Log File(s) Size (KB)%'
ORDER BY ls.cntr_value DESC
To answer your question, yes, it should be temporary, once the SHRINKDATABASE completes and your next transaction log backup occurs, you can prune the sizes of those files should they have grown too large.
I'm sure you already know this, but shrinking your DB is going to really fragment your indexes and when it finally completes, chances are you'll need to rebuild most of them.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
July 20, 2011 at 3:13 pm
Personally I would simply have switched to simple mode, checkpoint, truncate the log (which should free considerable space).
Then back to full to have pit recovery. That way you don't get immensly fragmented data and you still get lots of free space in no time flat!
July 21, 2011 at 8:02 am
Thanks so much for the help.
As for answering some of your questions, I've only got a single hard drive on this machine, but it's big. And I'm not sure how the SQL DB files are placed on the drives. (I didn't set this DB up, but have inherited its structure from someone else.)
When I ran the query that you provided to look at the size of the log files, it says that the transaction log for the database I'm trying to shrink is HUGE, and that it's currently 98% used.
I can move a little more off of my hard drive, but this process seems to be eating up space as I free it, and as it's only about 70% complete, I'm worried that it might run out of space and not complete.
In the short term, do you think I should stop the DBCC SHRINKDATABASE process? It's currently about 23 hours in, and like I said, the task shows that it's about 70% done in the sys.dm_exec_requests view. I know there would be consequences to stopping it... namely rolling back 23 hours of transactions... but I'm really worried about the space issue.
In the long term, in terms of maintenance, is there something I should be doing to keep the log files smaller? I don't understand why the log file is as big as it is shown to be in the query you provided.
Thanks again, and I really appreciate any additional insight you can give.
Also - I am well aware of the possible fragmenting of the indexes after the SHRINKDATABASE... rebuilding the indices shouldn't be too big of an issue if this process ever completes!
July 21, 2011 at 8:05 am
yes either switch to simple recovery or take log bakcups. There's no 3rd option.
here's how to shrink the logs. Please notice that I switch back to full and immediately take a full backup. Otherwise you can't take log backups nor have PIT recovery.
USE [master]
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
USE [PROD-FORDIA]
GO
DBCC SHRINKFILE (N'PROD-FORDIA_Log' , 3000) --mbs
GO
USE [master]
GO
ALTER DATABASE [PROD-FORDIA] SET RECOVERY FULL
GO
EXEC msdb.dbo.sp_start_job @job_name = 'Backup PROD-FORDIA'
July 21, 2011 at 8:11 am
Sorry if I'm a newb at this, but I'm looking in the properties menu, file page of the database I'm concerned about. And right now, it shows an initial log size, and then in the "Autogrowth" column, it shows restricted growth. When I click for more info, it shows a maximum log file size of around 2GB. But the log file is currently WAY over that.
How is that possible?
July 21, 2011 at 8:14 am
I've never seen that issue. Maybe the log is huge NOW because of the shrink and then will go back to normal, but I can't say for sure if this is the case.
July 21, 2011 at 8:19 am
Judging from the current size of the file, and the available room on the harddrive yesterday, there is no way that it could be this big JUST from this temporary task.
Also, when you talk about backing up the log file, I was hoping to get a little clarification. We have an independent backup system that creates regular backups and would have backed up that logfile many times. Does SQL somehow not "see" those backups, and requires independent backups also to be run?
Thanks again!
-Brigid
July 21, 2011 at 8:38 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply