SQL 2005 Maint Plan
Dec 8, 2007I am creating a sql 2005 maintenance plan backup job. How can i specify 'write log files to some particular drive'. I am new to sql server 2005
Please advice
I am creating a sql 2005 maintenance plan backup job. How can i specify 'write log files to some particular drive'. I am new to sql server 2005
Please advice
I need to create a maintplan for user databses,scheduled everyday 8pm
includes
Reoraganize index
fullbackup
Histoty clean up
What should be tasks order? is it okay reorganize index before full backup?
Please advice
I need to create a maintplan for user databses,scheduled everyday 8pm
includes
Reoraganize index
fullbackup
Histoty clean up
What should be tasks order? is it okay reorganize index before full backup?
Please advice
In maint. plan when reorg is selected for a user db, it will also work on
sys tables in user database. Is it a good thing to do ? How do I avoid sys tables in a user database not to be part of maint optimization plan ? Some script would be helpful !
Thanks,
Ivan
I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!
View 1 Replies View RelatedI have setup maintenance plan to run nightly.
(1) Maint plan is setup to backup the databases that users actually use to update company information (2) diffrent databases.
(2) maint plan backs up the master database this is setup to run by itself.
(3) maint plan is setup to backup the model - msdb databases together.
I setup the email notification option to inform me if my backups were successfull. below is an example that I'm recieving everymorning on every database. Something about the single user mode is causing the problem. can somone explain this to? Maybe give me a direction to follow to correct this problem. Below is an example error message that I'm recieving.
I can backup any database by right clicking selecting all tasks then backup database and I get a complete backup to problem. Why is my Maint. Plan not going through. the rebuilding of indexs is successful.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'My Server name' as 'My_Domain_NameMy_SQL_Admin_Account (trusted)
Starting maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:00 PM
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Leo'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[1] Database Leo: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database SlcBom: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:01 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Emailing report to operator 'Admin, SQL'
Thnaks for any help
SQL Rookie
I have a maint plan for a single database to backup the database, thats it, very simple. It fails every time.
When I do a view history of the maint plan, it is empty as if it has never run. When I do a view history of the job, it shows it has failed after 13 seconds. With this message.
"Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
Any help would be great as I cannot get any backups to run. I am worried to say the least right now.
It seems there are at least 3 ways to back up my databases in SQL2000
1) Veritas BackupExec
2) SQL Sever Backup
3) SQL Maintenance Plan
I don't quite see what the difference is between 2 & 3.
I'm currently using Veritas to do a full backup to tape, and a separate full backup to disk via a Maint Plan (a complete backup & trans log backup). It looks as though I've finally got my trans log to stop getting bigger (it started a new one) .... can I delete the huge one I don't need anymore. Does "truncate" mean the log is deleted, or just inactive as a new log gets created. I been reading up on this, but apparently it hasn't sunk in yet.
Please give me any long winded, but clear help you might have
I have just done an in-place upgrade of a sql2000 SP4 instance to SQL 2005. After the upgrade I applied SP2 and cumulative hotfix rollup 4, plus the SQL 2000 DTS designer components for backward compatibility.
all went well with the upgrade.
I then migrated the maintenance plan for the system databases to 2005 SSIS version, and find it includes tempdb! This causes the jobs to fail.
Have I come across anther maintenance plan issue or have I done something wrong? Should I have applied GDR2 before the cumulative roll up?
I'm missing something basic...
Trying to create a new job from sql server 2005 Management Studio. The nifty wizard under Management provides options to create backups, and clean history, but I cant seem to find out how to create a job to purge old backups.
Looking at the commnand line Sql Server Agent Job shows:
/SQL "Maintenance PlansFull Backups" /SERVER "JFSLIB-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
But as you can see, doesn't help a novice like myself, as it appears to be command options to run the "integration" package.
Any help is appreciated. Thanks!
Dear All,
With SQL Server 7 SP2 Maint. Plan, Integrity,
Attempt to repair minor problems - there was a feature
that sometimes left databases in single user mode.
I have recently updated to SQL Server 2000 with a
production database (option set OFF) - does this
feature still apply to SQL Server 2000?
Best Wishes,
Andrew
I created a single step plan that does integrity checks. It fails with the error below. I created a new connection using our clusters virtual sql name.
Executed as user: ACCTCOMsqlagent. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:10:06 PM Error: 2008-02-15 13:10:49.02 Code: 0xC00291EC Source: {0CF32F3D-A8D1-492A-9C0F-AD4E0FC67D14} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-15 13:10:49.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-02-15 13:11:31.26 Code: 0xC0024104 Source: Check Database Integrity Ta... The package execution fa... The step failed.
The agent and db are running under domain accounts and the job is owned by sa.
Thanks for your help.
We have an application that uses MS SQL 2005 to stored information related to user internet usage. It is storing a large amout of data on a daily bases. The application is set to create a new logging database, when the current database reaches 10GB, then it will start logging to the new database.
A new database is getting created at the rate of about 1 new db every 48hrs and the backup maintenance plan for the user databases, is not picking up the new database. I have to remember to refresh the maintenance job so, the new database gets picked up and is included in the daily backup.
Is there a way to have the backup maintenance job refesh itself? Or possibly a stored procedure or some sort of scripting option to do this?
Please provide information if you have a way of doing this. Thanks.
I am running 2005 SP2 (It is the SP2 refresh as I downloaded the SP2 on 3/15/07) and I can't get the .txt maint plans to delete. The job completes successfully; however, the old .txt files are still there. I found this technote: http://support.microsoft.com/kb/938085
although I don't receive the error message in the KB. The report does have "New Component Output" as the 1st line in the report. I still don't see how deleting the 1st line of an already run job (the report) will affect a new run of the job.
Also, I checked the version of the Microsoft.SqlServer.MaintenancePlanTasks.dll and it is at 9.00.3043.00 (which is the SP2 Refresh) in KB 933508 ( http://support.microsoft.com/kb/933508 ).
I have several SQL/2005 servers and they all have this problem. Any help would be appreicated...
Thanks,
DeWayne
SQL 2005 SP2 install succeeds but in SQL Maintenance Studio the instance is still 9.0.1399. Also when I interogate using TSQL it is reported as 9.0.1399
Here's the log (I did try it a couple of times)
Time: 01/25/2008 13:25:05.338
KB Number: KB921896
Machine: ------------- (I took this out)
OS Version: Microsoft Windows Server 2003 family, Standard Edition Service Pack 2 (Build 3790)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.2.3042 x86
Reporting Services (MSSQLSERVER) ENU SP2 9.00.3042.00 x86 STANDARD
SQL Server Native Client ENU 9.00.3042.00 x86
Client Components ENU SP2 9.2.3042 x86 STANDARD
MSXML 6.0 Parser ENU 6.10.1200.0 x86
SQLXML4 ENU 9.00.3042.00 x86
Backward Compatibility ENU 8.05.2004 x86
Microsoft SQL Server VSS Writer ENU 9.00.3042.00 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Setup Support Files
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SqlSupport.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Reporting Services (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRS9_Hotfix_KB921896_sqlrun_rs.msp.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : SQL Server Native Client
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_sqlncli.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Client Components
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : MSXML 6.0 Parser
Product Version (Previous): 1200
Product Version (Final) : 6.10.1129.0
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_msxml6.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : SQLXML4
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_sqlxml4.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Backward Compatibility
Product Version (Previous): 2004
Product Version (Final) : 2004
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SQLServer2005_BC.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
Product : Microsoft SQL Server VSS Writer
Product Version (Previous): 3042
Product Version (Final) : 3042
Status : Success
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixRedist9_Hotfix_KB921896_SqlWriter.msi.log
Error Number : 0
Error Description :
----------------------------------------------------------------------------------
**********************************************************************************
Summary
Success
Exit Code Returned: 0
Hi all,
I need to find out on runtime, a list of Maintenance plans and Jobs.
i dont know how to acquire the database name on which the plan/job are operating.
i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.
its quite urgent,
10x,
Gil.
I'm trying to create a maintenance plan to backup a database, and when I go to select the database in the dropdown, I don't have any of the user databases any more. They were there several weeks ago when I first created the 'plan'.
Does anyone know how to get the user db's to show up in the dropdown?
Hi there
I feel like a complete nitwit. I've been using SQL 2000 for about 5 years now and regularly set up automatic backups using the wizard for many people. I'm in no way an advanced user and have enjoyed some of the more user-friendly features of SQL 2000 such as the backup wizard.
Well now we have SQL 2005 and I can't find the "wizard". I did a lot of googling and found very little on this. The frustration is mounting - let alone the fact that I had to edit the registry just to be able to perform a manual backup in the first place... :-(
Apparently their is a "maintenance plans" folder but the only place I can find this is under "Legacy" and you can't create jobs there.
Has anybody else had this issue? Is there a bug in SQL 2005? I reinstalled the full version with all options selected and still can't find this folder. Am I just being completely "blonde"?
Hello, everyone:
I have a strange trouble on SQL Server 2005 on XP/local and Win 2003/server. When I connect local database and create a backup plan named by "backup plan", I can see the name under "maintenance plan" and "Jobs" under SQL Server Agent. But if I connect to server database and do the samething, I cannot see the name under "maintenance plan". I can see the name under "Jobs" under SQL Server Agent. But cannot delete this job, and message:
Drop failed for job "Backup Plan".(Microsoft.SqlServer.Smo).
Did someone meet this bug and have suggestions?
Thanks
ZYT
Hello All,
Few days before, I had setup Maintanance plan for taking backup in 2005. It was running nice.But suddenly it stops automatically. It is not creating log file too.
Does anyone faced this type of problem ? Or does anybody knows the workaround for this.
Thanks in advance
--kneel
I need to create sql 2005 maintenance plan for system and user databases which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...
View 3 Replies View RelatedI successfully created a simple Backup Maintenance Plan under SSMS. But now that the plan is created I need to change the number of days the backups are kept. When I pull up the plan udner SSIS and lok at the tasks I can't locate that option. I couldn't find it under the sql agent either. Anyone know where it is? (Or do I need to recreate the plan from scratch?)
TIA,
barkingdog
In SQL 2005...
How do I specifty in the maintenance plan to only keep 3 days worth of backup.
Andre
Hello,
The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2.
When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%.
Profiler stats for Original Query:
Duration = 130484 ms
CPU = 111141 ms
Reads = 85470
Profiler stats for Index Hint Query:
Duration = 64 ms
CPU = 687 ms
Reads = 5558
Statistics are updated every night (w/Fullscan)
Indexes are rebuilt/defragmented daily based on fragmentation levels.
What am I missing here/how do I fix this performance issue?
Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive)? What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing?
Thanks much,
Smitha
------------------------------------------------------------------------
QUERY:
select min(AccessLevel)
from Groups-- WITH(INDEX = ByClassID)
where name = 'Student Leader'
and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090,
202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291,
26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28,
2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709,
1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306,
1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536,
151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562,
1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637,
1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207,
36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057,
37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905,
375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369,
37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374,
37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922,
37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970,
37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093,
102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940,
1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191,
16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236,
1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,
1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112,
113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119,
112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662,
4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206,
29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232,
25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765,
8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813,
814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437,
641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549,
6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647,
6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442,
11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139,
94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392,
7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653,
952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069)
INDEX Definition:
CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel)
I need to build a query that shows which databases on a sql 2005 server are NOT included in a maintenance plan.
So far I have been unable to find a matching column from msdb sys.databases with any column in the following msdb tables:
sysmaintplan_plans
sysmaintplan_subplans
sysmaintplan_log
sysmaintplan_logdetail
Has anyone else been able to solve this challenge?
Thanks!
.40
I want to create sql 2005 maintenance plan which includes Database integrity check and Rebuild index tasks but these two things should happen at different times. Is it possible to schedule these two tasks at different times under single database maintenance plan, if so please let me know...ASAP
View 4 Replies View RelatedIn SQL 7, a single table cannot be restored. What if a week later one found out that a table was corrupted few days and no one realized, then how to address this ? Restoring entire db from a tape few days old would't be a solution since all other tables have changed meantime.
Your feed-backs are greatly appreciated !!!
-ISB
I am using the Database maintenance on a database that is about 4gb. The database optiiztion is running about an hour. Does this job only do an update stats? If I run the stored procedure sp_updatestats on the database it only takes a couple of minutes. Are thes two processes doin the same thing? Do I need them if the create, update statistics are turned on?
Thanks,
Glen
Just two days ago, I noticed none of the scheduled maintenance jobs (backups) are running. I can run them manually successfully, but they are not running according to the maintenance schedule. There are no errors thrown.
Just prior to this happening, our anti-virus detected and deleted what it described as a virus [trojan]. The file was named sqlmaint.rll. I have done extensive searching and have not found anything definitive for this file. Obviously, this has something to do with maintenance not running, but how can I resolve this?
Thanks in advance for any help.
Deacon
Windows Server 2003 Enterprise
SQL Server 2000
When I use Enterprise Manager to Maint views it basically brought up the view in simple text editor, where I could put in comments as to what the view was for.
I did this by just double clicking the view, but now in SQL Studio express when I modify the view it just brings it up in a query editor. I don't see my comments for my old SQL2000 views.
Is this just the way it is, and I need to get used to it. Or I am just missing something.
I created a database maintenance plan on sql server 2005 (standard SP2, cluster environment). The plan created successfully on scheduled successfully. But when execute the plan, it fails. Here is the information in the log:
The last step to run was step 1 (TranLog backup).,00:00:01,0,0,,,,0
09/19/2007 12:06:38,Tranlog backups.TranLog backup,Error,1,LAIWWORKSITE1LA,Tranlog backups.TranLog backup,TranLog backup,,Executed as user: STROOCKSQLSRV. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:06:38 PM Warning: 2007-09-19 12:06:39.27 Code: 0x80012017 Source: Tranlog backups Description: The package path referenced an object that cannot be found: "PackageTranLog backup.Disable". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning DTExec: Could not set PackageTranLog backup.Disable value to false. Started: 12:06:38 PM Finished: 12:06:39 PM Elapsed: 0.688 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
Any ideas? Thanks.
Hi,We have a SQL-cluster with over 6300 databases, most of them prettysmall in size and usage.For this reason all these databases are on auto-close ( Otherwise SQLwould need many many gigabytes ).We've recently upgraded to SQL 2005 because of the Management Studio,because the EM is unusable on a SQL-Server with lots of databases.But whenever you choose 'all user databases' or 'all databases' in anyof the DB Maintenance task, MSSQL begins starting up every databasewhich takes about 30 minutes. Until then, you cannot use your Studioanymore.My question : is there a possibility to avoid this behavior, it shouldnot go and check all those db's, just add my task.PS : Detaching these databases is not an option ...Thanks in advance,Sven Peeters
View 5 Replies View Related
I am experiencing extremely slow behavior (no activity for > 30 mins) during a SS 2005 Dev Edition install on XP SP2 on various steps during the SQL Server Database Services step - e.g. during "Setting File Security". This is very annoying.
I've found many postings on this issue indicating that OS calls in this part of the install (e.g. LookupAccountName()) are network speed dependent.
Does Microsoft have any plans to rework this part of the install to rid the need of these calls? I really wish the install would be possible without these calls - it's driving me crazy monitoring this for hours. We've got to roll SS2005 out to a couple hundred developers eventually and this isn't going to cut it.
Thanks for your time on this matter.
Best Regards,
Mike