July 16, 2012 at 2:59 pm
I do appreciate the information everyone has provided.
July 16, 2012 at 3:00 pm
1) Don't shrink on a regular basis
2) Don't shrink your log to 0, regrowing a log from 0 is an expensive operation. If you absolutely have to shrink, shrink to its usual size.
3) truncateOnly is not a valid option when shrinking a log, it's ignored. That command will shrink the log to 0
If you don't want to log growing during index rebuilds, firstly don't rebuild everything (Ola's script), second switch to bulk-logged recovery for the duration of the rebuild if the risks to point-in-time recovery are acceptable.
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
July 16, 2012 at 3:05 pm
I will set it to 3000 for 3GB I believe it is only using about 1GB during the week at the most so 3GB should give it some room.
Is there a command to change the recovery model on the fly. I was thinking of just pulling the rebuild index and maybe doing this like once every 6 months.
July 16, 2012 at 3:10 pm
You need to rebuild indexes regularly, not every 6 months. Ola's script will only rebuild what needs rebuilding so avoids the wasted effort of rebuilding indexes that don't need a rebuild.
ALTER DATABASE to change recovery models, read this on bulk-logged before you use it.
http://www.sqlservercentral.com/articles/Recovery+models/89664/
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
July 16, 2012 at 3:10 pm
lawson2305 (7/16/2012)
Is there a command to change the recovery model on the fly.
Considerations for Switching from the Full or Bulk-Logged Recovery Model
In the article they show the ALTER DATABASE commands to switch back and forth.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 12:11 pm
Ok I think I almost have everything up to snuff finally but a few last questions. I want to use these provided index and integrity check operations from my maintenance plan.
I'm using the Execute SQL Server Agent Job Task to do this but..
Later I get an error and what I think is going on is the task is executed but it is not waiting for completion of the agent job before moving on.
Any suggestions?
I'm running a integrity check twice once at start then again at end and it fails the second run:
Executing the query "EXEC msdb.dbo.sp_start_job @job_id=N'b79066e6-9d0a-4989-b475-0cb9bb45c7eb'
" failed with the following error: "SQLServerAgent Error: Request to run job DatabaseIntegrityCheck - USER_DATABASES (from User NT AUTHORITY\\SYSTEM) refused because the job is already running from a request by User NT AUTHORITY\\SYSTEM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
The other question I had was regarding a statistics update. It says the default is: Do not perform statistics maintenance. This is the default.
Is this true then it is not performing stat updates? Is there a way to modify this after running the maintenacesolution.sql?
September 5, 2012 at 12:24 pm
lawson2305 (9/5/2012)
Ok I think I almost have everything up to snuff finally but a few last questions. I want to use these provided index and integrity check operations from my maintenance plan.I'm using the Execute SQL Server Agent Job Task to do this but..
Later I get an error and what I think is going on is the task is executed but it is not waiting for completion of the agent job before moving on.
Any suggestions?
I'm running a integrity check twice once at start then again at end and it fails the second run:
Executing the query "EXEC msdb.dbo.sp_start_job @job_id=N'b79066e6-9d0a-4989-b475-0cb9bb45c7eb'
" failed with the following error: "SQLServerAgent Error: Request to run job DatabaseIntegrityCheck - USER_DATABASES (from User NT AUTHORITY\\SYSTEM) refused because the job is already running from a request by User NT AUTHORITY\\SYSTEM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
The other question I had was regarding a statistics update. It says the default is: Do not perform statistics maintenance. This is the default.
Is this true then it is not performing stat updates? Is there a way to modify this after running the maintenacesolution.sql?
This, EXEC msdb.dbo.sp_start_job @job_id=N'b79066e6-9d0a-4989-b475-0cb9bb45c7eb', is an asyncronous call to start the job. It does not wait until it is finished to return.
When you try to run it a second time, it is still running, hence the error you received.
September 5, 2012 at 12:29 pm
lawson2305 (9/5/2012)
Ok I think I almost have everything up to snuff finally but a few last questions. I want to use these provided index and integrity check operations from my maintenance plan.I'm using the Execute SQL Server Agent Job Task to do this but..
Later I get an error and what I think is going on is the task is executed but it is not waiting for completion of the agent job before moving on.
Any suggestions?
The SSIS Task is simply a pass-through to the proc sp_start_job which starts a job but does not wait for it to complete before returning control.
You can try using an Execute Package task to execute the Maintenance Plan which will wait for completion before returning control.
The other question I had was regarding a statistics update. It says the default is: Do not perform statistics maintenance. This is the default.
Is this true then it is not performing stat updates? Is there a way to modify this after running the maintenacesolution.sql?
You can edit the proc dbo.IndexOptimize and change this line replacing NULL with the constant you want:
@UpdateStatistics nvarchar(max) = NULL,
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 1:28 pm
I do not see an execute package task in sql 2005 options for maintenance plans.
How can I execute these jobs so that the plan waits?
Is there a way to maybe take the sql that is in the job and put in the maintenance plan as a execute sql task?
September 5, 2012 at 1:41 pm
lawson2305 (9/5/2012)
I do not see an execute package task in sql 2005 options for maintenance plans.How can I execute these jobs so that the plan waits?
Is there a way to maybe take the sql that is in the job and put in the maintenance plan as a execute sql task?
Sorry, when I mentioned the Execute Package Task I was thinking of the full version of SSIS. I do not use Maintenance Plans (MPs) unless it is for a client with a small or nonexistent IT department and tend to forget they are limited versions of SSIS. You can use the Execute T-SQL Statement Task to execute the code you want and that will have the rest of your MP waiting until it completes before moving on.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 1:43 pm
would I use the code in the job that runs the stored procedure?
example:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @LogToTable = 'Y'" -b
or do I need to pull from the stored procedure?
September 5, 2012 at 1:45 pm
You can just call the SQL directly, no need to use sqlcmd there.
If you pull that EXEC into a T-SQL Statement Task in your MP that will get it to wait.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 1:47 pm
you lost me here.
I think you meant I could run the command I pasted. Not sure though.
September 5, 2012 at 1:51 pm
This can go into your Execute T-SQL Statement Task:
EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES', @LogToTable = 'Y'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 1:55 pm
I see thanks!
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply