Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Shrink large database Expand / Collapse
Author
Message
Posted Tuesday, May 28, 2013 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:44 AM
Points: 7, Visits: 157
Database partitioning is not my first thought, and I think partitioning didn't work in SQL 2000 like we're used to now. I have cleaned out a SQL 2005 database around 350GB and much more important is that indexes should be present to support the delete.

Database partitioning is high maintenance to set up and maintain, I have created and updated scripts to create these partitions, and we have yet to delete partitions.
Negative aspects:
- your indexes need to be partition aligned (say if you split partitions on month basis, the index needs to be split on datetime type)
- if they're not, any move or remove may render your database blocked for extended periods of time during which data is moved and indexes are updated
- you need to understand it thoroughly before accepting the challenge
- applying that to an existing database, where the problem you want to solve is the speed op data movement, is not recommended. You can kill a script to delete records, you can not kill the data movement.

I have a 4,3TB database which is partitioned by month. That's a valid case. I feel 350GB is not a valid case and not worthwhile. Start Profiler, run the result through tuning wizard and apply indexes. I did that, and while I could just about delete the daily turnover, now the script I used will delete history at a rate of 1 hour per day. So my script takes an hour a day to delete that same day 3 months ago. It should be possible.
Post #1457313
Posted Tuesday, May 28, 2013 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
pnauta (5/28/2013)
Database partitioning is not my first thought, and I think partitioning didn't work in SQL 2000 like we're used to now. I have cleaned out a SQL 2005 database around 350GB and much more important is that indexes should be present to support the delete.

Database partitioning is high maintenance to set up and maintain, I have created and updated scripts to create these partitions, and we have yet to delete partitions.
Negative aspects:
- your indexes need to be partition aligned (say if you split partitions on month basis, the index needs to be split on datetime type)
- if they're not, any move or remove may render your database blocked for extended periods of time during which data is moved and indexes are updated
- you need to understand it thoroughly before accepting the challenge
- applying that to an existing database, where the problem you want to solve is the speed op data movement, is not recommended. You can kill a script to delete records, you can not kill the data movement.

I have a 4,3TB database which is partitioned by month. That's a valid case. I feel 350GB is not a valid case and not worthwhile. Start Profiler, run the result through tuning wizard and apply indexes. I did that, and while I could just about delete the daily turnover, now the script I used will delete history at a rate of 1 hour per day. So my script takes an hour a day to delete that same day 3 months ago. It should be possible.


Partitioning works just fine in SQL Server 2000. Lookup "Partitioned Views" and you'll see.

You're correct about the initial setup being a bit complex especially if you have an IDENTITY column in the table, but it's worth the effort even on a 350GB database especially when you consider things such as backup and restore times. Consider the following... will you ever update an audit table? If it's truly an audit table, the answer should be "NO". That means that you have (in this particular case) 6 months of data that won't ever change. Only the "current month" will change. If you use a "Partitioned View" of the table (unlike a "Partitioned Table"), you can store those older, static months in a separate database which has several advantages. First, the database can be set to the "SIMPLE" recovery mode so that there's no need to backup the log. That also decreases the log file backup time on the "main" database where the "current month" is stored. That also means that you only have to backup the separate database once per month when you add a new month's worth of data to it. It also means that a restore would take much less time. During a DR restore, you're not so much concerned with old log data as you are with getting the database back up and running so you can return to normal business. Properly indexing such a thing is a trivial matter.

As a bit of a sidebar, remember that if the "current month" table has an IDENTiTY column, you won't be able to do a direct insert into the view. Again, that's trivial because you can insert directly into the Current Month table instead of trying to insert into the "Partitioned View". It means changing the target of the audit triggers that are currently in place but, again, a trivial change compared to the benefits.

The code to do all of the above is relatively trivial and can be done as a "set it and forget it" scheduled job.

So far as your comment on being able to stop deletes but not datamovement, I'd say that just doesn't matter because once you setup the system to maintain the partitioning, it's just not going to matter at all. You are, in fact, supposed to test such things before you implement them to ensure that they not only work correctly but also have the necessary error checking to prevent any data loss.

So far as "350GB is not a valid case and not worthwhile", that's likely an incorrect assumption but does need to be evaluated especially when it comes to backup space and the amount of time to do a DR restore. If the largest database a company has is only 350GB, they're probably not setup with huge reserves of disk and tape backup space. Most companies just won't spend the money on it because, for example, buying and powering up 4TB of disk space and a relatively larger number of backup tapes doesn't make sense for a 350GB database because technology changes (no sense buying lots of hardware that will go out of date without ever being used). All of that usually makes such partitioning very worthwhile.

To summarize, the investigation and planning to pull off partitioning in the manner that I've just recommended would only take a day or two to come up with a rock solid plan. The coding for the automated monthly partition "moves" is trivial. The coding to use the new partitioned view is also trivial because, if you plan it correctly, the view will be named the same as the original monolithic audit table and no front end changes will be required. Only the triggers that put the audit data into the "current month" table would need to be changed and that's also a trivial change.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1457352
Posted Tuesday, May 28, 2013 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:44 AM
Points: 7, Visits: 157
Hi Jeff,

I still think we're talking about different things here. I don't see how partitioned views in SQL 2000 are going to help (I have experience with partitioned tables in SQL2005 and beyond which makes it easy to switch out a partition based on date), but if the TS is an expert on SQL 2000 I will not hold him back. I still maintain that to delete info, it's worth his while to check whether indexes are in place to aid in doing that. Furthermore he mentions years of neglect, and failing backups, so chances are even that there were no index rebuild tasks scheduled. So, I always try to start from there, instead of adding complexity. And I would never recommend going into simple recovery mode, as backups can be done while the delete job runs, to free up space in the log. I would always want to have a full DR path to enable me to do a point in time restore.

Regards,

Peter
Post #1457385
Posted Thursday, May 30, 2013 4:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:49 AM
Points: 129, Visits: 861
terry.home (5/27/2013)
Not a bad suggestion. I have considired this, but not done it.
I dont know whether the last six months worth of data is any smaller in comparison to previous.
My original problem was a lack of disk space which caused performance issues. Therefore deleting and shrinking helped to free up some space and allowed the database to respond.

I could potentially now move the last 6 months to a new file, but once this is done, I assume I will still need to get the data older than 6 months deleted.

Am I wrong to think that I will therefore be adding another step into my process but still end up with the same result at the end of the process?


Regards
T


Hi Terry,

Sorry late reply!

Jeff Moden brought up a great point, that you could possibly use Partitioning as a method. Have you looked in that before?

But may be right right adding more complexity than you really want. It might be just as easy to just schedule some type of delete for records older than 6 months if you've already trimemd it down-depending how large it still is/performance of delete operations. It really would depend on your situation, how much down time you can have, and impact to business/users)


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1458487
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse