ExpressMaint

New Post: Sql Server 2008

julior77 — Sun, 21 Sep 2008 20:26:29 GMT

Hi

I upgraded to 2008 recently and now I'm having some problems running the app against it.
I followed the instructions and installed SMO 2005 Install Package (x86).

When I run the app I get the following error:

C:\Backup>expressmaint -S juliosbox\sqlexpress10 -D school2 -T DB -R c:\backup -RU WEEKS -RV 1 -B c:\backup -BU DAYS -BV 1 -V -C
Expressmaint error : Failed to connect to server juliosbox\sqlexpress10.
Expressmaint error : This SQL Server version (10.0) is not supported.

I verified the name of the instance and is OK.
Any idea on making it run against 2008?

Thanks

Julio

New Post: Maintenance type for Shrink of database

tlyczko — Wed, 17 Sep 2008 13:54:45 GMT

I put this in the issue tracker. I'm not sure which gets more attention quicker when the maintainer has time, issues or discussion, I would hope issues.
My variation upon this request would be for separate shrinks of database and logs, sometimes one only wants to shrink the logs and not the databases.
Thank you, Tom

New Post: Maintenance type for Shrink of database

graemeandrew — Wed, 17 Sep 2008 02:40:17 GMT

Hi

Was just wondering if there was any maintenance option for shrinking the database.

Thanks in advance.

Regards
Graeme Andrew

Created Issue: Suggested best practices??

tlyczko — Tue, 16 Sep 2008 13:31:11 GMT

Would you please offer your suggestions for best practices with this app??
I was thinking the best way to do this would be to run the transaction log backups, say 2-4 times daily, then the database backup at the end of the day.
With this method, restoring the database would require the previous day's backup plus the current day's most current log backup.
Any other suggestion??
Thank you, Tom

Created Issue: Shrink databases and logs??

tlyczko — Mon, 15 Sep 2008 20:13:04 GMT

Please include the ability to shrink databases and logs, or document how we could do this with the current version.

Thank you, Tom

New Post: errors connecting to server

tlyczko — Mon, 15 Sep 2008 20:11:44 GMT

I discovered today by reading some of the issue trackers that my original syntax was incorrect.
I had parentheses around SERVERNAME\SQLEXPRESS.
When I removed these, the batch files ran properly.
Thank you, Tom

New Post: errors connecting to server

tlyczko — Fri, 12 Sep 2008 18:16:44 GMT

My batch file statement is:

expressmaint -S (local) -D ALL_USER -T DB -R d:\reports -RU WEEKS -RV 1 -B d:\Database_Backups -BU DAYS -BV 1 -V -C

Expressmaint error: Failed to connect to server (SERVERNAME\SQLEXPRESS).
OR
Expressmaint error: Failed to connect to server (local).

Expressmaint error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Remote connections ARE set to be allowed in Surface Area Configuration, using both TCP/IP and named pipes.

Batch file is being run locally and manually by local administrator account, which has SysAdmin rights on the instance.

Hope you can help...

Thank you, Tom

New Post: errors connecting to server

tlyczko — Fri, 12 Sep 2008 00:25:16 GMT

I *was* starting the batch file manually, and it was a fairly standard error message about named pipes, TCP, etc., yet both of these items are enabled.$0I don't have access to the PC now.$0I will try again and post the exact error message with an example batch file statement.$0$0It has something to do with the ID/account specified in the batch file.$0$0The PC has only two local accounts: 1) local administrator; 2) local user.$0$0I'm first trying to get it working with the local administrator before scheduling it etc.$0$0Thank you, Tom$0$0

New Post: errors connecting to server

jaspersmith — Thu, 11 Sep 2008 20:51:30 GMT

What error message are you getting? If the exact same batch file works manually it would seem to suggest a permissions issue for the account used to run the scheduled task

New Post: BV variable questions

jaspersmith — Thu, 11 Sep 2008 20:42:49 GMT

Correct (as long as the BU parameter is DAYS). The BV parameter determines how may BU units (MINUTES,HOURS,DAYS,WEEKS) to keep backups for

New Post: errors connecting to server

tlyczko — Thu, 28 Aug 2008 20:03:28 GMT

I have SQL Express set to allow local and remote connections with both named pipes and TCP/IP in Surface Area Configuration.

Nevertheless ExpressMaint keeps being unable to connect to the server, and the batch file is presently being run manually with the local administrator account.

I've tried using computername\sqlexpress, computername, and sqlexpress for the server name, no success.

Any suggestions??

Thank you, Tom

New Post: BV variable questions

tlyczko — Thu, 28 Aug 2008 19:33:15 GMT

To keep more than 1 day's backup of anything, BV should > 1??

For now I am using the statements as they stand from the sqldbatips article.

Thank you, Tom

Updated Release: ExpressMaint v1.5.0.0 (Jul 04, 2008)

Tue, 19 Aug 2008 20:29:03 GMT

Added new Statistics Update operations
Added new switch to allow multiple database operations to continue if some databases fail
Added fix for SMO Index Reorganize bug

For more details and usage instructions see

Automating Database maintenance in SQL 2005 Express Edition Part II

Note To run against SQL 2008 instances you must install SQL 2005 SMO available here

New Post: Expressmaint.sql - dump to UNC?

jaspersmith — Mon, 11 Aug 2008 20:59:44 GMT

What you need to do is make sure that the SQL Service account (the account which will actually create the backup file) has read/write access to the remote share and use a UNC path (a mapped drive won't work because you're mapping the drive under your account but the SQL Service account won't know about it). You shouldn't have to change the stored procedure.

New Post: Expressmaint.sql - dump to UNC?

underlig — Wed, 30 Jul 2008 09:38:25 GMT

Hi there

I'm still using the old 2004 version of expressmaint, saw that it has been updated a few days ago.

Anyway, I'm wondering if there is any way to make the dump to another server, when i try to set @backupfldr to '\\server2\folder' i get an error, when i map \\server2\folder to m: and try to set @backupfldr to 'm:\' i get an error

Msg 50000, Level 16, State 1, Server DB-SQL1, Procedure expressmaint, Line 165
The folder n:\ does not exist on this server

SQL-server isn't logging this error, i get it in the output logfile from expressmaint.

I just thought i should ask here before i started writing a script to pull the dumps to server2, instead of using expressmaint to not only put the dumps on server2 but also manage the versions.

Could i perhaps just comment out the "does this file/folder exist"-check and hope for the best?

Windows Server 2008 x64
Microsoft SQL 2005

Updated Release: ExpressMaintTSQL v1.0 (Aug 18, 2007)

Mon, 28 Jul 2008 20:34:29 GMT

Initial TSQL release to Codeplex v1.0

For more details and usage instructions see

Automating Database maintenance in SQL 2005 Express Edition Part I

New Post: 'fillfactor' is not a recognized ALTER INDEX REORGANIZE option

timr — Wed, 09 Jul 2008 16:58:30 GMT

Upgrading to expressmaint 1.5.0.0 seems to have done the trick so far - thanks!

Commented Issue: Unhandled Exception: System.Security.SecurityException

jaspersmith — Fri, 04 Jul 2008 11:16:19 GMT

When run from a network share it generates an exception:

P:\> expressmaint -?
Unhandled Exception: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
.
.
where "P:" is a network share.
The work around is not to run it from a network share but this is not ideal for us.
Comments: ** Comment from web user: jaspersmith **

This is a .NET security issue not anything to do with the tool. By default, applications run from shares or UNC paths have limited permissions. You need to configure .NET to trust the assembly location. You can do this using the .NET 2.0 Configuration tool (Start>Programs>administrative Tools>.NET Framework 2.0 configuration. Expand My Computer and browse to Machine>Code Groups>All_Code. Right click and select New. Give it a name e.g. ExpressMaint and click Next. Select URL as the Condition Type and enter P:\expressmaint.exe as the URL and click Next. Accept the default on the next page (Full Trust) and click Next and then Finish. I have attached a picture to help clarify this.

Commented Issue: fillfactor' is not a recognized ALTER INDEX REORGANIZE option

jaspersmith — Fri, 04 Jul 2008 10:58:22 GMT

While trying to run a reorg command against a user database, I get the following error. This is happening on more then one server, on different databases.

Reorganizing indexes for table dbo.dtproperties
Reorganizing indexes for table dbo.Groups
Reorganize failed for Index 'PK_Groups'.
An exception occurred while executing a Transact-SQL statement or batch.
'fillfactor' is not a recognized ALTER INDEX REORGANIZE option.

I seem to be able to manually run a rebuild or reorganize on the index from within SQL Management Studio Express.

The database was originally created on a SQL 2000 Server, and was attached to SQL 2005 Express.

Any suggestions?
Comments: ** Comment from web user: jaspersmith **

I have added a fix for this SMO bug in version 1.5.0.0

Commented Feature: What about "Update statistics" / "sp_updatestats"

jaspersmith — Fri, 04 Jul 2008 10:57:10 GMT

Hi

Could you consider adding "updatestats" as an extra maintenance operation?
I can see that I can use "REINDEX" to rebuild indexes, and that will take care of the statistics as well, but since using WITH (ONLINE = ON) is not available in "SQL Express", it means that each table will become inaccessible during the rebuild.

But if I use "REORG", there is no option to update statistics after that, and in many cases thats the best way to optimize query performance.
On small databases, running "UPDATE STATISTICS tablename WITH FULLSCAN" on all tables, will not take too long (and will not lock the tables during the operation).

Thanks
Comments: ** Comment from web user: jaspersmith **

This functionality has been added to version 1.5.0.0 with the new STATS and STATSFULL operations