35

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old ...


28

If you don't have the maintenance window for it, updating statistics daily is probably a little overkill. Especially if you have Auto Update Statistics turned on for the database. In your original post, you said that users are seeing a performance degredation due to this maintenance plan. Is there no other time to run this maintenance plan? No other ...


26

When To Update Statistics? if and only if auto update statistics feature is not good enough for your requirements. i mean if auto create and auto update statistics are ON and you are getting a bad query plan because the statistics are not accurate or current then it might be a good idea to have control over statistics creation and update. but if you are ...


26

I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.


26

The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can use the "Maintenance Clean up Task" in your maintenance plan to clean up backup files older than a certain number of days.


22

I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server. First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, ...


22

I'm going to take a guess that you are using automatic storage. (Not that this could happen otherwise...it is just easy to have this happen with automatic storage.) The problem is most likely that your database reclaimed the space for itself but did not release the disk back to the operating system. This can be shown very easily by checking the High Water ...


21

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are: It can improve read-ahead performance for large range scans; and It may improve the page density (number of rows per page) Both of these factors are less important for small indexes, by definition. The counter-argument to ...


20

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by: how big your database is how fast your IO subsytem ...


16

Is the database going to grow again? If so then the effort you're going to put into the shrink operations are just going to be a waste, because when you've got the file size down and then you add more data, the file will just have to grow again, and transactions have to wait for that growth to happen. If you have sub-optimal auto-growth settings and/or a ...


15

One way would be to make a system procedure in master and then create a wrapper in your maintenance database. Note that this will only work for one database at a time. First, in master: USE [master]; GO CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required @tableName NVARCHAR(128) = NULL, @indexID INT = NULL, @partNumber INT ...


15

Well, there's bad news, good news with a catch, and some really good news. The bad news T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions: stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly ...


15

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO. I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation ...


14

A reorganise-and-shrink is never recommended really. If you can take the apps the database is serving offline, you can speed up the process and reduce index fragmentation by removing all indexes and primary/foreign key constraints before the shrink (this will mean there is less data to be moved around as only the data pages will be shuffled not the now non-...


14

Maintenance plans make use of SSIS packages that are stored in MSDB. These packages use connection strings, which are not changed after a server rename. Use the script (reproduced below) posted by NancySon in the comments of How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server to get some inspiration on how to change these connection ...


14

In general, a well designed cluster can live for YEARS without being touched. I've had clusters that ran for years hands-off. However, here are some guidelines: Monitoring is hugely important: 1) Monitor latencies. Use opscenter or your favorite metrics tools to keep track of latencies. Latencies going up can be signs of problems coming, including GC ...


13

if any table has page count more [than] 50 [pages] That is still extremely small. I typically don't take fragmentation into account for any index with less than 1000 pages. and avg_fragmentation_in_percent > 30 then the index is REBUILD That's a good threshold for rebuilds, but I'd also consider reorganizing indexes with fragmentation in the range of 5%...


13

You've addressed a boatload of items in your question. Thanks for being so thorough! Just a couple of things I notice off hand: How various hardware / load factors influence what should be done. Are you running a 24x7 instance? What is the load around the clock? I notice you have backup compression disabled; is that by design for the test, or ...


13

If you detach a database from an instance, you will need to perform an OS-level delete of the file. The safer approach is to drop the database instead. What I suggest is taking a final backup of the database after you put it into Read Only mode (as this will ensure no activity is occurring during the backup), after which remove it from you system by way of ...


13

I've upvoted John's answer; I'd just like to add some details about other items that you might want to clean up. SQL Server Agent jobs and alerts might reference the database. Cleaning them up will prevent unnecessary errors being reported. Remove any Logins that were created specifically for the database. The following T-SQL will identify possible ...


12

In addition to the recommendation by @Remus to use a SAMPLED scan, I don't know that this query can't start until your maintenance window starts. Why not pre-populate a table with the results? If you start this query (let's say a sampled scan takes 10 minutes) about 15-20 minutes before your maintenance window, and stuff the results in a table, the data ...


12

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only. Yet, two things seem important to me: There seems to be a consensus, that autovacuum should never be switched off, unless you ...


12

If sys.dm_exec_procedure_stats is not reliable for you (probably more because the information doesn't survive restarts than anything to do with the plan cache), SQL Server doesn't keep track of this in any other way. The only way to do this would be to add logging to your stored procedures (or to the app that calls them, if that is feasible and inclusive ...


12

The job owner determines the execution context of the job's steps. For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job ...


11

Using @filiprem's answer in a my case and simplifying it: -- Connecting to the current user localhost's postgres instance psql -- Making sure the database exists SELECT * from pg_database where datname = 'my_database_name' -- Disallow new connections UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'my_database_name'; ALTER DATABASE ...


11

If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works. In fact, rebuilding can even lead to larger data files on disk, as new pages and extents are allocated to hold the copy of the data. When the old pages are dropped from the index, they're not actually removed from anything, they're just deallocated. ...


10

Late answer but could be of use to other readers. Please, have in mind that there are lots of maintenance or reporting tasks, you can create, that carry unseen risks associated with them. What would happen when a drive gets filled up during differential backups performed daily? And what if an index rebuild job runs unusually long? How about if a data load ...


10

First things first. Make sure you have developed and documented a disaster recovery (DR) strategy. Spend some time thinking up ways that things can go wrong, how to recover from them, and testing them to get an idea of how long it will take, especially when restoring from a backup. Some general ideas: mysql crashed, but server is fine: fix error and start ...


10

The Database Upgrade Advisor can report that trace flag 8017 needs to be turned off during the upgrade process, and re-enabled afterward. Trace flag 8017 controls whether SQL Server creates schedulers for all logical processors, including those that are not available for SQL Server to use (according to the affinity mask). Enabling the flag does not create ...


10

Since all the details on backups are maintained in the msdb database, you should just extract the backup file name from the source server. You could create a linked server from your Dev Server to access the Production Server's msdb database. Or you can use OPENQUERY to query the same data. (OPENQUERY may be faster since the query is actually being run on ...


Only top voted, non community-wiki answers of a minimum length are eligible