Order Of Executing Rebuild / CheckDB After Upgrade To 2012

Aug 28, 2015

what is the order of executing rebuild/checkdb after attaching database in SQL 2012 from 2008R2.

1. change compatibility mode
2. run dbcc checkdb
3. alter index rebuild all on <table> with fillfactor=90

Is above order ok? or there is no particular order to execute above 3 steps after upgrade.

View 6 Replies


ADVERTISEMENT

How DBCC CHECKDB And DBCC CHECKCATALOG After Executing These Two Then Store Result

Mar 5, 2005

For Backup Database i used script
BACKUP DATABSE.

Then after that, i need to check Database inigrity, for that
i used DBCC CHECKDB and DBCC CHECKCATALOG

but, i want to store the result in a text file, after executing these two commends, how to write commend line please help me.

View 1 Replies View Related

Executing SSIS Packages In Order

Oct 27, 2006

Project => New SSIS package => Execute package task and in this many more Execute package task with connectors. When we execute the master package how it will decide which is the first package.

View 8 Replies View Related

SQL 2012 :: DBCC CHECKDB Runs On All Our Databases

Dec 2, 2014

Every night all our DBCC CHECKDB runs on all our databases. The trouble is one of them is very large and the database is inaccessible whilst this runs. DBCC CHECKDB (dbname) WITH physical_only executed by user found 0 errors and repaired 0 errors. Elapsed time: 0 hours 24 minutes 46 seconds

This normally happens fairly shortly after the backup, and normally (but not always) after a series of these entries in the log.SQL Server has encountered 7976 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:Program FilesMicrosoft SQL ServerMSSQL11. MSSQLSERVERMSSQ LDATA empdb.mdf] in database [tempdb] (2).Would this cause SQL to automatically run a CheckDB.

View 9 Replies View Related

SQL 2012 :: Rollback Of DBCC CheckDB Stuck

Oct 6, 2015

DBCC CHECKDB was affecting performance. Screens were hanging, timeouts, etc.

So I made the unfortunate decision of Killing the 2 processes.

I keep checking the percent complete and they are stuck at 33% and 17%.

Will I have to restart the SQL Server Service?

Are they any other options?

View 3 Replies View Related

SQL 2012 :: When To Restore And Rebuild Master Database

May 11, 2014

If one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.

View 1 Replies View Related

SQL 2012 :: Index Reorganizing Is 10 Times Slower Than Rebuild?

Oct 19, 2014

why index reorganizing is 10 times slower then rebuild with "ONLINE=ON" clause?

View 9 Replies View Related

SQL 2012 :: OLA Script - Online Index Rebuild On Web Edition?

Nov 4, 2015

We use below OLA script to do our index maintenance and one of our previous engineer designed below script on web edition and I have a question of how online index rebuild works when we have web edition. Does the online Index rebuild really works? I am thinking it only reorganizes and does not do online index rebuild.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "
EXECUTE [dbo].[IndexOptimize] @Databases = 'DBName1',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = 'Y'" -b

View 4 Replies View Related

SQL 2012 :: DBCC CheckDB Output File With Date Stamp

Jul 8, 2015

I would like to know if it is possible to date stamp the output file for the DBCC integrity check?

The following cmd works fine however I tried different ways to date stamp the output file with no luck.

sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:LOGSDBCCResults.txt"

View 5 Replies View Related

SQL 2012 :: Index Rebuild Job Fails Periodically At Update Stats?

Mar 18, 2015

I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.

USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."

I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.

I looked at the logs but they didn't show any thing.

View 9 Replies View Related

Upgrade SQL 6.5 To 7.0 With Different Sort Order

Jan 26, 2001

Hi,

I am trying to upgrade peoplesoft database which is in SQL server 6.5 with Dictionary sort order to SQL Server 7.0 Binary sort order. SQL Server Upgrade wizard fails because of different sort order. If anyone knows about upgrading SQL Server 6.5 with Dictionary sort order to SQL 7.0 with Binary sort order, would be appreciated.

Thanks,
Mohana

View 1 Replies View Related

SQL 2012 :: Sleeping Queries Blocking Rebuild Index And Update Statistics Job In AlwaysOn

Feb 3, 2015

At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.

I have to stop update statistics job manually once i come to office manually.

Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.

View 4 Replies View Related

SQL 2012 :: Maintenance Plan - Rebuild Indexes Only Run If Required Or Open Fires On Schedule?

Aug 23, 2015

Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.

View 2 Replies View Related

Upgrade From SQL 7 To 2000(also Change Sort Order)

Jul 9, 2002

Hi,
We ae planning to upgrade SQL 7 to SQL 2000 curretly we have BInar sort order.We have to cahnge the sort order to Dictionary sort order in SQL 2000 as per the recomendation from application vendor.
What is the best way Ican do this? Can I cahnge the sort order at the time of upgrade without effecting the data?
Thanks in advance
Mohan

View 1 Replies View Related

About Character Set And Sort Order Involved In Upgrade SQL 6.5 To 7.0

Jun 13, 1999

i have 2 questions,

1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:

99/06/11 12:59:24.29 spid1 SQL Server's default sort order is:
99/06/11 12:59:24.29 spid1 'nocase' (ID = 52)
99/06/11 12:59:24.29 spid1 on top of default character set:
99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)

so my old installation's character set and sort order is as above,am i right?

2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?

thanks if any suggestions!
welcome to send me mail about the question.

View 1 Replies View Related

SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date

Mar 5, 2015

I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.

Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.

View 2 Replies View Related

SQL 2005 Upgrade Insert Into Select * Changes Order Of Columns

Dec 13, 2007



Hi there,

This appears to be a change in behaviour between SQL 2000 & 2005. Can anyone confirm?

We have two tables with same schema but different column orders. In Sql 2000, the statement

Insert Into table1 select * from table2

appears to map the column names between the two tables. There is one column out of order, however Sql 2000 doesn't seem to care and correctly inserts the data.

In Sql 2005 the behaviour is to return the columns in the order of table2, rather than mapping column names. This results in incorrect values being added to the columns. (Column shift)

The Sql 2005 behaviour seems to be correct, and select * is bad practice anyway, however I would like to confirm why this was changed and whether there is a service pack/hotfix in 2000 that would have the same result.

We are running compatibility mode in Sql 2005 v9.0.3042.

Sql 2000 is 8.0.2187

thanks,
Andrew

View 1 Replies View Related

Setup And Upgrade :: Clarification On Non-integer Constants Are Not Allowed In ORDER BY Clause In 90

May 15, 2015

We're upgrading our SQL Server database from 2005 to 2012.I ran the Upgrade Advisory report and got this issue "Non-integer constants are not allowed in the ORDER BY clause in 90" because of the script below

SELECT
gp.BRAND+' <> '+gp.CATEGORY AS 'full name',
gp.PRODCODE,
gp.CATEGORY
FROM dbo.GFK_PRODUCT gp
ORDER BY
'full name'

I tried running the same query in our test SQL Server 2012 and it ran successfully. Now I'm confuse if i still need to change it.I google the issue a bit and came across this link and mentioned this.

1.) Non-integer constants are ... constants that are not integer number.

Example: 'string1' represents
a string constant
0x01 represents
a varbinary constant
{ts
'2015-02-26 06:00:00'} represents a datetime constant

1.23 represents
a numeric constants

2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier
delimiter:
SELECT ... expression AS 'Column1'
FROM ...

In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name."Do I still need to change the existing code even though it's working fine in 2012? If yes, it is because of best practice reason or it will total get deprecated/not working in the future version?

View 4 Replies View Related

SQL 2012 :: Executing SP From Trigger?

Mar 21, 2014

Below is my requirement..

Create trigger on Table_XYZ ( After Insert,After Update)
select @var=Col_Last field is null from inserted
if @var is null
Execute sProc1
create proc sproc1
Business logic will create two csv files. Success and error files
success record will be deleted from Table_XYZ,
Error records updated with error_message.

Problem:- When i am updating Col_Last value to null or insert a record with NULL for Col_Last

Trigger is firing and executing SP.....But i was not able query the Table_XYZ and taking too much time...

sp_who2 shows blocked by...my SPID only

when i am executing SP alone ...every thing works fine....

when i am doing testing on trigger with Insert/Update... i am getting executinf query... no response...

If i comment below ones in Trigger...working fine...

--if @var is null
--Execute sProc1

View 1 Replies View Related

SQL 2012 :: Executing Stored Procedure As Different User

Nov 26, 2014

I have a database user dsrpReader that can execute stored procedures in one database; it's the only thing that this user can do. Works great except for the below stored procedure.

[code=sql]
CREATE PROCEDURE [dbo].[__usp_DatabaseFieldsize_Get]
@pTablename nvarchar(128)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] ....

If I run the above as an administrative user (windows login), I get N rows of information back (N > 0). If I run it as an unprivileged user (see beginning of post), I get 0 rows back and no error messages.

Adding 'with execute as owner' solves the issue, but I'm not sure of the implications. Am I opening up the database to attacks (or even the complete server)?

If so, how to continue.

In an attempt to solve the issue I have given permissions to the user dsrpReader on the information_schema.columns but have no success. It did not work. This was just a try, I actually want to set up a dedicated user with some permissions that I can use in the 'with execute as 'limiteduser'.

View 2 Replies View Related

SQL 2012 :: Executing Parameterized Stored Procedure From Excel

Aug 12, 2013

I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.

Create a new connection to SQL Server, then in the Connection Properties dialog, specify

Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"

but if I want to pass a parameter, I would normally do something like

SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()

but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)

View 9 Replies View Related

SQL Server 2012 :: Case Statement Executing A Stored Procedure

Aug 20, 2014

Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????

Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case

View 1 Replies View Related

SQL Server 2012 :: Permissions For Executing Procedure Which Executes Other Procedures?

May 13, 2015

I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.

I want to run this top-level procedure using an account which can't do anything else.

Is there a simple way to give it all the permissions it needs without empowering it to do anything else?

View 0 Replies View Related

SQL 2012 :: Parameter Error When Executing A Package With Built In Stored Procedures

Jul 23, 2014

I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.

@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint

[code]....

When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.

View 4 Replies View Related

SQL 2012 :: Error Executing DTSX From SSISDB But Runs Fine When Run It Manually

Sep 12, 2014

When i run it from sql server data tools on the test box it runs fine. However when i run it from the ssisdb directly i get the below error.

reload table tablename1 from Excel file:Error: The "filetoload.Outputs[Excel Source Output].Columns[Product]" failed because truncation occurred, and the truncation row disposition on "filetoload.Outputs[Excel Source Output].Columns[Product]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

reload table t from Excel file:Error: There was an error with filetoload.Outputs[Excel Source Output].Columns[Product] on filetoload.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

View 1 Replies View Related

SQL 2012 :: Error Executing Packages In SSIS Project On Server After Editing On Client

Sep 24, 2014

I am building a bunch of packages on our new server and all was going well until I edited the project using the client tools on my PC. I now receive the below error if I try to execute any of the packages on the server (all is still fine on the client). I have scoured the net but I don't seem to be able to come up with a solution. I have tried altering the folder & object permissions for my login (that created the project on the server and edited using the client) but I still get the error.

ERROR:

TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------

ADDITIONAL INFORMATION:

Exception deserializing the package "Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied. (mscorlib)
------------------------------
BUTTONS:

OK
------------------------------

View 4 Replies View Related

SQL Server 2008 :: Logic To Rebuild Only Clustered Indexes / Skipping To Rebuild Non Clustered Indexes In Same Table

Jun 25, 2015

I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.

In order to do that, I have taken the records based on the fragmentation %.

But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.

create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),

[Code] ....

View 5 Replies View Related

SQL 2012 :: AG Failover Order

Mar 10, 2014

If I have multiple servers in my AG group and set two of the secondaries to synchronous with auto failover, how do I control which one one of the two secondaries will become primary if the original primary goes down?We need to maintain HA even during patching so need a first choice auto failover and a second choice auto failover.

View 9 Replies View Related

SQL Server 2012 :: How To Order By Using Parameter Value

Jul 15, 2014

I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.

currently I am ordering it by:

c.CurrentItemNumber
, p.PriorItemNumber

among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).

Is this possible?

This is my stored procedure for reference:

ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)

[Code] ....

View 5 Replies View Related

SQL 2012 :: How To Run Scripts From A Folder In Particular Order

Dec 2, 2014

I want to run multiple SQL script files from a directory in a particular order. I want to read file names from a text file and run / execute SQL files accordingly. Can I achieve my goal using a batch file?

View 2 Replies View Related

SQL 2012 :: View Resolution Order

Jun 11, 2015

I'm just wondering what is the resolution or execution order for a query like this (simplified version):

CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10

The thing I want to know is:

- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition

My real case is much more complex but that is an interesting point in my performance analysis.

View 2 Replies View Related

SQL 2012 :: Upgrade Advisor - The Network Path Was Not Found

Jul 2, 2013

We installed the SQL Server 2012 GUI tools, including "Upgrade Advisor," on a new virtual machine. We can connect to the old SQL Server 2008 R2 clustered databases just fine over port 1433 using SQL Server Management Studio 2012, but Upgrade Advisor is a mixed bag. Here are the steps we are taking:

- Click on "Start," "All Programs," "Microsoft SQL Server 2012," "SQL Server 2012 Upgrade Advisor"

- Fill in the server name with a valid SQL Server Network Name

- Select "SQL Server" as the only component to be analyzed

- Fill in the instance name with a valid named instance

- Use Windows Authentication (the default)

- The "SQL Server Parameters" window displays valid database names, meaning that we have a successful connection to the desired SQL Server instance at this point

- Leave all of the databased selected (the default)

- Click the "Run" button

- The "Upgrade Advisor Progress" window spins for a while and eventually generates the following error:

TITLE: mscorlib
------------------------------
The network path was not found.
------------------------------
BUTTONS:
OK
------------------------------

Why would Upgrade Advisor be able to display the database names after making a successful connection only to fail later saying that it cannot find the network path?

View 4 Replies View Related

SQL 2012 :: Upgrade Compatibility Mode And Rollback Process?

Mar 24, 2014

We upgraded QA and production to sql server 2012 last year ( in place) leaving the user databases at sql 2005 ( 90 ). A few months ago the QA user databases were set to sql 2012 compatibility mode. Management is worried about upgrading production and wants to know if we can quickly roll back.

I want to confirm that we can roll back using the same command, and if dbcc freeproccache can be used to avoid having to update all statistics.

ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 110

ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 90

This works fine in QA on my own test user database. No errors.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved