0
votes
1answer
25 views

Index Maintenance Plan

At our organisation we are migrating to SQL Server from visual foxpro dbf and hence had no maintenance plans and have no DBA. We have gone through our backup plans and Index maintenance is only index ...
3
votes
2answers
370 views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
3
votes
1answer
2k views

Central stored procedure to execute in calling database context

I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on ...
5
votes
6answers
662 views

Find procedures that haven't been called in <n> days

We are deleting old stored procedures and tables. How can I know what procedures haven't been called recently? dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return ...
1
vote
1answer
803 views

Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan

I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
6
votes
1answer
376 views

Can I “Execute T-SQL Statement Task” against all user databases?

I'm setting up a maintenance plan on Sql Server. I want to add an Execute T-SQL Statement Task to the plan, however I do not see any way to set this task to run in every [user|system|all] database as ...
2
votes
1answer
3k views

SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the ...
3
votes
1answer
784 views

Daily database maintenance using SQL Server 2008 and a stored procedure

I have created a database maintenance plan using wizard though that link for SQL Server 2005. I have created a Maintenance Plan for SQL Server 2008 and it works fine. Now I required to complete the ...
1
vote
1answer
77 views

Where and with what should I be looking for a good hardware backup tool?

Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs. Now I want to push that on more professional and ...
1
vote
1answer
802 views

How often should I run integrity and consistency checks on SQL Server 2008?

Before updates of our application, I run some checks on the database like CheckAllocations and CheckCatalog, so I know the database is in good shape before the update scripts get executed. What is ...
4
votes
1answer
2k views

Compact Large Objects in Reorganize Index task

I'm setting up a maintenance plan in SQL Server 2008 R2. On the Reorganize Index Task it has an option for "Compact Large Objects". What does that mean and do I want to turn it on?
5
votes
4answers
3k views

SQL Rebuild Index, Recovery Model, and db log filesize?

I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am. Every Saturday at 3am, we have a job setup ...
9
votes
5answers
491 views

SQL Server Backups - A couple of questions

We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at ...
0
votes
0answers
204 views

changing fill factor on msrepl_command db in distribution

I can't think of any reason why you couldn't change the fill factor on the table without any issues, but I've never change the fill factor on a system table before and would like to know if anyone has ...
9
votes
6answers
2k views

SQL Server Checklists

Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming ...
1
vote
3answers
7k views

No Maintenance Plans Folder in SQL Studio 2008 R2

A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server. Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy ...
1
vote
1answer
664 views

How to ensure that SQL Jobs run in the correct order within a minimal maintenance window?

From this question I learned about a script which creates jobs for backing up databases, checking database integrity, optimizing indexes, and cleaning files. According to the documentation, this ...
2
votes
2answers
483 views

Should I create a maintenance plan for my database or use jobs?

I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
3
votes
2answers
2k views

How to check that stored procedure is valid

As we know, it is possible to compile and to have so stored procedures: CREATE PROC dbo.MyProc1 AS SELECT * FROM Not_Exists_Or_Removed_Table or CREATE PROC dbo.MyProc2 AS SELECT Id ...
7
votes
1answer
7k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
16
votes
4answers
8k views

Task scheduler for SQL Server Express

I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database. Unfortunately the ...
4
votes
3answers
3k views

SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup ...