SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Modifying a SQL 2000 maintenance plan -- and shrinking


Modifying a SQL 2000 maintenance plan -- and shrinking

Author
Message
peterdru401
peterdru401
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218653 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24016 Visits: 13698
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

---------------------------------------------------------------------
sqladmin 45377
sqladmin 45377
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218653 Visits: 46278
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, MVP, M.Sc (Comp Sci)
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


sqladmin 45377
sqladmin 45377
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search