June 17, 2015 at 8:02 am
I have a database that we have been keeping full user session history for our users for years, and it has created a lot of large tables. Unfortunately we don't use any of this data for longer than 3 months, so I began purging the data a few months ago, a little bit at a time. When I started, the database size was 360gb, and now after removing 600 million records, the database is still at 360gb, but a query shows that I have 217gb of free space.
With that being said, I have started running into some issues in the past few weeks. The biggest of which is that the database will sometimes go into recovery for 1.5 hours after a reboot, and has made it so that I had to outright kill my re-index maintenance job. There are also occasional slowdowns, and a full backup seems to take twice as long as it used to.
I was planning on this course of action, but was wondering if it would be my best course:
1. ShrinkDb
2. CheckDb
3. Re-Index tables.
Any thoughts or recommendations would be appreciated. Thank you in advance.
-Russ
June 17, 2015 at 9:26 am
The database size will not change no matter how many rows you delete. To better understand this you should do some research on how SQL actually stores data. To keep it simple though...Imagine a bucket (database file) that can hold 1 gallon of water (data/pages). It doesn't matter if you have nothing in it, its half full or completely full. The bucket will still take the same amount of space on a shelf (disk drive). In your case SQL has allocated 360GB to hold your data and will either stay at that size or grow based on your growth settings.
So that being said you should have created a purge job to run on a schedule. This in theory would keep your database from continually growing assuming you are not ingesting more data than you are purging.
Technically you can shrink your file but it is rarely ever a good idea. Largely because it will start to reorder the pages and cause fragmentation.
Besides you'll need the space again when more data is added in. If maintained properly you can keep it from growing any larger.
June 19, 2015 at 1:18 pm
I actually already have a purge job in place that's been there for a while. Also, we won't be keeping any of that historical data going forward, so the database should have no reason to grow that big again.
The issue that I'm left with is that I still have a sluggish database and I'm having problems coming up with a game plan for fixing it.
Thx
-Russ
June 23, 2015 at 1:02 pm
If you haven't checked already I would suggest you look through your SQL Error Logs. Specifically during recovery or backups. It may provide some clues into some of your issues.
How to:
https://msdn.microsoft.com/en-us/library/ms187109.aspx
June 23, 2015 at 1:44 pm
How many VLF's (Virtual Log Files) do you have in the transaction log of your database? A large number of VLF's could slow down the recovery of your DB; but, should not slow down a full backup.
How many rows are returned (VLF's) when you run the following command (note: change DatabaseName to the name of your database):
DBCC LOGINFO (DatabaseName)
June 23, 2015 at 1:51 pm
Returns 93 rows.
June 23, 2015 at 2:22 pm
Then that's not it.
June 26, 2015 at 9:40 am
Well, let me ask this. To make sure I'm protected, and have good up time, what kind of maintenance plan and schedule should I have in place?
I'd love to outsource a pro dba for this, but there simply isn't money in the budget right now.
July 10, 2015 at 3:42 pm
can I someone explain why shrinking the db/db files won't release space?
I was thinking the same, if you have 217GB free space, shrinking db files, will release space....and I would re-index all tables as well.
Regards,
SQLisAwe5oMe.
July 13, 2015 at 7:00 am
If you are wanting to shrink the data file, did you select the "Shrink", "Files" and "Reorganize pages before releasing unused space" dialog box?
If you are wanting to shrink the log file you may have to run a TLog backup if the DB is fully logged as a VLF may be in use. To determine if a VLOG is in use run the following command (the TLog will only shrink from the bottom up until it hits an active VLF):
DBCC LOGINFO (DBNameHere)
A "2" in the status field of the LOGINFO command indicates that that VLF is in use. You may need to run a TLog backup again before shrinking the log file (look for all zeros in the status field then shrink the TLog).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply