I am running an overnight job for table Maintenance - this job execs a SP. It has been working okay for a while and is suddenly failing (last two weeks) with Error 515 cannot insert nulls into column. I realise that it is possible that a change has been made but cannot for the life of me ascertain what it is. I have checked server config, database properties and user options and it all appears to be the same as a server that is running this job okay. Please help!!
Hi there,Used the wizard to create a maintenance plan for a db. But it fails most ofthe time. I checked the log-files.At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeedsalways.At 1.05 AM, for 1 second, it runs "integrity check", but most of the time itfails, and says that it couldn't switch to single-user because other usersare using the database.And when "integrity check" fails the "backup" job won't run at 1.10 AM. Whenit DOES run it takes 2 seconds.How do I find out who is using the database ? How do I put a sp_who (orsimilar) in the "integrity check" job ?I got this tip:----------Assuming you have SQL2000, then you can use something like this tokick out all the users:ALTER DATABASE foo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATEIf you want to know who is using the database, then this is one way:select suser_sname(sid)from master..sysprocesseswhere dbid = db_id('foo')---------But how do I actually do it ? How do I edit a maintenance plan ?thx/jim
I have several Maintenance jobs that keep failing. They are , Integrity Checks, Index rebuild, and Update statistics. I keep getting a similar error on all three. I have pasted it below. None of my other SQL 2000 servers have any problems. I have tried changing the database options in question. The error I keep getting is as follows:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'DAPHNE' as 'CDCNorm' (trusted) Starting maintenance plan 'FSPRD84A Integrity Checks' on 4/21/2003 10:20:59 AM [1] Database FSPRD84A: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. ** Execution Time: 0 hrs, 0 mins, 8 secs **
End of maintenance plan 'FSPRD84A Integrity Checks' on 4/21/2003 10:21:07 AM SQLMAINT.EXE Process Exit Code: 1 (Failed)
I created a maintenance plan to back up a database. It fails and I get the follow error in the log file.
Microsoft (R) SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4064: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
What user is it referring to? What user does a Maintenance plan use to login as? SA on the server has full access to all databases. SA’s default database is master and I can read and write to it with the enterprise manager and query analyzer.
I'm running SQL 2005 SP2 and having a problem with a Database Maintenance task created in SSMS doing transaction log backups of all user databases.
When I look at dbo.sysmaintplan_logdetail I see the message:
Database 'Database1' will not be backed up because it does not have its recovery model set to Full or BulkLogged.
Great - that's what we want! Skip the SIMPLE recovery mode databases. However, the next entry is the following error message:
Executing the query "BACKUP LOG [Database1] TO DISK = N'D:\MSSQL$Windows\MSSQL.2\MSSQL\Backup\Database1\Database1_backup_200707231600.trn' WITH NOFORMAT, NOINIT, NAME = N'Database1_backup_20070723160023', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Which says to me it is trying to backup the same database(s) it just said it was skipping!
What's going on? I see that there is a fix to a similar error message with the cumulative update KB936305 (http://support.microsoft.com/kb/936305) for maintenance cleanup tasks.
On SQL 2005 with mixed authentication, I am simply trying to create a maintenance plan to backup one single database. When I get to the end of the wizard, the creation process fails with this message:
Create maintenance plan failed().
I installed SQL 2005 using my domain account and the services are running under my account credentials. I am a domain and local admin.
I created a user db integrity maintenance task. The created job runs once a week. It fails. How do you tell at what point is fails? The history log is not specific about where things go off the rails.
The job errors as follows - Execution of job "User Integrity check subplan_1 failed. See history log for details
If I run the sql in a query window it goes right through - error free.
USE [AcAspNetDB] GO DBCC CHECKDB(N'AcAspNetDB', NOINDEX) GO USE [LCCIntranet_Config] GO DBCC CHECKDB(N'LCCIntranet_Config', NOINDEX) GO USE [library_SSP_Search_DB] GO DBCC CHECKDB(N'library_SSP_Search_DB', NOINDEX) GO USE [Librarysspdb] GO DBCC CHECKDB(N'Librarysspdb', NOINDEX) GO USE [seeunity] GO DBCC CHECKDB(N'seeunity', NOINDEX) GO USE [SharedServicesv2_DB] GO DBCC CHECKDB(N'SharedServicesv2_DB', NOINDEX) GO USE [SharedServicesv2_Search_DB] GO DBCC CHECKDB(N'SharedServicesv2_Search_DB', NOINDEX) GO USE [SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d] GO DBCC CHECKDB(N'SharePoint_AdminContent_bc87e79f-4873-4ec0-b2bb-734054a2564d', NOINDEX) GO USE [WSS_Content] GO DBCC CHECKDB(N'WSS_Content', NOINDEX) GO USE [WSS_Content_lccintranet80] GO DBCC CHECKDB(N'WSS_Content_lccintranet80', NOINDEX) GO USE [WSS_Content_LCCSSPv2] GO DBCC CHECKDB(N'WSS_Content_LCCSSPv2', NOINDEX) GO USE [WSS_Content_library] GO DBCC CHECKDB(N'WSS_Content_library', NOINDEX) GO USE [WSS_Content_librarymysite] GO DBCC CHECKDB(N'WSS_Content_librarymysite', NOINDEX) GO USE [WSS_Content_libraryssp] GO DBCC CHECKDB(N'WSS_Content_libraryssp', NOINDEX) GO USE [WSS_Content_mysitev2] GO DBCC CHECKDB(N'WSS_Content_mysitev2', NOINDEX) GO USE [WSS_Search_ISSMOSS] GO DBCC CHECKDB(N'WSS_Search_ISSMOSS', NOINDEX)
W2k3SP2 running SQL2k5SP2 in mixed mode. Trying to run a daily maintenance plan with a domain admin service account [domainsqlsvc]. This account is having login failure when I try to run the maintenance job. Would like to figure out why this account cannot authenticate with SQL. I believe I have local login denied, but login as a svc and login as a batch job are both enabled in Domain Policy.
What am I missing?
TIA for your help!
-Nick
EDIT: Here is the error data.
Date 11/14/2007 9:36:16 AM Log SQL Server (Current - 11/14/2007 9:36:00 AM) Source Logon Message Login failed for user 'domainsqlsvc'. [CLIENT: <local machine>] Error:18456, Severity: 14, State: 16
I have a maintenance plan step to perform transaction log backups. There are a few databases that are offline. The backup seems to fail because it tries to backup the offline databases. The backup step is by itself as one of three subplans, although it also fails when it is the only step in a plan with only one subplan.
I have another server that is only on SP1 and it does not have problems handling the offline databases.
Ever since upgrading SQL Server 2005 to SP2 build 3050 all my maintenance plans which begin with a Check Database Integrity Task are failing! If I remove the task from the plan they all execute properly. If I run the T-SQL equivalent manually it executes properly.
I upgraded the server to build 3054 and rebooted several times and Im still having no luck. What am I doing wrong? Thanks for your help with this. -Kenny
Our backups by default go to a network location, but I'd like to modify our maintenance plans to backup to an alternative location if the primary location isn't available. I've setup two Backup Database Tasks where the second one runs only if the first one Fails, and if the second one runs (on first one's failure) it then sends a notification to me so I know this occurred.
The Plan is running as expected, when I simulate a bad path in the first Backup Task the second one runs and the notification is sent, but the Job shows failure. I'd like to show the job as Successful when this occurs since I'm handling the issue and notification within the Plan, but I'm unable to find out how. I've set FailParentOnFailure to False on the Plan and I've changed the MaximumErrorCount to 2 with the assumption that this would work, but neither didn't.
Also I'm running into this in both SQL 2008 and SQL 2012.
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.Â
Created a maintenance plan to backup my sharepoint databases. When I execute it the following error occurs: Execution failed. See the maintenance plan and SQL Server Agent job history logs for details: Additional Information: Job 'SharePointBackUp.Backup_SharePoint faild. (SqlManagerUI) - Execute maintenance plan. SharePointBackUP (Error) Messages * Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
When checking the Maintenance PlansharePointBackUP log it is empty! Under Job History I thinks this: Date 4/18/2008 12:55:35 PM Log Job History (SharePointBackUP.Backup_SharePoint) Step ID 1 Server DESD7 Job Name SharePointBackUP.Backup_SharePoint Step Name Backup_SharePoint Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed. line 1? Is that line one of the T-SQL statement? Because if that's the case then it's not because I copied and pasted the line in to a query and it executed without error. Under SQL Agent there are no entries! So what bloody log am I suppose to be checking?! This is very frustrating. I then copied and pasted every sql statement in to a query and THEY all ran just fine.
What's going wrong here, and how can I correct it?
I get the following message when I execute a mantenance plan to delete files older than 1 day.
Error # -1073548784
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2007-09-30T07:56:09' " failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc). I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?
The advantage with this I think is consolidating the data and probably minimizing the administration The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.
I have been asked to look at some performance isssues with an application that utilises a 800GB table. This table is huge and contains 4 int columns and 1 decimal column. The table has a clustered index that covers 4 of the int columns and is heavily fragmented and it has not been maintained for a long time. The system has limited free space to even attempt rebuilding the index. Does anyone have any experience of running a the Alter Index Reorganize command on such a large table? Any information on what storage would be required to attempt this, how long would this take?
sql server 2000I am currently maintaining a table that contains 30 Million+ records,30 columns, and 11 indexes and will double within the next six mouths.The application that accesses this table, mainly for read onlypurposes, runs without any problems. We have begun using Crystalreports and are now having problems. When we create reports thataccesses the large table our server has significant performance dip.The application begins to time out and the reports take a very longtime, even with simple selects on indexed field.I have began looking into partitioning the large table on its key fieldand creating a partition view. But from what I have read this willonly help if we key on the partitioned field. And all other searcheswill actually take a little longer.Archiving old data is not an option. All the data is being usedAny suggestions will be appreciated. Thanks in advance.Rick
SQL Server 2005 SP2 Error: Script failed for Table 'dbo.TableName'
I keep getting this error when trying to generate a snapshot for transact replication. Here is the publishing table schema:
CREATE TABLE [dbo].[viMediaPlaylist]( [MemberID] [bigint] NOT NULL, [xmlPlaylist] [xml](CONTENT [dbo].[viMediaPlaylistCollectionSchema]) NOT NULL, PRIMARY KEY CLUSTERED ( [MemberID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY]
I tried the following: #1)Create table schema at the subscriber manually, and change package to Keep object as is. Got the same error during snapshot.
#2Filtered replication to NOT include XML column. This worked, and no error was generated, replication was up and running.
So my question is, what is the problem with XML column being replicated? Any ideas how i can make this work? At this point i'm not sure what the problem is with the xml column, but it seems like ti tries to run schema script, but i'm not asking it to create the schema i already did it myself. Thanks!
Here is a log dump from the distributor:
2007-09-11 16:40:14.56 SQL Command dump 2007-09-11 16:40:14.56 ================ 2007-09-11 16:40:14.56 Server: SQL02 2007-09-11 16:40:14.56 Database: Video 2007-09-11 16:40:14.56 Command Text: sys.sp_releaseapplock 2007-09-11 16:40:14.56 Parameters: 2007-09-11 16:40:14.56 @Resource = SQL02-Video_viMediaPlaylis-71 2007-09-11 16:40:14.56 @LockOwner = Session 2007-09-11 16:40:14.56 @DbPrincipal = db_owner 2007-09-11 16:40:15.17 [0%] The replication agent had encountered an exception. 2007-09-11 16:40:15.17 Source: Unknown 2007-09-11 16:40:15.17 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException 2007-09-11 16:40:15.17 Exception Message: Script failed for Table 'dbo.viMediaPlaylist'. 2007-09-11 16:40:15.17 Message Code: Not Applicable 2007-09-11 16:40:15.17 2007-09-11 16:40:15.17 Call Stack: 2007-09-11 16:40:15.17 Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.viMediaPlaylist'. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version. 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) 2007-09-11 16:40:15.17 --- End of inner exception stack trace --- 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle) 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting() 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting() 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot() 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun() 2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.AgentCore.Run()
I have a question that I hope someone can clear up for me. I have come across a number of different suggestions on DB maintenance, for example reindexing with the following script:
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',90) FETCH NEXT FROM TableCursor INTO @TableName END
CLOSE TableCursor
DEALLOCATE TableCursor
My question is, doesn't the maintenance plan have this functionality inherent in it when you create the maintenance jobs to reindex? Is there a benefit to scripting things out vs just using the maintenance plan wizard for this sort of thing and any of the items it covers? I came from an Oracle background where this was a no-brainer but I am a bit confused on the choices with SQL Server.
I am looking for a table where Maintenance Clean Up Task configuration is stored. For example, Delete file older than the following - which is 2 days. Which table can I retrieve the setting in msdb ?
I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.
-------- Error Number : 3628
Message :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.
[Microsoft][ODBC SQL Server ------------ This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..
Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.
I always got this error when i change the identity key of the table A to yes or no
when i performing an insert into table B and b,accountid is in table A .
seems that when i save, the foreign keys reflect a change of schema...and when i do an insert operation, it fails.
how can i avoid such thinsg to happen? i m using a cursor to load the data and comparing them to see if they are already loaded in the table before inserting them,
I have SQL Server 2000 DB that was converted via DTS from Acccess 97. Replication fails for any table with a single hypen in the table name. If I copy one of these tables to a name using an underscore rather than a hyphen, then it replicates successfully.
Below are errror messags from agent with -Outputverboselevel set to 2. I have also seen 'Agent error code 20082', in which case the replication command is incomplete.
Having some issues with our apps. We are trying to get our applications to work with sql2005.
Ive got the databases "setup", and all our apps run fine... ...except for when queries are made without the owner of the table being specified in the query.
The connection is opened with the username that is associated with that owner. And it fails in Manager as well. Is there something im missing, because you should be able to do this.
eg: select * from <table_name>
Gives the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '<table_name>'.
However if i were to query like this: select * from <owner>.<table_name>
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
I am running on mssql 6.5, sp4. We have been trying to use EM transfer manager to move one test database on one server to another database on another server. We are dealing with 135 tables on this database. The transfer works up until about the 80th atble and then just dies but the scheduled task says it failed and check error log. The transfer creates the tables on the destination database but only loads the data until this one table.
WE use all of the options in EM Transfer manager which are st as defaults.
THere is no one on the source or destination databases locking this table.
Other smaller databases were successfully transferred from one database on one server to the other database on the other server without any problems today and yesterday.
This is the error I get with a simple Data Reader Source (SELECT * FROM A) to SQL Server Destination copy between identical tables from a linked to a local server:
We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.
I set my Transfer Sql Transfer Objects Task to use the following settings:
CopyData = true
ObjectsTocopy = All Tables
As soon as it hits a table that has a foreign key, I get the following error:
"Truncate data failed for Table nnn"
I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.
First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?
Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?
I am testing some maintenance tasks sql commands such as index rebuild, index reorg, update statistics and db integrity check on a SQL Server 2014 Database. This is a new non-production vendor database (DB Size 500 GBs, Log Size 25 GBs) which eventually will be created in production. Currently, it is in full recovery model and without log backups. The database has a whole lot of indexes. I am just trying to rebuild and reorganize all the indexes (that need it), in addition to trying to get an idea of how long these maintenance task will take and the space needed in the log file to complete these tasks/commands. I would like to execute these tasks manually (the first time) to gather the duration and space required information. Eventually, I would probably schedule a weekly job to perform this maintenance.
I ran the index rebuild task on the database and noticed that the log file grew by over 50 GBs. I killed the process and truncated and shrunk the log file back down.
1. Does the index rebuild, index reorg, update statistics and db integrity check commands all use the log file?
2. Does Indexs Reorg have less impact on log file then Index Rebuild?
3. Should a truncate log and shrink log file be performed after these maintenance commands?
4. Should a full database backup be performed after these maintenance commands? Or before the maintenance commands?
I have read and understand that shrinking is not good for the database (could lead to more fragmentation and more data file growth when data is added) and I know about rebuilding indexes when fragmentation is GT 30% and reorganizing indexes when fragmentation is GT 5% and LE 30%.
Since this is a non-production database maybe I should set the recovery model to simple, run the maintenance commands and leave the database in simple recovery model unless the vendor needs it in full recovery model for some unknown reason.
5. With the simple recovery model the log file should be reused in a circular manner and not grow during these maintenance tasks. Is this correct?
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.
What might be the issue in such a small database? I would understand in a big database, but not on this one.
