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

Modifying a SQL 2000 maintenance plan -- and shrinking Expand / Collapse
Author
Message
Posted Thursday, November 8, 2012 12:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:34 AM
Points: 23, Visits: 128
For shrinking,fragmentation and forecast the future database space growth u should use a dedicated tool that will fulfill your requirements regarding SQL SERVER STORAGE .....there are many tool fall in this category like Lepide SQL Storage Manager ,idera ,quest software sql tool ..trial version of these tool are available free for 30 days
Post #1382292
Posted Thursday, November 8, 2012 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
sqladmin 45377 (11/7/2012)
So I shouldn't see any more of these very large translog files unless/until I run another reindex job?


Reindexing should be one of your regular maintenance tasks

If I decide to run a DBCC DBREINDEX job in the future (from inside Query Analyzer I assume?) do I have to put the DB in Single User mode before I do so?


No.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1382308
Posted Thursday, November 8, 2012 4:08 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:50 PM
Points: 5,974, Visits: 12,877
Yes, there is a very large translog file (2.1GB) in the TRN backup folder, followed by others no larger than 35MB. So I shouldn't see any more of these very large translog files unless/until I run another reindex job?


reindex is often the cause of the largest tran log backup, check the time of this backup relative to you reindex job to confirm this. If its not giving you space issues don't worry about it, its normal behaviour.

If I decide to run a DBCC DBREINDEX job in the future (from inside Query Analyzer I assume?) do I have to put the DB in Single User mode before I do so?


as Gail said, no, but it is an intrusive process that will block user queries so be careful when you run it, best to leave it to your out of hours maintenance plan.

....and you dont need to buy any tools for any of this stuff


---------------------------------------------------------------------

Post #1382387
Posted Friday, November 9, 2012 5:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
Points: 8, Visits: 61
If I decide to run a DBCC DBREINDEX job in the future (from inside Query Analyzer I assume?) do I have to put the DB in Single User mode before I do so?

as Gail said, no, but it is an intrusive process that will block user queries so be careful when you run it, best to leave it to your out of hours maintenance plan.
....and you dont need to buy any tools for any of this stuff

Thanks so much to you and to Gail for all the helpful info. It really helps.

I've been trying to locate a performance hit that's been slowing down my users during the translog backups and I think I've found the cause. On the Integrity tab of the plan, "Check database integrity" and "Include indexes" are checked *along with* "Perform tests before backing up the db or translog". Looking at the translog backup log I can see that it takes about 4 min for this DB check to run. So is there a way to have the integrity check run only for the DB and not the translog backups? Or should I create separate plans for each so I can avoid the 4-minute drag on resources?

Maybe the simplest thing to do is just to Uncheck "Perform these tests..." and have the checks run only with the weekly reindex job?

Sorry for the long post. Hope it makes sense.

Thanks.
Post #1383307
Posted Friday, November 9, 2012 6:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
You should have two separate plans, one that does full backups and runs however often is required for full backups and a second plan that does log backups and runs however often is required for log backups, usually a lot more frequently than full backups.

The integrity check (DBCC CheckDB) should be run before the full backup preferably.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1383308
Posted Friday, November 9, 2012 6:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, June 30, 2013 9:59 PM
Points: 8, Visits: 61
GilaMonster (11/9/2012)
You should have two separate plans, one that does full backups and runs however often is required for full backups and a second plan that does log backups and runs however often is required for log backups, usually a lot more frequently than full backups.

The integrity check (DBCC CheckDB) should be run before the full backup preferably.

Thanks, I'll do that. When I tried to Uncheck "Perform these tests before backing up the db or translog" it refused to take for some reason. It would display as checked when I went back in.

In any case I'll split the original plan into a daily (evening) Complete Backup plan and an hourly (during business hrs) Trans Log Backup plan so I can have more control over each.

Thanks again!
Post #1383312
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse